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

Question

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.

Ramesh Maruthi · Answer

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 Sharma · Answer

Hi Ramesh, This is for insert. I want to write code for update. Thanks, Rahul

Ramesh Maruthi · Answer

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

rakesh kumar · Answer

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 Choudhary · Answer

Hi Rahul Sharma, use this function. public static void UpdateData (List 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(); } } } }