none
what is the best way to handle a connection? RRS feed

  • Question

  • Below is the way that I have been handling my connections. I think I have also seen a connection inside of a using block

    as well. I am wondering if it is better to put the connection code inside of a using block. Is there a difference when these two different ways of handling the connection are compiled? Do they end up being basically the same thing after they are compiled?

     

    I am also wondering if it is better to put the connection within a method and just call the method when I need to connect. There are a couple things that I am not sure how to handle if I do it this way. The first is the closing of the connection. Should the openning and closing of the connection be in two different methods. I am thinking that this would be a good way to do this. It would allow me to do any datareader or data adapter work(by calling methods that contain them) after the connection is opened and close it after the data reader and data adapter work is done(by calling the close method). This would keep all of the opening and closing code indepedent of any data reader or adapter code. It seems like this would increase reusibility and reduce redundant code. 

     

    I am assuming this would be called a global connection method. The different instances of the connections will be using different

    tables and different sprocs from the same database.

     

    SqlConnection myConnection = new SqlConnection();

     

    myConnection.ConnectionString = ConfigurationManager.AppSettings["myConnectionString"];

     

    try

    {

    myConnection.Open();

    Console.WriteLine("The connection was just opened");

    SqlCommand thisCommand = myConnection.CreateCommand();

    thisCommand.CommandText = "SELECT CustID, PetName, BreedID FROM Pets";

    SqlDataReader thisReader = thisCommand.ExecuteReader();

     

    while (thisReader.Read())

    {

    Console.WriteLine("\t\t{0}\t{1}\t{2}", thisReader["CustID"], thisReader["PetName"], thisReader["BreedID"]);

    }

    thisReader.Close();

    myConnection.Close();

    }

    catch

    {

    Console.WriteLine("Something went wrong");

    }

    }

     

    .............................................................thanks in advance
    Friday, January 4, 2008 8:48 PM

