Tuesday, November 24, 2009

Getting SqlConnection from EntityConnection

I love ADO.NET Entity Framework. The problem I ran into is I wanted to call a stored procedure, but I can’t really do that unless the stored procedure returns entity information, etc. I really just wanted to call a stored procedure, though it could have been embedded SQL also (if you do that sort of thing :), but I needed a SqlConnection to do that. All I had was a DbConnection which can be cast to and EntityConnection since I am using the EF. The question was how do I get to the SqlConnection that EF uses. Sure, I could have created another entry in my web.config or app.config and added another connection string. I don’t like to have some many connection strings though. A little looking around in the debugger and the solution became obvious.

Below is a simple method that takes my DbConnection and casts it to a EntityConnection. It then accesses the StoreConnection which returns a DbConnection and is cast to a SqlConnection. It then just gets the connection string from the SqlConnection,. From that point I can open a new connection of my own. Alternatively, I could have checked the state of that connection and opened and closed it appropriately. I didn’t want to worry about the state of the connection and messing up the EF, so I just create a new connection. Though both seem to work for my basic testing.

using System.Data.EntityClient;
using System.Data.SqlClient;
...
private string GetADOConnectionString()
{
SalesSyncEntities ctx = new SalesSyncEntities();
EntityConnection ec = (EntityConnection)ctx.Connection;
SqlConnection sc = (SqlConnection)ec.StoreConnection;
string adoConnStr = sc.ConnectionString;
return adoConnStr;
}

14 comments:

Anonymous said...

Brad V,

Your article, "Getting SqlConnection from EntityConnection", was very helpful to me.

Many thanks.

T. Sinnott

Vern B said...

You probably want to wrap your call to "new SalesSyncEntities()" in a "using" statement, or else explicitly call Dispose() on it.

Anonymous said...

Great post.. Saved my time.. Thanks!

Anonymous said...

Thank you

Anonymous said...

Thanks a lot!

Anonymous said...

Great post. Thank you!

Anonymous said...

Thanks and Nice Articles and save my time

Anonymous said...

Amazing! Spent hours on this. Thanks so much!

Anonymous said...

I started with a separate connectionstring in web.config. Then I saw your article. Thanks!

Anonymous said...

Really helped me. Thanks :)

The Last Expert said...
This comment has been removed by the author.
The Last Expert said...


RemoteViewEntities dbContext = new RemoteViewEntities();
string sqlstring ="";
sqlstring = (((System.Data.Entity.DbContext)(dbContext)).Database.Connection).ConnectionString;

Anonymous said...

adamsın adam

Ivfaris said...

Muchas Gracias. Saludos.