ADO.NET to SqlServer批量Insert

 实现步骤: 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;
            }
        }

 

万级控制在秒