C Realization of SQL Batch Insertion of Data into Tables
- 2021-09-12 01:57:59
- OfStack
This article illustrates how C # implements SQL batch insertion of data into tables. Share it for your reference, as follows:
#region Help instance: SQL Insert data in batches Multiple methods
/// <summary>
/// SqlBulkCopy Insert data into the database in batches
/// </summary>
/// <param name="sourceDataTable"> Data source table </param>
/// <param name="targetTableName"> Target table on server </param>
/// <param name="mapping"> Create a new column map and reference the column names of the source and destination columns with column ordinal numbers. </param>
public static void BulkToDB(DataTable sourceDataTable, string targetTableName, SqlBulkCopyColumnMapping[] mapping)
{
/* Invoke method -2012 Year 11 Month 16 Daily writing
//DataTable dt = Get_All_RoomState_ByHID();
//SqlBulkCopyColumnMapping[] mapping = new SqlBulkCopyColumnMapping[4];
//mapping[0] = new SqlBulkCopyColumnMapping("Xing_H_ID", "Xing_H_ID");
//mapping[1] = new SqlBulkCopyColumnMapping("H_Name", "H_Name");
//mapping[2] = new SqlBulkCopyColumnMapping("H_sName", "H_sName");
//mapping[3] = new SqlBulkCopyColumnMapping("H_eName", "H_eName");
//BulkToDB(dt, "Bak_Tts_Hotel_Name", mapping);
*/
SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString);
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); // Effective batch loading with data from other sources sql server In the table
bulkCopy.DestinationTableName = targetTableName; // The name of the target table on the server
bulkCopy.BatchSize = sourceDataTable.Rows.Count; // Every 1 Number of rows in batch
try
{
conn.Open();
if (sourceDataTable != null && sourceDataTable.Rows.Count != 0)
{
for (int i = 0; i < mapping.Length; i++)
bulkCopy.ColumnMappings.Add(mapping[i]);
// Copies all rows from the supplied data source to the target table
bulkCopy.WriteToServer(sourceDataTable );
}
}
catch (Exception ex)
{
//throw ex;
Common.WriteTextLog("BulkToDB", ex.Message);
}
finally
{
conn.Close();
if (bulkCopy != null)
bulkCopy.Close();
}
}
/// <summary>
/// SQL2008 Custom table types can be supported by the above : Calling stored procedure cursors - Insert data into the database in batches , attention
/// </summary>
/// <param name="sourceDataTable"></param>
public void DataTableToHotelDB(DataTable sourceDataTable)
{
/* -2012 Year 11 Month 15 Daily writing
ALTER PROCEDURE [dbo].[P_InsertSubject]
@tempStudentID int
AS
DECLARE rs CURSOR LOCAL SCROLL FOR
select H_ID from Tts_Hotel_Name
OPEN rs
FETCH NEXT FROM rs INTO @tempStudentID
WHILE @@FETCH_STATUS = 0
BEGIN
Insert student (tempStudentID) values (@tempStudentID)
FETCH NEXT FROM rs INTO @tempStudentID
END
CLOSE rs
* ***************************************************************
* create table Orders
(
Orders_ID int identity(1,1) primary key,
ItemCode nvarchar(50) not null,
UM nvarchar(20) not null,
Quantity decimal(18,6) not null,
UnitPrice decimal(18,6) not null
)
-- Create user-defined table types in programmability -> Typicality -> User-defined table type
create type OrdersTableType as table
(
ItemCode nvarchar(50) not null,
UM nvarchar(20) not null,
Quantity decimal(18,6) not null,
UnitPrice decimal(18,6) not null
)
go
create procedure Pro_Orders
(
@OrdersCollection OrdersTableType readonly
)
as
insert into Orders([ItemCode],[UM],[Quantity],[UnitPrice])
SELECT oc.[ItemCode],oc.[UM],[Quantity],oc.[UnitPrice] FROM @OrdersCollection AS oc;
go
*
*/
SqlParameter[] parameters = {new SqlParameter("@OrdersCollection", SqlDbType.Structured)};
parameters[0].Value = sourceDataTable;
new SQLHelper().ExecuteScalar("P_DataTable_ToHotelDB", parameters, true);
}
#endregion
For more readers interested in C # related content, please check out the topics on this site: "C # Data Structure and Algorithm Tutorial", "C # Common Control Usage Tutorial", "C # Object-Oriented Programming Introduction Tutorial" and "C # Programming Thread Use Skills Summary"
I hope this article is helpful to everyone's C # programming.