How to do bulk update from datatable to SQL Table using C#?

Rahul Sharma

Hello,

How to do bulk update from datatable to SQL table in C#.
I have data in datatable and I have target table in SQL.
I want to do bulk update into SQL table considering some columns as condition.

Please help me with this.

Thanks,
Rahul.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

rakesh kumar6y ago
Bulk Update:

Step 1:Put the data which you want to update and primary key in a list.

Step 2:Pass this list and ConnectionString to BulkUpdate Method As shown below

Example:
//Method for Bulk Update the Data public static void BulkUpdateData(List list, string connetionString)
{ DataTable dt = new DataTable("MyTable");
dt = ConvertToDataTable(list);
using (SqlConnection conn = new SqlConnection(connetionString))
{
using (SqlCommand command = new SqlCommand("CREATE TABLE #TmpTable([PrimaryKey],[ColumnToUpdate])", conn))
{
try
{
conn.Open();
command.ExecuteNonQuery();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
{
bulkcopy.BulkCopyTimeout = 6600;
bulkcopy.DestinationTableName = "#TmpTable";
bulkcopy.WriteToServer(dt);
bulkcopy.Close();
}
command.CommandTimeout = 3000;
command.CommandText = "UPDATE P SET P.[ColumnToUpdate]= T.[ColumnToUpdate] FROM [TableName Where you want to update ] AS P INNER JOIN #TmpTable AS T ON P.[PrimaryKey] = T.[PrimaryKey] ;DROP TABLE #TmpTable;";
command.ExecuteNonQuery(); }
catch (Exception ex)
{ // Handle exception properly }
finally {
conn.Close();
} } } }
Step 3:Put The ConvertToDataTable Method as shown Below

Example:
public static DataTable ConvertToDataTable(IList data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties) row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
Notes:WhereEver SquareBracket[] is there put your own value.
Manish Kumar Choudhary11y ago
Hi Rahul Sharma,

use this function.
public static void UpdateData < T > (List < T > list, string TabelName)
{
DataTable dt = new DataTable("MyTable");
clsBulkOperation blk = new clsBulkOperation();
dt = ConvertToDataTable(list);
ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
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 = TabelName;
     bulkcopy.WriteToServer(dt);
     bulkcopy.Close();
    }
    // Updating destination table, and dropping temp table
    command.CommandTimeout = 300;
    command.CommandText = "UPDATE T SET ... FROM " + TabelName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
    command.ExecuteNonQuery();
   } catch (Exception ex)
   {
    // Handle exception properly
   } finally
   {
    conn.Close();
   }
  }
}
}
Ramesh Maruthi11y ago
Hey Rahul,

its also mentioned for updating if it exists but here is the extra thing u need to do is, creating a temp table and writing a stored proc for updating. I think directly with sql bulk copy u can't do without creating a temp table. In entity framework we can do bulkupdates by using entity bulkupdate but I don't know without entity framework

please see the below mentioned links

http://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp

http://forums.asp.net/t/1831869.aspx?SQLBulkCopy+Update+possible+
Rahul Sharma11y ago
Hi Ramesh,

This is for insert. I want to write code for update.

Thanks,
Rahul
Ramesh Maruthi11y ago
Hi Rahul,

You can use Sqlbulkcopy as shown in the below links, but instead of using List as shown in the example u can use datatable

bulkCopy.WriteToServer(CustomerList); // instead of this

u can do something like this

bulkCopy.WriteToServer(Yourdatatable)

http://stackoverflow.com/questions/12521692/c-sharp-bulk-insert-sqlbulkcopy-update-if-exists

http://www.codeproject.com/Articles/16922/SQL-Bulk-Copy-with-C-Net