CODEWITHSUNDEEP
c#asp.netsql
Cokegod
Cokegod

Reputation: 8434

Start and close SQL connection - At start and in the end or every time needed?

I'm making a web application in C# with ASP.Net, and I'm using an SQL database for it. In the guides I saw (about SqlConnection and SqlCommand) they open and close the SQL connection each time they want to send a query. But I'm not sure this is the best way to handle the connection, because I also learned some PHP, and as far as I know in PHP you open a connection only once, at start. So what is the best way to handle the connection?

Upvotes: 1

Views: 3621

Answers (6)

Tim Schmelter
Tim Schmelter

Reputation: 460278

Ado.Net has connection pooling already managed for you, so you don't need to worry about reusing connections.

You could use the Using statement to close and dispose connection:

   DataTable dt = new DataTable();
   using (SqlConnection conn = new SqlConnection("my_connection_string"))
   {
      using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * from Table", conn))
      {
        conn.open();
        adapter.Fill(dt);    
      }
   }

Upvotes: 1

Vijay
Vijay

Reputation: 446

Try Data Access Application Block in the Enterprise Library if you do not want to worry about opening and closing the connections.

Upvotes: 0

James L
James L

Reputation: 16874

You should generally have one connection, and transaction, for the length of the web request.

If you have two connections, you could potentially have inconsistent data. e.g. in the first query you check the bank balance, then you close it. You then add $5 to the balance and save that away in the next connection. What if someone else added $5 between the two connections? It would go missing.

The easiest thing to do is to open the connection global.asax BeginRequest and save that away into the HttpContext. Whenever you need a connection, pull it from there. Make sure that you .Close the connection in your EndRequest

Also, read up here on connection pooling: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx It is not 'expensive' to repeatedly open and close connections, provided the username is the same each time.

Upvotes: 3

Mirko
Mirko

Reputation: 4282

For starters they do not as there is a connection pool that is being used, but SqlCommand has an overload to take a connection object, so you could keep the connection around and pass it.

Generally:

using (var ts = new TransactionScope()) { using (var connection = new SqlConnection(...)) { using (var command = new SqlCommand(connection, ...) { ... }

using (var command = new SqlCommand(connection, ...)
{
   ...
}

}

ts.Complete(); }

Of course I would recommend using LINQ-to-SQL or EF at this point.

Upvotes: 0

Giorgi
Giorgi

Reputation: 30883

As a connection to database is an expensive resource it's better to open sql connection as late as possible and close early. So you should open the connection just before executing a command and close it once you have executed it.

On the other hand if you are going to execute many commands in a short interval of time it's better to open the connection once and close after all the commands are executed.

Upvotes: 0

Tejs
Tejs

Reputation: 41256

Normal practice in .NET is to open a SqlConnection and use a SqlCommand, then dispose of both of them. 

using(SqlConnection connection = new SqlConnection("myConnectionString"))
{
    using(SqlCommand command = new SqlCommand("dbo.SomeProc"))
    {
        // Execute the command, when the code leaves the using block, each is disposed
    }
}

Upvotes: 0

Related Questions