Geeks With Blogs
Gavin Stevens's Blog the ramblings of another developer....

I've been looking at the Microsoft Data Access Application Block recently.

I've noticed a problem that maybe someone can help me with, if not this will make you aware of it.  It seems most the places I've worked recently use the Data Application Block, and normally use these methods most:

SqlHelper.ExecuteNonQuery(_connstring, CommandType.StoredProcedure, _spName, _arParms)
SqlHelper.ExecuteReader(_connstring, CommandType.StoredProcedure, _SPName, _arParms)
SqlHelper.ExecuteDataset(_connstring, CommandType.StoredProcedure, _spName, _arParms)

Of course passing in the correct values. But specifically passing in the connection string. 

The thing I notice is this: 

If you look at the DAB's implementation of the method that takes a connection string as the first parameter:

public static int ExecuteNonQuery
(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{ //create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
//call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}}

It's understandable that using the “Using” statement in C# will call dispose automatically on the object after the “Using” Block.  Is this the best method? By doing this, the connection isn't closed until the GC needs resources or gets around to it. 

Using the built in method with leaving the GC to dispose of the connection object, doesn't return the connection to the pool immediately, and when calling alot of Stored Procedures, I have actually gotten the error:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection (SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlStateConnection..ctor(String
sqlconnectionstring)

This means, because I left it upto the GC, I actually ran out of available connections in the connection pool. And yes, if I look in SQL with an sp_who2, my machine has about 30 open connections. I would then have to manually kill my aspnet_wp.exe process to free the tied up connections.

Why not instead control the connection yourself and call the overloaded method to take the connection itself?  This way you can actually close and dispose of the connection. 

example:

strSQL = "SELECT STATEMENT HERE"
Dim conn As SqlConnection = new SqlConnection(connString)

Try
conn.Open()

objDR = SqlHelper.ExecuteReader(conn, CommandType.Text, strSQL)
If (objDR.Read) Then
'Do Something
End If

objDR.Close()
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If

conn.Dispose()
End Try

 Why would MS make this version of the overloaded method and simply leave the closing of the connection upto the GC?  This doesn't seem like a best practice in my opinion.  I understand the need for ease of use, but this seems to complicate matters by not closing the connections!

I would appreciate some comments if anyone else has ran into this issue.

Gavin Stevens

 

Posted on Tuesday, September 21, 2004 8:45 PM | Back to top


Comments on this post: Microsoft Data Access Application Block for .NET

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Personally, I push the connection creation to the DAB so I dont have to associate myself with anything beyond "IDb". Now you have no way around this when it comes to data type, but all other instances of the parameters show only go System.Data deep. Another reason to stay away from working with SqlConnection yourself is the fact that you are not the only one to write code and if you make it a precedent to use SqlConnection, a junior might not be as sensitive as you are with the Close() or Dispose().
Left by Jeff Julian on Sep 21, 2004 11:36 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
I think you may also be missing something else here. Garbage Collectors do not close connections. They are there as part of the Framework to clean up Objects that no longer have reference. Most GCs act in a way of passing over memory once; marking unreferenced objects and then actually cleaning them up on the second pass. That's a basic explanation of the model anyway. You might want to investigate explicitly calling the GC if you feel it is not collecting fast enough but my experience has been that rarely will you ever have to do anything with a GC. They are [supposed to be] pretty much optimized. I would look for another possible culprit for your issue, perhaps a connection timeout parameter in your connection string?
Left by DK on Sep 23, 2004 4:36 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
I just did a project using the DAAB and you need to make sure to close your readers after you use the connection string version of the methods. That will prevent the timeouts. The DAAB doesn't close the connection for you if it returns a reader because the reader needs it. I don't have the code handy but it has to do with an option they pass somewhere inside the DAAB.
Left by Phil on Sep 23, 2004 8:14 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
I had the same problem using the DAAB in a very demanding enviroment. I had to modify some lines in the code of the SQLHelper class (close the connections in ExecuteNonQuery, ExecuteScalar and ExecuteDataset) to support that situation.
Left by Demian on Sep 27, 2004 9:32 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Hi,

If anyone has the solution to this I would be very interested. We are working exclusivly with the ExecuteReader method and often end up with 2 open connections. This seems strange as after stepping through the code, a second connection is often opened after a connection.close is called (!!!)

Any thoughts?

Cheers

Rich
Left by Richard Slade on Oct 12, 2004 4:03 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
I am using the data access block, now I have 105 connections open to my database which is strange and funny. now I am looking everywhere on my code to find those open connection. I think when you call objreader.close, the connection will be closed as well.
Left by guoqi on Oct 31, 2004 8:14 AM

# re: Connection Handling Basics
Requesting Gravatar...
As others have pointed out, the real problem here is simply that the conenction isn't being explicitly closes. Proper handling of a database connection in large apps should always looks somthing like this:

Open database connection.
Try
Do stuff with the connection.
Finally
Close the connection.
End Try

Otherwise there are all too many ways for a connection to sit idle for long periods of time before being returned to the pool, and your apps will be prone to erros like the ones described in the article.
Left by Billy Brown on Nov 09, 2004 8:19 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
I think the whole point of the DAAB was not to do EVERYTHING for you guys ... as the name implies it ACCESS the data in a very convenient and more or less code free way, its ALWAYS a programmer responsibility to make sure that you return the resources you use so I really do not see anything wrong with the not closing of the connections ... Just add another Method to your classes or another line to call Dispose and that’s about it ... how hard is that … I mean MS programming its TOO EASY Already …
Left by Akrion on Dec 23, 2004 9:58 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
hi,
ok automatically the Microsoft DAAB closes connection by the using keyword so no need to do it explicity, basically it has to do with connection pooling, and you can find a solution in the article below:

http://www.error-bank.com/microsoft.public.dotnet.framework.adonet/78771_Thread.aspx
Left by Akram Hussein on Feb 20, 2005 9:14 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
i used Microsoft Data Access Application Block and ended by getting thread abort exception while trying to execute query that takes more than 4 min and lots of memory( as my query execute on a view and creates cursors during execution).

irm_joshy@hotmail.com
Left by joshy CA on Feb 25, 2005 7:37 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
You need to explicitly close the connection. Dispose method is called only when GC cleans the memory and disposes the object.

Following are some Remarks copied from Help Document of DAAB

Finalize operations have the following limitations:

The exact time when the finalizer executes during garbage collection is undefined. Resources are not guaranteed to be released at any specific time, unless calling a Close method or a Dispose method.
The finalizers of two objects are not guaranteed to run in any specific order, even if one object refers to the other. That is, if Object A has a reference to Object B and both have finalizers, Object B might have already finalized when the finalizer of Object A starts.
The thread on which the finalizer is run is unspecified.
The Finalize method might not run to completion or might not run at all in the following exceptional circumstances:

Another finalizer blocks indefinitely (goes into an infinite loop, tries to obtain a lock it can never obtain and so on). Because the runtime attempts to run finalizers to completion, other finalizers might not be called if a finalizer blocks indefinitely.
The process terminates without giving the runtime a chance to clean up. In this case, the runtime's first notification of process termination is a DLL_PROCESS_DETACH notification.
Left by Bhagvan on Mar 10, 2005 8:46 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
I experience the same problem in more than one of my projects.

Now I follow the the simple rule in all applications that use pools:

Open AND close connections all by my own code.

Such a rule makes me think of system structure before accessing database. For instance, I never pass DataReader between layers. Database Accessing Code never open connection by itself.

The following code piece is what all my db access code look like:

using ( SqlConnection conn = new SqlConnection( ConnString ) )
{
conn.Open();

//Access database
DBAccessCode( conn, //or transaction as the first para
.... )
}

Such code piece will always be at application logic layer. This make it possible to enclose more than one DBAccess in one SqlTransaction.

I don't know if this is a good design idea. Anybody has better ones?
Left by hzwang on Apr 04, 2005 9:43 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Hi Guys,
I found a solution to this problem a while ago, it was all concerned with a very simple thing i did. Basically don't use named pipes when connecting to the database, it mainly causes problems with connecting to the database. i tried TCP/IP and it worked just find. If you have more than 1 sql user or windows user accessing the database i recommend you use TCP/IP.

Left by Akram Hussein on Apr 09, 2005 2:39 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Hi Guys,
I found a solution to this problem a while ago, it was all concerned with a very simple thing i did. Basically don't use named pipes when connecting to the database, it mainly causes problems with connecting to the database. i tried TCP/IP and it worked just find. If you have more than 1 sql user or windows user accessing the database i recommend you use TCP/IP.

Left by Akram Hussein on Apr 09, 2005 2:39 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
HI Guys
I am using DAAB in my application, basically i have multiple threads executng queries ti retrieve datasets, scalers, executing non queries and opeinign Datareader. Interestingly once i open a DataReader on a table, i see all subsquent Execute nonqueires and executescalers failing with time out, Error message shows "The timeout period elapsed prior to completion of the operation or the server is not responding"

I initially thought it might be because, my connections are not being created as somehow the MaxConnections on the pool has got set as 1 but then i realized that the connections were being made, but somehow the queries were waiting on someting. Then i twiked my code to close the reader just after opeing it and i observed that all subsequent queries were being fired successfully.
I am not sure whats happening Can anybody help me understand few things.

1. Does opeing a Datareader on a table puts any kind of lock on the rows, My assumtion was that it does not as Datareader is anyway readonly

2. I have not toched the DAAB, so is there someting in DAAB causing this issue, that i might be overlooking.

3. Most importently has anyone of you faced this problem earlier and have hit a solution

Any help would be greatly appricited
Left by Sadanand Sudeer on Apr 29, 2005 9:27 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
IDispose's Dispose method was introduced to make finalization of objects that use finite resources somewhat deterministic. Usually if Dispose is implemented then it calls GC.SuppressFinalize() which tells the GC that it doesn't need to pass the object onto gen2 for finalization. So if IDispose is implemented, and dispose is called, the object does not get finalized by the GC because it was finalized when dispose was called by the user code. In the case of SqlConnection the dispose method most likely closes the connection to the sql server and frees any resources used to talk to the sql server. That is why Microsoft has implemented those functions in that fasion. However, as mentioned in a previous post, the data reader method will not close the sql connection as it needs to be open to use the DataReader.
Left by Josh Perry on May 13, 2005 5:48 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
You create an instance in a using statement to ensure that Dispose is called on the object when the using statement is exited. A using statement can be exited either when the end of the using statement is reached or if, for example, an exception is thrown and control leaves the statement block before the end of the statement.

The object you instantiate must implement the System.IDisposable interface
Left by dr on Jun 20, 2005 6:42 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
How can i Pass a SqlServer view to the Data access block ? Do i have to pass it has a Executenonquery.
help me out neal
Left by Neal on Jun 28, 2005 6:03 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Wow!

I can't believe what I'm reading. The Dispose pattern is a way of cleaning up unmanaged resources. There are some serious misunderstandings of it in the original post and several of these comments. Let me say this clearly:

DISPOSE() IS CALLED WHEN YOU CALL IT, NOT WHEN THE GARBAGE COLLECTOR RUNS!!!

The Dispose() method of the SqlConnection object calls Close(), returning the connection to the pool. This is called when execution leaves the using code block, not when GC executes. It is Finalize() that is called by GC, and only if Dispose() was not called. Again, Dispose is called when you call it.

And yes, with ExecuteReader(), you need to close the connection yourself. Using DAAB, you can create a connection yourself (hopefully created in a using statement) and pass it to ExecuteReader(). In this case you are managing the connection yourself. If you pass a connection string instead of a connection object, SQLHelper manages the connection for you by setting the CommandBehavior property to CloseConnection. This means the connection will be closed when the DataReader is closed. It is still up to you, though, to close the DataReader (easily done by calling either Close() or Dispose()). After all, you wanted to have an open DataReader in your app. Therefore, all you have to do to is create your DataReader in a using statement. It's that simple.

With ExecuteNonQuery(), this is all done for you because you don't require an open reader and its associated open connection and you should never have a problem. Just call the method and you never have to worry about the connection.
Left by Appleseed on Jul 14, 2005 1:45 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
According to the DataAccess Quick Start sample, you have to close the DataReader when using calling the ExecuteReader method. The connection will be closed automatically when the DataReader is closed.

The connection is automatically closed when call the ExecuteDataSet method.

- Cory Cissell
Left by Cory Cissell on Aug 30, 2005 1:09 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Just backing up Appleseed here; the code:

using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}

