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 mySqlTagsAdapte r;

private string strQuery;

public DBLayer()

{

strQuery = "";

conn = new
SqlConnection(C onfigurationSet tings.AppSettin gs["ConnectionStri ng"]);

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]

Re: open and close database connection in classes

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 mySqlTagsAdapte r;
>
private string strQuery;
>
public DBLayer()
>
{
>
strQuery = "";
>
conn = new
SqlConnection(C onfigurationSet tings.AppSettin gs["ConnectionStri ng"]);
>
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.

[Michael Nemtsev]

RE: open and close database connection in classes

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 mySqlTagsAdapte r;
>
private string strQuery;
>
public DBLayer()
>
{
>
strQuery = "";
>
conn = new
SqlConnection(C onfigurationSet tings.AppSettin gs["ConnectionStri ng"]);
>
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.
>
>
>

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

Re: open and close database connection in classes

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" <imran@tb2.netw rote in message
news:e$bBE3GwGH A.4432@TK2MSFTN GP04.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 mySqlTagsAdapte r;
>
private string strQuery;
>
public DBLayer()
>
{
>
strQuery = "";
>
conn = new
SqlConnection(C onfigurationSet tings.AppSettin gs["ConnectionStri ng"]);
>
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]

Re: open and close database connection in classes

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


"Michael Nemtsev" <MichaelNemtsev @discussions.mi crosoft.comwrot e in
message news:E1724138-A217-4124-9D4F-084594D5EE81@mi crosoft.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 mySqlTagsAdapte r;
>>
>private string strQuery;
>>
>public DBLayer()
>>
>{
>>
>strQuery = "";
>>
>conn = new
>SqlConnection( ConfigurationSe ttings.AppSetti ngs["ConnectionStri ng"]);
>>
>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]

Re: open and close database connection in classes

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

"John Duval" <JohnMDuval@gma il.comwrote in message
news:1155650237 .706596.247990@ m73g2000cwd.goo glegroups.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 mySqlTagsAdapte r;
>>
>private string strQuery;
>>
>public DBLayer()
>>
>{
>>
>strQuery = "";
>>
>conn = new
>SqlConnection( ConfigurationSe ttings.AppSetti ngs["ConnectionStri ng"]);
>>
>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.

>

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

Re: open and close database connection in classes

Hi,

"Imran Aziz" <imran@tb2.netw rote in message
news:O8V$3aHwGH A.3964@TK2MSFTN GP04.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

[Imran Aziz]

Re: open and close database connection in classes

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.mach in AT dot.state.fl.us wrote
in message news:uoTlMdHwGH A.2204@TK2MSFTN GP03.phx.gbl...

Hi,
>
"Imran Aziz" <imran@tb2.netw rote in message
news:O8V$3aHwGH A.3964@TK2MSFTN GP04.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
>

[Imran Aziz]

Re: open and close database connection in classes

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

Imran.

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.mach in AT dot.state.fl.us wrote
in message news:e8HdTaHwGH A.1512@TK2MSFTN GP04.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" <imran@tb2.netw rote in message
news:e$bBE3GwGH A.4432@TK2MSFTN GP04.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 mySqlTagsAdapte r;
>>
>private string strQuery;
>>
>public DBLayer()
>>
>{
>>
>strQuery = "";
>>
>conn = new
>SqlConnection( ConfigurationSe ttings.AppSetti ngs["ConnectionStri ng"]);
>>
>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.
>>
>>

>
>