Creating a BOM using VB.Net by importing values through SQL

[ibrady37E54]

Hi,

I am new to AutoCAD (using AutoCAD Mechanical 2022 and Visual Studio 2019) and my company has me trying to automate a way to use a block name to pull data from a database through an ODBC connection, and use that query to populate a BOM. It looks as if VB.Net will be the best way for me to do so. For instance, I would have a block name such as C01-05555, and use that name to cross reference the database through an SQL query, populating a BOM with fields such as  Manufacturer Name, Manufacturer Part Number, Description and Company Part Number (this would be the Block name and my primary key in the database) pulled from our ERP system through the database connection. I have set up an ODBC connection through DBConnect and can query the database, but it looks like in order to automate this I will need to use an API instead. Any guidance on how to best tackle this would be appreciated!

[Gepaha]

I have a plug-in written in .NET similar to what you are thinking which makes use of a block with attribute and has attached database fields through dbconnect and generates tables.

At first I use an attribute called "ID" which represents a number and all blocks that have the same CAO.KeyValues ​​have the same number.

A good starting point is the example that comes with AutoCAD written in VBA called "caotest.dvb".

There is a lot of content on the internet, just search and post the problems that appear here.

https://adndevblog.typepad.com/autocad/2013/04/obtaining-the-top-level-object-in-cao-using-net.html

https://www.autodesk.com/autodesk-university/class/Harnessing-Power-AutoCAD-COM-APIs-2015

https://gredos.usal.es/bitstream/10366/120141/1/TFM_KorosoI_Herramientas.pdf

I don't know what your needs are and if dbconnect will solve the problem then it's always good to wait for an answer from someone with more in-depth knowledge.

[norman.yuan]

The process you want to do can be divided into 2 different programming tasks: a) get data from outside source, and b) update the BOM in drawing with the obtained data. Only the latter is AutoCAD (Mechanical) specific. The 2 task should not be tightly coupled (that is, changes in one task should not have impact in the other task). There are different ways to get data from different sources, since you post in AutoCAD .NET API forum, I assume you would use .NET technologies for the data access.

If you have direct access to the SQL server in your network, you can use ADO.NET to do this. There are tons of tutorials. code samples on line you can learn about how to access SQL Server (or other DB servers, for that matter). For read-only access, the code would be rather simple. At this stage of technology advancement, it is also possible that the DB admin in your organization may not like to give user direct access to the database server and a service layer is built to wrap up the data access, most likely, REST services. If so, you need to learn how to access data via services.

As for the CAO technology in @Gepaha 's reply, while it would work for your purpose, I would not recommend it at all, it is an very old, out-of-date technology, and setting up ODBC source in each user's computer is a tedious thing to do, to say the least.

[Gepaha]

Hello Norman. I appreciated your response regarding the use of CAO technology.

I agree that she is old and not that attractive to be involved with COM and has her problems but, she has served my purposes.

I'm always willing to learn newer technologies that make things easier. So I ask:

How would you connect SQL Server data with each entity in AutoCAD and generate the list? Could you expose some pseudo-code?

[JTBWorld]

If you want to hire someone for the customization feel free to contact us.

[ibrady37E54]

Hi Norman and gphanauer,
Agreed, I was planning to have the two tasks independent of each other. I do plan to utilize VB.Net for all aspects of the task. I have set up the link to the ODBC source on my own computer and through AutoCAD 2022 via DBConnect, but it's actually not horrible since there are only a couple engineers who require this ability. I will try to implement it into my code for the SQL queries, though. You are correct that I will give them read-only access to the database; they have no need to write anything and will only import information into AutoCAD based on the block name matching a field in the database. I've been working along with the database manager on this project, so I don’t believe that there will be any issues with him giving access to the database, especially as it will be read-only.
We actually already use ODBC and set it up on everyone’s computers because of the way we utilize our MRP system, and the Crystal Reporting with it.
Is there documentation on how the BOM object can be accessed and modified? I have looked into the .Net Developer's Guide but haven't seen much information on how to create those attributes in an API. I'm assuming that I might need to create a table and store the values that I pull from SQL in that table before using them to create and populate the BOM.

Thanks, Ian

[ibrady37E54]

Hi Norman and gphanauer,
Agreed, I was planning to have the two tasks independent of each other. I do plan to utilize VB.Net for all aspects of the task. I have set up the link to the ODBC source on my own computer and through AutoCAD 2022 via DBConnect, but it's actually not horrible since there are only a couple engineers who require this ability. I will try to implement it into my code for the SQL queries, though. You are correct that I will give them read-only access to the database; they have no need to write anything and will only import information into AutoCAD based on the block name matching a field in the database. I've been working along with the database manager on this project, so I don’t believe that there will be any issues with him giving access to the database, especially as it will be read-only.
We actually already use ODBC and set it up on everyone’s computers because of the way we utilize our MRP system, and the Crystal Reporting with it.
Is there documentation on how the BOM object can be accessed and modified? I have looked into the .Net Developer's Guide but haven't seen much information on how to create those attributes in an API. I'm assuming that I might need to create a table and store the values that I pull from SQL in that table before using them to create and populate the BOM.

Thanks, Ian