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.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?

 

[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

 

[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.

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?





.

 

[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"

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?





.

.