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?

Answer 1

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.