Connect to SQL database from C# plugin

[ahmedshawkey95]

I am developing a C# AutoCAD plugin that needs to connect to SQL database to save and retrieve data from it.

when i tried to use the database from the plugin i got this error "No connection string named 'QSurvEntities' could be found in the application config file".

I tried to connect to the database through DBCONNECT but i got the same error.

Can I add the following connection string in AutoCAD configuration file using AutoCAD API ?

<connectionStrings>
<add name="QsurvEntities" connectionString="metadata=res://*/QsurveEntityModel.csdl|res://*/QsurveEntityModel.ssdl|res://*/QsurveEntityModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=Qsurv;Integrated Security=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"/>
</connectionStrings>

[norman.yuan]

Do you want to connect to AutoCAD built-in DBConnect (via COM API CAO 1.0), or as a general data connection to a database server, such as SQL server. They are 2 different things, in terms of AutoCAD programming.

The former is a special data connection built into AutoCAD. It can be programmatically manipulated through special API CAO1.0, which is COM API. This type of data connection is only used with AutoCAD built-in data-linked template/label.

For more generic data manipulation from AutoCAD (retrieving/updating data from/to database, such as SQL Server), with .NET platform, you can use ADO.NET to directly connect to database and do whatever you need/could do. In these days, it would be more often to have AutoCAD connect to services to retrieve/update data indirectly from/to database, rather than direct connect.

To give more detailed/specific suggestion, you may want to provide more description of your case. 

But if you indeed want to connect to AutoCAD built-in DBConnect, then you need to add refernce to CAO 10. library. And you need to setup a datasource in AutoCAD (which you already have, according to the picture you attached). Then you simply create a DBConnec object and call Connect() method. Something like:

Dim cn As New CAO.DbConnect()

cn.Connect("TheDataSourceName")

[ahmedshawkey95]

Thanks for your reply.

I don't want to use built-in DBConnect. Instead, I want to connect my AutoCAD plugin to the database through Entity Framework.

but when I tried to retrieve data using the Database Entity Model in my Plugin, I got the mentioned error.

[Alexander.Rivilis]

---

@ahmedshawkey95 wrote:

...Can I add the following connection string in AutoCAD configuration file using AutoCAD API ?...

 

---

No! Moreover acad.exe.config usually protected from modification if user is not in Administrator (Local Administrator) group.

[BKSpurgeon]

hi there

is it possible to start up a hello world console application and to try to connect to the database using entity frameworks without including the autocad plugin. if you are not careful when instantiating the db context you could have some trouble. try hard coding the string at first, and then once you have that working then try getting it dynamically. i have included the comments which might help you understand where you could possibly be going wrong.

here is some code which worked for me when hardcoded. i had tried to dynamically get the connection string, but am not sure how successful i was with it. it has been some time since i used entity frameworks on my desktop.

    [DbConfigurationType(typeof(MySqlEFConfiguration))]
    public class PanelContext : DbContext
    {
        // Your context has been configured to use a 'PanelContext' connection string from your application's 
        // configuration file (App.config or Web.config). By default, this connection string targets the 
        // 'localMysqlPanelTest.PanelContext' database on your LocalDb instance. 
        // 
        // If you wish to target a different database and/or database provider, modify the 'PanelContext' 
        // connection string in the application configuration file.
        
        //// This was the old constructor
        //public PanelContext() : base("name=PanelContext")
        
        // connection string is hard coded
        public PanelContext() : base("server=localhost;port=3306;database=PanelContext;uid=TYPEUSERNAMEHERE;password=LUCKYICHECKEDTHISBEFOREPOSTINGTOTHEFORUM😊")         
        {            
        }

        private void GetConnectionString()
        {
            //SettingsPropertyCollection test = localMysqlPanelTest.Properties.Settings.Default.Properties;
            //ConnectionStringSettingsCollection cs = ConfigurationManager.ConnectionStrings;
            //string connectionString = ConfigurationManager.AppSettings["PanelContext"];

            // var connection = new ConnectionFactory().GetConnection(ConfigurationManager.ConnectionStrings["PanelContext"].ConnectionString, DataBaseProvider);
            //var connection = System.Configuration.ConfigurationManager.ConnectionStrings["PanelContext"].ConnectionString;

            //string connectionString = ConfigurationManager.ConnectionStrings["PanelContext"].ConnectionString;
            //ExeConfigurationFileMap configFileMap = new ExeConfigurationFileMap();
            //configFileMap.ExeConfigFilename = roamingConfig.FilePath;

            //// Get the mapped configuration file.
            //Configuration config = ConfigurationManager.OpenMappedExeConfiguration(configFileMap, ConfigurationUserLevel.None);
            
        }

        public DbSet<Panel> Panels { get; set; }

        public DbSet<Block> Blocks { get; set; }   

        // Add a DbSet for each entity type that you want to include in your model. For more information 
        // on configuring and using a Code First model, see http://go.microsoft.com/fwlink/?LinkId=390109.

        // public virtual DbSet<MyEntity> MyEntities { get; set; }
    }

[hgasty1001]

Hi,

I think you need to create the connection *inside* your application. I usually create a class with all the db business needed to connect, retrieve data and so on. It's very simple to do so.

Gaston Nunez

[norman.yuan · Accepted solution]

When you use Entity Framework, if you let VisualStudio does the work, it adds its connection information in app.config. If your application is EXE, or a web app, then you do not have to bother too much with how to get the connection info from config file. However, you are doing DLL as Acad add-in, Acad as EXE app, it gets configuration from acad.exe.config. So, you either need to merge the connection string section in the DLL's app.config into acad.exe.config, or let your code to somehow load the settings from the DLL's app.config (which would be built as *.dll.config). Search the net for how to use *.config with DLL. You may also want to learn/try how to pass Entity Framework's DBConext connection information without relying on VS created configuration.

[dgorsman]

---

@hgasty1001 wrote:

Hi,

I think you need to create the connection *inside* your application. I usually create a class with all the db business needed to connect, retrieve data and so on. It's very simple to do so.

Gaston Nunez

---

This (because I can only give one kudo).  If there is some concern about having to change database at a later point (e.g. supporting multiple clients, systems migration), you can always store the variable information in a registry or data file if you don't want to handle that inside the program as well.

[alvaro_gt1]

Have you founded the connection??

[fieldguy]

@norman.yuan gave the answer. you can create / use  yourapp.dll.config as long as it's installed in the same folder as your dll.

https://stackoverflow.com/questions/594298/c-sharp-dll-config-file