insert batch record

Question

what is the best way to insert,update batch records from code behind to ms sql?

Answer 1

Depends on how your data access layer looks like. If you are using enterprise library, then I would say this. And if you are using linq to sql then this.

Answer 2

In terms of INSERTs, SqlBulkCopy is the fastest way to bulk load data into SQL Server. I've blogged about how to use it/demonstrated the performance here - comparing to the other approach of using an SqlDataAdapter to send batched inserts via SqlDataAdapter.Update in conjunction with an SqlDataAdapter.InsertCommand.

In terms of UPDATEs, one technique is to bulk load the data into a "temporary" staging table in the database using SqlBulkCopy. And then, run an update on your underlying table from this staging table. Alternatively, you can use the SqlDataAdapter.Update approach, in conjunction with the SqlDataAdapter.UpdateCommand

For raw throughput, SqlBulkCopy (INSERTs only) is the ideal way. However, for handling errors with specific records, the SqlDataAdapter approach is good because you can tell it to continue sending rows to the DB in the event that one fails (e.g. say you get a constraint error on a particular record, you can choose to ContinueUpdateOnError, and then at the end identify those that did error.