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 DatabaseConnect ion 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.E xecTimeOut=10;
NuclearWeapon.L aunch=True;
OhWhatIHaveDone ();

[Hans Kesting]

Re: SQL Connection Example

> Hello[color=blue]
>
> 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 DatabaseConnect ion 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)[/color]

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.ExecuteNonQ uery();
}
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]

Re: SQL Connection Example

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.ExecuteNonQ uery();
}
finally
{
cmd.Connection. Close();
}

can be written as:

using(SqlConnec tion 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(SqlConnec tion connection = new SqlConnection() )
using(SqlComman d command = new SqlCommand())
{
}

Hope that helps
Mark Dawson

http://www.markdawson.org

"Hans Kesting" wrote:
[color=blue][color=green]
> > 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 DatabaseConnect ion 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)[/color]
>
> 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.ExecuteNonQ uery();
> }
> 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
>
>
>[/color]

[Otis Mukinfus]

Re: SQL Connection Example

On Thu, 09 Feb 2006 13:13:48 +0100, "Hans Kesting"
<news.2.hansdk@ spamgourmet.com > wrote:
[color=blue][color=green]
>> 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 DatabaseConnect ion 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)[/color]
>
>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.ExecuteNonQ uery();
>}
>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
>[/color]
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]

Re: SQL Connection Example

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

Here i have placed my sample DatabaseConnect ion 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.Sql Client;
using System.Componen tModel;

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

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

/// <summary>
/// Opens the connection with the SQL Server database
/// </summary>
public void OpenConnection( )
{
sSQLConn =
System.Configur ation.Configura tionSettings.Ap pSettings["DBConnectionSt ring"];
sSQLConn +=
System.Configur ation.Configura tionSettings.Ap pSettings["DBUserName "];
sSQLConn +=
System.Configur ation.Configura tionSettings.Ap pSettings["DBServerNa me"];
SQLConnection = new SqlConnection(s SQLConn);
SQLConnection.O pen();
}

/// <summary>
/// Closes the opened connection
/// </summary>
public void CloseConnection ()
{
if(SQLConnectio n.State == ConnectionState .Open)
{
SQLConnection.C lose();
SQLConnection.D ispose();
this.Dispose();
}
}

public void Dispose()
{
component.Dispo se();
GC.SuppressFina lize(this);
}
}
}

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

=========
NuclearWeapon.E xecTimeOut=10;
NuclearWeapon.L aunch=True;
OhWhatIHaveDone ();

[Hans Kesting]

Re: SQL Connection Example

> Thank you Hans Kesting, Mark R. Dawson and Otis Mukinfus for replying to my[color=blue]
> post
>
> Here i have placed my sample DatabaseConnect ion 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.Sql Client;
> using System.Componen tModel;
>
> namespace GREW
> {
> /// <summary>
> /// DatabaseConnect ion Class, used to establish the connection with the SQL
> server
> /// </summary>
> class DatabaseConnect ion : IDisposable
> {
> Component component = new Component();
>
> public SqlConnection SQLConnection;
> string sSQLConn;
> internal DatabaseConnect ion()
> {
> //
> // TODO: Add constructor logic here
> //
> }
>
> /// <summary>
> /// Opens the connection with the SQL Server database
> /// </summary>
> public void OpenConnection( )
> {
> sSQLConn =
> System.Configur ation.Configura tionSettings.Ap pSettings["DBConnectionSt ring"];
> sSQLConn +=
> System.Configur ation.Configura tionSettings.Ap pSettings["DBUserName "];
> sSQLConn +=
> System.Configur ation.Configura tionSettings.Ap pSettings["DBServerNa me"];
> SQLConnection = new SqlConnection(s SQLConn);
> SQLConnection.O pen();
> }
>
> /// <summary>
> /// Closes the opened connection
> /// </summary>
> public void CloseConnection ()
> {
> if(SQLConnectio n.State == ConnectionState .Open)
> {
> SQLConnection.C lose();
> SQLConnection.D ispose();
> this.Dispose();
> }
> }
>
> public void Dispose()
> {
> component.Dispo se();
> GC.SuppressFina lize(this);
> }
> }
> }
>
> I will be waiting fo your responses. please do reply. And Thanks once again
> for your precious time.
>
> =========
> NuclearWeapon.E xecTimeOut=10;
> NuclearWeapon.L aunch=True;
> OhWhatIHaveDone ();[/color]


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

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

In "Dispose", also call CloseConnection .

Hans Kesting

[Beau Peep]

Re: SQL Connection Example

Hello Hans Kesting

[AS OF MY KNOWLEDGE]

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

The reasone is, when we use "Dispose()" method of any object provided by
microsoft
it releases all resources used by "System.Compone nt" internally. So if you
have some
10 objects created and never "Dispose()" method is called, if you create a
object of
"Component" from System.Componen t 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.E xecTimeOut=10;
NuclearWeapon.L aunch=True;
OhWhatIHaveDone ();

[Otis Mukinfus]

Re: SQL Connection Example

On Thu, 9 Feb 2006 20:39:11 -0800, Beau Peep
<BeauPeep@discu ssions.microsof t.com> wrote:

[snip]
One comment Beau.
[color=blue]
> if(SQLConnectio n.State == ConnectionState .Open)
> {
> SQLConnection.C lose();
> SQLConnection.D ispose();
> this.Dispose();
> }[/color]

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(SqlConnectio n.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]

Re: SQL Connection Example

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.E xecTimeOut=10;
NuclearWeapon.L aunch=True;
OhWhatIHaveDone ();



"Otis Mukinfus" wrote:
[color=blue]
> On Thu, 9 Feb 2006 20:39:11 -0800, Beau Peep
> <BeauPeep@discu ssions.microsof t.com> wrote:
>
> [snip]
> One comment Beau.
>[color=green]
> > if(SQLConnectio n.State == ConnectionState .Open)
> > {
> > SQLConnection.C lose();
> > SQLConnection.D ispose();
> > this.Dispose();
> > }[/color]
>
> 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(SqlConnectio n.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
>[/color]