locked
Create a separated class for database connection RRS feed

  • Question

  • User539757411 posted

    Hi,

    i have the following code:

            string myConnectionString = "Data Source=xxx.xxx.com; database=example; uid=example; pwd=xxxx; connection timeout = 10";
    SqlConnection myConnection = new SqlConnection(myConnectionString);
     myConnection.Open();
    string myInsertQuery = "SELECT * FROM aspnet_Users";
    SqlCommand myCommand = new SqlCommand(myInsertQuery, myConnection);
    //myCommand.ExecuteNonQuery();
    GridView1.DataSource = myCommand.ExecuteReader();
    GridView1.DataBind();
    myCommand.Connection.Close();

    Example of class Connection.cs:

    public void Connect()
    {
    string myConnectionString = "Data Source=xxx.xxx.com; database=example; uid=example; pwd=xxxx; connection timeout = 10";
    SqlConnection myConnection = new SqlConnection(myConnectionString);
     myConnection.Open();
    }


    I was trying to implement as above.
    How can i call this class from the code behind of my page, and call the methods Open and Close connection ?
    How do i instantiate the object ?

    Friday, December 10, 2010 4:57 AM

Answers

  • User-952121411 posted

    I think you have the proper mentality about refactoring code and code reuse, but have hurt yourself a bit by making a wrapper around a wrapper with the SQLConnection object. Either explicitly calling myConnection.Close or by utilizing a 'Using' statement you can dispose of and close the connection.  The way you have refactored the code, it would be the caller's responsibility to still manually close the connection on the returned instance.  Trying to create an additional "Close" method of your own to wrap the native .Close" method on the SQLConnection object really shows no gain in my opinion.

    The idea of centrally locating the connection string is a good one (actually 1 better to refactor out into the web.config and encrypt the "connectionStrings" section with aspnet_regiis.exe tool). However the instantiation and return of the connection object I am not so sure of. Here is what I might suggest you try instead:

    public class Infrastructure
    {
    
      public static string DataBase1Connection 
      {
          //As mentioned prior, pulling this value from an encrypted "connectionStrings" section in the web.config is preferred to hard coding.
          get { return "Data Source=xxx.xxx.com; database=xxx; uid=xxx; pwd=xxx; connection timeout = 10"; }
       }
    }
    

     

    protected void Page_Load(object sender, EventArgs e) 
    {
        //Comments are direct from MSDN said best:
        //If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. 
        //Close and Dispose are functionally equivalent. The "End Using" statement automatically calls the .Dispose method on the SQLConnection
        //object ensuring clean up and that the connection is closed.
    
        using (SqlConnection myConnection = new SqlConnection(Infrastructure.DataBase1Connection)) 
        {
    
          //To ensure that connections are always closed, open the connection inside of this 'using' block. 
          //Doing so ensures that the connection is automatically closed when the code exits the block. 
          myConnection.Open();
    
          string myInsertQuery = "SELECT * FROM aspnet_Users";
          SqlCommand myCommand = new SqlCommand(myInsertQuery, myConnection);
          GridView1.DataSource = myCommand.ExecuteReader();
          GridView1.DataBind();
          object myCommand2 = new SqlCommand(myInsertQuery, myConnection);
          ListView1.DataSource = myCommand2.ExecuteReader();
          ListView1.DataBind();
        }
    }
    

    As you can see the important part (the connection) was refactored for reuse, but in your case there is not a lot of gain from wrapping .Open and .Close/.Dispose methods on a SQLConnection object that already native wrappers on the object itself.  You really had (3) lines of code with the Using, .Open, and End Using. 

    One last piece of advice, you have ADO.NET database calls and GridView binding in the same method, and my guess is all right behind an .aspx web page.  At some point in the future you might want to move into an introductory layered approach where separate layers have separate concerns and code is segregated to the appropriate areas.  This helps with scalability and many other factors, but that is a conversation for a different thread.

    Hope this helps! Smile

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 13, 2010 2:45 PM

