Driving a SQL database with Dynamo

Today, I decided to move on rough terrain (or at least, for me this is the case): “handling databases”. I got the question from a customer if it is possible to drive a SQL database using Dynamo and to feed the tables it with Revit data (preferable crossed data). So, I took this is as a challenge and the answer for them is: “Yes, we can ! ”

With the steps below, I would like to help you as well, setting up this workflow. The scope of the script below is dual. First I want to feed the Furniture objects in a Revit model with data coming from the Room they belong to. Secondly I want to extract all the Furniture and Room data to a MySQL database, which is located on another host machine, in this case a Virtual Machine.

Before you get started

I’ve listed some prerequisites you need to install before you can run this script, below:

• You need to set up a MySQL server, preferable on a virtual machine or have at least the MySQL .NET Connectors installed.
• In the video below, a MySQL viewer is used, called Workbench. You can find this viewer here.
• You will also need a Dynamo package called Slingshot! (made by Nathan Miller, The ProvingGround.org) Read more about this package in the package manager. Slingshot! is a node library for connecting Dynamo to external Relational Database Management Systems (RDBMS).

Crossing data between Furniture and Rooms

The first requirement is to collect Room data and provide this data to the Furniture objects that belong to the appropriate Room. Therefore a custom node called “Elements in Room” is used. This node can be found in Conrad Sobon’s archi-lab.net package (Grimshaw). This custom node detects if the location point of a Furniture object is within the Room boundaries. In case of, the custom node is included in the datasets at the bottom of this message.

Some Rooms don’t contain Furniture and this generates empty lists in the output of this custom node. To filter that out you can perform a List.IsEmpty node on each of the sublists. This gives you at the end a filtered list of Furniture, sorted per Room and a equal list of Rooms that contains Furniture. Combine the two lists results in the values of the Room Number send back to Revit, by filling values for the RoomNr project parameter that is added to the Furniture objects in the project.

Connect to the MySQL server

Before feeding data from the Revit model into the MySQL database, we need to make a connection between Dynamo and the server. This can be executed using the following Slingshot! nodes:

• Connection.MySQL_ConnectionString : generates the connection string using the input data from the Code Block on the left
• A Code Block containing the command string for creating a new database. This code block is driven by a variable called “Schema”
• Command.MySQL_Command : this will send the command to the MySQL server, indicated in the ConnectionString. The result is a boolean which indicates if the “schema” or “database” is created.
  

Create tables in the MySQL database

The Revit data needs to be extracted to several tables. One for the Furniture and one for the Rooms. With Dynamo and Slingshot! it is possible to define these tables with the specific columns. In this case, I did this using a code block to send the command string the MySQL server. (This string is in fact a copy past from the MySQL command prompt, when creating tables using the Workbench viewer).

//Syntax for command to create the Room table with indicated columns
“CREATE TABLE `” + DBase + “`.`” + Table + “` (
`ROOM_ID` INT NOT NULL COMMENT ”,
`NUMBER` VARCHAR(45) NULL COMMENT ”,
`NAME` VARCHAR(45) NULL COMMENT ”,
`AREA` DOUBLE NULL COMMENT ”,
PRIMARY KEY (`ROOM_ID`)  COMMENT ”)”;

Feeding the MySQL database with data from Revit

In this part of the script I will extract the data from the Furniture and Room instances and feed them in the right tables of the database. The Revit ID of each object is unique and will be used as Primary Key in the database. On top of that, the Revit ID of the Rooms, the Furniture instances belong to, will be written into the Furniture table as well. This value can be used later to make queries across the tables.

Below is shown how to collect the Furniture data and send them to the SQL.InsertInto node. This last node generates the command strings for the input of data in the database. The Table input of this node is a concatenated string of the database name and table name, which is here “MillenniumRail.Furniture”

In the next image, similar is done for the collection of Room data.

When all data is collected and the strings are generated, we can send them to the Command.MySQL_Command in order to connect to the database and write the values. This is done in one time for Furniture and Rooms using a List.Join (which joins the SQL.InsertInto output from Room and Furniture instances.

Change data in the MySQL database

Using the MySQL Workbench you can change the data within the database. In this case, the values of the Furniture marks are added to the table.

Read data from the MySQL database

The changes that have been executed in the database, can be read in Dynamo and can be transferred back to Revit if necessary. Below you can find the statement that is needed to read data with the Query.MySQL_Query node. This is basically the same as the prompt command in MySQL: “SELECT * FROM millenniumrail.furniture”

Datasets & Video

You can download the Dynamo dataset below . Before you run the script be sure that you have:

• Added a (project) parameter called “RoomNr” to the Furniture objects (or change the parameter name in Dynamo)
• an active connection to your MySQL server
• installed Slingshot! package.

Download dataset

This video shows quickly how it works:

Special thanks to my colleague Ives Veelaert, with helping me out when having problems connecting with the VM SQL Server.

Advertisement