C# System.Data.SqlClient.SqlBulkCopy.WriteToServer方法代码示例

        public void TransferData(string excelFile, string sheetName, string connectionString="")
        {
            connectionString = ConfigurationManager.ConnectionStrings["testStr"].ConnectionString;
            DataSet ds = new DataSet();
            try
            {
                //获取全部数据
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                strExcel = string.Format("select * from [{0}$]", sheetName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, sheetName);

                //如果目标表不存在则创建
                string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
                foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
                {
                    strSql += string.Format("[{0}] nvarchar(255),", c.ColumnName);
                }
                strSql = strSql.Trim(',') + ")";

                using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    sqlconn.Open();
                    System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                    command.CommandText = strSql;
                    command.ExecuteNonQuery();
                    sqlconn.Close();
                }
                //用bcp导入数据
                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                {
                    //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    bcp.BatchSize = 100;//每次传输的行数
                    bcp.NotifyAfter = 100;//进度提示的行数
                    bcp.DestinationTableName = sheetName;//目标表
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
        }

        /// <summary>
        /// Use bulk copy to insert the worksheet rows into the specified table
        /// </summary>
        /// <param name="tempTableName"></param>
        /// <param name="excelConnectionString"></param>
        /// <param name="worksheetName"></param>
        private void OleCopyToTempTable(string tempTableName, string excelConnectionString, string worksheetName)
        {
            using (System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(excelConnectionString))
            {
                try
                {
                    excelConnection.Open();
                    if (string.IsNullOrEmpty(ExcelQuery))
                        ExcelQuery = "SELECT * FROM [" + worksheetName + "$]";
                    else
                        ExcelQuery += "FROM [" + worksheetName + "$]";
                    using (System.Data.OleDb.OleDbCommand selectAllComand = new System.Data.OleDb.OleDbCommand(ExcelQuery))   //SELECT * FROM [" + worksheetName + "$]"))
                    {
                        selectAllComand.Connection = excelConnection;
                        using (System.Data.OleDb.OleDbDataReader excelReader = selectAllComand.ExecuteReader())
                        {

                            using (System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(_dbConn))
                            {
                                bc.BatchSize = 50;

                                bc.DestinationTableName = @"[dbo].[" + tempTableName + @"]"; //tempDatasheetImport]";
                                // User notification with the SqlRowsCopied event
                                //bc.NotifyAfter = 100;
                                //bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                                if (_dbConn.State != System.Data.ConnectionState.Open)
                                    _dbConn.Open();
                                bc.WriteToServer(excelReader);
                                bc.Close();
                            }
                            excelReader.Close();
                            _dbConn.Close();
                        }

                    }
                    excelConnection.Close();
                }
                catch (InvalidOperationException ex)
                {
                    //if (ex.Message != @"Column 'tmpIndentNo' does not allow DBNull.Value.") //not a problem, just reaching the footer and easier to skip an exception than try to filter it out
                    //    throw new Exception("OleCopyToTempTable 1: " + ex.Message);
                    //else if (ex.Message != @"Column 'tmpMSEP' does not allow DBNull.Value.") //not a problem, just reaching the footer
                    //    throw new Exception("OleCopyToTempTable 2: " + ex.Message);
                    //else if (ex.Message != @"Column 'tmpIndentNo' does not allow DBNull.Value.")
                    //    throw new Exception("OleCopyToTempTable 3: " + ex.Message);
                }
                finally
                {
                    excelConnection.Close();
                    _dbConn.Close();

                }
                //catch (Exception ex)
                //{
                //    throw new Exception("OleCopyToTempTable 4: " + ex.Message);
                //}
            }
        }

        /// <summary>
        /// 使用datatable保存方法，table中的列要與數據表中的列一至，并且將數據表名放到table.TableName中
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool InsertDataTable(DataTable dt)
        {
            try
            {
                string dbcon = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLModel.Properties.Settings.HBPMSDBConnectionString"].ConnectionString;
                using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(dbcon))
                {
                    sbc.BatchSize = 100000;//每次传输的行数
                    sbc.BulkCopyTimeout = 900;
                    sbc.DestinationTableName = dt.TableName;

                    sbc.WriteToServer(dt);
                    sbc.Close();
                }
                return true;
            }
            catch
            {

            }

            return false;
        }

 /// <summary>
 /// BulkCopy
 /// </summary>
 /// <param name="dtSrc"></param>
 /// <param name="dtDesc"></param>
 public void BulkCopy(System.Data.DataTable dtSrc, string dtDesc)
 {
     using (DbConnection connection = m_db.CreateConnection())
     {
         System.Data.SqlClient.SqlConnection cn = connection as System.Data.SqlClient.SqlConnection;
         if (cn == null)
         {
             throw new NotSupportedException("BuldCopy is only supported in MS SQL SERVER now!");
         }
         cn.Open();
         using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(cn))
         {
             copy.BulkCopyTimeout = 3600 * 24;
             for (int i = 0; i < dtSrc.Columns.Count; ++i)
             {
                 copy.ColumnMappings.Add(i, i);
             }
             copy.DestinationTableName = dtDesc;
             copy.WriteToServer(dtSrc);
         }
     }
 }

        private async void btnDownload_Click(object sender, EventArgs e)
        {
            const string CONN_STRING = @"Data Source=(local);Initial Catalog=DataPro;Trusted_Connection=true;Connect Timeout=60";
            //IEnumerable<string> uris = new string[] { "http://mlb.com/lookup/named.cur_bio.bam", "http://mlb.com/lookup/named.cur_hitting.bam", "http://mlb.com/lookup/named.cur_hitting.bam?season=%272013%27", "http://mlb.com/lookup/named.cur_pitching.bam", "http://mlb.com/lookup/named.cur_fielding.bam" };
            List<MLBAMFeed> feeds = new List<MLBAMFeed>();
            feeds.Add(new MLBAMFeed() { Url = "http://mlb.com/lookup/named.cur_hitting.bam?season=%272013%27", FileName = "cur_hitting_2013.xml", DestinationTable="temp.curbat_v2" });
            feeds.Add(new MLBAMFeed() { Url = "http://mlb.com/lookup/named.cur_hitting.bam", FileName = "cur_hitting.xml", DestinationTable = "temp.curbat_v2" });

            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            
            //build the collection of threaded tasks
            List<Task> tasks = new List<Task>();
            foreach (var xmlFeed in feeds)
            {
                tasks.Add(downloadFile(xmlFeed));
            }
            UpdateStatus(string.Format("downloading {0} files.", feeds.Count()));
            Cursor = Cursors.AppStarting;
            sw.Start();

            //kick them all off & wait until they've all completed.
            await Task.WhenAll(tasks.ToArray());

            sw.Stop();
            Cursor = Cursors.Default;
            UpdateStatus(string.Format("Completed in {0} seconds.", sw.Elapsed));
            this.Refresh();

            Cursor = Cursors.WaitCursor;
            //initialize the db tables
            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("truncate table temp.curBio_v2;", conn))
                {
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }

            //write them all to disk
            //clean up any previous .xml files
            DirectoryInfo di = new DirectoryInfo(Directory.GetCurrentDirectory());
            FileInfo[] files = di.GetFiles("*.xml")
                     .Where(p => p.Extension == ".xml").ToArray();
            foreach (FileInfo file in files)
            {
                file.Attributes = FileAttributes.Normal;
                File.Delete(file.FullName);
            }
                
                
            // save the Task results to disk as .xml files
            foreach (Task<MLBAMFeed> result in tasks)
            {
                MLBAMFeed xmlResult = result.Result;
                System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
                xmlDoc.LoadXml(xmlResult.XML);
                string fileName = xmlResult.FileName; //string.Format("{0}.xml", xmlDoc.DocumentElement.Name);
                //append to the .xml if it exists
                using (StreamWriter writer = new StreamWriter(fileName, true))
                {
                    writer.Write(xmlResult.XML);
                }

                //Load it into a dataset so we can bcp it
                var ds = new DataSet();
                ds.ReadXml(fileName);
                lstPlayers.Items.Add(string.Format("{0} tables in {1} dataset...", ds.Tables.Count, fileName));
                lstPlayers.Items.Add(string.Format("{0} rows.", ds.Tables[1].Rows.Count));

                if (fileName.ToLower().Equals("cur_bio.xml"))
                {
                    using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(CONN_STRING))
                    {
                        sbc.DestinationTableName="temp.curBio_v2";
                        sbc.BatchSize = 1000;
                        sbc.BulkCopyTimeout = 300;
                        //map the xml attribute to the db column
                        foreach (System.Data.DataColumn col in ds.Tables[1].Columns)
                        {
                            //don't include the ado queryResults node
                            if (col.ColumnName.ToLower() != "queryresults_id")
                            {
                                System.Diagnostics.Debug.WriteLine(col.ColumnName);
                                sbc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                        }
                        
                        sw.Reset();
                        sw.Start();
                        //push it up to the server
                        sbc.WriteToServer(ds.Tables[1]);
                        sw.Stop();
                        lstPlayers.Items.Add(string.Format("{0} rows inserted in {1} seconds.", ds.Tables[1].Rows.Count, sw.Elapsed));
                    }   
                }
            }
            Cursor = Cursors.Default;
        }