CODEWITHSUNDEEP
c#sql-server-2008
Unknown Coder
Unknown Coder

Reputation: 6741

SQL Server connection management with C#

I have a C# application that has been running just fine for the past year. However, in the past couple of months we have added more clients and the database has been reporting connection errors on a more consistent basis. It might be time to revisit the way connections are handled and perhaps there is a better way to do this. Here is the class that manages my connections:

public class myDAL
    {
        protected SqlConnection sqlConnection = new SqlConnection();

        protected void openConnection(string connection)
        {
                sqlConnection.ConnectionString = connection;
                sqlConnection.Open();            
        }


        protected void closeConnection()
        {            
            sqlConnection.Close();
        }

}

Note that I'm really doing nothing special to manage the connection. I just call the open and close as needed and this is happening from multiple clients at the same time. Am I doing anything obviously wrong here?

Upvotes: 2

Views: 5808

Answers (5)

ThunderGr
ThunderGr

Reputation: 2367

I believe that what you want has been answered here. Implementing a retrying procedure is essential when you expect a lot of traffic to the DB. In that question, the focus is on deadlocks, but it is very useful with all sorts of SQL Exceptions as well, including the connection exceptions.

This may be more than an year late but, it can be useful to other people that want an answer to this question.

Upvotes: 0

David
David

Reputation: 2569

Depending on how frequently the calls are made I would consider something like:

  using (var sqlconnection = new SqlConnection())
  {
  }

or if that's not fitting the task you can maybe append your DAL with something like (just pseudo-example):

 protected void SqlExecute(Action a)
 {
   if (sqlConnection.State != ConnectionState.Connected)
     sqlConnection.Open();

   a();
 }

and in your call:

       SqlExecute(() =>
                       {
                           DoSth();
                       });

Upvotes: 0

Nivid Dholakia
Nivid Dholakia

Reputation: 5462

Use IDisposable Interface on your class, Also use Disconnected Data that will be beneficial 

public class myDAL:IDisposable
 {
    protected SqlConnection sqlConnection = new SqlConnection();
    protected void openConnection(string connection)
    {
            sqlConnection.ConnectionString = connection;
            sqlConnection.Open();            
    }


    protected void closeConnection()
    {            
        sqlConnection.Close();
    }

    public void Dispose()
    {
        sqlconnection.Close();
       //Dispose of the connection
    }

 }

use the statement 

using (MyDal Conn= new MyDal())
{
  //Code 
}

Upvotes: 1

Davide Piras
Davide Piras

Reputation: 44595

Jim your practice of having open and close connection methods inside another class is very old, modern .NET development follows a patter like this nowadays:

using (SqlConnection conn = new SqlConnection("connection string here"))
using (SqlCommand cmd = new SqlCommand("sql query", conn))
{
    // execute it blah blah
}

see here: Closing SqlConnection and SqlCommand c# or search in SO for hundreds of questions and answers all telling the same, close the connection immediately either with a using like in this example or with a try/finally inside the same method, no need for one method to open and on method to close it, just prone to errors if anything happens in the between.

Upvotes: 3

Max
Max

Reputation: 1066

You could implement a IDisposable interface in a wrapper class.

And/Or

With the using statement open and close your connection.

using(var db = new SqlConnection()
{
  db.Something();
}

Now the connection is closed automatically.

When do you close your connection? After each batch of commands or after a interval?

Upvotes: 0

Related Questions