C# SqlCommand Returning Input Parameters when Inserting multiple Records

Question

I have an .exe project with 4 threads. Each thread makes a call to a WCF service hosted in a Windows Service and inserts a record (loop from 1 to 5,000 records). The test project will try to insert 20,000 records into the WCF service. The service behavior in the WCF service is per session.

I use a stored procedure to insert the records into SQL Server 2008R2 Express. The problem I'm having is with the SqlCommand. When only one thread is running, no error happens, but when two or more threads are running, the code throws an error, but not sure about the error type.

If you look at the code below, the error is raised when reading the result from the .ExecuteReader (it's a cast exception error). It does not return the errors that I have defined in the stored procedure (I'm guessing it never gets to the database), it returns an XML with all the parameters of the txn record, but it does not return only the current transaction, it also return record from transactions running on a different thread. If I execute the stored procedure directly in SQL Server Management Studio, it works fine, so I discarded any isolation level issue at the database side.

As you can see the method is not static, the SqlCommand is created and disposed on each call, so I'm really concerned about this. Any ideas?

private InsertInvoiceDataTable SaveTransaction(Transaction Trans, ClientInfo InfoCliente)
    {
        InsertInvoiceDataTable returnData = new InsertInvoiceDataTable();

        try
        {
            using (SqlConnection con = new SqlConnection(ConnStr1)
            {

                using (SqlCommand cmd = new SqlCommand("InsertInvoice", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@TIPO", SqlDbType.Int).Value = Trans.InvoiceType;
                    cmd.Parameters.Add("@CAJERO", SqlDbType.VarChar).Value = Trans.Cashier;
                    cmd.Parameters.Add("@TERMID", SqlDbType.Int).Value = Trans.Term;
                    cmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value = Trans.DOB;
                    cmd.Parameters.Add("@CLIID", SqlDbType.VarChar).Value = InfoCliente.ClientId;
                    cmd.Parameters.Add("@VENTANETA", SqlDbType.Decimal).Value = Convert.ToDecimal(Trans.SubTotal);
                    cmd.Parameters.Add("@IMPUESTO", SqlDbType.Decimal).Value = Convert.ToDecimal(Trans.TaxTotal);
                    cmd.Parameters.Add("@VENTATOTAL", SqlDbType.Decimal).Value = Convert.ToDecimal(Trans.Total);                      

                    con.Open();

                    using (SqlDataReader results = cmd.ExecuteReader())
                    {
                        while (results.Read())
                            {
                                InsertInvoiceRow row = returnData.NewInsertInvoiceRow();
                                try
                                {
                                    row.TIPO_log = results["Type_log"].ToString();
                                    row.VALOR_LOG = results["Value_log"].ToString();

                                }
                                catch (Exception ex)
                                {
                                    returnData.AddInsertInvoiceRow("ERROR", ex.Message);
                                    break;
                                }
                                returnData.AddInsertInvoiceRow(row);
                            }
                        }

                    con.Close();
                    cmd.Dispose();
                }
            }
        }
        catch (Exception ex)
        { 
            Log.Error(ex);
            returnData.AddInsertInvoiceRow("ERROR", ex.Message);
        }

        return returnData;
    }

C# SqlCommand Returning Input Parameters when Inserting multiple Records

Answers (1)

Related Questions