/// <summary> /// This method inserts block data. /// </summary> /// <param name="connection">The already open System.Data.SqlClient.SqlConnection instance that will be used.</param> /// <param name="transaction">An existing System.Data.SqlClient.SqlTransaction instance under which the bulk copy will occur.</param> /// <param name="data">A System.Data.DataTable whose rows will be copied to the destination table.</param> /// <param name="tableName">The name of the table that will be inserted into the data.</param> /// <param name="bulkCopyOptions">Bitwise flag that specifies one or more options to use with an instance of System.Data.SqlClient.SqlBulkCopy.</param> /// <param name="batchSize">Number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server. /// The integer value of the System.Data.SqlClient.SqlBulkCopy.NotifyAfter property, or zero if the property has not been set.</param> /// <param name="bulkCopyTimeout">Number of seconds for the operation to complete before it times out. /// The default is 30 seconds. A value of 0 indicates no limit; the bulk copy will</param> /// <param name="notifyAfter">Defines the number of rows to be processed before generating a notification event. /// System.Data.SqlClient.SqlBulkCopy.NotifyAfter property, or zero if the property has not been set.</param> public static void InsertInBlock(this SqlConnection connection, SqlTransaction transaction, DataTable data, string tableName, SqlBulkCopyOptions bulkCopyOptions = SqlBulkCopyOptions.TableLock, int batchSize = 0, int bulkCopyTimeout = 30, int notifyAfter = 0) { if (tableName == "") { throw new Exception(string.Format("Parameter {0} cannot be null", nameof(tableName))); } if (connection.State != ConnectionState.Open) { throw new Exception("Connection is not open"); } var table = connection.GetTableStructure(transaction, tableName); foreach (DataRow row in data.Rows) { table.AddRow(row); } using (SqlBulkCopy bulk = new SqlBulkCopy(connection, bulkCopyOptions, transaction)) { bulk.BatchSize = batchSize; bulk.BulkCopyTimeout = bulkCopyTimeout; bulk.NotifyAfter = notifyAfter; bulk.DestinationTableName = tableName; bulk.WriteToServer(table); } }
/// <summary> /// This method gets the structure of the database table. /// </summary> /// <param name="connection">The already open System.Data.SqlClient.SqlConnection instance that will be used.</param> /// <param name="tableName">The name of the table that will be inserted into the data.</param> /// <returns>the table structure in System.Data.DataTable</returns> public static DataTable GetTableStructure(this SqlConnection connection, string tableName) => connection.GetTableStructure(transaction: null, tableName: tableName);