sqlbulkcopy

Mapping columns in a DataTable to a SQL table with SqlBulkCopy

You probably need some thing like public void BatchBulkCopy(DataTable dataTable, string DestinationTbl, int batchSize) { // Get the DataTable DataTable dtInsertRows = dataTable; using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = DestinationTbl; // Number of records to be processed in one go sbc.BatchSize = batchSize; // Add your column mappings here sbc.ColumnMappings.Add(“field1″,”field3”); sbc.ColumnMappings.Add(“foo”,”bar”); … Read more

SqlBulkCopy – The given value of type String from the data source cannot be converted to type money of the specified target column

For the people stumbling across this question and getting a similar error message in regards to an nvarchar instead of money: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. This could be caused by a too-short column. For example, if your column … Read more

Sql Bulk Copy/Insert in C#

TL;DR If you have your data already represented as DataTable, you can insert it to the destination table on the server with SqlBulkCopy: string csDestination = “put here the a connection string to the database”; using (SqlConnection connection = new SqlConnection(csDestination)) using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { connection.Open() bulkCopy.DestinationTableName = “TUrls”; bulkCopy.WriteToServer(dataTableOfUrls); } If … Read more

sqlbulkcopy using sql CE

BULKCOPY is not supported in SQL CE. Here is the fastest way if you have a huge number of rows in your table; insert is too slow! using (SqlCeConnection cn = new SqlCeConnection(yourConnectionString)) { if (cn.State == ConnectionState.Closed) cn.Open(); using (SqlCeCommand cmd = new SqlCeCommand()) { cmd.Connection = cn; cmd.CommandText = “YourTableName”; cmd.CommandType = CommandType.TableDirect; … Read more

Any way to SQLBulkCopy “insert or update if exists”?

I published a nuget package (SqlBulkTools) to solve this problem. Here’s a code example that would achieve a bulk upsert. var bulk = new BulkOperations(); var books = GetBooks(); using (TransactionScope trans = new TransactionScope()) { using (SqlConnection conn = new SqlConnection(ConfigurationManager .ConnectionStrings[“SqlBulkToolsTest”].ConnectionString)) { bulk.Setup<Book>() .ForCollection(books) .WithTable(“Books”) .AddAllColumns() .BulkInsertOrUpdate() .MatchTargetOn(x => x.ISBN) .Commit(conn); } trans.Complete(); … Read more

Get an IDataReader from a typed List

Get the latest version from the code on this post Nothing like code churn in plain sight: Here is a pretty complete implementation. You can instantiate an IDataReader over IList IEnumerable, IEnumerable (ergo IQueryable). There is no compelling reason to expose a generic type parameter on the reader and by omitting it, I can allow … Read more