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;
    }

Perhaps you could share the actual error message? Also, the stored procedure code. Otherwise we are left guessing and it could be anything. — Sean Lange
– Sean Lange, Commented Apr 19, 2017 at 20:02
If there is any error on the SP, will return a table with Id and error Description, that I retrieve here: results["Value_log"].ToString(); after 400 records running with 2 Threads, instead of returning that DataTable, return a complete different XML Dataset with the SP Insert parameters and their values: Tipo, Cajero, TermId, etc. Weird thing is that not only return the current transaction but all previous 400 transactions that has been inserted previously. So, the error is a CastException, can't find Value_log on the return that the program is expecting from the SP Datatable. — Franml32
– Franml32, Commented Apr 19, 2017 at 22:15

Rahul · Accepted Answer · 2017-04-19 20:05:26Z

1

You are performing a DML operation, in your case INSERT (from your posted code new SqlCommand("InsertInvoice", con)) then why ExecuteReader() it rather should be cmd.ExecuteNonQuery()

answered Apr 19, 2017 at 20:05

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Franml32 Over a year ago

"InsertInvoice" is the name of the SQL Stored Procedure, the reason of using ExecuteReader is because the SP returns a DataTable with informartion that I return in the Method here: InsertInvoiceRow row = returnData.NewInsertInvoiceRow(); try { row.TIPO_log = results["Type_log"].ToString(); row.VALOR_LOG = results["Value_log"].ToString(); }

Collectives™ on Stack Overflow

C# SqlCommand Returning Input Parameters when Inserting multiple Records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related