open and close database connection in classes

[Imran Aziz]

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.

 

[John Duval]

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

 

[Guest]

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

 

[Ignacio Machin \( .NET/ C# MVP \)]

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 \( .NET/ C# MVP \)]

Hi,

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.

 

[Imran Aziz]

Thanks for the response, I am going to close them explicitly now.

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

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.

 

[Imran Aziz]

Thanks for the response John, I am going to close them explicitly now.

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

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.

 

[Imran Aziz]

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.

 

[Imran Aziz]

Thank you very much for the detailed explanation, I am following this now.

Imran.

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

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.