Properly terminating connections

[Ivan Demkovitch]

Hi!

I use following code to execute SP's on SQL server from my web page:
// Create Instance of Connection and Command Object
SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlCommand myCommand = new SqlCommand("spcGetItem",
myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;

// Add Parameters to SPROC
SqlParameter parameterItemKey = new SqlParameter("@_iItemKey",
SqlDbType.Int, 4);
parameterItemKey.Value = iItemKey;
myCommand.Parameters.Add(parameterItemKey);
// Add OUT Parameters to SPROC
SqlParameter parameterRetVal = new SqlParameter("@_oRetVal",
SqlDbType.Int, 4);
parameterRetVal.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterRetVal);
// Execute the command
myConnection.Open();
SqlDataReader result =
myCommand.ExecuteReader(CommandBehavior.CloseConne ction);

Looks like connection get closed, but I looked into process info and can see
many connections open, however no user browsing site at the same time...

What is the sure way to close/terminate connection?

[Jerry Negrelli]

Have you tried calling myConnection.Close() or
myConnection.Dispose()? How are you handling connection
pooling?

Jerry Negrelli
Senior Software Engineer
Data Scientific Corporation

-----Original Message-----
Hi!

I use following code to execute SP's on SQL server from my web page: // Create Instance of Connection and Command Object SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings ["connectionString"]); SqlCommand myCommand = new SqlCommand ("spcGetItem",myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterItemKey = new SqlParameter("@_iItemKey",SqlDbType.Int, 4);
parameterItemKey.Value = iItemKey;
myCommand.Parameters.Add(parameterItemKey);
// Add OUT Parameters to SPROC
SqlParameter parameterRetVal = new SqlParameter("@_oRetVal",SqlDbType.Int, 4);
parameterRetVal.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterRetVal);
// Execute the command
myConnection.Open();
SqlDataReader result =
myCommand.ExecuteReader(CommandBehavior.CloseConn ection);

Looks like connection get closed, but I looked into process info and can seemany connections open, however no user browsing site at the same time...
What is the sure way to close/terminate connection?

.

[Ivan Demkovitch]

I don't know what you mean by "connection pooling"...

All I do is Open - get - close (???) every time I need data.
This is Web App and I do not keep connections open at all.

"Jerry Negrelli" <je************@nospam.datascientific.com> wrote in message
news:01****************************@phx.gbl...

Have you tried calling myConnection.Close() or
myConnection.Dispose()? How are you handling connection
pooling?

Jerry Negrelli
Senior Software Engineer
Data Scientific Corporation

-----Original Message-----
Hi!

I use following code to execute SP's on SQL server from

my web page:

// Create Instance of Connection and Command

Object

SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings

["connectionString"]);

SqlCommand myCommand = new SqlCommand

("spcGetItem",

myConnection);

// Mark the Command as a SPROC
myCommand.CommandType =

CommandType.StoredProcedure;

// Add Parameters to SPROC
SqlParameter parameterItemKey = new

SqlParameter("@_iItemKey",

SqlDbType.Int, 4);
parameterItemKey.Value = iItemKey;
myCommand.Parameters.Add(parameterItemKey);
// Add OUT Parameters to SPROC
SqlParameter parameterRetVal = new

SqlParameter("@_oRetVal",

SqlDbType.Int, 4);
parameterRetVal.Direction =

ParameterDirection.Output;

myCommand.Parameters.Add(parameterRetVal);
// Execute the command
myConnection.Open();
SqlDataReader result =
myCommand.ExecuteReader(CommandBehavior.CloseConn ection);

Looks like connection get closed, but I looked into

process info and can see

many connections open, however no user browsing site at

the same time...

What is the sure way to close/terminate connection?

.

[Jerry Negrelli]

SQL Server Connection Pooling is enabled by default & it
is definitely recommended, since any connection you
create with an identical connection string will just
reuse the same connection. If you really want the
connection to go away, you may be able to
add "Pooling=false" or something like that to your
connection string, but that syntax may not be exact.

This article may give you a bit more info:

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnmdac/html/pooling2.asp

Jerry Negrelli
Senior Software Engineer
Data Scientific Corporation

-----Original Message-----
I don't know what you mean by "connection pooling"...

All I do is Open - get - close (???) every time I need data.This is Web App and I do not keep connections open at all.
"Jerry Negrelli" <je************@nospam.datascientific.com> wrote in
messagenews:01****************************@phx.gbl...

Have you tried calling myConnection.Close() or
myConnection.Dispose()? How are you handling connection pooling?

Jerry Negrelli
Senior Software Engineer
Data Scientific Corporation

>-----Original Message-----
>Hi!
>
>I use following code to execute SP's on SQL server from

my web page:

> // Create Instance of Connection and

Command Object

> SqlConnection myConnection = new
>SqlConnection(ConfigurationSettings.AppSettings

["connectionString"]);

> SqlCommand myCommand = new SqlCommand

("spcGetItem",

>myConnection);
>
> // Mark the Command as a SPROC
> myCommand.CommandType =

CommandType.StoredProcedure;

>
> // Add Parameters to SPROC
> SqlParameter parameterItemKey = new

SqlParameter("@_iItemKey",

>SqlDbType.Int, 4);
> parameterItemKey.Value = iItemKey;
> myCommand.Parameters.Add

(parameterItemKey); >
>
> // Add OUT Parameters to SPROC
> SqlParameter parameterRetVal = new

SqlParameter("@_oRetVal",

>SqlDbType.Int, 4);
> parameterRetVal.Direction =

ParameterDirection.Output;

> myCommand.Parameters.Add(parameterRetVal);
>
>
> // Execute the command
> myConnection.Open();
> SqlDataReader result =
>myCommand.ExecuteReader (CommandBehavior.CloseConnection); >
>Looks like connection get closed, but I looked into

process info and can see

>many connections open, however no user browsing site

at the same time...

>
>What is the sure way to close/terminate connection?
>
>
>
>
>
>.
>

.