c#大批量数据插入

 /// <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;
        }