C# SqlClient.SqlBulkCopy类代码示例

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

        /// <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>
        /// 批量插入数据
        /// </summary>
        /// <param name="dt">要插入的数据（列名与数据库中的列名相同）</param>
        /// <param name="destinationTableName">数据库中的目标表的名称</param>
        /// <returns>如果插入成功则返回true，否则返回false</returns>
        /// <remarks>作成者：高奇，作成日：2013-9-6</remarks>
        public static bool BulkInsert(DataTable dt, string destinationTableName)
        {
            if (dt == null || dt.Rows.Count == 0) return false;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
                bulkCopy.DestinationTableName = destinationTableName;
                bulkCopy.BatchSize = dt.Rows.Count;
                try
                {
                    connection.Open();
                    bulkCopy.WriteToServer(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                    if (bulkCopy != null)
                        bulkCopy.Close();
                }
                return true;
            }
        }

        /// <summary>
        /// Wrapper class to produce an Ado.Net Datatable from any entity, 
        /// and perform SqlBulkCopy operations
        /// </summary>
        public SqlEntityBulkCopy(string sqlCnnString, Type entityType)
        {
            if (Cfg == null)
            {
                //Note: The NHibernate.Cfg.Configuration is meant only as an initialization-time object.
                //Note: NHibernate.ISessionFactory is immutable and does not retain any association back to the Session

                Cfg = new Configuration();
                //Cfg.SetProperty("proxyfactory.factory_class", "NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle");
                Cfg.SetProperty("dialect", "NHibernate.Dialect.MsSql2008Dialect");
                Cfg.SetProperty("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
                Cfg.SetProperty("connection.driver_class", "NHibernate.Driver.SqlClientDriver");
                Cfg.SetProperty("connection.connection_string", sqlCnnString);

                //add all the mappings embedded in this assembly
                Cfg.AddAssembly(typeof(SqlEntityBulkCopy).Assembly);

                var sessionFactory = Cfg.BuildSessionFactory();
                SessionFactoryImpl = (ISessionFactoryImplementor)sessionFactory;
            }
            EntityType = entityType;
            //_session = SessionFactoryImpl.OpenSession();
            _metaData = SessionFactoryImpl.GetClassMetadata(EntityType);
            _persistentClass = Cfg.GetClassMapping(EntityType);
            _sqlCnn = new SqlConnection(sqlCnnString);
            _sqlBulkCopy = new SqlBulkCopy(_sqlCnn);

            //Debug.WriteLine("EntityName = " + _metaData.EntityName);
            //Debug.WriteLine("IdentifierPropertyName = " + _metaData.IdentifierPropertyName);
            //Debug.WriteLine("IdentifierType = " + _metaData.IdentifierType);

            BuildDataTable();
            BuildAndMapSqlBulkCopy();
        }

        public void InsertTenThousandAlphabets1()
        {
            using (var entities = new Entities())
            {
                var insertTime = new Stopwatch();
                var alphabets  = Enumerable.Range(0, 10000).Select(i => new Alphabet { A = "A", B = "B", C = "C", D = "D", E = "E", F = "F", G = "G", H = "H", I = "I", J = "J", K = "K", L = "L", M = "M", N = "N", O = "O", P = "P", Q = "Q", R = "R", S = "S", T = "T", U = "U", V = "V", W = "W", X = "X", Y = "Y", Z = "Z" }).ToList();

                insertTime.Start();
                var sqlBulkCopy = new SqlBulkCopy(entities.SqlConnection)
                { 
                    DestinationTableName = "Alphabets",
                    ColumnMappings       = {{"A", "A"}, {"B", "B"}, {"C", "C"}, {"D", "D"}, {"E", "E"}, {"F", "F"}, {"G", "G"}, {"H", "H"}, {"I", "I"}, {"J", "J"}, {"K", "K"}, {"L", "L"}, {"M", "M"}, {"N", "N"}, {"O", "O"}, {"P", "P"}, {"Q", "Q"}, {"R", "R"}, {"S", "S"}, {"T", "T"}, {"U", "U"}, {"V", "V"}, {"W", "W"}, {"X", "X"}, {"Y", "Y"}, {"Z", "Z"}}
                };

                using (var dataReader = new ObjectDataReader<Alphabet>(alphabets))
                {
                    sqlBulkCopy.WriteToServer(dataReader);
                }
                
                sqlBulkCopy.Close();
                insertTime.Stop();

                double actualInsertTimePerAlphabet = (double)insertTime.ElapsedMilliseconds / alphabets.Count();

                Debug.WriteLine("Insert Time Milliseconds Per Row: " + actualInsertTimePerAlphabet);
            }
        }

        /// <summary>
        /// DataTable批量添加(有事务)
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="destinationTableName"></param>
        /// <returns></returns>
        private void MySqlBulkCopy(DataTable dataTable, string destinationTableName)
        {
            string connectionString = "server=172.16.8.208,1109;database=10W;uid=sa;pwd=sa";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))
                    {
                        bulkCopy.DestinationTableName = destinationTableName;
                        //获取映射关系
                        SqlBulkCopyColumnMapping[] mapping = GetMapping();
                        if (mapping!=null)
                        {
                            foreach (SqlBulkCopyColumnMapping columnMapping in mapping)
                            {
                                bulkCopy.ColumnMappings.Add(columnMapping);
                            }
                        }
                        try
                        {
                            bulkCopy.WriteToServer(dataTable);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
            }
        }

        private async Task BulkCopyExecuteAsync(IDataReader sourceDataReader, SqlConnection targetConnection, string targetTableName,
           IEnumerable<ColumnsMapping> columnsMappings, Action<long> rowsCopiedNotify)
        {
            using (var bulkCopy = new SqlBulkCopy(targetConnection))
            {
               
                if (columnsMappings != null)
                {
                    var bulkMappings = CreateBulkCopyMappings(columnsMappings);
                    bulkCopy.AddMappings(bulkMappings);
                }

                bulkCopy.DestinationTableName = targetTableName;

                bulkCopy.BatchSize = _batchSize;
                bulkCopy.NotifyAfter = _notifyAfter;
                bulkCopy.BulkCopyTimeout = _bulkCopyTimeout;

                if (rowsCopiedNotify != null)
                {
                    bulkCopy.SqlRowsCopied += (sender, e) => rowsCopiedNotify(e.RowsCopied);
                }
                
                await bulkCopy.WriteToServerAsync(sourceDataReader);
            }
        }

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