By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,696 Members | 1,486 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,696 IT Pros & Developers. It's quick & easy.

open and close database connection in classes

P: n/a
Hello All,
Like in C++ I tried to use constructor to open a database connection and
distructor to close the database connection, it now turns out that one
cannot create distrutors in C# classes.

Here is my code

public class DBLayer

{

private string strError;

private SqlConnection conn;

private SqlDataAdapter mySqlTagsAdapter;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

strError = "";

try

{

conn.Open();

}

catch (Exception ex)

{

strError = "Unable to open the database " + ex.ToString();

}

}

~DBLayer()

{

if (conn != null)

{

if (conn.State != ConnectionState.Closed)

{

try

{

conn.Close();

}

catch (Exception ex)

{

strError = "Unable to close the database " + ex.ToString();

};

}

}

}

}

Can you suggest how to sort out this issue now, I have already got lot of
classes and code that uses this approach and now the connections are not
being closed, can you please suggest a solution how to sort this out. How
can I close connection in the classes when the calling asp.net page ends
execution.

Regards,

Imran.
Aug 15 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Hi Imran,
One way to accomplish this by making a disposable object and use the
"using" construct. The idea is that the object's Dispose( ) method
will be called when the using block is exited (much like a dtor for a
stack object in C++).

John

Imran Aziz wrote:
Hello All,
Like in C++ I tried to use constructor to open a database connection and
distructor to close the database connection, it now turns out that one
cannot create distrutors in C# classes.

Here is my code

public class DBLayer

{

private string strError;

private SqlConnection conn;

private SqlDataAdapter mySqlTagsAdapter;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

strError = "";

try

{

conn.Open();

}

catch (Exception ex)

{

strError = "Unable to open the database " + ex.ToString();

}

}

~DBLayer()

{

if (conn != null)

{

if (conn.State != ConnectionState.Closed)

{

try

{

conn.Close();

}

catch (Exception ex)

{

strError = "Unable to close the database " + ex.ToString();

};

}

}

}

}

Can you suggest how to sort out this issue now, I have already got lot of
classes and code that uses this approach and now the connections are not
being closed, can you please suggest a solution how to sort this out. How
can I close connection in the classes when the calling asp.net page ends
execution.

Regards,

Imran.
Aug 15 '06 #2

P: n/a
Close connection manually (or use using statement for it)

PS: and forget about descructors in .net :)

--
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche


"Imran Aziz" wrote:
Hello All,
Like in C++ I tried to use constructor to open a database connection and
distructor to close the database connection, it now turns out that one
cannot create distrutors in C# classes.

Here is my code

public class DBLayer

{

private string strError;

private SqlConnection conn;

private SqlDataAdapter mySqlTagsAdapter;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

strError = "";

try

{

conn.Open();

}

catch (Exception ex)

{

strError = "Unable to open the database " + ex.ToString();

}

}

~DBLayer()

{

if (conn != null)

{

if (conn.State != ConnectionState.Closed)

{

try

{

conn.Close();

}

catch (Exception ex)

{

strError = "Unable to close the database " + ex.ToString();

};

}

}

}

}

Can you suggest how to sort out this issue now, I have already got lot of
classes and code that uses this approach and now the connections are not
being closed, can you please suggest a solution how to sort this out. How
can I close connection in the classes when the calling asp.net page ends
execution.

Regards,

Imran.
Aug 15 '06 #3

P: n/a
Hi,

You can have a destructor in C# too.

But that is another matter, you should keep your connection open the minimun
time possible ! , I would suggest you to just open/close the connection in
each method that you need it.
like

void DoSomething()
{
using (SqlConnection con = new SqlConnection(......) )
{
}
}

This will allow your app to scale. If you keep connections open you are
holding resources for nothing. ADO.NET use a connection pooling that handles
the connection status for you.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Imran Aziz" <im***@tb2.netwrote in message
news:e$**************@TK2MSFTNGP04.phx.gbl...
Hello All,
Like in C++ I tried to use constructor to open a database connection
and distructor to close the database connection, it now turns out that one
cannot create distrutors in C# classes.

Here is my code

public class DBLayer

{

private string strError;

private SqlConnection conn;

private SqlDataAdapter mySqlTagsAdapter;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

strError = "";

try

{

conn.Open();

}

catch (Exception ex)

{

strError = "Unable to open the database " + ex.ToString();

}

}

~DBLayer()

{

if (conn != null)

{

if (conn.State != ConnectionState.Closed)

{

try

{

conn.Close();

}

catch (Exception ex)

{

strError = "Unable to close the database " + ex.ToString();

};

}

}

}

}

Can you suggest how to sort out this issue now, I have already got lot of
classes and code that uses this approach and now the connections are not
being closed, can you please suggest a solution how to sort this out. How
can I close connection in the classes when the calling asp.net page ends
execution.

Regards,

Imran.


Aug 15 '06 #4

P: n/a
Thanks for the response, I am going to close them explicitly now.
"Michael Nemtsev" <Mi************@discussions.microsoft.comwrote in
message news:E1**********************************@microsof t.com...
Close connection manually (or use using statement for it)

PS: and forget about descructors in .net :)

