Connection string: convert from config file to in code

Question

I'm having some trouble getting my Entity Framework connection strings working from my main application and from my class library. Both my main application (an *.exe) and my class library (a COM *.dll that I want to use with Excel) will need to create a connection to either a SQL Server CE database or a SQL Server database.

At the moment, I define two 'base' connection strings in the config file, one for the SQL Server CE implementation and one for the SQL Server implementation. This works fine for my main application, as it reads the appropriate 'base' connection string from the config file (either SQL Server CE or SQL Server), I adjust the connection string value (e.g. file location via 'data source=' for SQL Server CE or database name for SQL Server), and I'm good to go. This however doesn't work well for my class library, as the config file depends on the application that is using the library. I'd like to therefore get rid of my config file dependency completely, by defining my connection strings in code as opposed to in the config file.

The current config file configuration looks as follows:

<add name="ConnectionCE" providerName="System.Data.SqlServerCe.4.0" connectionString="" />

<add name="ConnectionServer" providerName="System.Data.SqlClient" connectionString="" />

I then create the context as follows:

var context = new DbContext("name=ConnectionCE")
//Do things with the context above here, e.g. change the connection string value.

How can I convert the "ConnectionCE" and "ConnectionServer" connection strings so that I don't have to rely on the config file, and can create these directly in my C# code (with the correct provider)?

Thanks!

marc_s · Accepted Answer · 2017-02-19 13:07:53Z

2

DbContext(string) constructor accepts name or connection string.

You can simply build the connection string in code and pass it into the constructor.

You can use System.Data.SqlClient.SqlConnectionStringBuilder class to build connection string for SQL Server. I think, for SQL Server CE connection string, it is easy to use string.Format().

The code for SQL Server will look like:

var connStr = new SqlConnectionStringBuilder
{
    InitialCatalog = "YourDb",
    IntegratedSecurity = true,
    DataSource = @".\SQLServer",
};
var db = new DbContext(connStr.ConnectionString);

Provider should be specified for SQL Server CE, so the code will look like next:

var conn = DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0").CreateConnection();
conn.ConnectionString = "DataSource = \"C:\\ExistingDBFile.db\"";
var db = new DbContext(conn, true);

edited Feb 19, 2017 at 13:07

marc_s

760k185 gold badges1.4k silver badges1.5k bronze badges

answered Mar 19, 2016 at 17:26

rsage

563 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user9993 Over a year ago

Plus 1 for mentioning the ConnectionStringBuilder

User_FSharp1123 Over a year ago

So interestingly this again works fine from my main application, by setting var db = new DbContext(@"DataSource="C:\ExistingDBFile.db"). However, as soon as I call the exact same code from within my class library, I get the exception 'System.InvalidOperationException - The supplied SqlConnection does not specify an initial catalog or AttachDBFileName'. The DbContext is defined in an external class library (separate from main app and the class library giving the exception), so would expect that if it works for main app it would work for Excel class library?

rsage Over a year ago

My bad. SqlClient provide is used by default. You need specify provider explicitly for SQL CE. Hopefully, this will fix the issue. I added the details to my answer.

User_FSharp1123 Over a year ago

Thanks, this works well. I unfortunately had to still add a config file to the Excel directory, as otherwise I would get a "provider doesn't exist" exception. After adding the config file to the Excel directory, defining the "entityFramework" section in the config file with the appropriate providers and copying over the relevant EntityFramework dll's, I got everything working. I would have liked to get rid of the config files completely, but given the use of the library by an external app (Excel), I imagine that won't be possible.

Collectives™ on Stack Overflow

Connection string: convert from config file to in code

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related