Bulk Update in C#

Bulk Update in C#

What I've done before is perform a bulk insert from the data into a temp table, and then use a command or stored procedure to update the data relating the temp table with the destination table. The temp table is an extra step, but you can have a performance gain with the bulk insert and massive update if the amount of rows is big, compared to updating the data row by row.

Example:

public static void UpdateData<T>(List<T> list,string TableName)
{
    DataTable dt = new DataTable("MyTable");
    dt = ConvertToDataTable(list);

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
    {
        using (SqlCommand command = new SqlCommand("", conn))
        {
            try
            {
                conn.Open();

                //Creating temp table on database
                command.CommandText = "CREATE TABLE #TmpTable(...)";
                command.ExecuteNonQuery();

                //Bulk insert into temp table
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                {
                    bulkcopy.BulkCopyTimeout = 660;
                    bulkcopy.DestinationTableName = "#TmpTable";
                    bulkcopy.WriteToServer(dt);
                    bulkcopy.Close();
                }

                // Updating destination table, and dropping temp table
                command.CommandTimeout = 300;
                command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // Handle exception properly
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

Notice that a single connection is used to perform the whole operation, in order to be able to use the temp table in each step, because the scope of the temp table is per connection.

Fastest way of performing Bulk Update in C# / .NET

The fastest way would be to bulk insert the data into temporary table using the built in SqlBulkCopy Class, and then update using join to that table

Or you can use a tool such as SqlBulkTools which does exactly this in an easy way.

var bulk = new BulkOperations();

using (TransactionScope trans = new TransactionScope())
{
    using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI")
    {
        bulk.Setup()
            .ForCollection(items)
            .WithTable("Items")
            .AddColumn(x => x.QuantitySold)
            .BulkUpdate()
            .MatchTargetOn(x => x.ItemID) 
            .Commit(conn);
    }

    trans.Complete();
}

Bulk update using LINQ

Get the list of users first, update them, then save changes. You would still need a foreach to update it though. This takes the List and selects only the Id, turning it into List (or whatever your ID field is), then gets all the users in that list from the database, updates them, and saves.

var dbUserList = db.Users.Where(x => users.Select(y => y.Id).Contains(x.Id));
foreach (var user in users)
{
     var dbUser = dbUserList.First(x => x.Id == user.Id);
     dbUser.name = user.name;
     dbUser.cat = user.cat;
}
db.SaveChanges();

C# code to bulk update SQL server

Your query seems to be the same for all cases..

Can't you rewrite your code to build just one SQL statement?

Something like this:

public int UpdateClaims(List<stSRK> _lt, string strClaimType)
{
    try
    {
        string allSno = String.Join(",", _lt.Select(l=> l.Sno.ToString()).ToArray());
        string allClaimID = String.Join(",", _lt.Select(l=> l.SClaimID.ToString()).ToArray());

        // Warning: NOT Production code, SQLInjection hazard!
        return Convert.ToInt16(SQL.ExecuteSQL("UPDATE table SET ClaimType = '" + strClaimType + @"' WHERE 
                        (Sno IN(" + allSno + ") AND ClaimID IN(" + allClaimID + "))"));
    catch (Exception e)
    {
        //This is not a good idea, as this way you loose all innerException 
        // information about the exception. And you might just want to remove 
        // the try-catch alltogether if you just rethrow the exception.
        throw e.Message;
    }
}

Bulk update SQL Server C#

You could use the SQL Server Import and Export Wizard:

http://msdn.microsoft.com/en-us/library/ms141209.aspx

Alternatively you could use the BULK TSQL Statement:

BULK
INSERT YourTable
FROM 'c:\YourTextFile.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

SELECT * FROM YourTable
GO

Assuming you are splitting on a comma delimiter. If you are using another character such as a space, change the FIELDTERMINATOR to the associated character.

Edit (To achieve the update from my comment):

UPDATE RealTable
SET value = tt.value
FROM
  RealTable r
INNER JOIN temp_table tt ON r.mid = tt.mid

How to perform batch update in Sql through C# code

Yes, you can use an SqlDataAdapter.

The SqlDataAdapter has InsertCommand and UpdateCommand properties which allow you to specify an SQLCommand to use to insert new rows into the database and an SqlCommand to update rows in the database respectively.

You can then pass a DataTable to the Update method of the dataadapter, and it will batch up the statements to the server - for rows in the DataTable that are new rows, it executes the INSERT command, for modified rows it executes the UPDATE command.

You can define the batch size using the UpdateBatchSize property.

This approach allows you to deal with large volumes of data, and allows you to nicely handle errors in different ways, i.e. if an error is encountered with a particular update, you can tell it to NOT throw an exception but to carry on with the remaining updates by setting the ContinueUpdateOnError property.

How to Bulk Update records in Entity Framework?

If you don't want to use an SQL statement, you can use the Attach method in order to update an entity without having to load it first :

using (myDbEntities db = new myDbEntities())
{
    try
    {
      //disable detection of changes to improve performance
      db.Configuration.AutoDetectChangesEnabled = false;

      //for all the entities to update...
      MyObjectEntity entityToUpdate = new MyObjectEntity() {Id=123, Quantity=100};
      db.MyObjectEntity.Attach(entityToUpdate);

      //then perform the update
      db.SaveChanges();
    }
    finally
    {
      //re-enable detection of changes
      db.Configuration.AutoDetectChangesEnabled = true;
    }
}

---

Related Topics