is procedurally identical to:

SqlConnection cn = new SqlConnection(connectionString);
try
{
cn.Open();
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
finally
{
cn.Dispose();
}
Left by Anonymous on Sep 01, 2005 4:37 PM

# SqlConnection problem
Requesting Gravatar...
hi
i am writted a windowsForm program by C#.NET it work on my pc but when i want to run exec file in another computer in the network, i get an error that tells sqlConnectionString is unknown.i used sqlConnection in my program.

i installed local SqlServer on my pc and installed SqlClient on another pcs on network .
please tell me what can i do .
i create connections by giving path like this:

workstation id=AZARANSRV1;packet size=4096;integrated security=SSPI;initial catalog=NorthWind;attachdbfilename="E:\Program Files\Microsoft SQL Server\MSSQL\Data\northwnd.mdf";persist security info=False

please help me i have not alot of time i should exec my project as soon as possible
Left by Nooshin on Oct 05, 2005 10:31 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
i want one help. i want to view the mdb (records )names in one listbox or tree view controls in c#.net
Left by guruji on Apr 05, 2006 2:11 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
i want one help. i want to view the mdb (records )names in one listbox or tree view controls in c#.net
Left by guruji on Apr 05, 2006 2:11 AM

# Microsoft Data Access Application Block for .NET, using Oracle as Backend!
Requesting Gravatar...
Is there any comprehensive documentation on using DAAB rather than the fragmented information available on the Web. I'd like to use DAAB, using Oracle as my backend database.

Thanks.
Left by Gbenga Abimbola on Jun 14, 2006 1:53 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Ignore my previous question on Comprehensive documentation. I have downloaded Microsoft Enterprise Library and installed it accordingly. The documentation that comes with it is enough to get me started.
Left by Gbenga Abimbola on Jun 14, 2006 4:55 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Hi to All Experts.

I want to know is there any .sql file available on net to create aspnetdb database, use in ASP.NET 2.0 security application block,In Oracle
My E-mail address is nitesh@thegt.com

Thanks in Advance.
nitesh
Left by Nitesh on Mar 19, 2007 12:17 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
The article was mindblooving...
Left by su jay on Jun 11, 2008 6:00 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
HI everyone.
first thanks to all firends above,obviously Appleseed give us the answer.but how do i using the method ExecuteDataset(),
need i manual using the method close() ?
Left by steven on Aug 12, 2008 1:35 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
supports for Oracle ??

thanks !!!
Left by ae on Oct 20, 2009 1:54 PM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
"SqlHelper.ExecuteReader".In this case how to set "commandtimeout" value for without geting timeout error. Please help me.
Left by Alisha on Mar 02, 2010 4:54 AM

# re: Microsoft Data Access Application Block for .NET
Requesting Gravatar...
Good heavens, please read the comment by Appleseed, folks. And please don't write technical responses when you don't know what you're talking about as you mislead other people.
Left by joniba on Nov 29, 2010 3:29 AM

Your comment:
 (will show your gravatar)


Copyright © Gavin Stevens | Powered by: GeeksWithBlogs.net