MS SQLHelper class .NET - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MS SQLHelper class .NET

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2003-06-11 : 09:09:51

Has anyone used these:
Microsoft Application Blocks for .NET (Data Access Application Block)
I am using the SQLHelper class in my ASP.Net application and it appears to be opening multiple connections and is not using connection pooling.

I am storing my connection settings in my web.config then I use code like this:


' Set up parameters (1 input and 3 output) 
            Dim arParms() As SqlParameter = New SqlParameter(3) {}

            ' @Username Input Parameter 
            arParms(0) = New SqlParameter("@UserID", SqlDbType.Int)
            arParms(0).Value = UserID
            ' @DepartmentID Input Parameter 
            arParms(1) = New SqlParameter("@DepartmentID", SqlDbType.Int)
            arParms(1).Direction = ParameterDirection.Output
            ' @Password Input Parameter 
            arParms(2) = New SqlParameter("@FirstName", SqlDbType.VarChar, 50)
            arParms(2).Direction = ParameterDirection.Output
            ' @UnitPrice Output Parameter
            arParms(3) = New SqlParameter("@LastName", SqlDbType.VarChar, 50)
            arParms(3).Direction = ParameterDirection.Output

                ' Execute the stored procedure
                SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("DBConnStr"), CommandType.StoredProcedure, "getUserDetails", arParms)

Anyone else having a problem like this? I was under the impression that the SQLHelper class would open/close and use connection pooling automatically?

Scott

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-06-11 : 09:42:44

I use .Net, but I do not use the Helper class. The code I've written doesn't seem to have these sort of issues.

How are you determining that it's opening multiple connections? Using Perfmon?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Scott
Posting Yak Master

145 Posts

Posted - 2003-06-11 : 10:01:34

Either running sp_who or in enterprise manager detatch database and it shows connections, then just cancel.

I remeber I used to just run a stored proc that counted connections, can't remember it right now.

Anyway have a bunch of the above code on a page and you get miltiple connections. If I could get SQLHelper to work properly it would save a bunch of coding.

Here is the link:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp[/url]

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-06-11 : 10:06:45

You might want to use PerfMon and connect i to your SQL Server. Then Monitor SQL Server User Connections. Watch what it does when you run you code.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

macka
Posting Yak Master

162 Posts

Posted - 2003-06-11 : 10:31:34

Scott,

I had similar problems with the DAAB a few months back, and caused because it doesn't explicitly close the connections. They simply go out of scope and wait for the GC to come and clean them up. Take a look at :

[url]http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=%239UX791LDHA.3976%40tk2msftngp13.phx.gbl&rnum=2&prev=/groups%3Fq%3Ddaab%2Bapplication%2Bpool%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D%25239UX791LDHA.3976%2540tk2msftngp13.phx.gbl%26rnum%3D2[/url]

You can change the size of the app pool in web.config but this doesn't really get around the fundamental problem.

Eventually, we gave up on the DAAB and went down the route of writing our own DAL using LLBLGEN ([url]www.llblgen.com[/url]).

Cheers,

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

macka
Posting Yak Master

162 Posts

Posted - 2003-06-11 : 10:36:59

On an aside, I found a nice analogy from Microsoft whilst trying to fix this problem:

"Think of pooling as a taxi service.A taxi service can carry hundreds of passengers per day using just a handful of actual taxis. As long as one customer is using a taxi nobody else can ride in it, so if one customer keeps the taxi for the entire day the ammount of passengers per day will drop significantly."

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources