Busy DataReader breaking my Web Site

Question

So ... I've got some static class with some method which returns some data to my Data Tables.

The trouble is multi-access to this method. When to different Client Sessions call this method in the same time DataReaded fails with error that I need to close current command (DataReader) before start new one. I even tried to use Lock but this didn't made any sense.

    private RepGroupsDataSourcelocker = Object()
    public RepGroupsDataSource() : array[(string * int)]
        lock(RepGroupsDataSourcelocker)
            def res = linq<#from pr in _Rep_Permission
                    from rg in _Rep_Group
                    where (pr.ID_User == System.Web.HttpContext.Current.User.Identity.Name)
                    where (rg.RepGroup_ID == pr.ID_RepGroup)
                    order by rg.nOrder
                    select (rg.RepGroup_Name, rg.RepGroup_ID)
                    #>
            res.Take(7).ToArray()

That is only example code , it fails on any linq2sql , SqlAdapter Fill commands.

Here is details :

System.InvalidOperationException was unhandled by user code
      Message = There is a designated this command Command open DataReader, which requires the prior close.
      Source = System.Data
      StackTrace:
           in System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute (SqlCommand command)
           in System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute (String method, SqlCommand command)
           in System.Data.SqlClient.SqlCommand.ValidateCommand (String method, Boolean async)
           in System.Data.SqlClient.SqlCommand.RunExecuteReader (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
           in System.Data.SqlClient.SqlCommand.RunExecuteReader (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
           at System.Data.SqlClient.SqlCommand.ExecuteReader (CommandBehavior behavior, String method)
           in System.Data.SqlClient.SqlCommand.ExecuteDbDataReader (CommandBehavior behavior)
           in System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader (CommandBehavior behavior)
           in System.Data.Common.DbDataAdapter.FillInternal (DataSet dataset, DataTable [] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
           in System.Data.Common.DbDataAdapter.Fill (DataTable [] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
           in System.Data.Common.DbDataAdapter.Fill (DataTable dataTable)
           in Flow_WEB_Nemerle.SQLModule.GetReport (String Param1, Int32 Param2, Int32 Group, Int32 DayOfMonth, String ContactHour, Int32 MyCase, Boolean ForDia, Int32 step, Int32 maximumRows, Int32 startRowIndex) in D: \ SVN \ FlowServer \ trunk \ FS WEB \ Flow_WEB_Nemerle2 \ SQLReportModule.n: line 96
      InnerException:

How can I avoid this troubles without creating a Windows Service / WCF for it. I want really fast solution ... Maybe I need dynamic class with methods ?

Your class should not be static, but the method should be static, if you post a method of the class then we could help you better. — Waqas Raja, Apr 12 '11 at 10:37
Why do you have static classes? Change them into instance classes and you'll be fine. — Mikael Östberg, Apr 12 '11 at 10:38
@Waqas - No, the method shouldn't be static either as it would cause the very same behavior. — Mikael Östberg, Apr 12 '11 at 10:40
@Waqas Raja but Method is a bit hardcoded and it's on Nemerle , I'll be happy to share but I don't think someone will take a look on it. — cnd, Apr 12 '11 at 10:40
@MikeEast I made the method / class static because it's really slow method and there is no sense to make it dynamic (Except this exception / trouble ) — cnd, Apr 12 '11 at 10:42
Static does not affect the execution time of the method, it just specifies that there should just be one of them. (And therefore not re-entrant) — DaveRead, Apr 12 '11 at 10:48
Try this: http://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c — oyaebunterkrah, May 10 '13 at 10:53

score 2 · Accepted Answer · answered Apr 12 '11 at 10:42

2

The web server uses several threads for handling requests. If they all use the same database connection, that is a bottle neck for the site.

You should use a separate database connection for each request. That way there is no concurrency problems between the threads, and you don't create an unneccesary bottle neck.

answered Apr 12 '11 at 10:42

Guffa

687,336
108
737
1,005

But how can I specify that my connection should be different ? – cnd Apr 12 '11 at 10:44
If you use a per-instance connection with the same connection string (i.e. not static) the ADO.NET connection pool will handle multiple connections to the database server – DaveRead Apr 12 '11 at 10:46

Busy DataReader breaking my Web Site

1 Answers1