ADO.NET Generic Copy Table Data Function

• Download source - 1.6 Kb

Introduction

Have you ever wanted to move data from a production system to a development box without the overhead of using DTS or SSIS? This generic function will allow you to copy data between any two ADO.NET providers with one simple method call. I have already used this function in place of DTS jobs on several occasions, due to its simplicity and ability to extend into more complex transformations with very little modification.

The CopyTable method

The CopyTable method takes 4 parameters:

1. The source database connection. This is the connection that you are going to copy data from.
2. The target database connection. This is the connection that you are going to copy data into.
3. The source SQL statement. Use "select *" to select all the fields. If you want to filter what columns get copied to the source table, then you can specify the columns.
4. The destination table name. This is the name of the table that the data will get copied to. Currently, the code assumes that you have already generated the table on the target database before you copy the data. It would be hard to automatically generate the table DDL due to the differences in the syntax for each database system.

/// <summary>
/// This method will copy the data in a table 
/// from one database to another. The
/// source and destination can be from any type of 
/// .NET database provider.
/// </summary>
/// <param name="source">Source database connection</param>
/// <param name="destination">Destination database connection</param>
/// <param name="sourceSQL">Source SQL statement</param>
/// <param name="destinationTableName">Destination table name</param>
public static void CopyTable(IDbConnection source,
    IDbConnection destination, String sourceSQL, String destinationTableName)
{
    System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss") + 
        " " + destinationTableName + " load started");
    IDbCommand cmd = source.CreateCommand();
    cmd.CommandText = sourceSQL;
    System.Diagnostics.Debug.WriteLine("\tSource SQL: " + sourceSQL);
    try 
    {
        source.Open();
        destination.Open();
        IDataReader rdr = cmd.ExecuteReader();
        DataTable schemaTable = rdr.GetSchemaTable();

        IDbCommand insertCmd = destination.CreateCommand();
        string paramsSQL = String.Empty;

        //build the insert statement
        foreach (DataRow row in schemaTable.Rows) 
        {
            if (paramsSQL.Length > 0)
            paramsSQL += ", ";
            paramsSQL += "@" + row["ColumnName"].ToString();

            IDbDataParameter param = insertCmd.CreateParameter();
            param.ParameterName = "@" + row["ColumnName"].ToString();
            param.SourceColumn = row["ColumnName"].ToString();

            if (row["DataType"] == typeof(System.DateTime)) 
            {
                param.DbType = DbType.DateTime;
            }

            //Console.WriteLine(param.SourceColumn);
            insertCmd.Parameters.Add(param);
        }
        insertCmd.CommandText = 
            String.Format("insert into {0} ( {1} ) values ( {2} )",
            destinationTableName, paramsSQL.Replace("@", String.Empty),
            paramsSQL);
        int counter = 0;
        int errors = 0;
        while (rdr.Read()) 
        {
            try 
            {
                foreach (IDbDataParameter param in insertCmd.Parameters) 
                {
                    object col = rdr[param.SourceColumn];

                    //special check for SQL Server and 
                    //datetimes less than 1753
                    if (param.DbType == DbType.DateTime) 
                    {
                        if (col != DBNull.Value) 
                        {
                            //sql server can not have dates less than 1753
                            if (((DateTime)col).Year < 1753) 
                            {
                                param.Value = DBNull.Value;
                                continue;
                            }
                        }
                    }

                    param.Value = col;

                    //uncomment this line to see the 
                    //values being used for the insert
                    //System.Diagnostics.Debug.WriteLine( param.SourceColumn + " --> " + 
                    //param.ParameterName + " = " + col.ToString() );
                }
                insertCmd.ExecuteNonQuery();
                //un-comment this line to get a record count. You may only want to show status for every 1000 lines
                //this can be done by using the modulus operator against the counter variable
                //System.Diagnostics.Debug.WriteLine(++counter);
            }
            catch (Exception ex ) 
            {
                if( errors == 0 )
                System.Diagnostics.Debug.WriteLine(ex.Message.ToString());
                errors++;
            }
        }
        System.Diagnostics.Debug.WriteLine(errors + " errors");
        System.Diagnostics.Debug.WriteLine(counter + " records copied");
        System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss") + 
        " " + destinationTableName + " load completed");
    }
    catch (Exception ex) 
    {
        Console.WriteLine( ex.StackTrace.ToString());
        System.Diagnostics.Debug.WriteLine(ex);
    }
    finally 
    {
        destination.Close();
        source.Close();
    }
}

How to use the code

Copying the Products table from the Northwind database:

//pre-requisite: Create the Products table in the target database
SqlConnection src = 
    new SqlConnection(Data Source=localhost; Initial Catalog=
    Northwind; Integrated Security=True);
OdbcConnection dest = 
    new OdbcConnection("DSN=my_database;Uid=northwind_user;Pwd=password");
Utils.CopyTable(src, dest, "select * from Products", "ProductsCopy");

With some imagination, you can find many uses for this method. For example, oftentimes I will join several tables in the source database and use the results of the complex query in order to create a "temporary working table" to run reports against. To create this temporary working area, just pass the complex SQL statement as the sourceSQL parameter, create a table in the destination database to accommodate the result set and run the CopyTable function to load the data.

Points of interest

As you may have noticed, there is a section of code that is SQL Server-specific. This basically has to do with date-time values that are less than 1/1/1753. Unfortunately, I could not figure out a way to deal with this in an abstract manner. You may want to remove the section of code if you are not using SQL Server. You could also wrap this block of code with an if statement and check to see if the target connection is of the type SqlConnection.

//special check for SQL Server and datetimes less than 1753
if (param.DbType == DbType.DateTime) 
{
    if (col != DBNull.Value) 
    {
        //sql server can not have dates less than 1753
        if (((DateTime)col).Year < 1753) 
        {
            param.Value = DBNull.Value;
            continue;
        }
    }
}

I currently have not had a need to make a user interface for this application because I have generally used it for "behind the scenes" operations. However, with a little bit of work you could make a UI for this application that simply passes parameters to the CopyTable method and displays a progress bar for the end user.

History

• 24 July, 2007 -- Original version posted
• 25 July, 2007 -- Updated code to remove the calls to Program.Log, replacing them with System.Diagnostics.Debug.WriteLine