This article describes the method of using SqlBulkCopy to batch copy data to a data table. Share it for your reference. The specific implementation method is as follows:
Use the SqlBulkCopy class to write data to SQL Server tables only. However, data sources are not limited to SQL Server; any data source can be used as long as the data can be loaded to the DataTable instance or the data can be read using the IDataReader instance
1. How to use Datatable as a data source:
The following code uses ColumnMappings. Because the structure of the target table and the data source Datatable are inconsistent, such a mapping is needed to specify the corresponding relationship.
{
var result = new AResult();
SqlConnection con = new SqlConnection(["**"].ConnectionString);
();
foreach (var item in datas)
{
("Data update processing, store name: " + + "Data date" + );
try
{
using (TransactionScope scope = new TransactionScope())
{
DataTable JHCOrderItemsdt = SaveJHCOrderItemsData(item);
SqlBulkCopy JHCOrderItemscopy = new SqlBulkCopy(con);
("orderId", "orderId");
("auctionId", "auctionId");
("itemTitle", "itemTitle");
("tradeAmt", "tradeAmt");
("alipayNum", "alipayNum");
("tradeTime", "tradeTime");
("uv", "uv");
("srcId", "srcId");
("srcName", "srcName");
("DataType", "DataType");
("DataDate", "DataDate");
("OrderSourceID", "OrderSourceID");
("ShopName", "ShopName");
= "JHCOrderItems";
(JHCOrderItemsdt);
+= 1;
+= + ",";
();
( + "Transaction Commit");
}
}
catch (Exception ex)
{
(());
continue;
}
}
();
return ();
}
2. Use IDataReader as a data source. I personally think this method is rarely used. First of all, you need to get the two database connections of the target table and the source table. If both can be obtained, you can generally solve the problem by directly operating SQL:
Here is the direct copy of the MSDN code.
The AdventureWorks database used can be downloaded directly online, and the download address is as follows:/releases
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
";",
sourceConnection);
long countStart = .ToInt32(
());
("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, " +
"ProductNumber " +
"FROM ;", sourceConnection);
SqlDataReader reader =
();
// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
();
// Set up the bulk copy object.
// Note that the column positions in the source
// data reader match the column positions in
// the destination table so there is no need to
// map columns.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{
=
"";
try
{
// Write from the source to the destination.
(reader);
}
catch (Exception ex)
{
();
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = .ToInt32(
());
("Ending row count = {0}", countEnd);
("{0} rows were added.", countEnd - countStart);
("Press Enter to finish.");
();
}
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Practical combat: Dynamically build Datatable data source with the help of type reflection, and batch save and store it into the library through SqlBulkCopy
1. Get an empty Datatable:
2. Fill in DataTable. Here, by traversing the external collection, the attribute attributes are assigned to the target Datatable one by one.
{
try
{
TopBrand topbrand = new TopBrand
{
BrandIndex = ,
BrandName = item.c58,
Date = date,
WinnerAmt = item.c60,
WinnerPeople = item.c62,
WinnerProNum = item.c61,
HotTaobaoCategoryID = cid
};
CreateDtByItem<TopBrand>(topbrand, dt);
}
catch (Exception ex)
{
(());
continue;
}
}
Here, with the help of reflection, iterates over the entity attribute collection and dynamically builds the DataTableRow object
{
[] properties = ().GetProperties( | );
var newrow = ();
foreach ( pitem in properties)
{
string name = ;
if (name == "children")
{
continue;
}
object value = (item, null);
newrow[name] = value == null ? : value;
}
(newrow);
}
3. Save and enter the library:
Here, because the Datatable data structure of the target table and the data source are consistent, the ColumnMappings column mapping operation is omitted, and it can be saved directly from WriteToServer.
{
try
{
if ( == )
{
();
}
SqlBulkCopy topbranddtcopy = new SqlBulkCopy(con);
= destinationtablename;
(sourcedt);
();
}
catch (Exception ex)
{
("Batch new data:" + destinationtablename + "," + ());
}
}
Complete call code:
{
try
{
var dt = <TopBrand>(TopBrand._.ID == -1, ).ToDataTable();
foreach (var item in brandselldataitems)
{
try
{
TopBrand topbrand = new TopBrand
{
BrandIndex = ,
BrandName = item.c58,
Date = date,
WinnerAmt = item.c60,
WinnerPeople = item.c62,
WinnerProNum = item.c61,
HotTaobaoCategoryID = cid
};
CreateDtByItem<TopBrand>(topbrand, dt);
}
catch (Exception ex)
{
(());
continue;
}
}
BulkWriteToServer(con, "TopBrand", dt);
}
catch (Exception ex)
{
throw new Exception("CreateTopBrandData:" + ());
}
}
I hope this article will be helpful to everyone's C# programming.