SqlConnection wondering

Sign in to answer this question.

• EU

  eddy uk18y agoPosted Aug 15, 2008, 10:22 AM

  Thanks a lot. Thats a very usefull information.
• SK

  Satish Kathi18y agoPosted Aug 15, 2008, 10:13 AM

  Connections are valuable resources. You have to open a connection when you need and close immediately when you are finished with it. Here is one article on SQL Connection

  http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx

  And you also go through the topic “Connection Pooling”

  http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

   

  Hope this clarifies.
• EU

  eddy uk18y agoPosted Aug 15, 2008, 2:47 AM

  I have a class that handles database transactions. And i have
  SqlConnection defined in that class. I create it and open it
  in a constructor and then, before executing any query i just
  check connection state and then running the query:

  public class TMgr
  {
   private string connStr;
   private SqlConnection conn;
   private DataSet ds;
   
   public TMgr(string connStr)
   {
    this.connStr = connStr;
    conn = new SqlConnection(connStr);
    CONN.Open();
    ds = new DataSet();
   } 

   public void Executer(string query)
   {
    try
    {
     SqlDataAdapter da = new SqlDataAdapter(query, conn);
     if(conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
     {
      conn = new SqlConnection(connStr);
      CONN.Open();
     }
     ds.Reset();
     da.Fill(ds);
     da.Dispose();
    }
    catch
    {
    }
   }
   
   public DataTable GetTable()
   {
    return ds.Tables[0];
   }
  }

  I store instance of it in Session and use it on every .aspx page
  in my application:

  protected void Page_Load(object sender, EventArgs e)
  {
   TMgr sql = (TMgr)Session["TMgr"];
   string query = "SELECT * FROM online_usr";
   sql.Executer(query);
   //
   // some actions goes here
   //
   query = "SELECT * FROM registered_usr WHERE id=" + Convert.ToString(sql.GetTable().Rows[x][y]);
   sql.Executer(query);
   //
   // some actions goes here
   //
  }

  So I wounder if its better to keep the SqlConnection in TMgr class
  as it is now, or make new one on every query in Executer class, like:

  public class TMgr
  {
   private string connStr;
   private DataSet ds;
   
   public TMgr(string connStr)
   {
    this.connStr = connStr;
    ds = new DataSet();
   } 

   public void Executer(string query)
   {
    SqlConnection conn = null;
    try
    { 
     using(conn = new SqlConnection(connStr))
     {
      SqlDataAdapter da = new SqlDataAdapter(query, conn);
      ds.Reset();
      da.Fill(ds);
      da.Dispose();
     }
    }
    catch
    {
    }
    finally
    { 
     if(conn != null)
      if(conn.State == ConnectionState.Open)
       conn.Close();
    }
   }
  }

  Thanks in advanced.
• 

  Muhammad Shahid Farooq18y agoPosted Aug 14, 2008, 6:39 PM

  Please give more detail, what you need ?