SqlBulkCopy WriteToServer方法在使用datatable時不寫入任何數據

c# sql sqlbulkcopy sql-server

所有以下處理都在localmachine上進行:

我有一個源數據庫(在服務器上)和一個目標數據庫(本地機器)。我有一個表的列表,我希望從源複製到目標,即服務器 - >本地。

我首先使用簡單的SELECT *語句並使用Adpter.Fill(myDataTable)將來自服務器的所有數據存儲在DataTable數組中,然後將myDataTable添加到DataTable數組中。

然後在本地我運行一個我在磁盤上的SQL腳本來刪除本地數據庫並重新創建它。使用[RightClick - > Tasks - > Generate Scripts]從SSMS獲取腳本

刪除並重新創建本地數據庫後,我使用SqlBulkCopy和前面的DataTable數組將服務器數據複製到新創建的本地數據庫中。


問題是,在我點擊SqlBulkCopy部分之前,一切都按預期工作。我沒有例外,沒有消息,也沒有觸發的bcp_SqlRowsCopied事件。數據根本沒有被複製過......在這裡發生什麼,我至少會發現某種錯誤......


以下是控制台應用程序的完整代碼:請注意,它還沒有生產就緒,因為還沒有任何類型的錯誤處理。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
using System.Diagnostics;

namespace TomboDBSync
{
    class Program
    {
        //Names of all the tables to copy from the server (the source) to our local db (the destination)
        public static string[] tables = new string[] {"br_Make_Model", "br_Model_Series", "br_Product_EngineCapacity", "br_Product_ProductAttributeDescription", "CompanyPassword", "dtproperties", "EngineCapacity", "Make", "Model", "PetrolType", "Product", "ProductAttribute", "ProductAttributeDescription", "ProductsImport", "ProductType", "Role", "SearchString", "Series", "Supplier", "Tally", "Transmission", "Users", "Year", "GRV"};


        static void Main(string[] args)
        {
            //Get Data from SourceDB
            DataTable[] dtTables = GetDataTables(tables);

            //Drop and Recreate Destination DB using SQL scripts
            DropAndRecreateDB();

            //Populate Destination with Data from SourceDB DataTables
            InsertDataFromDataTables(dtTables);
        }

        /// <summary>
        /// Takes all the data in the dtTables array which we got from the server (the source) and
        /// Bulk Copy it all into the local database (the destination)
        /// </summary>
        /// <param name="dtTables"></param>
        private static void InsertDataFromDataTables(DataTable[] dtTables)
        {
            foreach (DataTable dtTable in dtTables.ToList<DataTable>())
            {
                using (SqlBulkCopy bcp = new SqlBulkCopy(getLocalConnectionString(), SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
                {
                    bcp.DestinationTableName = dtTable.TableName;

                    bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    for (int colIndex = 0; colIndex < dtTable.Columns.Count; colIndex++)
                    {
                        bcp.ColumnMappings.Add(colIndex, colIndex);
                    }
                    bcp.WriteToServer(dtTable);

                }
             }                    
        }


        /// <summary>
        /// Row Copied eEvent handler for SqlBulkCopy
        /// </summary>
        static void bcp_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("row written");
        }

        /// <summary>
        /// 1) Takes a list of tablenames.
        /// 2) Connects to the server (the source)
        /// 3) Does a SELECT * on all the tables and stick the results into DataTables
        /// </summary>
        /// <param name="tables"></param>
        /// <returns>Returns an array of DataTables with all the data from the server in them</returns>
        public static DataTable[] GetDataTables(string[] tables)
        {            
            //Query all the server tables and stick 'em into DataTables           
            DataTable[] dataTables = new DataTable[tables.Length];

            for (int tableIndex = 0; tableIndex < tables.Length; tableIndex++)
            {
                string qry = "SELECT * FROM " + tables[tableIndex] + ";";
                Console.Write(qry);
                DataTable dtTable = new DataTable();

                using (SqlConnection connection = new SqlConnection(getServerConnectionString()))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    using (SqlCommand cmd = new SqlCommand(qry, connection))
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        adapter.Fill(dtTable);
                    }
                }
                dtTable.TableName = tables[tableIndex];
                dataTables[tableIndex] = dtTable;
                Console.WriteLine(" Rows: " + dtTable.Rows.Count);
            }
            return dataTables;
        }


        /// <summary>
        /// Parses and executes the script needed to drop and recreate the database
        /// </summary>
        private static void DropAndRecreateDB()
        {
            using (SqlConnection connection = new SqlConnection(getLocalConnectionString()))
            {
                string[] queries = getDropAndRecreateScript().Split(new string[] { "GO\r\n", "GO ", "GO\t" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string qry in queries)
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    using (SqlCommand cmd = new SqlCommand(qry, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        /// <summary>
        /// Reads in the createdbscript.sql file from disk.
        /// It contains all the SQL statements needed to drop and recreate the database.
        /// </summary>
        /// <returns>SQL to drop and recreate the database</returns>

        public static string getDropAndRecreateScript()
        {
            string qry = "";
            StreamReader re = File.OpenText("createdbscript.sql");
            string input = null;
            while ((input = re.ReadLine()) != null)
            {
                qry += (" " + input + "\r\n"); 
            }
            Console.WriteLine(qry);
            re.Close();
            return qry;
        }

        public static string getServerConnectionString()
        {
            return ConfigurationManager.AppSettings["SOURCEDB"];
        }

        public static string getLocalConnectionString()
        {
            return ConfigurationManager.AppSettings["DESTINATIONDB"];
        }

    }
}

一般承認的答案

我已經嘗試過你的代碼,它為我成功複製了表格!

為了SqlRowsCopied事件,需要將bcp.NotifyAfter設置為某個> 0的值。

至於為什麼你沒有看到價值觀,我不完全確定。如果數據庫不在那裡,你會得到一個例外(或者,至少,我做過)。我的代碼中的一個區別是我註釋掉了DropAndRecreateDB() ,當我在調試器中點擊那一點時,我在SQL中手動運行了一個drop-create腳本並驗證了表是否存在。

由於您發布的實際複製代碼對我來說很好,我會仔細檢查以確保您的連接字符串符合您的想法。如果您可以發布該信息,則繼續追踪會更容易。

更新:

FWIW,這是我的drop / create腳本:

USE [master];
ALTER DATABASE MyTestDB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE MyTestDB2;
GO
CREATE DATABASE MyTestDB2;
GO

USE [MyTestDB2];

CREATE TABLE [dbo].[tblPetTypes](
    [commonname] [nvarchar](50) NOT NULL,
    PRIMARY KEY CLUSTERED ([commonname])
)

CREATE TABLE [dbo].[tblPeople](
    [oid] [int] IDENTITY(1,1) NOT NULL,
    [firstname] [nvarchar](30) NOT NULL,
    [lastname] [nvarchar](30) NOT NULL,
    [phone] [nvarchar](30) NULL,
    PRIMARY KEY CLUSTERED ([oid])
)

CREATE TABLE [dbo].[tblPets](
    [oid] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    [pettype] [nvarchar](50) NULL,
    [ownerid] [int] NULL,
    PRIMARY KEY CLUSTERED ([oid])
) ON [PRIMARY]

...我在同一台服務器上從MyTestDB複製到MyTestDB2



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow