c#大批量数据插入

来源：互联网发布：阿里云 lnmp 无法访问编辑：程序博客网时间：2024/06/06 01:51
 /// <summary>        /// 批量插入数据        /// </summary>        /// <param name="list"></param>        /// <returns></returns>        public string InsertPaperCertInfoListData(List<PaperCertInfo> list)        {            string returnStr = string.Empty;            DataTable dt = new DataTable();            var con = dataProvider.GetSqlConnection();            try            {                dt = GetPaperCertInfolDataTable(list);            }            catch (Exception ex)            {                returnStr = "Error"; ;            }            if (dt != null && dt.Rows.Count > 0)            {                try                {                    if (con.State == ConnectionState.Closed)                    {                         con.Open();                    }                                       if (con.State == ConnectionState.Open)                    {                        using (SqlTransaction ts = con.BeginTransaction())                        {                            try                            {                                using (SqlBulkCopy bulk = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, ts))                                {                                    bulk.BatchSize = 1000;                                    bulk.DestinationTableName = "dbo.TPaperCertInfo";                                    bulk.ColumnMappings.Clear();                                    bulk.ColumnMappings.Add("fPaperCertCode", "fPaperCertCode");                                    bulk.ColumnMappings.Add("fCertTypeID", "fCertTypeID");                                    bulk.ColumnMappings.Add("fCertTypeName", "fCertTypeName");                                    bulk.ColumnMappings.Add("fPosition", "fPosition");                                    bulk.ColumnMappings.Add("fCertBatchNo", "fCertBatchNo");                                    bulk.ColumnMappings.Add("fCertBatchName", "fCertBatchName");                                    bulk.ColumnMappings.Add("fStatus", "fStatus");                                    bulk.ColumnMappings.Add("fPrintStatus", "fPrintStatus");                                    bulk.ColumnMappings.Add("fMailStatus", "fMailStatus");                                    bulk.ColumnMappings.Add("fCreateTime", "fCreateTime");                                    bulk.ColumnMappings.Add("fCreateUserID", "fCreateUserID");                                    bulk.ColumnMappings.Add("fNotes", "fNotes");                                    bulk.NotifyAfter = 100;                                    //bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlRowsCopied);                                    bulk.WriteToServer(dt);                                    ts.Commit();                                    //returnStr = "OK";                                }                            }                            catch (Exception ex)                            {                                               ts.Rollback();                                returnStr = "Error";                            }                        }                        dt.Dispose();                    }                }                catch (Exception ex)                {                                        returnStr = "Error";                }                finally                {                    con.Close();                    con.Dispose();                }            }            return returnStr;        }        /// <summary>        /// 建立datatable映射到数据库中的表        /// </summary>        /// <param name="list"></param>        /// <returns></returns>        private DataTable GetPaperCertInfolDataTable(List<PaperCertInfo> list)        {            DataTable dt = new DataTable();            //dt.Columns.Add("fPaperCertID");            dt.Columns.Add("fPaperCertCode");            dt.Columns.Add("fCertTypeID");            dt.Columns.Add("fCertTypeName");            dt.Columns.Add("fPosition");            dt.Columns.Add("fCertBatchNo");            dt.Columns.Add("fCertBatchName");            dt.Columns.Add("fStatus");            dt.Columns.Add("fPrintStatus");            dt.Columns.Add("fMailStatus");            dt.Columns.Add("fCreateTime");            dt.Columns.Add("fCreateUserID");            dt.Columns["fCreateUserID"].DataType = typeof(Guid);            dt.Columns.Add("fNotes");            for (int i = 0; i < list.Count; i++)            {                DataRow dr = dt.NewRow();                //dr["fPaperCertID"] = Convert.ToInt32(list[i].PaperCertID);                dr["fPaperCertCode"] = list[i].PaperCertCode;                dr["fCertTypeID"] = Convert.ToInt32(list[i].CertTypeID);                dr["fCertTypeName"] = list[i].CertTypeName;                dr["fPosition"] = list[i].Position;                dr["fCertBatchNo"] = list[i].CertBatchNo;                dr["fCertBatchName"] = list[i].CertBatchName;                dr["fStatus"] = Convert.ToInt32(list[i].Status);                dr["fPrintStatus"] = Convert.ToInt32(list[i].PrintStatus);                dr["fMailStatus"] = Convert.ToInt32(list[i].MailStatus);                dr["fCreateTime"] = Convert.ToDateTime(list[i].CreateTime);                              dr["fCreateUserID"] = list[i].CreateUserID;                //dr["fCreateUserID"] = Guid.NewGuid();                dr["fNotes"] = list[i].Notes;                                dt.Rows.Add(dr);            }            return dt;        }
0 0