1

Our code uses the following code to send queries to the database for our reports:

    public DataTable RunQuery(QueryDatabase queryDatabase, string theQuery, IEnumerable<DatabaseParameter> parameters, IEnumerable<DbParameterList> listParameters)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString(queryDatabase)))
            {
                connection.Open();

                using (System.Data.SqlClient.SqlCommand cmdToExecute = new System.Data.SqlClient.SqlCommand())
                {
                    cmdToExecute.CommandText = theQuery;
                    cmdToExecute.CommandType = CommandType.Text;
                    cmdToExecute.CommandTimeout = 900;
                    foreach (DatabaseParameter p in parameters)
                    {
                        cmdToExecute.Parameters.Add(p.AsSqlParameter());
                    }
                    foreach (DbParameterList l in listParameters)
                    {
                        l.AddToCommand(cmdToExecute);
                    }
                    using (System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmdToExecute))
                    {

                        cmdToExecute.Connection = connection;

                        DataTable dt = new DataTable();

                        int _numberOfRowsAffected = adapter.Fill(dt);
                        return (dt);
                    }
                }
            }
        }
        catch (Exception e)
        {
            Log.LogError("DatabaseService.RunQuery", e.Message);
            return null;
        }
    }

This has been working well for us. In fact, not shown is that the connection string may very well be pulled from the database by another call to this same method (Transactional versus Reporting databases).

Recently, for business requirements, we ran into one of SQL Server's limits: You can only have ~300 columns in a table. One report was generating ~700 columns in a temporary table. That won't work, so I decided to split the result into several queries and splice the resulting tables in code (Thank you Microsoft, I shouldn't have to do that).

The code looks like the following:

        DataTable result = DatabaseService.Instance.RunQuery(QueryDatabase, Query, Parameters, ListParameters);
        resetSlicer();
        IList<T> paramterSlice = getParameterSlice();
        while (paramterSlice.Count > 0)
        {
            DataTable slice = getSlice(paramterSlice);
            result.AppendTableColumns(slice, KeyColumns);
            paramterSlice = getParameterSlice();
        }

The getSlice() call contains another "RunQuery()" call.

When I run this code, first call to "GetSlice()" fails with the following exception:

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The stack Trace is as follows:

at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at <Anonymized>.Common.Database.DatabaseService.RunQuery(QueryDatabase queryDatabase, String theQuery, IEnumerable`1 parameters, IEnumerable`1 listParameters)

To try to diagnose the problem I commented out the first "RunQuery()" call. This resulted in the first "getSlice()" returning the results correctly; however, the second call failed with the same error.

Given that we have been able to run multiple queries using this pattern elsewhere in the code, why would this particular usage be generating an error?

user1542042
  • 195
  • 1
  • 9
  • "You can only have ~300 columns in a table." - if you have ~300 columns in a table, then there is a good chance it is modelled wrong – Mitch Wheat Aug 15 '14 at 15:55
  • Please note that I said that it was a temporary table (in fact generated by the optimizer). The output of the query may be that many columns due to business _requirements_, nothing I can do about it. – user1542042 Aug 15 '14 at 16:13
  • 1
    I note that you are adding your SqlCommand to each DBParameterList, presumably for some logging/tracing reason?? However, these SqlCommands will have been Disposed of by the time you could use them. Also, they might be Disposing of their attached SqlParameters. Could you be trying to reuse a Disposed parameter? – Mark Peters Aug 16 '14 at 02:11
  • DBParameterList uses that idiom because I wrote it before I knew about Extension methods, which is what that method actually is. DbParamterList encapsulates the information of an SqlParameter which has a list of values as its "value". I will review that code for possible re-use issues. – user1542042 Aug 18 '14 at 14:51

1 Answers1

0

Hat Tip to Mark Peters for pointing in the correct direction.

DbParameterList exists to pass list to SQL (e.g. try to find people with last names "Smith", "Doe", or "Jones" in a single statement). This avoids ugly constructs with interpolated IN (...) lists in the SQL. To do this, the class implements the IEnumerable interface. I made the assumption that Microsoft would use their own interfaces correctly. In this case, I assumed that before enumerating across the IEnumerable, they would call the Reset() method on the IEnumerable. However, they do not.

The first time the DbParameterList was called the enumerator was at the beginning and everything worked correctly. The second call would crash the connection (yes, if the IEnumerable used in an SqlParameter is empty the connection crashes) because the enumerator was never reset.

So, adding a Reset() call to the enumerator at the time it is attached to the SqlCommand corrects the problem.

user1542042
  • 195
  • 1
  • 9