CODEWITHSUNDEEP
c#sqlsql-serversqlbulkcopy
n4rzul
n4rzul

Reputation: 4078

SqlBulkCopy WriteToServer method not writing any data when using datatable

All of the following processing takes place on the localmachine:

I have a source database (on the server) and a destination database (local machine). I have a list of tables that I wish to copy from the source to the destination, ie server-->local.

I start by storing all the data from the server in a DataTable array using simple SELECT * statements and using Adpter.Fill(myDataTable) and then adding myDataTable to the DataTable array.

Then locally I run a SQL script that I have on disk to drop the local database and recreate it. Got the script from SSMS using [RightClick--> Tasks--> Generate Scripts]

After dropping and recreating the local database I use SqlBulkCopy with the DataTable array of earlier to copy the server data into the newly created local database.

Problem is, everything works as expected until I hit the SqlBulkCopy part. I get no exceptions, no messages, and no bcp_SqlRowsCopied events that fire. The data is simply not copied over... Whats going on here, I would at least expect some kind of error...

Here is the code for the console application in it's entirety: Please note that it is not production ready as there is as yet no error handling of any kind.

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"];
        }

    }
}

Upvotes: 0

Views: 8511

Answers (1)

Matt
Matt

Reputation: 4388

I've tried your code and it successfully copies tables for me!

In order to get the SqlRowsCopied event to fire, you need to set bcp.NotifyAfter to some > 0 value.

As for why you're not seeing values, I'm not exactly sure. If the DB or tables aren't there, you will get an exception (or, at least, I did). One difference in my code is that I commented out DropAndRecreateDB() and, when I hit that point in the debugger, I ran a drop-create script manually in SQL and verified that the tables were present.

Since your actual copy code works fine for me as you've posted it, I would double check to make sure your connection strings are what you think they are. If you could post that information, it'd be easier to continue tracking down.

Update:

FWIW, here is my drop/create script:

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]

...and I copied from MyTestDB to MyTestDB2 on the same server.

Upvotes: 6

Related Questions