SQL Connection Example

[Beau Peep]

Hello

Can any one show me whats the best way to open the SQL connection?.

I am creating a ClassLibray (.dll) file, Methods, Structures, Enumerations are
created in this dll file.

Here we have more than 1000 customers connected at the same time. I have
created a DatabaseConnection Class and I am using it in all modules in that
dll.
This is a web project. can any one show me how to use SQL connection the dll
file and used in the modules, so it should not slow up the process for the
logged
in users.

tanks for time. (its a exigency)
--
=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();

[Hans Kesting]

> Hello

Can any one show me whats the best way to open the SQL connection?.

I am creating a ClassLibray (.dll) file, Methods, Structures, Enumerations
are created in this dll file.

Here we have more than 1000 customers connected at the same time. I have
created a DatabaseConnection Class and I am using it in all modules in that
dll.
This is a web project. can any one show me how to use SQL connection the dll
file and used in the modules, so it should not slow up the process for the
logged
in users.

tanks for time. (its a exigency)

Especially for a webapplication: Open() as late as possible, Close() as
soon as possible, preferably within the "finally" of a
try(/catch)/finally block.

Something like:

try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
finally
{
cmd.Connection.Close();
}

This way the connections are put back in the connection-pool as soon as
possible, so they can be re-used by other database calls (maybe from
other users).

Hans Kesting

[Mark R. Dawson]

I agree with Hans about keeping the connection utilized for a short as
possible. A nice keyword in C#, the "using" keyword, can be used to save you
from having to call Close in a finally. Instead the using statement
guarantees that the Dispose method is called when you go outside of the scope
of the {}, even in the case when an exceptio is thrown, for example:

try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
finally
{
cmd.Connection.Close();
}

can be written as:

using(SqlConnection connection = new SqlConnection())
{
//initialize conenction settings

connection.Open();

//do work
}

when the using brace is exited Dispose is called on the conenction, Dispose
internally will call Close and release your connection back to the connection
pool.

You can also stack using statements for easier readability, rather than
embedding them inside one another, like:

using(SqlConnection connection = new SqlConnection())
using(SqlCommand command = new SqlCommand())
{
}

Hope that helps
Mark Dawson
http://www.markdawson.org
"Hans Kesting" wrote:

Hello

Can any one show me whats the best way to open the SQL connection?.

I am creating a ClassLibray (.dll) file, Methods, Structures, Enumerations
are created in this dll file.

Here we have more than 1000 customers connected at the same time. I have
created a DatabaseConnection Class and I am using it in all modules in that
dll.
This is a web project. can any one show me how to use SQL connection the dll
file and used in the modules, so it should not slow up the process for the
logged
in users.

tanks for time. (its a exigency)

Especially for a webapplication: Open() as late as possible, Close() as
soon as possible, preferably within the "finally" of a
try(/catch)/finally block.

Something like:

try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
finally
{
cmd.Connection.Close();
}

This way the connections are put back in the connection-pool as soon as
possible, so they can be re-used by other database calls (maybe from
other users).

Hans Kesting

[Otis Mukinfus]

On Thu, 09 Feb 2006 13:13:48 +0100, "Hans Kesting"
<ne***********@spamgourmet.com> wrote:

Hello

Can any one show me whats the best way to open the SQL connection?.

I am creating a ClassLibray (.dll) file, Methods, Structures, Enumerations
are created in this dll file.

Here we have more than 1000 customers connected at the same time. I have
created a DatabaseConnection Class and I am using it in all modules in that
dll.
This is a web project. can any one show me how to use SQL connection the dll
file and used in the modules, so it should not slow up the process for the
logged
in users.

tanks for time. (its a exigency)

Especially for a webapplication: Open() as late as possible, Close() as
soon as possible, preferably within the "finally" of a
try(/catch)/finally block.

Something like:

try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
finally
{
cmd.Connection.Close();
}

This way the connections are put back in the connection-pool as soon as
possible, so they can be re-used by other database calls (maybe from
other users).

Hans Kesting

I'm currently assigned to do improvements on a web app that does
exactly what Beau says he has done. One of those *critical*
improvements is to remove the code from all of the places it was done
and open and close connections only as long as they are needed.

Your instructions to Beau are correct.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

[Beau Peep]

Thank you Hans Kesting, Mark R. Dawson and Otis Mukinfus for replying to my
post

Here i have placed my sample DatabaseConnection Class code exactly what i have
writte. please look to it and let me know your valuable suggestions on this,
which point to improve and stuff like that. Let me know all what you have to
say.

using System;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;

