bulk insert dataTable into a database table

 private bool InsertIntoComments(DataTable dataTable)
        {
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[“ASCGrants_DevConnection”].ConnectionString;
            if (string.IsNullOrWhiteSpace(connectionString))
                return false;
            SqlConnection connection = new SqlConnection(connectionString);
            using (connection)
            {
                SqlTransaction transaction = null;
                connection.Open();
                try
                {
                    transaction = connection.BeginTransaction();
                    using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
                    {
                        sqlBulkCopy.DestinationTableName = “Comments”;
                        sqlBulkCopy.ColumnMappings.Add(“CommentsGuid”, “CommentsGuid”);
                        sqlBulkCopy.ColumnMappings.Add(“HostApplicationGuid”, “HostApplicationGuid”);
                        sqlBulkCopy.ColumnMappings.Add(“GrantsEntityGuid”, “GrantsEntityGuid”);
                        sqlBulkCopy.ColumnMappings.Add(“StatusGuid”, “StatusGuid”);
                        sqlBulkCopy.ColumnMappings.Add(“Description”, “Description”);
                        sqlBulkCopy.ColumnMappings.Add(“Active”, “Active”);
                        sqlBulkCopy.ColumnMappings.Add(“CreatedBy”, “CreatedBy”);
                        sqlBulkCopy.ColumnMappings.Add(“CreatedOn”, “CreatedOn”);
                        sqlBulkCopy.WriteToServer(dataTable);
                    }
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                }
                connection.Close();
            }
            return true;
        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s