Using (SqlCommand command = new SqlCommand("CREATE TABLE Using (SqlConnection conn = new SqlConnection(connetionString)) Public static void BulkUpdateData(List list, string connetionString)
ICOLLECTIONS LITE VS COLLECTIONS UPDATE
Step 2: pass this list and ConnectionString to BulkUpdate Method As shown belowĮxample: //Method for Bulk Update the Data Step 1: put the data which you want to update and primary key in a list. NET and SQL handle transferring the table and updating the records itself. While there may be ways that offer performance improvements (such as dropping it into a temporary table then iterating over that table), I think they're outweighed by the simplicity of letting. This method provides the benefit of both clarity and ease of maintenance.
NET piece, it's a simple matter of setting the parameter type as SqlDbType.Structured and setting the value of said-parameter to the Data Table that contains the records you want to update. Microsoft has both syntax reference and an article with examples for the Merge.įor the. Within the Stored Procedure, you can either join directly on some unique key (if all rows you are updating exist), or - if you might run into a situation where you are having to do both updates and inserts - use the SQL Merge command within the stored procedure to handle both the updates and inserts as applicable. Just set up the type with the columns of the data table, and pass in said-data table as a parameter in the SQL command. In my personal experience, the best way to handled this situation is utilizing a Stored Procedure with a Table-Valued Parameter and a User-Defined Table Type. 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. FROM " + TableName + " T INNER JOIN #TmpTable Temp ON. Updating destination table, and dropping temp tableĬommand.CommandText = "UPDATE T SET. Using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))īulkcopy.DestinationTableName = "#TmpTable" Using (SqlCommand command = new SqlCommand("", conn))Ĭommand.CommandText = "CREATE TABLE #TmpTable(.)" Using (SqlConnection conn = new SqlConnection()) 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.Įxample: public static void UpdateData(List list,string TableName) 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. Now I want to do an update operation, is there any way as for inserting data is done by SqlBulkCopy for Updating data to DataBase From C#.Net Row = prop.GetValue(item) ? DBNull.Value (prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ? prop.PropertyType)
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)) įoreach (PropertyDescriptor prop in properties) Public static DataTable ConvertToDataTable(IList data) Using (SqlBulkCopy bulkcopy = new SqlBulkCopy())īulkcopy.DestinationTableName = TableName Where InsertData is public static void InsertData(List list,string TableName)ĬlsBulkOperation blk = new clsBulkOperation() ĬonfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal) Which contain information of all bulk data which I want to insert to databaseĪnd pass it to my bulk insertion operation InsertData(LiMyList, "MyTable") I then insert that list to a database via SqlBulkCopy.
For inserting a huge amount of data in a database, I used to collect all the inserting information into a list and convert this list into a DataTable.