C# Sql Updating row by row from a List of Objects

Question

public int UpdateAmount(List<MyTable> myBizObjList)
     {
         SqlTransaction sqltxn;
         DbClass db = new DbClass();
         SqlConnection cs;
         cs = db.GetConnection();

         string commandText = @"Update MyTable Set amt = @amt  where empno = @empno and mydate = @mydate";

         int x = myBizObjList.Count;
         int y = 0,rowsaffected;
         cs.Open();

         using (cs)
         {
             sqltxn = cs.BeginTransaction();
             foreach (MyTable myBizObj in myBizObjList)
              { 
                 SqlCommand command = new SqlCommand(commandText, cs, sqltxn);
                 command.Parameters.Add("@empno", SqlDbType.Int);
                 command.Parameters["@empno"].Value = myBizObj.Empno;
                 command.Parameters.Add("@mydate", SqlDbType.Date);
                 command.Parameters["@mydate"].Value = myBizObj.Mydate;
                 command.Parameters.Add("@amt", SqlDbType.Decimal);
                 command.Parameters["@amt"].Value = myBizObj.Amt;
                                 
                 
                 try
                 {
                     rowsAffected = command.ExecuteNonQuery();
                     if (rowsAffected == 1)
                         y++;
                 }
                 catch (Exception ex)
                 {
                     throw (ex);
                    
                 }
             }
             if (y == x)
             {
                 sqltxn.Commit();
                 
             }
             else
             {
                sqltxn.Rollback();
                 y = 0;
             }
             cs.Close();
             return y;
         }
         
     }

Question: I am querying a table and getting say 50K records which I am converting to a List of objects. I am processing the List in my BLL and sending to my DAL. The above is a method in my DAL. Is there a better way? I am also checking if all rows are updated & then Commit or Rollback.

Instead of updating record by record for 50K records, you can use the user defined table type and stored procedure to update all records at once in single transaction at Database end. — user1672994
– user1672994, Commented Jan 14, 2021 at 17:24
You need to dispose the transaction with using, also you should move using(cs) up to the setter cs = .... Also you can shorten command.Parameters.Add("@empno", SqlDbType.Int).Value = myBizObj.Empno — Charlieface
– Charlieface, Commented Jan 14, 2021 at 17:53
Never do throw ex; where ex is a caught exception. You will lose your stack trace. Just do throw;. Of course...your catch block isn't doing anything at all except rethrowing, so it's entirely redundant and the try/catch should be removed from this code. There's also no need for cs.Close();, the using block will ensure that the connection is closed and disposed when it goes out of scope. — mason
– mason, Commented Jan 14, 2021 at 18:02
@Charlieface Thanks for command.Parameters.Add("@empno", SqlDbType.Int).Value = myBizObj.Empno. One line less of code. So much to learn. — Ajoy Khaund
– Ajoy Khaund, Commented Jan 15, 2021 at 4:48

Charlieface · Accepted Answer · 2021-01-15 04:56:12Z

1

You can convert this to a table-valued parameter.

First we need a table type:

CREATE TYPE dbo.MyTVP (
    empno int not null,
    mydate date not null,
    amt decimal not null
    primary key (empno, mydate)
);

Then we pass it through. You don't necessarily need a stored procedure, you can do this as an ad-hoc batch:

public int UpdateAmount(List<MyTable> myBizObjList)
     {
         var table = new DataTable();
         table.Columns.Add("empno", typeof(int));
         table.Columns.Add("mydate", typeof(datetime));
         table.Columns.Add("amt", typeof(decimal));
         foreach (MyTable myBizObj in myBizObjList)
             table.Rows.Add(myBizObj.Empno, myBizObj.Mydate, myBizObj.Amt);
         
         const string commandText = @"
Update tbl
Set amt = t.amt
FROM MyTable AS tbl
JOIN @tmp AS t ON t.empno = tbl.empno AND t.mydate = tbl.mydate;
";

         using (var cs = db.GetConnection())
         {
             SqlCommand command = new SqlCommand(commandText, cs, sqltxn);
             command.Parameters.Add(
             new SqlParameter("@tmp", SqlDbType.Structured)
             {
                 Direction = ParameterDirection.Input,
                 TypeName = "dbo.MyTVP",
                 Value = table
             });

             cs.Open();
             return command.ExecuteNonQuery();
         }
     }

edited Jan 15, 2021 at 4:56

answered Jan 14, 2021 at 17:52

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

2 Comments

Ajoy Khaund Over a year ago

command.Parameters.Add("@tmp", SqlDbType.Structured) { Direction = ParameterDirection.Input, TypeName = "dbo.MyTVP", Value = table }; This section gave an error so I broke it: SqlParameter tvp = command.Parameters.Add("@UserDefineTable", SqlDbType.Structured); tvp.Direction = ParameterDirection.Input; tvp.TypeName = "WageentryType"; tvp.Value = dt; (This is working) Overall great.

Charlieface Over a year ago

Whoops sorry fixed. Note that I didn't use a transaction because it's all one statement

Collectives™ on Stack Overflow

C# Sql Updating row by row from a List of Objects

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related