C# and MS Access Database

[parikhnidi]

Hi Friends,

Anyone knows how to read Access tables and queries from C#. I am fairly comfortable with relational database. I have developed many objects where for testing purpose, I have hard coded the values of various properties. And the results are as expected.

However, as I dive into a greater depth, I begin to encounter extensive look ups from the dimensional tables and I now want to read data from the database instead of me typing in those values.

Any help would be appreciated.

Thanks,

Nimish

[norman.yuan]

By saying "...I am fairly comfortable with relational database...", have you done any database programming in pre-.NET era (VBA, classical VB...), or .NET data access programming?

Now that you are asking in this Acad .NET discussion forum, naturally, you would use .NET data access technology (ADO.NET) to directly access data in a database (either file based, such as MS Access, SqlLite.., or server based, such as Sql Server, MySql...). So, you need to firstly do some study on ADO.NET. There are a lot tutorials available online/Youtube.

Now comes to database of choice. IMO, MS Access is not a good (if not the worst) choice one would choose to work with custom AutoCAD application. The only time that is OK to choose it is that the user computer has already had 64-bit MS Access DB Engine installed by company computer setup requirement (say, as  the result of 64-bit MS Office suite, which includes MS Access). Otherwise, in order for AutoCAD application to access data in MS Access, the user computer must have separate 64-bit MS Access DB engine installed, which is very tricky/difficult, if the use has 32-bit MS Office suite installed (it is most likely). So, by using MS Access, you simply add an unnecessary/difficult dependency to your CAD application.

For small data set, for the mobility reason, you can choose SqlLite, which is file based. If your CAD app is used in a office environment for multiple user, you might consider use server based database (such as SQLExpress), the extra benefit of using database server is that the dependency to the database server would make your CAD app sort of  "theft-proof": no one can use it (or user it properly) without having access to the database server.

[parikhnidi]

Hi Norm,

Thanks for the guidance. I have well defined database with proper normalization of various columns and well defined queries to manipulate the data in a way I wanted to. I have dimensional tables for various components I come across in my day to day life. These tables are fairly static because I am simply reading the dimensions based on size of a component. And that's why I resorted to MS Access (I am using Access 2010).

Nimish

[norman.yuan]

Because you are asking accessing data with .NET language in AutoCAD .NET API forum, how your MS Access database is well designed/normalized and the how the data in it is managed have very little impact to your AutoCAD .NET application development. Ideally, the data access code in the Acad .NET APP should not care/know what type of database is used, if the app developed well. If you just started to learn ADO.NET, then that is OK to use MS Access as your existing data source. And you would want to get started outside AutoCAD to avoid the tedious AutoCAD debugging and do the study with EXE app and class library (for the data access, which can be later used in Acad .NET add-in project).

I only pointed out that by letting AutoCAD .NET app to DIRECT access MS Access database, you only add a troublesome dependency to your CAD application, which I am almost certain in most cases, one would regret the database choice later on.