This post is the equivalent of buying a pizza and getting a slice delivered. I don’t know much about SQL databases, don’t know how to set them up, don’t know how to populate the databases but this week I learned at least one way to access them through Dynamo and Revit.
My company is starting to implement AutoCAD PID, an AutoCAD based drawing software that accesses a SQL database to populate it’s data. For example, we have air flow diagrams (AFDs) that show every room in the project with a lot of information for each one. See a typical one below.
In the past these have been in AutoCAD, the MSPaint of engineering. Without any way to relate airflows to rooms or rooms to system, there was little chance of finding a way to connect this to Revit.
However with AutoCAD PID, we now have databases that store this data and their relationships.
How does one connect to an SQL database through Dynamo? Well for me, a lot of trial and error. I attempted using Slingshot (@archinate), Dynatools (@CesareCaoduro), ODBC connections, OLEDB connections, Windows Authenticated, MSAccess, Microsoft SQL Server, and a shit ton of connection strings. What did I settle on? Excel.
I don’t know how the SQL databases are setup however I was given a server name and could use my windows authentication to access it. I had tested this in Microsoft SQL server and it worked fine but could not figure out how to use Windows Authentication in Dynamo.
I had given up and thought if I can get a connection from SQL to Excel, I could then grab the spreadsheet with Dynamo. Not ideal but it would work and cover my ass. When setting up the excel connection, it gave me a connection string at the end. I saw OLEDB in the string and figured I would try putting it into Slingshot’s Connect.OLEDB node not even knowing what an OLEDB is.
I did and it fucking worked.
We are going to use excel to get our connection string, which is a paragraph or so of information on your database, server, connection type, security details, etc. that allows you to access the data. You could also figure out what to replace in my connection string with your database information however they seem to vary so much, I would just use excel to make sure you get the right string.
Go to Excel>Data>From Other Sources>SQL Server and put in your server name.
Select the database you want to connect to.
Click OK and finish. The database should populate in Excel. Now we don’t care about this, all we want to do is find the connection string. So we go to Connections>Properties>Definition.
Copy this connection string.
Setup this simple graph in Dynamo using the Slingshot package.
Paste your connection string into the string node. Run and you have access.
Now you can use Spring.Dictionary nodes to associate rooms, pipes, pieces of equipment, and whatever you want between your PIDs and Revit. Below is a quick example of such a script to bring airflows from the PID over to space parameters. This is only for reading the SQL database, I’m assuming there is a way to write to it but I haven’t gotten write permissions on my database so I can’t speak to that.
I apologize ahead of time for the can of worms I’m opening up with this post. You’ve probably read this and have more questions than when you started, feel free to ask away.
Inspiration for post: https://www.reddit.com/r/SequelMemes/