Reading External Database or Excel

[deusextechnic]

Hi, 

I have a concept for a number of applications. The piece I am missing is how to go about reading an external database or excel sheet from the autocad.net application.

Thanks,

[norman.yuan]

Without knowing your programming knowledge, it is really a too broad topic of "how to read data from external database...". If you have programming data access (to data file, database, servcies) experience outside AutoCAD, then there is no difference in terms how to do it inside AutoCAD (with VBA, .NET API). Almost all generic database access programming examples, Excel sheet data reading/writing programming examples you can find, or you have done, could be used in AutoCAD prgramming with minimum, or without, change.

[deusextechnic]

Hi, Thanks for the reply.

In a nutshel, I want to know what approach is good and reliable and any good examples would be helpful. I will need to read both excel and access dbs at some stage. I already tried a NuGet package called LINQtoExcel which didn't work in AutoCAD API even though the program compiles, so I don't want to spend a heap of time with similar trial and error. I have some experience with databases using EF so that was going to be my next move. For the moment, I am stuck with having to read .csv files.  

[Ed__Jobe]

If you use the DataSource tools in VS, you can connect to xls and mdb. VS comes with the necessary drivers and it builds a set of objects that you can then use in code. To view the Data Sources palette, View>Other Windows>Data Sources or Shift+Alt+D.

[Dale.Bartlett]

That is a good suggestion. Simply, there is nothing special to connect to a database (or spreadsheet) from AutoCAD; it is exactly the same as you would do from any .NET WinForm application. I think if you look in that direction (ie separate to AutoCAD) you will get plenty of samples to fit your needs. As a side note, I have used the OpenXML SDK for some time to read/write XLSx files. This avoids the issue of your application being dependent on the users Office version/installation. I can look for some snippets if you don't have any luck.  

[deusextechnic]

Appreciate the suggestions so far. I am running into a common problem with all the approaches I tried so far though. All the autodesk products in the office are 64 bit, while all the Microsoft office installs are 32. From what i can gather, the service providers used to connect to the data sources (such as jet or ace) need to be 64 bit to run in 64 bit autocad .net - with no exeptions, but cannot be used in a 32bit Microsoft office environment. Any ideas? I am going to try  OpenXML SDK fo as suggested by dale in the meantime.

[norman.yuan]

It looks like you want to connect data stored in either MS Access DB or Excel sheet with MS Access DB Engine. Yes, with 64-bit AutoCAD, the MS Access DB Engine must be 64 bit, which is a free download from MS. 

As you stated, the computers of yours all have 32-bit MS Office installed. Thus, installing 64bit MS Access DB Engine could be an issue, or not possible, if the 32-bit MS Office suite is older version (2007, maybe even 2010, I could not recall exactly). But with later version of MS Office suite, at least MS Office 2013 or later, you can install 64-MS Access DB Engine with MS office 32bit in the same computer. With this said, making your CAD app dependent on MS Access DB Engine is not a very good choice. I'd avoid to use MS Access DBfor data storage whenever possible.

If you do go with MS Access DB engine, you do not need to create DSN (data source name) in each computer, as the other reply suggested. With ADO.NET, you can create DSN-less OldDb.net connection easily to read data fromo MS Access DB or from Excel sheet. 

I know many people tend to use Excel sheet for data storage. In this case, you can use OpenXML to read/wriet data back and forth to Excel sheet (so that you can avoid the dependency to MS Access DB Engine).

[dgorsman]

SQLite may also be an option.

Proper selection of data format is important - no point setting up a SQL Server system for storing a couple of system variables but works very well with exceptionally large data sets and large numbers of users; an Excel workbook can be inappropriate for very large amounts of values even though it is easily human-modified; MS Access can be a good option for mid-sized data sets but has problems with sharing large numbers of users and ties you to an MS Office bit-ness; XML can be slow for very large data sets but works well for smaller data sets, handles irregular/"jagged" data better, and converts to other formats very easily.

[deusextechnic]

Thanks for the input. All the approaches work in isolation but don't transfer well to other machines since the installs are all different. I ended up going with reading a CSV instead of using OpenXML or DataTools. This avoids the driver and File version issue. I am then writing the values to an SQL database using Entity Framework and reading values from there. This introduces an extra step into the process but the database only needs to be updated once a week or so. 

[CADbloke]

For Excel I have used http://epplus.codeplex.com/ with good results. Another alternative is Syncfusion's community license for their XLSIO

I have also had good results with SQLite and Entity Framework using https://github.com/msallin/SQLiteCodeFirst