ADO.NET to SqlServer批量Insert

来源：互联网发布：淘宝智能版改专业版编辑：程序博客网时间：2024/05/23 11:56

实现步骤: 1,构造DataTable

        2,封装DataTable为DataTabl赋值

         3,利用SqlBulkCopy提交DataTable

代码:1,构造DataTable

       public DataTable BuildTable()        {            try            {                DataTable dt = new DataTable();                dt.Columns.AddRange(new DataColumn[] { new DataColumn("PointX", typeof(float))});                dt.Columns.AddRange(new DataColumn[] { new DataColumn("PointY", typeof(float)) });                dt.Columns.AddRange(new DataColumn[] { new DataColumn("PointType", typeof(string))});                dt.Columns.AddRange(new DataColumn[] { new DataColumn("SbType", typeof(string)) });                return dt;            }            catch (Exception ex)            {                LogHelp.WriteInLog("构造DataTable异常:" + ex.Message, true);                return null;            }        }

    2,DataTabl赋值
        DataTable dt =BuildTable();        dataRow dr = dt.NewRow();         dr["PointX"] = x;              dr["PointY"] = y;             dr["PointType"] = "kk";           dr["SbType"] = "kk";             dt.Rows.Add(dr);

    3,提交DataTable

    public bool WriteToServer(DataTable dt)        {            try            {                OpenConn();                using (SqlBulkCopy sqlBC = new SqlBulkCopy(sqlConn))                {                    sqlBC.BatchSize = 1000;        //设置逾时的秒数                           sqlBC.BulkCopyTimeout = 60;         //设置 NotifyAfter 属性，以便在每拷贝 10000 条记录至数据表后，呼叫事件处理函数                          sqlBC.NotifyAfter = 10000;                    sqlBC.DestinationTableName = "目标表名";                    sqlBC.ColumnMappings.Add("PointX", "PointX");                    sqlBC.ColumnMappings.Add("PointY", "PointY");                    sqlBC.ColumnMappings.Add("PointType", "PointType");                    sqlBC.ColumnMappings.Add("SbType", "SbType");                    sqlBC.WriteToServer(dt);                }                sqlConn.Dispose();                return true;            }            catch (Exception ex)            {                LogHelp.WriteInLog("数据批量插入异常:" + ex.Message, true);                return false;            }        }

万级控制在秒