C# SqlBulkCopy.WriteToServer方法代码示例

        /// <summary>
        /// Insert questions to database when create questionnarie
        /// </summary>
        /// <param name="dtQuestionnarie">for save the bulk of data to database</param>
        /// <returns>DataTable</returns>
        public DataTable SaveQuestionDAL(DataTable dtQuestionnarie)
        {
            try
            {
                connection.Open();
                SqlBulkCopy objbulk = new SqlBulkCopy(connection);
                // Assigning Destination table name
                objbulk.DestinationTableName = "RE_Questions";
                // Mapping Table column

                objbulk.ColumnMappings.Add("QuestionName", "QuestionName");
                objbulk.ColumnMappings.Add("QuestionType", "QuestionType");
                objbulk.ColumnMappings.Add("AnswerOption", "AnswerOption");
                objbulk.ColumnMappings.Add("QuestionnaireId", "QuestionnaireId");
                // Inserting bulk Records into DataBase
                objbulk.WriteToServer(dtQuestionnarie);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
            return dtQuestionnarie;
        }

 public static void CopyData(IDataReader sourceReader, SqlConnection targetConnection,string targetTableName)
 {
     SqlTransaction tran = null;
     try
     {
         if (targetConnection.State != ConnectionState.Open)
         {
             targetConnection.Open();
         }
         tran = targetConnection.BeginTransaction();
         using (SqlBulkCopy copy = new SqlBulkCopy(targetConnection,SqlBulkCopyOptions.Default, tran))
         {
             copy.DestinationTableName = targetTableName;
             copy.WriteToServer(sourceReader);
         }
         tran.Commit();
     }
     catch (Exception ex)
     {
         if (tran != null)
         {
             tran.Rollback();
         }
         throw new Exception("数据复制失败", ex);
     }
 }

        public bool AddBrand(List<Brand> list)
        {
            DataTable table = GetTable(list);
            if (table == null)
            {
                return false;
            }

            using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(Conn))
            {
                //2.0.1 映射字段
                copy.ColumnMappings.Add("UserID", "UserID");
                copy.ColumnMappings.Add("BrandName", "BrandName");
                copy.ColumnMappings.Add("AuthorizationImgUrl", "AuthorizationImgUrl");
                copy.ColumnMappings.Add("BrandRemark", "BrandRemark");
                copy.ColumnMappings.Add("Status", "Status");
                copy.ColumnMappings.Add("CreateTime", "CreateTime");

                //2.0.2 指定插入的数据表
                copy.DestinationTableName = "Brand";

                //2.0.3 将数据一次性插入数据表
                copy.WriteToServer(table);
            }

            return true;
        }

        public void Import(IEnumerable<ProductData> products)
        {
            var table = new DataTable();
            table.Columns.Add("ProductID");
            table.Columns.Add("Description");
            table.Columns.Add("Price");

            using (var bulkCopy = new SqlBulkCopy(_settings.ConnectionString))
            {
                bulkCopy.DestinationTableName = "Products";
                bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
                bulkCopy.ColumnMappings.Add("Description", "Description");
                bulkCopy.ColumnMappings.Add("Price", "Price");

                foreach (var product in products)
                {
                    table.Rows.Add(product.ProductID, product.Description, product.ProductID);

                    if (table.Rows.Count == _settings.BatchSize)
                    {
                        bulkCopy.WriteToServer(table);
                        table.Rows.Clear();
                    }
                }

                bulkCopy.WriteToServer(table);
            }
        }

 /// <summary>
 /// 批量插入数据表
 /// </summary>
 /// <param name="dt">内容表</param>
 /// <param name="DestinationTableName">插入目标表名</param>
 public static void BulkToDataTable(DataTable FromDataTable, string DestinationTableName)
 {
     using (SqlConnection conn = new SqlConnection(connectionString))
     {
         SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
         bulkCopy.DestinationTableName = DestinationTableName;
         bulkCopy.BatchSize = FromDataTable.Rows.Count;
         try
         {
             conn.Open();
             if (FromDataTable != null && FromDataTable.Rows.Count > 0)
                 bulkCopy.WriteToServer(FromDataTable);
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             conn.Close();
             if (bulkCopy != null)
                 bulkCopy.Close();
         }
     }
 }

        /// <summary>
        /// Bulk insert
        /// </summary>
        /// <param name="records"></param>
        /// <param name="tableName"></param>
        /// <param name="listTableColumn"></param>
        /// <param name="entityType"></param>
        public void BulkInsert(IEnumerable<object> records, string tableName, List<string> listTableColumn, Type entityType)
        {
            //
            // Try to create bulk copy through sqlconnection
            try
            {
                //
                // Open sql connection
                SqlConnection sqlConnection = new SqlConnection(this._connectionString);
                sqlConnection.Open();

                //
                // Create and config sql bulk copy
                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection);
                sqlBulkCopy.DestinationTableName = tableName;
                foreach (string column in listTableColumn)
                {
                    sqlBulkCopy.ColumnMappings.Add(column, column);
                }

                //
                // Start insert to database
                DataTable dataTable = ToDataTable(records, entityType);
                sqlBulkCopy.WriteToServer(dataTable);

                //
                // Close connection
                sqlConnection.Close();
            }
            catch (Exception exc)
            {
                LogService.Log.Error("Can not insert new record into database by SQLBulkInsert.", exc);
            }
        }

    protected void btnSend_Click(object sender, EventArgs e)
    {
        //String strConnection = "Integrated Security=false;Persist Security Info=False;Data Source=SQL-SERVER;Initial Catalog=schoolerpsoft ;User ID=sa;Password=techmodi123";
        //String strConnection = "Integrated Security=false;Persist Security Info=False;Data Source=199.79.62.22;Initial Catalog=fashvash_inifdvashi ;User ID=inifd_user;[email protected]#";
        //String strConnection = "Integrated Security=false;Persist Security Info=False;Data Source=TWS-ERPSERVER\\SQLEXPRESS;Initial Catalog=AsnASoftwaresolutionDB";
        String strConnection = "Integrated Security=SSPI;Persist Security Info=False;Data Source=TWS-ERPSERVER\\SQLEXPRESS;Initial Catalog=AsnASoftwaresolutionDB";
        //file upload path
        if (fileuploadExcel.HasFile)
        {
            string path = fileuploadExcel.PostedFile.FileName;
            path = Server.MapPath("~") + "/" + fileuploadExcel.PostedFile.FileName;
            fileuploadExcel.SaveAs(path);
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            //Create OleDbCommand to fetch data from Excel

          //  OleDbCommand cmd = new OleDbCommand("Select [AD_AddressId],[AD_PermanentAddress],[AD_OfficeAddress] ,[AD_CommercialAddress] ,[AD_OptionalAddress],[AD_LandLineNo],[AD_OfficeNo],[AD_MobileNo],[AD_MobileOptionalNo],[AD_EmailId],[AD_PinCode],[AD_ZipCode],[AD_CountryId],[AD_StateId],[AD_CityId],[AD_AddressDate],[AD_Status] from [Sheet1$]", excelConnection);
           // OleDbCommand cmd = new OleDbCommand("Select [SA_ApplicationId],[SA_StudentPNRId],[SA_FirstName] ,[SA_MiddleName] ,[SA_LastName],[SA_DateOfBirth],[SA_PlaceOfBirth],[SA_AcadamicYear],[SA_BloodGroup],[SA_StudentImage],[SA_Nationality],[SA_Gender],[SA_Caste],[SA_SubCaste],[SA_DateOfApplication],[SA_MotherTounge],[SA_SchoolCategoryId] ,[SA_TransportStatus] ,[SA_MedicalProblem],[SA_LastSchoolAttended],[SA_LastClassAttended],[SA_LastClassPer],[SA_FatherFullName],[SA_FatherPhoto],[SA_FatherEduQualification],[SA_FatherOccupation],[SA_FatherAddressId],[SA_FatherOrgName],[SA_FatherDsgtName],[SA_MotherFullName],[SA_MotherPhoto],[SA_MotherEduQualification],[SA_MotherOccupation],[SA_MotherAddressId],[SA_MotherOrgName],[SA_MotherDsgtName] ,[SA_AddressId] ,[SA_AgeProof],[SA_LastProgressReportCard],[SA_OriginalSchoolLC],[SA_PassportAndVisa],[SA_RegistrationLetter],[SA_ApplicationStatus],[SA_Status] from [Sheet1$]", excelConnection);
            //OleDbCommand cmd = new OleDbCommand("Select [UL_LoginId],[UL_UserName],[UL_UserPassword],[UL_EmailId],[UL_UserType],[UL_CreatedBy],[UL_ModifiedBy],[UL_CreatedDate],[UL_ModifiedDate],[UL_LastLoginDate],[UL_Status] from [Sheet1$]", excelConnection);
            OleDbCommand cmd = new OleDbCommand("Select [FD_FacultyDetailsId],[FD_LoginId],[FD_FacultyId],[FD_SchoolCatgoryId],[FD_Title],[FD_FirstName],[FD_MiddleName],[FD_LastName],[FD_Date],[FD_Caste],[FD_SubCaste],[FD_BirthPlace],[FD_BloodGroup],[FD_MotherTounge],[FD_Nationality],[FD_TransportStatus],[FD_MedicalProblem],[FD_SchoolCategoryId],[FD_SchoolSubCategoryId],[FD_SubjectId],[FD_InterestPosition],[FD_CertifiedSubject],[FD_CertifiedAgeGroup],[FD_FullTimeTeaching],[FD_IndustryExpertise],[FD_SpecificExpertise],[FD_HighestDegreeCompletd],[FD_FieldOfStudy],[FD_University],[FD_CompletionDate],[FD_AddressId],[FD_QualificationDetails],[FD_ExperienceFrom],[FD_ExperienceTo],[FD_ExperienceInYrs],[FD_Image],[FD_JoiningDate],[FD_BirthDate],[FD_Gender],[FD_MarrialStatus],[FD_AgeProof],[FD_Resume],[FD_ReleavingLetter],[FD_RetiredOn],[FD_ExtraOne],[FD_CreatedBy],[FD_LastSchoolwas],[FD_LastSchoolFromDate],[FD_LastSchoolToDate],[FD_Status] from [Sheet1$]", excelConnection);
            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            //Give your Destination table name
            //sqlBulk.DestinationTableName = "TMS_AddressDetails";
            sqlBulk.DestinationTableName = "TMS_FacultyDetails";
            //sqlBulk.DestinationTableName = "TMS_UserLogin";
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();
        }
    }

 public static void BulkCopy(DataTable table, string connectionString)
 {
     using (var connection = new SqlConnection(connectionString))
     {
         SqlTransaction transaction = null;
         connection.Open();
         try
         {
             transaction = connection.BeginTransaction();
             using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
             {
                 sqlBulkCopy.BatchSize = table.Rows.Count;
                 sqlBulkCopy.DestinationTableName = table.TableName;
                 sqlBulkCopy.MapColumns(table);
                 sqlBulkCopy.WriteToServer(table);
             }
             transaction.Commit();
         }
         catch
         {
             transaction?.Rollback();
             throw;
         }
     }
 }

        public static bool UpdateBulkCustomerInfo(SqlCommand cmd, Data.dsData.CustomerInfoDataTable BulkTable)
        {
            bool outPut = false;
            DateTime dtStart = DateTime.Now;
            const string BulkTableName = "CustomerInfo";
            try
            {
                //Delete All Recored Before Inserting
                cmd.CommandText = "DELETE FROM CustomerInfo";
                cmd.ExecuteNonQuery();

                SqlBulkCopy bulkCopy = new SqlBulkCopy(cmd.Connection);
                cmd.CommandTimeout = 0; bulkCopy.BulkCopyTimeout = 0;

                bulkCopy.ColumnMappings.Clear();
                bulkCopy.ColumnMappings.Add("Distribution Channel", "Distribution Channel");
                bulkCopy.ColumnMappings.Add("Customer", "Customer");
                bulkCopy.ColumnMappings.Add("Start date of validity", "Start date of validity");
                bulkCopy.ColumnMappings.Add("End date of validity", "End date of validity");
                bulkCopy.ColumnMappings.Add("City", "City");

                bulkCopy.DestinationTableName = BulkTableName;
                bulkCopy.BatchSize = BulkTable.Count;
                bulkCopy.WriteToServer(BulkTable);
                outPut = true;
            }
            catch (SqlException ex)
            {
                Classes.Core.LogException(Logger, ex, Classes.Core.ExceptionLevelEnum.General, Classes.Managers.UserManager.defaultInstance.User.UserId);
                System.Windows.Forms.MessageBox.Show("Error while trying to save Bulk - " + ex.Message);
            }
            return outPut;
        }

        public static int bulkinsert(DataTable source_table, SqlConnection conn, string detination_table)
        {
            int i = 1;

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            {
                conn.Open();
                bulkCopy.DestinationTableName = detination_table;
                //"[dbo].[ShowAudit_Table]";
                try
                {
                    bulkCopy.WriteToServer(source_table);
                }
                catch (Exception ex)
                {
                    i = 0;
                    Microsoft.Practices.EnterpriseLibrary.Logging.Logger.Write(ex.Message.ToString());
                }
                finally
                {
                    conn.Close();
                }
            }
            return i;
        }

 /// <summary>
 /// splbulkcopy函数
 /// </summary>
 public static void SqlBCP(string ConnString, DataTable dt, string destablename)
 {
     SqlBulkCopy sqlBulkCopyMobile = new SqlBulkCopy(ConnString);
     sqlBulkCopyMobile.DestinationTableName = destablename;
     sqlBulkCopyMobile.WriteToServer(dt);
     sqlBulkCopyMobile.Close();
 }

        /// <summary>批量导入DataTable  
        /// 批量导入DataTable  
        /// </summary>  
        /// <param name="dt">DataTable数据表</param>  
        /// <param name="tblName">表名</param>  
        /// <param name="dtColumn">数据列集合</param>  
        /// <return>Boolean值:true成功，false失败</return>  
        public static Boolean insertTbl(DataTable dt, String tblName, DataColumnCollection dtColumn)
        {
            try
            {
                using (SqlBulkCopy sqlBC = new SqlBulkCopy(connString))
                {
                    //一次批量的插入的数据量
                    sqlBC.BatchSize = 1000;
                    //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                    sqlBC.BulkCopyTimeout = 60;

                    //设置要批量写入的表
                    sqlBC.DestinationTableName = tblName;

                    //自定义的datatable和数据库的字段进行对应
                    //sqlBC.ColumnMappings.Add("id", "tel");
                    //sqlBC.ColumnMappings.Add("name", "neirong");
                    for (int i = 0; i < dtColumn.Count; i++)
                    {
                        sqlBC.ColumnMappings.Add(dtColumn[i].ColumnName.ToString(), dtColumn[i].ColumnName.ToString());
                    }
                    //批量写入
                   VipSoft.Common.NPOI2.ExcelUtils.printDT(dt);
                    sqlBC.WriteToServer(dt);
                }
            }
            catch(Exception e)
            {
                Console.Write(e.Message);
                return false;
            }
            return true;
        }

        public static void Convert(string shapefile, string connectionString, string tableName, int srid = 0, string targetProjectionWKT = null)
        {
            GeometryTransform transform = GeometryTransform.GetTransform(shapefile, targetProjectionWKT);
            GeometryFactory factory = new GeometryFactory(new PrecisionModel(), srid);

            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlBulkCopy copy = new SqlBulkCopy(conn))
            using (ShapefileDataReader reader = new ShapefileDataReader(shapefile, factory, transform))
            {
                conn.Open();

                string createTableSql = GenerateCreateTableQuery(reader, tableName);
                using (SqlCommand createTableComm = new SqlCommand(createTableSql, conn))
                    createTableComm.ExecuteNonQuery();

                copy.SqlRowsCopied += (object sender, SqlRowsCopiedEventArgs e) =>
                    {
                        System.Console.Clear();
                        System.Console.WriteLine("Copied " + e.RowsCopied);
                    };
                copy.NotifyAfter = 257;
                copy.DestinationTableName = tableName;
                copy.WriteToServer(new ShapefileBulkSqlReader(reader, srid));
            }
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        string strConnectionString = System.Configuration.ConfigurationSettings.AppSettings["strConLocal"].ToString();
        //string strConnectionString = System.Configuration.ConfigurationSettings.AppSettings["strConTest"].ToString();
        string strFileName = Strings.GetQueryString("F");
        string strGroupID = Strings.GetQueryString("G");
        strImportType = Strings.GetQueryString("T");
        //DataAccess.ExecuteDb("Truncate Table App_Scores_" + strImportType + "_Temp");
        SqlConnection sql = new SqlConnection(strConnectionString);
        SqlCommand truncate = new SqlCommand("Truncate table Scores_GPA_CAMS_Temp", sql);
        sql.Open();
        truncate.ExecuteNonQuery();
        sql.Close();
        truncate = null;
        sql = null;

        DataTable ImportData = PopulateDataTable(strFileName);
        ImportData.Rows.RemoveAt(0);
        using (SqlBulkCopy MoveXLSData = new SqlBulkCopy(strConnectionString))
        {
            MoveXLSData.DestinationTableName = "App_Scores_" + strImportType + "_Temp" ;
            MoveXLSData.WriteToServer(ImportData);
        }
        Response.Write("1");
        Response.End();
    }

        public static bool RecordDownload( List<int> ids,string type)
        {
            DataTable dt = new DataTable();
            DataColumn colid = new DataColumn("pid",typeof(int));
            DataColumn coltype = new DataColumn("type",typeof(string));

            dt.Columns.Add(colid);
            dt.Columns.Add(coltype);

            foreach (int i in ids)
            {
                DataRow row = dt.NewRow();
                row["pid"] = i;
                row["type"] = type;
                dt.Rows.Add(row);
            }

            using (SqlConnection con = SqlDbAccess.GetSqlConnection())
            {
                con.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(con, SqlBulkCopyOptions.CheckConstraints, null))
                {
                    copy.BatchSize = 5000;
                    copy.BulkCopyTimeout = 3000;
                    copy.DestinationTableName = "dbo.RecordDownload";
                    copy.ColumnMappings.Add("pid", "pid");
                    copy.ColumnMappings.Add("type", "type");
                    copy.WriteToServer(dt);
                }
                con.Close();
            }
            return true;
        }