Extract connection string from an Entity Connection String

Question

When creating and ADO.NET Entity Connection String you get something like

<add name="MREntities" connectionString="metadata=res://*/App_Code.MembershipAndRoll.MembershipAndRollEntities.csdl|res://*/App_Code.MembershipAndRoll.MembershipAndRollEntities.ssdl|res://*/App_Code.MembershipAndRoll.MembershipAndRollEntities.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=192.168.0.26;User Id=digitaliv;password=*******;Persist Security Info=True;database=digitaliv&quot;" providerName="System.Data.EntityClient" />

My question is how do you grab the true inner connection string from that to call make a manual sql connection to call stored procedures, custom sql statements etc.? Specifically I need this part extracted from it

server=192.168.0.26;User Id=digitaliv;password=*******;Persist Security Info=True;database=digitaliv

Answer 1

Assuming you have an instance of the ObjectContext (if you are using the built-in designer, your context derives from the EF ObjectContext class). You can cast the value of the ObjectContext.Connection property (which is a DbConnection) to an EntityConnection.

The EntityConnection class has a property StoreConnection which is the actual DbConnection used to connect to the database. This one actually has the ConnectionString property set to the one you are looking for.

Edit: Some sample code (Assign context to your ObjectContext):

ObjectContext context = entities;
EntityConnection entityConnection = context.Connection as EntityConnection;
if (null != entityConnection)
{
    Console.WriteLine(entityConnection.StoreConnection.ConnectionString);
}

Answer 2

While Jan Remunda rightly points out that you don't need to create a context, and while his solution is useful if you want to create a connection explicitly right after reading the EntityClient connection string, it's still a little convoluted if all you want is to retrieve the inner-provider connection string (which is what OP asks).

It's true that you don't have to open the connection, you can just retrieve the ConnectionString of the inner StoreConnection then discard it right after, but why?

Use the appropriate connection string builder instead:

new EntityConnectionStringBuilder(outerConnectionString).ProviderConnectionString

See EntityConnectionStringBuilder.

Answer 3

You don't need to create instance of ObjectContext. Because in that case you must reference assemblies where metadata are stored and for performance is not also very good.

There is simpler way to get provider connection string in StoreConnection property.

 using (var ec = new EntityConnection(connstr.ConnectionString))
 {
   var sqlConn = ec.StoreConnection as SqlConnection;
   sqlConn.Open();
 }