Topic: use code for both AutoCAD and Microsoft Applications

[jtoverka]

I have yet to create a .NET program for autoCAD and I have several questions before I do. Right now I use AutoLISP for my AutoCAD Electrical automation. On top of this I use Microsoft access and VBA to hold and process data that needs to go into Electrical Drawings. I would like to expand the functionality and involvement of databases in AutoCAD. Before I do, I would like to know if I am able to use say a dll (or any equivalent) file in both AutoCAD and Microsoft Access. I would like to keep my "manual" tools for AutoCAD while scaling up to larger automation programs. However, I do not want to maintain multiple programs that do the same thing.

[57gmc]

No, AutoCAD .NET programs can only run INSIDE AutoCAD. Its called in-process, or using the same process autocad uses at runtime. However, you can automate Access from within the same code. I'm not sure from your description what you mean by "larger automation programs", but its best to keep your architecture based on AutoCAD. If you truly, need to work in AutoCAD from another application, you are limited to AutoCAD's ActiveX (what vba uses) api.

[MickD]

To add to 57gmc noted, your best bet would be to use say SQLite, MySQL or a Microsoft SQL Server database. It will mean re-writing your queries and functions in .net but at least you will be bringing your code up to date and keeping it all together. You will also benefit from better performance and a greater storage capacity.
If you need a db for each project then SQLite would be ideal as it is just a file stored in a folder with no need for a db server etc. Much easier to get going with.

[nobody]

I have yet to create a .NET program for autoCAD and I have several questions before I do. Right now I use AutoLISP for my AutoCAD Electrical automation. On top of this I use Microsoft access and VBA to hold and process data that needs to go into Electrical Drawings. I would like to expand the functionality and involvement of databases in AutoCAD. Before I do, I would like to know if I am able to use say a dll (or any equivalent) file in both AutoCAD and Microsoft Access. I would like to keep my "manual" tools for AutoCAD while scaling up to larger automation programs. However, I do not want to maintain multiple programs that do the same thing.

My crude understanding is .Net has the power to work across multiple applications if done in a specific way. For example, I can make .net access a database then complete actions based on that in cad, the same is true for the opposite.  I've done this a lot. I've accessed software outside of cad and used the same .net application to perform actions inside cad based on that data and visa versa.

You should look into dynamo. It will allow you to build nodes to connect to the various applications or databases and give you a lot of power to do different things.

[jtoverka]

No, AutoCAD .NET programs can only run INSIDE AutoCAD. Its called in-process, or using the same process autocad uses at runtime. However, you can automate Access from within the same code. I'm not sure from your description what you mean by "larger automation programs", but its best to keep your architecture based on AutoCAD. If you truly, need to work in AutoCAD from another application, you are limited to AutoCAD's ActiveX (what vba uses) api.

To clarify, I am well aware that AutoCAD programs can only be run in AutoCAD. What I am trying to say is there are levels that tools can operate from. However, these programs need to be capable of scaling to higher order functions. Let me give an example. In AutoCAD Electrical there is a signal cross reference tool. This tool takes the value in the wireno attribute of a signal cross reference block and ties it to a value in the sigcode attribute. There are two types, source and destination signal codes. AutoCAD electrical stores this data in a database. When you run the signal cross reference tool, there are three options.
1. Pick
2. Drawing Wide
3. Project Wide

The pick allows you to select a signal cross reference block to update it.
The drawing wide tool runs on the active drawing.
The project wide tool runs on the active project which contains all of your drawings for the project. The drawings can either be closed or open (it uses objectDBX)

The central problem with this tool is that there are massive bugs with this tool because there are three separate routines embedded in the program to do the same thing. The drawing wide tool behaves differently (incorrectly) than a project wide tool and selecting only the active drawing. This is massively infuriating.

The way I would program it is to have one single function that does the work. Which is read signal code, query database, get wireno, write wireno to signal cross reference block. However, the other tools simply obtain and transfer the data to this one function (pick, drawing wide, and project wide). I have been programming this way for AutoCAD electrical and it has been a massive success as far as code maintenance goes (i.e. no code maintenance needed). If there is a bug, I fix it once and all of the relevant tools get updated.

My question is when my programs crosses software lines like AutoCAD Electrical versus Microsoft Access. I want to have Access be able to calculate the PLC Addressing with basic data in the database and store that data while AutoCAD Electrical uses that data to create a PLC card. This is only one small example. I don't want to write ANOTHER function that does the same thing in VBA when I have it written in AutoLISP. The reason for rewriting these functions is to SCALE up to a larger level of Automation with less code maintenance and more flexibility.

I have a tool that inserts a motor circuits with attribute keys. A database holds every possible combination of motor circuits and populates the circuit after insertion. There is only one function that does the insert. There are two functions that gather the data. One is the dialog box for manual insertion. The other is an automated function that is invoked when a higher order tool invokes it. For example, project wide automation will create an entire project with data given in an ascii file (which is generated from a program in excel). If the tool is broken, one fix will fix both tools.

Additionally, I don't want to have to find bugs in several functions that do the same thing.

So I will restate the question. Can I write functions in a language / compiled in a file that can be accessed by both AutoCAD and microsoft applications? I understand that certain actions like creating objects in drawings can only be done within AutoCAD, that is fine, however, this is the least of my worries, I need to calculate the correct data to populate these blocks.

[dgorsman]

Yes, that can be done.  The common code is in a separate DLL, which can then be referenced by other projects.

[jtoverka]

Yes, that can be done.  The common code is in a separate DLL, which can then be referenced by other projects.

How would I write the code so that I can use the functions and objects in both AutoCAD and Microsoft Office Products?

[n.yuan]

