SqlBulkCopy批量插入DB，异常处理，某行出错，保证正确行可插入DB

最新推荐文章于 2023-11-01 14:52:51 发布

原创最新推荐文章于 2023-11-01 14:52:51 发布 · 4.1k 阅读

0 ·

CC 4.0 BY-SA版权

C# 同时被 2 个专栏收录

45 篇文章

订阅专栏

MSSQL

7 篇文章

订阅专栏

本文介绍了一种使用SqlBulkCopy进行数据库批量插入数据的方法，并通过递归拆分数据集来处理插入过程中可能出现的键冲突问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="conString" connectionString="server=192.168.201.251;database=omsdata_function_test2;uid=sa;password=1234"/>
</connectionStrings>
</configuration>

program.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;

namespace InsertDBCon
{
    class Program
    {
        private DataTable FTable;
        private SqlConnection FCon;
        private string FStrConnecttion;

private const string strSqlInsert = "insert into Price2 values({0},'{1}','{2}',{3},{4},{5},{6},{7},{8})";
private const string strSqlCount = "select max(sequence) from Price2";

        private const string strSqlSelect = "select top 100000 * from Price";
        static void Main(string[] args)
        {
            Program pro = new Program();
            DateTime time;

//x从一个表中选出10万条要查入的数据
DataTable dt = pro.ExecuteQuery(Program.strSqlSelect);

#region sqlbulk

            DataTable table = Program.GetDataTable();
            DataRow row;
            pro.ExecuteNonQuery(Program.strSqlDelete);
            time = DateTime.Now;
            long sequence = pro.GetRecordCount();
            pro.Close();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = table.NewRow();
                row["Sequence"] = sequence++;
                row["DateTime"] = dt.Rows[i][1];
                row["Symbol"] = dt.Rows[i][2];
                row["Bid"] = dt.Rows[i][3];
                row["Offer"] = dt.Rows[i][4];
                row["LPrice"] = dt.Rows[i][5];
                row["BidSize"] = dt.Rows[i][6];
                row["Volume"] = dt.Rows[i][7];
                row["OfferSize"] = dt.Rows[i][8];
                table.Rows.Add(row);
                if (i == 99)
                {//Sequence是key，设置键重复，测试，此行出错，但是其他正确的依然可以插入数据库
                    row["Sequence"] = sequence - 2;
                }

                if ((i + 1) % 10000 == 0)
                {//每一万条插入一次DB
                    pro.InsertSqlBulk(table);
                    table.Rows.Clear();
                }
            }
            if (table.Rows.Count != 0)
            {//不足10000行的记录，一起插入DB
                pro.InsertSqlBulk(table);
                table.Rows.Clear();
            }

//算出所用时间
Console.WriteLine("sqlbulk batch:" + (DateTime.Now - time).TotalSeconds);

#endregion

}

        public Program()
        {
            FTable = GetDataTable();
            FStrConnecttion = ConfigurationManager.ConnectionStrings["conString"].ToString();
            FCon = new SqlConnection(FStrConnecttion);
        }

        public int GetRecordCount()
        {
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(strSqlCount, FCon);
            sda.Fill(ds, "recordcount");

            if (ds.Tables.Count != 0)
                if (ds.Tables[0].Rows[0][0].Equals(System.DBNull.Value))
                    return 0;
                else
                    return (int)(ds.Tables[0].Rows[0][0]);

return 0;
}

        public void InsertSqlBulk(DataTable dt)
        {
            // Open();
            //SqlBulkCopy sqlBulk = new SqlBulkCopy(FStrConnecttion, SqlBulkCopyOptions.UseInternalTransaction);
            //sqlBulk.NotifyAfter = dt.Rows.Count;
            //sqlBulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);

            SqlBulkCopy sqlBulk = new SqlBulkCopy(FStrConnecttion);
            sqlBulk.DestinationTableName = "Price2";
            try
            {
                sqlBulk.WriteToServer(dt);
            }
            catch (Exception e)
            {//如果出现异常，SqlBulkCopy 会使数据库回滚，所有Table中的记录都不会插入到数据库中，此时，把Table折半插入，先插入一半，再插入一半。如此递归，直到只有一行时，如果插入异常，则返回。
                if (dt.Rows.Count == 1)
                    return;
                int middle = dt.Rows.Count / 2;
                DataTable table = dt.Clone();
                for (int i = 0; i < middle; i++)
                    table.ImportRow(dt.Rows[i]);

InsertSqlBulk(table);

                table.Clear();
                for (int i = middle; i < dt.Rows.Count; i++)
                    table.ImportRow(dt.Rows[i]);
                InsertSqlBulk(table);

            }
            finally
            {
                sqlBulk.Close();
            }
        }

        public bool Open()
        {
            if (FCon.State.Equals(ConnectionState.Closed))
                FCon.Open();
            if (FCon.State.Equals(ConnectionState.Open))
                return true;
            return false;
        }

        public bool Close()
        {
            if (FCon.State.Equals(ConnectionState.Open))
                FCon.Close();
            if (FCon.State.Equals(ConnectionState.Closed))
                return true;
            return false;
        }

        public DataTable ExecuteQuery(string sql)
        {
            DataSet ds = new DataSet();
            DataTable dt;

            SqlDataAdapter sda = new SqlDataAdapter(sql, FCon);
            sda.Fill(ds);
            if (ds.Tables.Count != 0)
                dt = ds.Tables[0];
            else
                dt = new DataTable();

return dt;
}

        public static DataTable GetDataTable()
        {
            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Int32");
            dc.ColumnName = "Sequence";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.DateTime");
            dc.ColumnName = "DateTime";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.String");
            dc.ColumnName = "Symbol";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Double");
            dc.ColumnName = "Bid";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Double");
            dc.ColumnName = "Offer";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Double");
            dc.ColumnName = "LPrice";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Double");
            dc.ColumnName = "BidSize";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Double");
            dc.ColumnName = "Volume";
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Double");
            dc.ColumnName = "OfferSize";
            dt.Columns.Add(dc);
            return dt;

}

    public class TTickRecordNow
    {
        public string Symbol;
        public double Bid;
        public double Offer;
        public double Price;
        public double BidSize;
        public double Volume;
        public double OfferSize;
        public DateTime DateTime;
        public long Sequence;
    }
}