Microsoft Application Blocks Data Modification

[Guest]

I am using data access block(MABD) provided Micrososft. I am having error of
too many connections by MDAB. I want to modify it's code or some other way to
tackle this problem.I have also turned off connection pooling but it worked
for a little while but still I had the same problem. I am pasting a piece of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection, CommandType
commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so they can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[Sahil Malik [MVP]]

The problem isn't in the DAAB, but in the code that is using it. The code
that is using it, is not closing the connections properly, and don't disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Guest]

But isn't DAAB supposed to close the connection by itself?

The problem isn't in the DAAB, but in the code that is using it. The code
that is using it, is not closing the connections properly, and don't disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

I am using data access block(MABD) provided Micrososft. I am having error
of
too many connections by MDAB. I want to modify it's code or some other way
to
tackle this problem.I have also turned off connection pooling but it
worked
for a little while but still I had the same problem. I am pasting a piece
of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection, CommandType
commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so they
can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[Sahil Malik [MVP]]

Not if mustCloseConnection is False per the code below.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

But isn't DAAB supposed to close the connection by itself?

The problem isn't in the DAAB, but in the code that is using it. The code
that is using it, is not closing the connections properly, and don't
disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

I am using data access block(MABD) provided Micrososft. I am having
error
of
too many connections by MDAB. I want to modify it's code or some other
way
to
tackle this problem.I have also turned off connection pooling but it
worked
for a little while but still I had the same problem. I am pasting a
piece
of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection,
CommandType
commandType, string commandText, params SqlParameter[]
commandParameters)
{
if( connection == null ) throw new ArgumentNullException(
"connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so they
can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[Guest]

alright i have got it but one more thing should i be passing connection
string to it or the connection and then explicitly closing the connection?

But isn't DAAB supposed to close the connection by itself?

The problem isn't in the DAAB, but in the code that is using it. The code
that is using it, is not closing the connections properly, and don't disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

I am using data access block(MABD) provided Micrososft. I am having error
of
too many connections by MDAB. I want to modify it's code or some other way
to
tackle this problem.I have also turned off connection pooling but it
worked
for a little while but still I had the same problem. I am pasting a piece
of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection, CommandType
commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so they
can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[Sahil Malik [MVP]]

Passing a closed connection, or connection string is preferable to passing
an open connection that you need to close. This is because by passing a
string or a closed connection object, you ensure that the DAAB will close it
for you.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

alright i have got it but one more thing should i be passing connection
string to it or the connection and then explicitly closing the connection?

But isn't DAAB supposed to close the connection by itself?

The problem isn't in the DAAB, but in the code that is using it. The
code
that is using it, is not closing the connections properly, and don't
disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

message
I am using data access block(MABD) provided Micrososft. I am having
error
of
too many connections by MDAB. I want to modify it's code or some
other way
to
tackle this problem.I have also turned off connection pooling but it
worked
for a little while but still I had the same problem. I am pasting a
piece
of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection,
CommandType
commandType, string commandText, params SqlParameter[]
commandParameters)
{
if( connection == null ) throw new ArgumentNullException(
"connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out
mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so
they
can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[Guest]

Thanx for the help Mr.Malik.What would be ideal size of connection pool?I am
working on a shopping cart site which is experiencing heavu traffic.

Passing a closed connection, or connection string is preferable to passing
an open connection that you need to close. This is because by passing a
string or a closed connection object, you ensure that the DAAB will close it
for you.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

alright i have got it but one more thing should i be passing connection
string to it or the connection and then explicitly closing the connection?

But isn't DAAB supposed to close the connection by itself?

:

The problem isn't in the DAAB, but in the code that is using it. The
code
that is using it, is not closing the connections properly, and don't
disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

message
I am using data access block(MABD) provided Micrososft. I am having
error
of
too many connections by MDAB. I want to modify it's code or some
other way
to
tackle this problem.I have also turned off connection pooling but it
worked
for a little while but still I had the same problem. I am pasting a
piece
of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection,
CommandType
commandType, string commandText, params SqlParameter[]
commandParameters)
{
if( connection == null ) throw new ArgumentNullException(
"connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out
mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so
they
can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[Sahil Malik [MVP]]

Hammad,

The connection pool's default settings are good enough for a single server.
In my opinion, you shouldn't have to tinker around too much with it. For
instance, the 100 max connections limit is awfully high anyway. Even if with
a highly concurrent app (designed right) you are able to keep the actively
open concurrent connections to less than 4,5 .. max 15 .. and yet serve a
high traffic site (I know because I architected a site that gets 6 million
hits a month .

Okay, so besides that there is one additional important point. A connection
pool is not just the size of the pool at the client i.e. web server in your
case, it is also the number of connections that your SQL Server has to
juggle with. So for instance, if you have a web farm with 8 webheads, you
should explicitly modify the connection pool max-size to 1/8th, or you may
have to invest a heavy duty database server (you may have to do that anyway
to keep your performance optimal).

BTW, there is a good discourse around connection pool settings and tuning in
my upcoming book.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Thanx for the help Mr.Malik.What would be ideal size of connection pool?I am
working on a shopping cart site which is experiencing heavu traffic.

Passing a closed connection, or connection string is preferable to passing
an open connection that you need to close. This is because by passing a
string or a closed connection object, you ensure that the DAAB will close it
for you.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

alright i have got it but one more thing should i be passing connection
string to it or the connection and then explicitly closing the connection?

:

But isn't DAAB supposed to close the connection by itself?

:

The problem isn't in the DAAB, but in the code that is using it. The
code
that is using it, is not closing the connections properly, and don't
disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

-------------------------------------------------------------------------- --

message
I am using data access block(MABD) provided Micrososft. I am having
error
of
too many connections by MDAB. I want to modify it's code or some
other way
to
tackle this problem.I have also turned off connection pooling but it
worked
for a little while but still I had the same problem. I am pasting a
piece
of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection,
CommandType
commandType, string commandText, params SqlParameter[]
commandParameters)
{
if( connection == null ) throw new ArgumentNullException(
"connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out
mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so
they
can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[Guest]

Yes that was I thinking. I think our usage of MDAb is not ryte.Yesterday i
used SQL profiler to monitor audit login and logout.My application made login
to server but quite alot but only made logout upon close of session.

Hammad,

The connection pool's default settings are good enough for a single server.
In my opinion, you shouldn't have to tinker around too much with it. For
instance, the 100 max connections limit is awfully high anyway. Even if with
a highly concurrent app (designed right) you are able to keep the actively
open concurrent connections to less than 4,5 .. max 15 .. and yet serve a
high traffic site (I know because I architected a site that gets 6 million
hits a month .

Okay, so besides that there is one additional important point. A connection
pool is not just the size of the pool at the client i.e. web server in your
case, it is also the number of connections that your SQL Server has to
juggle with. So for instance, if you have a web farm with 8 webheads, you
should explicitly modify the connection pool max-size to 1/8th, or you may
have to invest a heavy duty database server (you may have to do that anyway
to keep your performance optimal).

BTW, there is a good discourse around connection pool settings and tuning in
my upcoming book.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Thanx for the help Mr.Malik.What would be ideal size of connection pool?I am
working on a shopping cart site which is experiencing heavu traffic.

Passing a closed connection, or connection string is preferable to passing
an open connection that you need to close. This is because by passing a
string or a closed connection object, you ensure that the DAAB will close it
for you.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

alright i have got it but one more thing should i be passing connection
string to it or the connection and then explicitly closing the connection?

:

But isn't DAAB supposed to close the connection by itself?

:

The problem isn't in the DAAB, but in the code that is using it. The
code
that is using it, is not closing the connections properly, and don't
disable
connection pooling to get around that issue.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

message
I am using data access block(MABD) provided Micrososft. I am having
error
of
too many connections by MDAB. I want to modify it's code or some
other way
to
tackle this problem.I have also turned off connection pooling but it
worked
for a little while but still I had the same problem. I am pasting a
piece
of
code of MADB.a help on this matter would be highly appreciated.

public static int ExecuteNonQuery(SqlConnection connection,
CommandType
commandType, string commandText, params SqlParameter[]
commandParameters)
{
if( connection == null ) throw new ArgumentNullException(
"connection" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null,
commandType, commandText, commandParameters, out
mustCloseConnection );

// Finally, execute the command
int retval = cmd.ExecuteNonQuery();

// Detach the SqlParameters from the command object, so
they
can
be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}

 

[lax4u]

i had the same problem and i think the problem is bool variable mustCloseConnection. In PrepareCommand method they are setting this variable.If connection is not open it opens the connection and set variable to true so in ExecuteNonQuery method it will close it,but if connection is already open
they setting this variable to false(which i think its wrong and cuasing this problem)bocz of that in ExecuteNonQuery method connection wont get close.

if (connection.State != ConnectionState.Open
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...