Properly terminating connections

  • Thread starter Thread starter Ivan Demkovitch
  • Start date Start date
I

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?
 
Have you tried calling myConnection.Close() or
myConnection.Dispose()? How are you handling connection
pooling?

Jerry Negrelli
Senior Software Engineer
Data Scientific Corporation
 
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 said:
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?





.
Click to expand...
Click to expand...
 
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
Click to expand...
from
my web page:
// Create Instance of Connection and
Click to expand...
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
Click to expand...
at
the same time...
What is the sure way to close/terminate connection?





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top