SqlConnection wondering

Thanks a lot. Thats a very usefull information.

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.

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.