Answers


  • I think I have also seen a connection inside of a using block

    as well. I am wondering if it is better to put the connection code inside of a using block. Is there a difference when these two different ways of handling the connection are compiled


    No, the MSIL will be the same. Just a way to organize your code.


    I am also wondering if it is better to put the connection within a method and just call the method when I need to connect. There are a couple things that I am not sure how to handle if I do it this way.

    This is where you are wrong.
    If you use a connection intensively then it is recommended to create the connection once, when the app starts and keep it alive throughout the application. The connection should be closed when the application exits.
    Best way is to create a singleton class that contains application wide information and settings - such as the connection.
    This way the same connection instance can be accessed anywhere in the application.


    I am assuming this would be called a global connection method. The different instances of the connections will be using different

    tables and different sprocs from the same database


    This is exactly why you should maintain the same connection throughout the application.
    All database operations should use this connection. This way you will eliminate a lot of overhead induced by creating/closing connections.

    As a design suggestion: you should separate data layer logic from presentation logic. I see in your example you mix them. This is not good programming - mostly because it is not reusable.
    Instead you should create separate classes that implement all the operations you need.
    You can, for example, make these functions(expecially those who do  SELECT's) return DataTable's. This way your code will be alot easier to maintain. Or, you could create your own data adapters, around the existing ones, but that really depends on what you need to do.

    Regards





    Saturday, January 5, 2008 1:12 AM

All replies


  • I think I have also seen a connection inside of a using block

    as well. I am wondering if it is better to put the connection code inside of a using block. Is there a difference when these two different ways of handling the connection are compiled


    No, the MSIL will be the same. Just a way to organize your code.


    I am also wondering if it is better to put the connection within a method and just call the method when I need to connect. There are a couple things that I am not sure how to handle if I do it this way.

    This is where you are wrong.
    If you use a connection intensively then it is recommended to create the connection once, when the app starts and keep it alive throughout the application. The connection should be closed when the application exits.
    Best way is to create a singleton class that contains application wide information and settings - such as the connection.
    This way the same connection instance can be accessed anywhere in the application.


    I am assuming this would be called a global connection method. The different instances of the connections will be using different

    tables and different sprocs from the same database


    This is exactly why you should maintain the same connection throughout the application.
    All database operations should use this connection. This way you will eliminate a lot of overhead induced by creating/closing connections.

    As a design suggestion: you should separate data layer logic from presentation logic. I see in your example you mix them. This is not good programming - mostly because it is not reusable.
    Instead you should create separate classes that implement all the operations you need.
    You can, for example, make these functions(expecially those who do  SELECT's) return DataTable's. This way your code will be alot easier to maintain. Or, you could create your own data adapters, around the existing ones, but that really depends on what you need to do.

    Regards





    Saturday, January 5, 2008 1:12 AM
  • I am trying to visualize what the code would be like if I do it the way you are suggesting. I understand having my connection in a seperate class. The one thing about doing it this way that I don't understand is how to close the connection.

     

    I am imagining having a OpenConnection() method called from the connection class. I would do this call in the beginning of my Main. Would I also have a CloseConnection() method call at the end of the Main? I want to be sure to explicity close the connection, right?

     

    I am assuming the if I do it this way then I would not use a using or a try block. Because all of the command builder, data table, reader and adapter code would be in each of the appropriate methods that are in the data access class. Or, would the using block(or some other conditional logic structure) be in the Main? It seems like this might be the way to handle this. It would allow me to explicitly close the connection. Am I over complicating this needlessly with a logic structure in this type of situation? Should I just call the myConnection.Close() methpod at the end of the Main without conditional logic?

     

    Thanks for the help. I am going to lose the interface code. This was an initial test to get the connection going.

     

    ..........................................Answers lead to questions that lead to more answers....................................
    Saturday, January 5, 2008 2:27 PM
  • Of course, you can do it at the end of main, or when your main form closes. It really depends on the design of your application.


    Should I just call the myConnection.Close() method at the end of the Main without conditional logic?

    No, because if your application somehow crashes the connection will remain open. Better on the finally branch of a try/catch.

    Regards
    Saturday, January 5, 2008 9:39 PM
  •  marcel nita wrote:
    This is where you are wrong.
    If you use a connection intensively then it is recommended to create the connection once, when the app starts and keep it alive throughout the application. The connection should be closed when the application exits.

     

    I'm sorry, Marcel, but this is where *you're* wrong. It is *not* good practice to keep the connection alive (I assume you mean open) throughout the life of the application and not close it until the application exits. Read this link about Connection Pooling:

     

    http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx

     

    New Guy, Marcel is correct, though, about separating your DataAccess from your UI. Here's a little something I wrote awhile back explaining some basic DataAccess stuff:

     

    Your DataAccess class should retrieve/pass DataSets between your database and your front-end UI (I throw a Web Service in between the

    front-end client-side stuff and the back-end server-side stuff, but let's just talk about DataAccess for now).

     

    First, you have a base DataAccess class that all of your other DataAccess classes should inherit from, and get the connection in it's

    constructor:

     

    Code Block

    public class MyDataAccess

    {

                protected SqlConnection oConnection;

     

                public MyDataAccess

                {

                            // Note that I wouldn't actually hard-code the connection string like this. It should be in

                            // your config settings. All the examples I currently have, use the old 1.1 way of doing this,

                            // which is this:

                            // string MyConnString = ConfigurationSettings.AppSettings["ConnectionString"];

     

                            this.oConnection = new SqlConnection("server=(local);database=MyDataBase;uid=sa;pwd=MyPassword");

                }

    }

     

     

     

    Depending on how complicated your application is, you may want to have a separate DataAccess class for different functionality, all sub-classing from the MyDataAccess base class.

      

    Retrieving the data is easy. Note that this DataAccess class returns a DataSet:

     

    Code Block

    public DataSet GetMyData()

    {

                // Note that a Fill does not require a Connection Open/Close. The Fill leaves

                // the connection in the state it was found (leaves it open if it was open,

                // and if it was closed, it opens it, Fills, then closes again).

     

                SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);

                DataSet ds = new DataSet();

                da.Fill(ds, "MyTable");

     

                return ds;

    }

     

     

    There are a few more options when updating the database.

     

    First, you can use the Update method of the DataAdapter. In order for this to work, your DataSet must have a PrimaryKey defined.

     

    You can do it using the CommandBuilder, which will generate update commands for you (note: if you use a Stored Proc, the CommandBuilder only

    generates the proper insert/update/delete commands for the first table retreived from the Stored Proc):

     

    Code Block

    public void UpdateMyData(DataSet ds)

    {

                // The same applies for the Update. It's not necessary to Open/Close the connection.

                SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);

                SqlCommandBuilder sb = new SqlCommandBuilder(da);

     

                da.Update(ds);

    }

     

     

     

    Or you can create the various update commands yourself instead of using the CommandBuilder:

     

    Code Block

    public void UpdateMyData(DataSet ds)

    {

     

                SqlCommand sc = new SqlCommand();

                sc.Connection = this.oConnection;

                da = new SqlDataAdapter(sc);

     

                da.InsertCommand = new SqlCommand("Insert into bob (xyz, abc) VALUES ( @xyz, @abc )", sc.Connection);

                da.InsertCommand.Parameters.Add("@xyz", SqlDbType.Int, 8, "xyz");

                da.InsertCommand.Parameters.Add("@abc", SqlDbType.VarChar, 50, "abc");

     

                // do the same for da.DeleteCommand & da.UpdateCommand

     

                da.Update(ds);

    }

     

     

     

    Or, you can take total control, not use the da.Update() and do it all yourself (this is basically the same code that gets done behind the

    scenes by the da.Update() method:

     

    Code Block

    public void UpdateMyData(DataSet ds)

    {

                SqlCommand sc = new SqlCommand();

                sc.Connection = this.oConnection;

                sc.Connection.Open();

                foreach (DataRow Row in ds.Tables[0].Rows)

                {

                            switch (Row.RowState)

                            {

                                        case DataRowState.Added :

                                                    sc.CommandText = "Insert into bob (xyz, abc) VALUES ( @xyz, @abc )";

                                                    sc.Parameters.Clear();

                                                    sc.Parameters.Add("@xyz", Row["xyz"]);

                                                    sc.Parameters.Add("@abc", Row["abc"]);

                                                    sc.ExecuteNonQuery();

                                                    break;

                                                   

                                        // Do the same for DataRowState Deleted and Modified

                                        case DataRowState.Deleted :

                                                    break;

                                        case DataRowState.Modified :

                                                    break;

                            }

                }

                sc.Connection.Close();

    }

     

     

     

    I hope this helps clear up some of your questions.

    Sunday, January 6, 2008 11:43 PM

  • I'm sorry, Marcel, but this is where *you're* wrong. It is *not* good practice to keep the connection alive (I assume you mean open) throughout the life of the application and not close it until the application exits. Read this link about Connection Pooling:

    Cool... That was my (incorrect) view of things...
    Read it and and learned it.

    Regards
    Sunday, January 6, 2008 11:54 PM
  • That's cool Marcel ... glad I could clear that up for you. 

    Monday, January 7, 2008 12:08 AM