Entity Framework getting an sql connection

Question

In the light of Closing connections explicitly in Entity Framework and http://msdn.microsoft.com/en-us/library/bb738582%28v=vs.90%29.aspx it seems that I should be using the context to create connections rather than doing the following

using (SqlConnection con = new SqlConnection("Persist Security Info=False;Integrated Security=true;Initial Catalog=Remember;server=(local)"))
{
    ...
}

My understanding is that I'll

Get rid of the connection string
Utilize connection pooling built into EF

But how do I acquire an SQL connection through the context?

What do you want the SQL Connection for? There's probably a way to do what you want directly in EF without the need for the actual SqlConnection. — siva.k
– siva.k, Commented Aug 1, 2014 at 15:01
Possible duplicate of Extract connection string from an Entity Connection String — Michael Freidgeim
– Michael Freidgeim, Commented Mar 7, 2017 at 3:54

user2864740 · Accepted Answer · 2017-11-21 20:08:34Z

20

In EF5 (changed for EF6) the following would return the connection:

var connection = ((EntityConnection)context.Connection).StoreConnection;

~~If you are using EF in a right way you will probably never need to get inner db connection.~~

edited Nov 21, 2017 at 20:08

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

answered Mar 12, 2011 at 23:31

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

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

2 Comments

Roman Pokrovskij Over a year ago

context.Connection was removed since EF6

Sÿl Over a year ago

@RomanPokrovskij in EF6 you can use context.Database.Connection

Peter Barbanyaga · Accepted Answer · 2014-05-05 09:35:18Z

11

I used next code to getting connection (Entity Framework 5.0):

var connection = Context.Database.Connection;

answered May 5, 2014 at 9:35

Peter Barbanyaga

5467 silver badges17 bronze badges

Comments

Carlo V. Dango · Accepted Answer · 2011-03-12 23:36:06Z

9

I found out that the magic lies in ExecuteStoreCommand()

  new AdventureEntities().ExecuteStoreCommand(
        @"    UPDATE Users
              SET lname = @lname 
              WHERE Id = @id",
        new SqlParameter("lname", lname), new SqlParameter("id", id));

Then there is no need for an explicit Connection, it actually made the code a lot cleaner. The one-liner above replaced all of the following code

  using (SqlConnection con = new SqlConnection("Persist Security Info=False;Integrated Security=true;Initial Catalog=Remember;server=(local)"))
  {
    con.Open();
    using (SqlCommand cmd = con.CreateCommand())
    {
      cmd.CommandText = @"
          UPDATE Users
          SET lname = @lname 
          WHERE Id = @id";
      cmd.Parameters.AddWithValue("lname", lname);
      cmd.Parameters.AddWithValue("id", id);
      cmd.ExecuteNonQuery();
    }
  }

answered Mar 12, 2011 at 23:36

Carlo V. Dango

14.1k17 gold badges74 silver badges115 bronze badges

3 Comments

Ladislav Mrnka Over a year ago

Yes that was one solution I described in question you deleted. But there is even one better without calling SQL manually. Check ObjectStateManager, GetObjectStateEntry, ObjectStateEntry and SetModifiedProperty.

Carlo V. Dango Over a year ago

It was deleted as the problem got messed up in the question. Can you repose the answer here and I'll accept it

Ladislav Mrnka Over a year ago

I already voted there for reopen so I will wait if other users with high rep. will vote as well before making duplicit answer.

Vuttipong L. · Accepted Answer · 2015-04-29 10:50:29Z

8

var efConnectionStringBuilder = new EntityConnectionStringBuilder(efConnectionString);
string sqlConnectionString = efConnectionStringBuilder.ProviderConnectionString;

edited Apr 29, 2015 at 10:50

answered Apr 29, 2015 at 8:37

Vuttipong L.

5295 silver badges7 bronze badges

Comments

Britt Wescott · Accepted Answer · 2014-08-01 14:57:13Z

5

If the EF connection string is stored in your web config file, you can assign it to a SQL Data Source with this code:

            var connString = ConfigurationManager.ConnectionStrings["MyDataEntities"].ConnectionString;
            EntityConnection ec = new EntityConnection(connString);
            var storeConnect = ec.StoreConnection;

            SqlDataSource1.ConnectionString = storeConnect.ConnectionString;

answered Aug 1, 2014 at 14:57

Britt Wescott

5681 gold badge9 silver badges30 bronze badges

Comments

Varun Mathur · Accepted Answer · 2017-08-18 04:50:38Z

4

I believe the easiest way to create a SqlConnection in EF6 is to do the following:

DBContext context = new DBContext();
SqlConnection sqlconn = new SqlConnection(context.Database.Connection.ConnectionString);

answered Aug 18, 2017 at 4:50

Varun Mathur

9488 silver badges12 bronze badges

Comments

Mantisimo · Accepted Answer · 2013-03-27 18:22:15Z

You could use the following as well. I wanted to inject some Dapper.NET into a existing project that already had Entity framework entity context so I created the following:

public class EFConnectionAccessor : IDisposable
{
    private readonly SqlConnection sqlConnection;
    private readonly MyEntities entities;

    public EFConnectionAccessor()
    {
        entities = new MyEntities();

        var entityConnection = entities.Connection as EntityConnection;

        if (entityConnection != null)
        {
            sqlConnection = entityConnection.StoreConnection as SqlConnection;
        }
    }

    public SqlConnection connection
    {
        get
        {
            sqlConnection.Open();
            return sqlConnection;
        }
    }

    public void Dispose()
    {
        sqlConnection.Close();
        sqlConnection.Dispose();
        entities.Dispose();
    }
}

Called using

using (SqlConnection sqlConnection = new EFConnectionAccessor().connection)
{
 // ADO.NET CODE HERE - Or in my case dapper.net
}

skaz · Accepted Answer · 2011-03-12 22:52:29Z

0

the DataContext will allow you to call your Entity Objects right off of your DataContext reference, abstracting away all of the details of the underlying connection logistics.

answered Mar 12, 2011 at 22:52

skaz

22.8k20 gold badges73 silver badges99 bronze badges

2 Comments

Carlo V. Dango Over a year ago

Yes, but I wanted to fire an UPDATE statement without first reading in the entity - hence I need an SQLConnection

Nikola Sivkov Over a year ago

it connects automaticly so to speak , you can fire the update and its then when EF connects.(unless you do other stuff before that )

Collectives™ on Stack Overflow

Entity Framework getting an sql connection

8 Answers 8

2 Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

2 Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related