SqlBulkCopy: Copies data in batches to the data table and sqlbulkcopy data.

SqlBulkCopy: Copies data in batches to the data table and sqlbulkcopy data.

UseSqlBulkCopyClass can only write data to the SQL Server table. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to the able instance or the IDataReader instance can be used to read data

The following code uses ColumnMappings. Because the structure of the target table and the data source Datatable is inconsistent, such a ing is required to specify the ing relationship.

Public string SaveJHCData (LzShopBasicData [] datas) {var result = new AResult (); SqlConnection con = new SqlConnection (System. configuration. configurationManager. connectionStrings ["**"]. connectionString); con. open (); foreach (var item in datas) {Logger. info ("data update processing, shop name:" + item. shopName + "Data Date" + item. sellDate); try {using (TransactionScope scope = new TransactionScope () {DataTable JHCOrderItemsdt = items (item); SqlBulkCopy JHCOrderItemscopy = new SqlBulkCopy (con); JHCOrderItemscopy. columnMappings. add ("orderId", "orderId"); JHCOrderItemscopy. columnMappings. add ("auctionId", "auctionId"); JHCOrderItemscopy. columnMappings. add ("itemTitle", "itemTitle"); JHCOrderItemscopy. columnMappings. add ("tradeAmt", "tradeAmt"); JHCOrderItemscopy. columnMappings. add ("alipayNum", "alipayNum"); JHCOrderItemscopy. columnMappings. add ("tradeTime", "tradeTime"); JHCOrderItemscopy. columnMappings. add ("uv", "uv"); JHCOrderItemscopy. columnMappings. add ("srcId", "srcId"); JHCOrderItemscopy. columnMappings. add ("srcName", "srcName"); JHCOrderItemscopy. columnMappings. add ("DataType", "DataType"); JHCOrderItemscopy. columnMappings. add ("DataDate", "DataDate"); JHCOrderItemscopy. columnMappings. add ("OrderSourceID", "OrderSourceID"); JHCOrderItemscopy. columnMappings. add ("ShopName", "ShopName"); JHCOrderItemscopy. destinationTableName = "JHCOrderItems"; JHCOrderItemscopy. writeToServer (JHCOrderItemsdt); result. updatedata + = 1; result. updatedataText + = item. sellDate + ","; scope. complete (); Logger. info (item. sellDate + "transaction commit") ;}} catch (Exception ex) {Logger. error (ex. toString (); continue ;}} con. close (); return result. toSerializeObject ();}

2. IDataReader is used as the data source. In this way, I personally think it is rarely used. First, you need to obtain the connection between the target table and the source table. If both databases can be obtained, generally, you can solve the problem by directly operating SQL:

Here is the MSDN Code directly copied,

The AdventureWorks database can be directly downloaded from the Internet,

Using System. data. sqlClient; class Program {static void Main () {string connectionString = GetConnectionString (); // Open a sourceConnection to the AdventureWorks database. using (SqlConnection sourceConnection = new SqlConnection (connectionString) {sourceConnection. open (); // Perform an initial count on the destination table. sqlCommand commandRowCount = new SqlCommand ("select count (*) FROM" + "dbo. bulkCopyDemoMatchingColumns; ", sourceConnection); long countStart = System. convert. toInt32 (commandRowCount. executeScalar (); Console. writeLine ("Starting row count = {0}", countStart); // Get data from the source table as a SqlDataReader. sqlCommand commandSourceData = new SqlCommand ("SELECT ProductID, Name," + "ProductNumber" + "FROM Production. product; ", sourceConnection); SqlDataReader reader = commandSourceData. executeReader (); // Open the destination connection. in the real world you wowould // 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) {destinationConnection. open (); // 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) {bulkCopy. destinationTableName = "dbo. bulkCopyDemoMatchingColumns "; try {// Write from the source to the destination. bulkCopy. writeToServer (reader);} catch (Exception ex) {Console. writeLine (ex. message);} finally {// Close the SqlDataReader. the SqlBulkCopy // object is automatically closed at the end // of the using block. reader. close () ;}// Perform a final count on the destination // table to see how many rows were added. long countEnd = System. convert. toInt32 (commandRowCount. executeScalar (); Console. writeLine ("Ending row count = {0}", countEnd); Console. writeLine ("{0} rows were added. ", countEnd-countStart); Console. writeLine ("Press Enter to finish. "); Console. readLine () ;}} 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 ;";}}View Code

 

Practice: Use Type reflection to dynamically build a Datatable data source and store data in batches using SqlBulkCopy

1. Get an empty Datatable

var dt = bisdal.From<TopBrand>(TopBrand._.ID == -1, OrderByClip.Default).ToDataTable();

2. Fill the DataTable. Here, the attribute is assigned one by one to the target Datatable by traversing the external set.

         foreach (var item in brandselldataitems)                    {                        try                        {                            TopBrand topbrand = new TopBrand                            {                                BrandIndex = item.mk,                                BrandName = item.c58,                                Date = date,                                WinnerAmt = item.c60,                                WinnerPeople = item.c62,                                WinnerProNum = item.c61,                                HotTaobaoCategoryID = cid                            };                            CreateDtByItem<TopBrand>(topbrand, dt);                        }                        catch (Exception ex)                        {                            Logger.Error(ex.ToString());                            continue;                        }                    }

Here, we use reflection to traverse Object Property sets and dynamically construct DataTableRow objects.

       private void CreateDtByItem<T>(T item, DataTable dt)        {            System.Reflection.PropertyInfo[] properties = item.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);            var newrow = dt.NewRow();            foreach (System.Reflection.PropertyInfo pitem in properties)            {                string name = pitem.Name;                if (name == "children")                {                    continue;                }                object value = pitem.GetValue(item, null);                newrow[name] = value == null ? DBNull.Value : value;            }            dt.Rows.Add(newrow);        }

3. Save and store the database,

     BulkWriteToServer(con, "TopBrand", dt);

Because the data structure of the target table and the data source's Datatable is consistent, the ColumnMappings column ing operation can be saved directly by WriteToServer.

Private void BulkWriteToServer (SqlConnection con, string destinationtablename, DataTable sourcedt) {try {if (con. state = ConnectionState. closed) {con. open ();} SqlBulkCopy topbranddtcopy = new SqlBulkCopy (con); topbranddtcopy. destinationTableName = destinationtablename; topbranddtcopy. writeToServer (sourcedt); con. close ();} catch (Exception ex) {Logger. error ("batch add data:" + destinationtablename + "," + ex. toString ());}}

 

Complete call code:

 private void CreateTopBrandData(int date, int cid, List<BrandSellDataItem> brandselldataitems)        {            try            {                var dt = bisdal.From<TopBrand>(TopBrand._.ID == -1, OrderByClip.Default).ToDataTable();                foreach (var item in brandselldataitems)                {                    try                    {                        TopBrand topbrand = new TopBrand                        {                            BrandIndex = item.mk,                            BrandName = item.c58,                            Date = date,                            WinnerAmt = item.c60,                            WinnerPeople = item.c62,                            WinnerProNum = item.c61,                            HotTaobaoCategoryID = cid                        };                        CreateDtByItem<TopBrand>(topbrand, dt);                    }                    catch (Exception ex)                    {                        Logger.Error(ex.ToString());                        continue;                    }                }                BulkWriteToServer(con, "TopBrand", dt);            }            catch (Exception ex)            {                throw new Exception("CreateTopBrandData:" + ex.ToString());            }        }

 

Error in batch data insertion using SqlBulkCopy

Stay in contact and watch it online at night.

Import excel to store the data in the able, and use sqlbulkcopy to import datable to the database tables in batches.

Winform or net? For winfrom, refer to the following code!

Public void excelintodatagri ()
{
// Open a file selection box
OpenFileDialog ofd = new OpenFileDialog ();
Ofd. Title = "Excel file ";
Ofd. FileName = "";
Ofd. Filter = "Excel file (*. xls) | ";
Try
{
If (ofd. ShowDialog () = DialogResult. OK)
{
String tableName = "";
// If (arratlist! = Null)
//{
// Arratlist. Clear ();
//}
String Path = ofd. FileName;
String strConn = "Provider = Microsoft. ace. oleDb.12.0; "+" data source = "+ Path +"; Extended Properties = 'excel 12.0; HDR = Yes; IMEX = 1 '";
OleDbConnection conn = new OleDbConnection (strConn );
Conn. Open ();
System. Data. DataTable schemaTable = conn. GetOleDbSchemaTable (System. Data. OleDb. OleDbSchemaGuid. Tables, null );
TableName = schemaTable. Rows [0] [2]. ToString (). Trim ();
// For (int I = 0; I <schemaTable. Rows. Count; I ++)
//{
// Arratlist. add (schemaTable. rows [I] [2]. toString (). trimStart ('/''). trim ('/'',' $ '));//
... The remaining full text>