All replies

  • User712082397 posted

    I am assuming that the name of class is Connection (say its within namespace ABC.Database - check what your case is)

    Ok, now in your code behind first include the namespace within that file using "using" keyword.

    After this whereever you want to create the object of Connection class you can write code similar to:

    Connection conn = new Connection(<pass parameters, if any, to constructor>);

    and then call the method on instance object "conn".

    conn.Connect();

    Friday, December 10, 2010 8:31 AM
  • User539757411 posted

    And what about Conn.Open() and Conn.Close ?
    I can´t figure out how to implement it.


    And how should i implement the method ?

    public static SqlConnection Connect() ?

    Friday, December 10, 2010 9:53 AM
  • User712082397 posted

    And what about Conn.Open() and Conn.Close ?
    I can´t figure out how to implement it.

    You are already having .Open in the Connect method. You would have to implement Close method for this.

    And how should i implement the method ?

    public static SqlConnection Connect() ?

    You can implement it like

    public SqlConnection Connect()

    This is just a basic guideline and the real implementation depends on what you would want to do within the method and with the return value.

    I would strongly recommend that you get a book on C# programming and start with basics. That would help you on a longer run. also, you can read at MSDN website here: http://msdn.microsoft.com/en-us/bb188199.aspx

    Friday, December 10, 2010 10:01 AM
  • User539757411 posted

    I did like this on my code behind page:

    protected void Page_Load(object sender, EventArgs e)
        {
            Connection myConnection = new Connection();
    
            string myInsertQuery = "SELECT * FROM aspnet_Users";
            SqlCommand myCommand = new SqlCommand(myInsertQuery, myConnection.Conectar());
            GridView1.DataSource = myCommand.ExecuteReader();
            GridView1.DataBind();
            var myCommand2 = new SqlCommand(myInsertQuery, myConnection.Conectar());
            ListView1.DataSource = myCommand2.ExecuteReader();
            ListView1.DataBind();
        }


    And in the connection class:

        public SqlConnection Connect()
        {
            string myConnectionString =
                "Data Source=xxx.xxx.com; database=xxx; uid=xxx; pwd=xxx; connection timeout = 10";
            SqlConnection myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            return myConnection;
        }



    It worked.

    1) How can i create a CloseConnection method ?
    2) How could i implement a connect method receveing the SQL query as parameter ?


    Thank you.

    Sunday, December 12, 2010 5:00 AM
  • User-952121411 posted

    I think you have the proper mentality about refactoring code and code reuse, but have hurt yourself a bit by making a wrapper around a wrapper with the SQLConnection object. Either explicitly calling myConnection.Close or by utilizing a 'Using' statement you can dispose of and close the connection.  The way you have refactored the code, it would be the caller's responsibility to still manually close the connection on the returned instance.  Trying to create an additional "Close" method of your own to wrap the native .Close" method on the SQLConnection object really shows no gain in my opinion.

    The idea of centrally locating the connection string is a good one (actually 1 better to refactor out into the web.config and encrypt the "connectionStrings" section with aspnet_regiis.exe tool). However the instantiation and return of the connection object I am not so sure of. Here is what I might suggest you try instead:

    public class Infrastructure
    {
    
      public static string DataBase1Connection 
      {
          //As mentioned prior, pulling this value from an encrypted "connectionStrings" section in the web.config is preferred to hard coding.
          get { return "Data Source=xxx.xxx.com; database=xxx; uid=xxx; pwd=xxx; connection timeout = 10"; }
       }
    }
    

     

    protected void Page_Load(object sender, EventArgs e) 
    {
        //Comments are direct from MSDN said best:
        //If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. 
        //Close and Dispose are functionally equivalent. The "End Using" statement automatically calls the .Dispose method on the SQLConnection
        //object ensuring clean up and that the connection is closed.
    
        using (SqlConnection myConnection = new SqlConnection(Infrastructure.DataBase1Connection)) 
        {
    
          //To ensure that connections are always closed, open the connection inside of this 'using' block. 
          //Doing so ensures that the connection is automatically closed when the code exits the block. 
          myConnection.Open();
    
          string myInsertQuery = "SELECT * FROM aspnet_Users";
          SqlCommand myCommand = new SqlCommand(myInsertQuery, myConnection);
          GridView1.DataSource = myCommand.ExecuteReader();
          GridView1.DataBind();
          object myCommand2 = new SqlCommand(myInsertQuery, myConnection);
          ListView1.DataSource = myCommand2.ExecuteReader();
          ListView1.DataBind();
        }
    }
    

    As you can see the important part (the connection) was refactored for reuse, but in your case there is not a lot of gain from wrapping .Open and .Close/.Dispose methods on a SQLConnection object that already native wrappers on the object itself.  You really had (3) lines of code with the Using, .Open, and End Using. 

    One last piece of advice, you have ADO.NET database calls and GridView binding in the same method, and my guess is all right behind an .aspx web page.  At some point in the future you might want to move into an introductory layered approach where separate layers have separate concerns and code is segregated to the appropriate areas.  This helps with scalability and many other factors, but that is a conversation for a different thread.

    Hope this helps! Smile

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 13, 2010 2:45 PM