C # Support for multithreaded application and SQL connections

Question

I need some tips regarding the application I wrote. The problems that I have are related to the fact that my DAL and connections to my SQL Server 2008 database do not close, however, I looked at my code and each connection always closes.

An application is a multi-threaded application that retrieves a set of records, and when processing a record, it updates information about it.

Here is the thread:

The administrator has the ability to set the number of threads to run and the number of entries in the stream.

Here is the code that runs after clicking the start button:

Adapters are abstractions for my DAL, here is an example of how they look:

public class UserDetailsAdapter: IDataAdapter<UserDetails>
{
     private IUserDetailFactory _factory;

        public UserDetailsAdapter()
        {
            _factory = new CampaignFactory();
        }

        public UserDetails FindById(int id){
             return _factory.FindById(id);
        }
}

As soon as _factory is called, it processes SQL and closes the connection immediately.

:

private int _recordsPerthread;


private int _threadCount;

    public void RunDetails()
    {
        //create an adapter instance that is an abstration
        //of the data factory layer
        var adapter = new UserDetailsAdapter();

        for (var i = 1; i <= _threadCount; i++)
        {
            //This adater makes a call tot he databse to pull X amount of records and 
            //set a lock filed so the next set of records that are pulled are differnt.
            var details = adapter.FindTopDetailsInQueue(_recordsPerthread);
            if (details != null)
            {
                var parameters = new ArrayList {i, details};
                ThreadPool.QueueUserWorkItem(ThreadWorker, parameters);
            }
            else
            {
                break;
            }
        }
    }

    private void ThreadWorker(object parametersList)
    {
        var parms = (ArrayList) parametersList;
        var threadCount = (int) parms[0];
        var details = (List<UserDetails>) parms[1];
        var adapter = new DetailsAdapter();


        //we keep running until there are no records left inthe Database
        while (!_noRecordsInPool)
        {
            foreach (var detail in details)
            {
                var userAdapter = new UserAdapter();
                var domainAdapter = new DomainAdapter();

                var user = userAdapter.FindById(detail.UserId);
                var domain = domainAdapter.FindById(detail.DomainId);

                //...do some work here......

                adapter.Update(detail);
            }

            if (!_noRecordsInPool)
            {
                details = adapter.FindTopDetailsInQueue(_recordsPerthread);


                if (details == null || details.Count <= 0)
                {
                    _noRecordsInPool = true;
                    break;
                }
            }
        }
    }

- . DAL, :

. . - ,

, . , , , , . , , .

: , . SQL, , :

WITH cte AS ( 
  SELECT TOP (@topCount) *
  FROM
  dbo.UserDetails WITH (READPAST) 
WHERE
  dbo.UserDetails where IsLocked = 0)

UPDATE cte 
  SET 
  IsLocked = 1

  OUTPUT INSERTED.*;

( ). , 99%. . .

Answer 1

, , SQL Server 2008 . , , :

sp_configure ' parallelism', 1; ; GO

, , .

MAXDOP (n) (n - ), . . -, , DALs Dispose GC.Suppressfinalize. , DAL . , !

Answer 2

, , , , , IDispose (, , using), using :

using (adapter = new UserDetailsAdapter())
{
    for (var i = 1; i <= _threadCount; i++)
    {
        [..]
    }
} // adapter leaves scope here; connection is implicitly marked as no longer necessary

ADO.NET , ( ) .

Answer 3

, . .

?

, .

I'm not too familiar with your environment, but I'm sure you really need a lot of open connections before your database starts complaining about it!