SqlConnection wondering

Question

Hi all

Generic question about SqlConnection... What is better?

Storing the connection in Session and use the same on for all queries?

OR

Every time i need to run a query to create a new instance of SqlConnection?

Thanks in advanced.

eddy uk · Answer

Thanks a lot. Thats a very usefull information.

Satish Kathi · Answer

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.

eddy uk · Answer

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 Farooq · Answer

Please give more detail what you need ?