none
Transactions and command parameters RRS feed

  • Question

  • In C# code that I inherited I find this basic sequence:

    SqlTransaction oTransaction = null;

    ...SqlCode to populate a table...

    oTransaction = connection.BeginTransaction();

    foreach (datarow r in table.rows)

    {

        ...Originally some RAW sql to insert the values from the datarow into another table

    }

    oTransaction.Commit();

    This seems to have been working for some time; however, being the anal developer that I am, I created a stored procedure to replace the raw sql and used the same command object except as CommandType.StoredProcedure.

    Now, the page fails with procedure has too many parameters after successfully processing the first row from the table.. It doesn't. I've counted them a billion times. :)

    AND I do:

    command.Parameters.Clear() for every iteration.

    Is it possible that since this whole blather is inside of one huge transaction that the command is not clearing the parameters?

    I even thought of creating the command new in every iteration but don't think I can do that if the command is tied to the transaction.

    I believe the original developer put all the processing inside the transaction so that all rows would process successfully or all would roll back.

    Any thoughts?

    Thanks,

    Friday, May 24, 2019 10:32 PM

All replies

  • Hello,

    When performing a SQL operations such as a INSERT you add parameters like shown below with or without a transaction. This way the parameters are only added once but if you use Parameters.AddWithValue then that is an issue e.g. too many parameters unless you clear them but why not simply add them once and then in a loop set values as per below. 

    The transaction can be in the stored procedure.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace YourNamespace
    {
        public class DataOperations
        {
    
            private string ConnectionString = "TODO";
    
            public void InsertRecordsFromDataGridView(DataTable pDataTable)
            {
                using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                    {
    
                        cmd.CommandText = "INSERT INTO Players " +
                                          "(Name,Team,Position,Height,[Weight],Age) " +
                                          "VALUES (@Name,@Team,@Position,@Height,@Weight,@Age); " +
                                          "SELECT CAST(scope_identity() AS int);";
    
    
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Name", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Team", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Position", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Height", DbType = DbType.Int32 });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Weight", DbType = DbType.Int32 });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Age", DbType = DbType.Decimal });
    
    
                        cn.Open();
    
                        foreach (DataRow row in pDataTable.Rows)
                        {
                            cmd.Parameters["@Name"].Value = row.FieldOrDefault<string>("Name");
                            cmd.Parameters["@Team"].Value = row.FieldOrDefault<string>("Team");
                            cmd.Parameters["@Position"].Value = row.FieldOrDefault<string>("Position");
                            cmd.Parameters["@Height"].Value = row.FieldOrDefault<int>("Height");
                            cmd.Parameters["@Weight"].Value = row.FieldOrDefault<int>("Weight");
                            cmd.Parameters["@Age"].Value = row.FieldOrDefault<decimal>("Age");
    
                            row.SetField("id", Convert.ToInt32(cmd.ExecuteScalar()));
    
                        }
                    }
                }
            }
        }
    
        public static class Extensions
        {
    
            public static T FieldOrDefault<T>(this DataRow row, string columnName)
            {
                return row.IsNull(columnName) ? default(T) : row.Field<T>(columnName);
            }
    
        }
    }

    In the following example a update is done (can do the same for inserts) where a bit value indicates success or failure in C# code.

    CREATE PROCEDURE [dbo].[UpateCustomer] 
        @flag bit output,-- return 0 for fail,1 for success 
        @CompanyName nvarchar(200), 
        @ContactName nvarchar(200), 
        @ContactTitle nvarchar(200), 
        @Identity int 
    AS 
    BEGIN 
    
        SET NOCOUNT ON; 
        BEGIN TRANSACTION  
        BEGIN TRY 
            UPDATE Customer SET  
                CompanyName = @CompanyName,  
                ContactName = @ContactName,  
                ContactTitle = @ContactTitle 
            WHERE Identifier = @Identity 
            set @flag=1;  
            IF @@TRANCOUNT > 0 
                BEGIN commit TRANSACTION; 
            END 
        END TRY 
        BEGIN CATCH 
            IF @@TRANCOUNT > 0 
                BEGIN rollback TRANSACTION;  
            END 
            set @flag=0; 
        END CATCH 
     END  


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 25, 2019 12:09 AM
    Moderator
  • >>Is it possible that since this whole blather is inside of one huge transaction that the command is not clearing the parameters?<<

    That's an interesting hypothesis ... no idea if it works that way though. Just for the heck of it, maybe you could try to use TransactionScope instead of a SQLTransaction:

    using (TransactionScope scope = new TransactionScope())
    {
        try
        {
            SqlCommand sc = new SqlCommand(); // etc.etc.
            foreach (DataRow r in table.Rows)
            {
                // clear, then set new parameters and call the stored proc
            }
            scope.Complete();
        }
        catch (Exception)
        {
            // handle your exception
        }
    }
    

    You might also want to see my blog post (https://geek-goddess-bonnie.blogspot.com/2010/12/transactionscope-and-sqlserver.html) if this looks like it works for you (there's a "gotcha" if you instantiate a TransactionScope() with no parameters, which may or may not matter to you).


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, May 25, 2019 1:29 AM
    Moderator
  • Is it possible that since this whole blather is inside of one huge transaction that the command is not clearing the parameters?

    No. The problem must be elsewhere. A transaction would not affect the parameters of the command in any way.

    > command.Parameters.Clear() for every iteration.

    Instead of that, define the command and its parameters once outside the loop. Then, inside the loop, simply change the values of the existing parameters without adding any parameters to the command or creating a new instance of the command (you use the same instance that you created outside the loop). If the number of parameters is correct in the first iteration, then it will be correct on all iterations.

    Saturday, May 25, 2019 9:45 AM
    Moderator
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 27, 2019 5:26 AM
    Moderator
  • While it is about to become OBE, given the way the original project was coded the transaction cannot be in the stored procedure because it is coded to encompass all calls to the SP.

    I did take your suggestion to change the code to add the params once and update the value in the loop but I am going to go a step further and change the code to do a transaction for each row.

    Wednesday, May 29, 2019 2:07 PM
  • What is the point of having a Transaction for each Row? If, as you speculated, "I believe the original developer put all the processing inside the transaction so that all rows would process successfully or all would roll back." then this will not be what you are doing if you do it per Row. In fact, a Transaction per Row is redundant at best, because you are only doing *one* operation in that Transaction … so, there's really no point. If the one update fails, it fails … nothing at all would get written for that one update.

    Keep the Transaction around the whole loop.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, May 29, 2019 2:15 PM
    Moderator
  • Digging further I discovered that the whole plan was flawed from the start. The original code inserts all selected records which is incorrect. 

    Each record has a date created associated with it. However, the record is not valid until the submitter's program manager approves the record. If this happens after the timer has run, that record is now effectively an orphan and will never get handled correctly.

    So, I am going to change the selection criteria and only process the records that are truly ready to be moved/copied.

    I am also going to change the timer job so that it doesn't use the created date for selection criteria.
    • Edited by gileslacy Wednesday, May 29, 2019 2:28 PM added more text
    Wednesday, May 29, 2019 2:26 PM