.NET GENES

Good day! Normally, in a situation where you want to inject thousands of rows to the database using C# and SQL Server, the optimal solution would be using built-in SQLBulkCopy() function. However, you may encounter the error message "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. String or binary data would be truncated." The code below works but in some instance may throw that kind of exception. private bool SQLBulkCopy<T>( string SqlConn, List<T> inList, string tableName, ref string errMsg, int optBatchSize = 5000, bool optTableLock = true ) { SqlBulkCopyOptions lockType; SqlTransaction transaction; if (optTableLock) lockType = SqlBulkCopyOptions.TableLock; else lockType = SqlBulkCopyOptions.Default; try { using ( var connection = new SqlConnection(SqlConn)) { connection.Open(); transaction = connection.BeginTransaction(); using ( var bulkCopy ...

Good afternoon! Before I go into details, this post is solely based on the solution provided in this link Bulk Update In C# . The purpose of this tutorial is to provide step by step approach on how bulk update can be achieved using SqlBulkCopy, C#.NET And SQL Server. First is to create a simple table in your database that holds some fictitious records. USE [testdatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( [ID] [int] NOT NULL , [Name] [varchar](50) NULL , [Address] [varchar](50) NULL ) ON [ PRIMARY ] GO Database table with temp records Next is to create a console app that will perform the bulk update of records. The key to solving performance issues in updating records is to use a temporary table approach. class Program { static void Main( string [] args) { var listPerson = new List<Person> { new Person() {Id = 1001, Name = "James A." , Address = "US" }, new Person(...