namespace GREW
{
/// <summary>
/// DatabaseConnection Class, used to establish the connection with the SQL
server
/// </summary>
class DatabaseConnection : IDisposable
{
Component component = new Component();

public SqlConnection SQLConnection;
string sSQLConn;
internal DatabaseConnection()
{
//
// TODO: Add constructor logic here
//
}

/// <summary>
/// Opens the connection with the SQL Server database
/// </summary>
public void OpenConnection()
{
sSQLConn =
System.Configuration.ConfigurationSettings.AppSett ings["DBConnectionString"];
sSQLConn +=
System.Configuration.ConfigurationSettings.AppSett ings["DBUserName"];
sSQLConn +=
System.Configuration.ConfigurationSettings.AppSett ings["DBServerName"];
SQLConnection = new SqlConnection(sSQLConn);
SQLConnection.Open();
}

/// <summary>
/// Closes the opened connection
/// </summary>
public void CloseConnection()
{
if(SQLConnection.State == ConnectionState.Open)
{
SQLConnection.Close();
SQLConnection.Dispose();
this.Dispose();
}
}

public void Dispose()
{
component.Dispose();
GC.SuppressFinalize(this);
}
}
}

I will be waiting fo your responses. please do reply. And Thanks once again
for your precious time.

=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();

[Hans Kesting]

> Thank you Hans Kesting, Mark R. Dawson and Otis Mukinfus for replying to my

post

Here i have placed my sample DatabaseConnection Class code exactly what i
have writte. please look to it and let me know your valuable suggestions on
this, which point to improve and stuff like that. Let me know all what you
have to say.

using System;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;

namespace GREW
{
/// <summary>
/// DatabaseConnection Class, used to establish the connection with the SQL
server
/// </summary>
class DatabaseConnection : IDisposable
{
Component component = new Component();

public SqlConnection SQLConnection;
string sSQLConn;
internal DatabaseConnection()
{
//
// TODO: Add constructor logic here
//
}

/// <summary>
/// Opens the connection with the SQL Server database
/// </summary>
public void OpenConnection()
{
sSQLConn =
System.Configuration.ConfigurationSettings.AppSett ings["DBConnectionString"];
sSQLConn +=
System.Configuration.ConfigurationSettings.AppSett ings["DBUserName"];
sSQLConn +=
System.Configuration.ConfigurationSettings.AppSett ings["DBServerName"];
SQLConnection = new SqlConnection(sSQLConn);
SQLConnection.Open();
}

/// <summary>
/// Closes the opened connection
/// </summary>
public void CloseConnection()
{
if(SQLConnection.State == ConnectionState.Open)
{
SQLConnection.Close();
SQLConnection.Dispose();
this.Dispose();
}
}

public void Dispose()
{
component.Dispose();
GC.SuppressFinalize(this);
}
}
}

I will be waiting fo your responses. please do reply. And Thanks once again
for your precious time.

=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();

I'm not sure why you would need that "component" here.

In "CloseConnection", you might want to check if the connection is not
"null".

In "Dispose", also call CloseConnection.

Hans Kesting

[Beau Peep]

Hello Hans Kesting

[AS OF MY KNOWLEDGE]

Actually, I am creating a new instance of the "Component" from the
System.Component Module.

The reasone is, when we use "Dispose()" method of any object provided by
microsoft
it releases all resources used by "System.Component" internally. So if you
have some
10 objects created and never "Dispose()" method is called, if you create a
object of
"Component" from System.Component and if you call "Dispose()" method there,
all
other 10 object's resources are released along with "Component's" resources
too.

So I have created a object of that there and disposing it in my own dispose
method.
So i dont have to worry about the other object creations.

What actually you thought on that "Component" object? Is there any better
way to
create the class file for database connection other that this?

Thanks for your time in replying back to me Hans Kesting.

Hope to see your response as soon.
--
=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();

[Otis Mukinfus]

On Thu, 9 Feb 2006 20:39:11 -0800, Beau Peep
<Be******@discussions.microsoft.com> wrote:

[snip]
One comment Beau.

if(SQLConnection.State == ConnectionState.Open)
{
SQLConnection.Close();
SQLConnection.Dispose();
this.Dispose();
}

In the above code you are closing the connection only if it is open.
Since connections can be in other states I check for

if(SqlConnection.State != ConnectionState.Closed)
{
// do closing code here
}

Doing so assures that you really did get it closed, even if it's in
another state.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

[Beau Peep]

Thank you , Otis Mukinfus.

I implemented your suggestion of the code on closing connection and made it
exactly as you said.

Is there any thing you see more from the point of improvement? If yes, let
me know.

Thanks a lot for your valuable suggestion.
--
=========
NuclearWeapon.ExecTimeOut=10;
NuclearWeapon.Launch=True;
OhWhatIHaveDone();

"Otis Mukinfus" wrote:

On Thu, 9 Feb 2006 20:39:11 -0800, Beau Peep
<Be******@discussions.microsoft.com> wrote:

[snip]
One comment Beau.

if(SQLConnection.State == ConnectionState.Open)
{
SQLConnection.Close();
SQLConnection.Dispose();
this.Dispose();
}

In the above code you are closing the connection only if it is open.
Since connections can be in other states I check for

if(SqlConnection.State != ConnectionState.Closed)
{
// do closing code here
}

Doing so assures that you really did get it closed, even if it's in
another state.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com