Database connections: How to use c# 4.0

Question

Beginner:

Hi Guys - looking for some help to see how should i open and close database connections.

Problem i am trying to resolve: I have a set of stored procedures which needs to be executed in the data access layer.

My service call the DA method Get(Request req) as:

public Data Get(Request request)
    {
        var data = new Data();

        data = GetData();
        data.AppleData = GetGrapeData();
        data.OrangeData = GetGrapeData();
        data.GrapeData = GetGrapeData();

        return data;
    }

where all the getmethods getdata, getgrapedata etc are private methods in the Data access class and different SP's are called in each methods.

Now in each method i am opening and closing the database connection as:

{  try{   
  using (var connection = new SqlConnection(connectionString)
  using (var command = connection.CreateCommand())
   {
      connection.open();
      ExecuteSP();
      connection.Close();
   }
   }catch()
     {
     }
}

Now Is there any way i can do this so i have to open/ close the connection just once? I am doing try catch in each private method. is that ok? Is there any issue in the way i am doing it above?

You could make a class that manages your connection and command objects and just instantiate that as you need it. I will work-up a quick example for you and post it as answer. — Brian, Nov 08 '13 at 21:57
Are you sure you don't want to use an ORM? They've already solved this problem... — Austin Salonen, Nov 08 '13 at 21:58
If I were you, I'd move 'connection.Close()' to a finally. If you get an exception while executing the Stored procedure, the connection will remain open and you'll have a nasty leak (and you won't even know about it - since you're failing silently. — Haedrian, Nov 08 '13 at 22:03
@Haedrian Fortunately, since he makes using(), he don't need to close connection manually - and "finally" is already done on background for him, by framework, http://stackoverflow.com/questions/278902/using-statement-vs-try-finally ... If I were him, I'd get rid of Close, first of all. After it I'd come for issues of code duplication and catch(){}. — Dmytro, Nov 08 '13 at 22:12

score 1 · Answer 1 · answered Nov 08 '13 at 22:05

With SQL Server, you don't want to leave connections open any longer than you need to. Other parts of the system, or another system, can be waiting for them.

And second on the ORM - either EF or NHibernate does this much better than most programmers.

score 1 · Accepted Answer · answered Nov 08 '13 at 22:30

Yes, you can open the connection just once. You could use a class or something to manage this, but that seems overkill to me for a simple scenario.

public Data Get(Request request)
{
    using (var connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.open();

            var data = new Data();

            data = GetData(connection);
            data.AppleData = GetGrapeData(connection);
            data.OrangeData = GetGrapeData(connection);
            data.GrapeData = GetGrapeData(connection);

            return data;
        }
        finally
        {
            connection.close()
        }

    }
}

And then in the methods that call the stored procedure:

private Date GetDate(SqlConnection connection)
{
    using (var command = connection.CreateCommand())
    {
        return ExecuteSP();
    }
}

You can put exception handling wherever you'd like, but if you aren't going to do anything with the exception, then you absolutely should not catch it.

I am going to use this..and i was able to get rid of the catches..they were really not required.... thanks jeff — fireholster, Nov 09 '13 at 00:40

Brian · Answer 3 · 2013-11-08T22:16:20.407

So, to support the example I mentioned in my comment above, you could do something like this:

// This is your main class.
namespace WindowsFormsApplication1
{
   public partial class Form1 : Form
   {
       public Form1()
       {
          InitializeComponent();
       }

       private void SomeMethod(object sender, EventArgs e)
       {
          ConnectToSql connToSql = new ConnectToSql(); // Instantiate the new class here.
          connToSql.SqlConnection();  // Use the new class
       }
   }   
}

// This is the class that manages your SqlCommand and connection objects
namespace WindowsFormsApplication1
{
   class ConnectToSql
   {
      public void SqlConnection()
      {
         try
         {
            string strConn = "Some connection string here.";
            string strCmd = "Some Sql query string here.";

            // Create your connection object.
            using (SqlConnection sqlConn = new SqlConnection(strConn))
            {
                // Create your command object and pass it the connection.
                using (SqlCommand sqlCommand = new SqlCommand(strCmd, sqlConn))
                {
                    sqlConn.Open();
                    // Do some work here.
                    sqlConn.Close();
                }
            }
         }

         catch (SqlException sqlExc)
         {
            throw;
         }

         catch (Exception exc)
         {
            throw;
         }
     }
}

A few things to note here:

This isn't the best approach. As was mentioned in comments, an ORM may be a better fit.
Each time you need to open the connection, you will need to use the new class so, it's best to instantiate it at the top-level of your class.
The using keyword will manage your connection and command objects for you since they both implement IDisposable.

Database connections: How to use c# 4.0

3 Answers3