Carlo V. Dango Carlo V. Dango - 2 months ago 21
C# Question

Entity Framework getting an sql connection

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?

Answer Source

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();
    }
  }