C# BulkCopy方式数据批量导入数据库

素颜马尾好姑娘i 2022-01-09 23:13 216阅读 0赞

/// <summary>
            /// BulkCopy方式批量导入黑名单
            /// </summary>
            /// <param name="mobileList"></param>
            /// <returns></returns>
            public static int BulkCopyWhite_UserId(List<string> mobileList, string UserCode)
            {
                try
                {
                    Base.WriteErrorLog("开始BulkCopyBlack", HttpContext.Current.Server.MapPath("..\\ErrorLog\\"));
                    DataTable dt = new DataTable();
                    dt.Columns.Add("mobile", typeof(string));
                    dt.Columns.Add("usercode", typeof(string));
                    dt.Columns.Add("whiteno", typeof(string));
                    dt.Columns.Add("createdate", typeof(DateTime));
                    //选择了多个用户
                    string[] arr = UserCode.Split(',');
    
                    foreach (string mobile in mobileList)
                    {
                        foreach (var item in arr)
                        {
                            DataRow dr = dt.NewRow();
                            dr["mobile"] = mobile;
                            dr["usercode"] = item;
                            dr["whiteno"] = "用户添加";
                            dr["createdate"] = DateTime.Now.ToString();
                            dt.Rows.Add(dr);
                        }
                    }
                    string str = sqlConnString();
                    SqlConnection conn = new SqlConnection(str);
                    conn.Open();
                    Base.WriteErrorLog("conn.Open！！", HttpContext.Current.Server.MapPath("..\\ErrorLog\\"));
                    using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
                    {
                        sqlBC.BatchSize = 1000;
                        sqlBC.BulkCopyTimeout = 60;
                        //sqlBC.NotifyAfter = 10000;
                        //sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                        sqlBC.DestinationTableName = "dbo.B_Sms_WhiteList_Info";
                        sqlBC.ColumnMappings.Add("mobile", "White_No");
                        sqlBC.ColumnMappings.Add("usercode", "Creator");
                        sqlBC.ColumnMappings.Add("whiteno", "White_Note");
                        sqlBC.ColumnMappings.Add("createdate", "CreateDate");
                        sqlBC.WriteToServer(dt);
                    }
                    dt.Dispose();
                    conn.Dispose();
                    Base.WriteErrorLog("结束BulkCopyBlack", HttpContext.Current.Server.MapPath("..\\ErrorLog\\"));
                    return 1;
                }
                catch
                {
                    return -1;
                }
            }