Author Topic: Using SQL Server Compact Edition with an C# AutoCAD plugin  (Read 255 times)

0 Members and 1 Guest are viewing this topic.

Kralj_klokan

  • Mosquito
  • Posts: 18
Using SQL Server Compact Edition with an C# AutoCAD plugin
« on: February 24, 2020, 12:52:30 PM »
Hi,

I m' lately trying to set up a SQL Server Compact Edition connection to my AutoCAD plugin. My goal is to have one db object that i will deploy with my plugin, so that i do not have to install MS SQL Server on every machine i want to deploy to.
My current setup is a Code first ADO.net with Entity Framework. Initially I was trying to do the following:

Code - C#: [Select]
  1.  
  2.         public MyDbContext() : base("name=MyContextString")
  3.         {
  4.         }
  5.  
  6.  

This code above gave me an error that the connection string does not exist in the App.config file.
I found a post that says that since I m developing a plugin. Acad.exe reads from Acad.exe.config and not my App.Config.

A solution would be to alter the acad.exe.config, but i was hoping for a solution where i could set my connection string with provider name from code.

Does anyone have a working example of a code based database configuration for a SQL Compact Edition database?


MickD

  • Gator
  • Posts: 3357
  • (x-in)->[process]->(y-out)
Re: Using SQL Server Compact Edition with an C# AutoCAD plugin
« Reply #1 on: February 25, 2020, 12:43:54 AM »
I think MS deprecated the Compact edition in favour of the Express version so maybe your better off reading up on that tech.

Personally, I'd use SQLite, very portable, fast and easy to work with (server-less) with something like 140 terabytes of capacity!
Forth is like the Tao: it is a Way, and is realized when followed.
Its fragility is its strength; its simplicity is its direction - Michael Ham

Lao Tzu: ďTo attain knowledge, add things
every day; to obtain wisdom, remove things every day.Ē

n.yuan

  • Bull Frog
  • Posts: 285
Re: Using SQL Server Compact Edition with an C# AutoCAD plugin
« Reply #2 on: February 25, 2020, 11:40:57 AM »
Hi,

I m' lately trying to set up a SQL Server Compact Edition connection to my AutoCAD plugin. My goal is to have one db object that i will deploy with my plugin, so that i do not have to install MS SQL Server on every machine i want to deploy to.
My current setup is a Code first ADO.net with Entity Framework. Initially I was trying to do the following:

Code - C#: [Select]
  1.  
  2.         public MyDbContext() : base("name=MyContextString")
  3.         {
  4.         }
  5.  
  6.  

This code above gave me an error that the connection string does not exist in the App.config file.
I found a post that says that since I m developing a plugin. Acad.exe reads from Acad.exe.config and not my App.Config.

A solution would be to alter the acad.exe.config, but i was hoping for a solution where i could set my connection string with provider name from code.

Does anyone have a working example of a code based database configuration for a SQL Compact Edition database?

I am with MikeD on using SQLite instead of SQL Compact, but you would still have the same issue (of loading connection string from app.config).

While in .NET add-in DLL project you can add an app.config file, the code using System.Configurations.ConfigurationManager would not read the configuration data in your DLL project's app.config (its build output should be [Add-in Name].dll.config) without special code. In earlier .NET (1.x), all the configuration content in DLL's app.config should be merged into app.config of the EXE app that loads the DLL. IN AutoCAD, that is Acad.exe.config.

So, yes, you can merge the DLL's app.config into acad.exe.config.

However, with later .NET (I do not remember since when, 2.x or 3.x?), you can directly load configuration in dll's *.dll.config in some way, the only as System.Configuration.Configuration.AppSettings.

For example, in your DLL's app.config, you can add appSettings like:

Code: [Select]
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="MyDBName" value="Database Name" />
    <add key="MyDBFileName" value="C:\MyData\MyDBFile.mdb" />
  </appSettings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
  </startup>
</configuration>

When you build the project, you get [Add-in name].dll.config, you place it in the same folder as the DLL. You can have code like this to load the settings:

Code - C#: [Select]
  1. private void LoadSettings(out string dbName, out string fileName)
  2.         {
  3.             dbName="";
  4.             fileName="";
  5.  
  6.             Configuration config = null;
  7.             string exeConfigPath = this.GetType().Assembly.Location;
  8.             try
  9.             {
  10.                 config = ConfigurationManager.OpenExeConfiguration(exeConfigPath);
  11.             }
  12.             catch (Exception ex)
  13.             {
  14.                 throw new System.IO.FileFormatException(
  15.                     $"Loading configuration error: {ex.Message}");
  16.             }
  17.  
  18.             if (config != null)
  19.             {
  20.                 dbName = config.AppSettings.Settings[MyDBName].Value;
  21.                 fileName = config.AppSettings.Settings[MyDBFileName].Value;
  22.             }
  23.             else
  24.             {
  25.                 throw new InvalidProgramException(
  26.                     "Invalid *.dll.confg file.");
  27.             }
  28.         }
  29.  

If the settings is per user based, you can also simply add settings in project->Resource->Settings and add settings in User scope. Then you can simply load the settings with
Properties.Settings.Default.xxxxx (make sure you give it default value when the code runs the first time, and save the settings at some point).


HTH


It's Alive!

  • BricsCAD
  • Needs a day job
  • Posts: 7006
  • AKA Daniel
Re: Using SQL Server Compact Edition with an C# AutoCAD plugin
« Reply #3 on: February 26, 2020, 01:18:10 AM »
++ for SQLite as an embedded server, Iíve never tried it with .NET, great for C++ projects.
In C/C++ you donít need a provider or connection string, so once your project is built, you donít need worry about Microsoft screwing around with things.

That said, express is probably installed on most machines these days, Iíve never tried though.

Also, what are you trying to store? If its just data like system variables, you might be better off using serialization

CADbloke

  • Bull Frog
  • Posts: 311
  • Crash Test Dummy
Re: Using SQL Server Compact Edition with an C# AutoCAD plugin
« Reply #4 on: February 26, 2020, 05:33:10 PM »
Definite UpVote for SQLite with .NET, I use it with Entity Framework and https://github.com/msallin/SQLiteCodeFirst in tvCAD and I've also used it with raw SQL using https://www.nuget.org/profiles/SQLite things. It is lightweight, fast and works well. I would only go to SQL Express if you need a client - server thing, SQLite is my 100% goto for a desktop app database. Don't expect it to be on any end-user's PC. I never use it.

I find Entity Framework is also performant but you can to do things like turn off tracking for read queries if you want to optimise the hell out of it. That's not difficult, plenty have done it before and documented their travels. Schema migrations with Entity Framework + SQLite can be tricky, the trick is to  completely avoid the built-in EF migrations and DIY. If any of you need help with this, ping me - I have have weathered that storm.

The config for EF + SQLite is usually in the plugin DLL's config, I actually use class properties for the settings since my app needs change databases for different projects.

Another way to use DLL settings is via the Properties.Settings.Default static class that is generated when you add settings to your project (in the project properties area in Visual Studio), this is the one I use as they are per-user.

For looking at / editing SQLite databases, try https://sqlitebrowser.org/ and LinqPad.