--
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do
not
cease to be insipid." (c) Friedrich Nietzsche


"Imran Aziz" wrote:
>Hello All,
Like in C++ I tried to use constructor to open a database connection
and
distructor to close the database connection, it now turns out that one
cannot create distrutors in C# classes.

Here is my code

public class DBLayer

{

private string strError;

private SqlConnection conn;

private SqlDataAdapter mySqlTagsAdapter;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

strError = "";

try

{

conn.Open();

}

catch (Exception ex)

{

strError = "Unable to open the database " + ex.ToString();

}

}

~DBLayer()

{

if (conn != null)

{

if (conn.State != ConnectionState.Closed)

{

try

{

conn.Close();

}

catch (Exception ex)

{

strError = "Unable to close the database " + ex.ToString();

};

}

}

}

}

Can you suggest how to sort out this issue now, I have already got lot of
classes and code that uses this approach and now the connections are not
being closed, can you please suggest a solution how to sort this out. How
can I close connection in the classes when the calling asp.net page ends
execution.

Regards,

Imran.

Aug 15 '06 #5

P: n/a
Thanks for the response John, I am going to close them explicitly now.

"John Duval" <Jo********@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Hi Imran,
One way to accomplish this by making a disposable object and use the
"using" construct. The idea is that the object's Dispose( ) method
will be called when the using block is exited (much like a dtor for a
stack object in C++).

John

Imran Aziz wrote:
>Hello All,
Like in C++ I tried to use constructor to open a database connection
and
distructor to close the database connection, it now turns out that one
cannot create distrutors in C# classes.

Here is my code

public class DBLayer

{

private string strError;

private SqlConnection conn;

private SqlDataAdapter mySqlTagsAdapter;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

strError = "";

try

{

conn.Open();

}

catch (Exception ex)

{

strError = "Unable to open the database " + ex.ToString();

}

}

~DBLayer()

{

if (conn != null)

{

if (conn.State != ConnectionState.Closed)

{

try

{

conn.Close();

}

catch (Exception ex)

{

strError = "Unable to close the database " + ex.ToString();

};

}

}

}

}

Can you suggest how to sort out this issue now, I have already got lot of
classes and code that uses this approach and now the connections are not
being closed, can you please suggest a solution how to sort this out. How
can I close connection in the classes when the calling asp.net page ends
execution.

Regards,

Imran.

Aug 15 '06 #6

P: n/a
Hi,

"Imran Aziz" <im***@tb2.netwrote in message
news:O8**************@TK2MSFTNGP04.phx.gbl...
Thanks for the response, I am going to close them explicitly now.
The recommended coding is wrapping the conenction class with an using
statement, this will assure that even if an exception occur the connection
is closed.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
Aug 15 '06 #7

P: n/a
Thanks for that, I will surly use this from now on, the issue at the moment
however is that I have got around 15 classes already using this technique,
the constructor opens the connections hence in order to use the using option
I will endup altering all methods which is quite a big task, so for now I am
just closing the connection making sure I close it in try/catch blocks as
well .

Thanks a lot,

Imran.
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.uswrote
in message news:uo**************@TK2MSFTNGP03.phx.gbl...
Hi,

"Imran Aziz" <im***@tb2.netwrote in message
news:O8**************@TK2MSFTNGP04.phx.gbl...
>Thanks for the response, I am going to close them explicitly now.

The recommended coding is wrapping the conenction class with an using
statement, this will assure that even if an exception occur the connection
is closed.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Aug 15 '06 #8

P: n/a
Thank you very much for the detailed explanation, I am following this now.

Imran.

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.uswrote
in message news:e8**************@TK2MSFTNGP04.phx.gbl...
Hi,

You can have a destructor in C# too.

But that is another matter, you should keep your connection open the
minimun time possible ! , I would suggest you to just open/close the
connection in each method that you need it.
like

void DoSomething()
{
using (SqlConnection con = new SqlConnection(......) )
{
}
}

This will allow your app to scale. If you keep connections open you are
holding resources for nothing. ADO.NET use a connection pooling that
handles the connection status for you.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Imran Aziz" <im***@tb2.netwrote in message
news:e$**************@TK2MSFTNGP04.phx.gbl...
>Hello All,
Like in C++ I tried to use constructor to open a database connection
and distructor to close the database connection, it now turns out that
one cannot create distrutors in C# classes.

Here is my code

public class DBLayer

{

private string strError;

private SqlConnection conn;

private SqlDataAdapter mySqlTagsAdapter;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

strError = "";

try

{

conn.Open();

}

catch (Exception ex)

{

strError = "Unable to open the database " + ex.ToString();

}

}

~DBLayer()

{

if (conn != null)

{

if (conn.State != ConnectionState.Closed)

{

try

{

conn.Close();

}

catch (Exception ex)

{

strError = "Unable to close the database " + ex.ToString();

};

}

}

}

}

Can you suggest how to sort out this issue now, I have already got lot of
classes and code that uses this approach and now the connections are not
being closed, can you please suggest a solution how to sort this out. How
can I close connection in the classes when the calling asp.net page ends
execution.

Regards,

Imran.



Aug 15 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.