It sounds like your focus is to write one set of code (with the same language, or the same platform, such as .NET, so that the language of choice of C# or VB.NET would not be an issue) for specific process/functionality, and use them in different applications (in your case, AutoCAD and various MS Office Apps, Access, mainly).

It also sounds to me, that you use MS Access not only as data source, but also as a user application (thus VBA code in MS Access), that is the reason you want to write code that can be shared by both AutoCAD and MC Access. However, you did not provide more details on what kind of "the same thing/process" that would be done in both AutoCAD and MS Access? The example scenario you described on "wireno", "sigcode" can all be handled on AutoCAD side. But I assume in your workflow, the data used by AutoCAD would be manipulated both inside AutoCAD and outside AutoCAD by other app (MS Access's UI, for now).

Based on these scenario, let's see what you can do:

1. AutoCAD side: you can write .NET DLLs that can be shared with any other apps, as long as the DLLs do not have references to AutoCAD .NET API assemblies. For example, you build .NET DLL that only handles data access from external data source (doing the usual CRUD work). Then this DLL can be shared with other apps, be that apps are 64-bit or 32-bit. Ideally, you would create a set of data access interfaces, and implement the interfaces differently against different types of data source, be it MS Access, SQL Server, or web services... (if you know what I am talking).

2. Other Apps that may share the same DLL that is used in AutoCAD. It is unfortunate that MS Access as front application, the UI part, does not support .NET Add-in as other MS Office (Word/Excel). So, you cannot directly share the .NET DLL. You must expose the .NET DLL as COM interop, then you can use the COM interop with MS Access VBA code. While this still makes AutoCAD and your MS Access app share the same DLL, making .NET DLL COM-able adds quite some complexity to the solution and complicates the app's installation/update process a lot. IMO, using MS Access as front UI is bad choice (more likely due to historical reason), if the MS Access is both front UI and database itself, then it is even worse case (MS Access UI should be separated from MS Access database).

If it is indeed that the data need to be manipulated from both inside AutoCAD and outside AutoCAD, then the ideal situation as I can see is to create a .NET stand-alone app (desktop, or web). Then you can have completely share-able DLL(s) that handle data access, ideally through interfaces. In this way, both AutoCAD and the external app can have access to any kind of external data source with very little configuration changes.

Again, if you limit the external app with MS Access, be it by choice, or by historical reason, thing does not look good and the options are limited (COM-able .NET DLLs). If possible, I'd leave MS Access behind (both as front UI and data source) and move on to other better choices. Otherwise, you would struggle with the same issue every time new requirements for updating come up.

[jtoverka]

Luckily, I don't have a lot invested in Access, so a change is acceptable.

The reason I don't have a lot of examples to provide is because I am in the planning stages of developing a larger automation program. Right now all of my automation code is in AutoLISP. I would like to transition to a C# dxf writer. This way I don't have to work with all of the bugs that plague AutoCAD Electrical.

However, I cannot devote all of my time making this new process work, I need to transition to this. Luckily I considered this when I made my current automation program. All of my data processing is stored in separate functions that returns data to be used for making autocad entities and objects.

What do you recommend as the external program of choice other than Access?

[MickD]

What do you recommend as the external program of choice other than Access?

If you want a database for each project I'd use SQLite, in fact you could use it for all projects depending on how large a record set you envisage over time. If you are talking millions of records then move to MS SQL server or something like MySQL or Postgres.
The beauty of SQLite is that it is a simple file and you don't need to configure a server etc. In fact I'd use it for development anyway, your data and queries can be transferred to a server hosted database easily when needed.

All your business logic could then be embedded in your one AutoCAD app plugin with no need for external app's. As n.yuan noted above, the alternatives are a lot of work and upkeep.
hth

[CADbloke]

What do you recommend as the external program of choice other than Access?

If you're building a stand-alone app from scratch then you're got Winforms or WPF. I prefer WPF but there's a learning curve. I use the http://www.mvvmlight.net/ and  https://github.com/MahApps/MahApps.Metro toolkits for most of my WPF apps. For an AutoCAD hosted App search this forum for Palettes, it works well as a modeless UI. My https://www.CADreplace.com is a WPF plugin in a palette.

I agree with @MickD about SQLite - it is a great database engine, fast and compact. I use it with Entity Framework and https://github.com/msallin/SQLiteCodeFirst and it works really well. Migrations with Entity Framework Code First can be a pain when updating your data model - ping me of you run into this, I have a solution that works if you're adding things to the model, it also runs a list of manual SQL migrations if you want to change / delete things. You don't have to use Entity Framework with SQLite, the .NET ADO providers System.Data.SQLite & System.Data.SQLite.Linq work well if you want to stick with SQL.

If you want to stick with Lisp then check out http://www.theswamp.org/index.php?topic=28286 - SQLite for AutoLisp

SQLite locks the database file for writes (so use transactions etc. to release those locks) but you can read the database from another app as long as it isn't locked for writing at that moment. Hello race conditions. You don't need to shoehorn it all into one app if you don't want to.

For code sharing you can go the shared DLL route or you can add the code files as links to other Visual Studio projects ("Add Existing item as link" so there is only ever one version of the file) - you need to watch your housekeeping if you do this, it is easy to break things if you're not careful. DLLs are probably safer.

[It's Alive!]

Yeah, stay away from access, or any other system that needs proprietary providers. I had years invested in access when 64bit hit and Microsoft decided not to support JET/ADO for 64 bit machines (they do now). It’s the whole reason I made SQLiteLsp..  IMHO, embedded servers are the way to go.

I noticed in another thread your interested in fields, SQLiteLsp has a SQLite field evaluator, where the evaluator parses SQL statements. Its open source, so your more than welcome to draw inspiration from it, it shouldn't be too hard to port to .net : )