Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / C#
Article

ADO.NET Generic Copy Table Data Function

Rate me:
Please Sign up or sign in to vote.
4.42/5 (15 votes)
25 Jul 20072 min read 110.2K   606   35   26
This generic function will allow you to copy data between any two ADO.NET providers with one simple method call

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.
C#
/// <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:

C#
//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.

C#
//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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior) Concepts2Code
United States United States
Michael is the co-founder and master consultant for Concepts2Code, a software consulting company based in Buffalo, New York. He's been programming since the early 1990's. His vast programming experience includes VB, Delphi, C#, ASP, ASP.NET, Ruby on Rails, Coldfusion and PHP. Michael also is a Microsoft Certified Application Developer and a Certified Technology Specialist for SQL Server.

Visit his blog.

Comments and Discussions

 
QuestionAwesome useful utility Pin
JDaniels1327-Jul-12 12:51
JDaniels1327-Jul-12 12:51 
AnswerRe: Awesome useful utility Pin
Michael Ceranski27-Jul-12 16:57
Michael Ceranski27-Jul-12 16:57 
GeneralGood Pin
loyal ginger5-Nov-09 9:52
loyal ginger5-Nov-09 9:52 
GeneralModified Function Pin
mouthbow8-Jun-09 5:35
mouthbow8-Jun-09 5:35 
GeneralProblem with supposed null value Pin
Jinx10120-Aug-08 18:09
Jinx10120-Aug-08 18:09 
GeneralRe: Problem with supposed null value Pin
Michael Ceranski21-Aug-08 2:01
Michael Ceranski21-Aug-08 2:01 
GeneralRe: Problem with supposed null value (FIXED) Pin
Jinx10121-Aug-08 8:56
Jinx10121-Aug-08 8:56 
GeneralRe: Problem with supposed null value (FIXED) Pin
Michael Ceranski21-Aug-08 8:59
Michael Ceranski21-Aug-08 8:59 
GeneralSweet Pin
Jinx10120-Aug-08 14:44
Jinx10120-Aug-08 14:44 
Questionthats very good program... Pin
tusharparmar@rediffmail.com31-Oct-07 14:42
tusharparmar@rediffmail.com31-Oct-07 14:42 
AnswerRe: thats very good program... Pin
Michael Ceranski1-Nov-07 4:51
Michael Ceranski1-Nov-07 4:51 
GeneralAccess DateTime fields do not copy Pin
nmg19618-Sep-07 7:00
nmg19618-Sep-07 7:00 
GeneralRe: Access DateTime fields do not copy Pin
Michael Ceranski19-Sep-07 2:16
Michael Ceranski19-Sep-07 2:16 
GeneralGreat code but I have a problem Pin
nmg19612-Sep-07 1:18
nmg19612-Sep-07 1:18 
GeneralRe: Great code but I have a problem Pin
Michael Ceranski12-Sep-07 1:38
Michael Ceranski12-Sep-07 1:38 
GeneralRe: Great code but I have a problem Pin
nmg19612-Sep-07 1:42
nmg19612-Sep-07 1:42 
GeneralRe: Great code but I have a problem Pin
Michael Ceranski12-Sep-07 1:54
Michael Ceranski12-Sep-07 1:54 
GeneralRe: Great code but I have a problem Pin
nmg19612-Sep-07 2:04
nmg19612-Sep-07 2:04 
GeneralRe: Great code but I have a problem [FIX FOR PROBLEM] Pin
nmg19612-Sep-07 2:19
nmg19612-Sep-07 2:19 
GeneralRe: Great code but I have a problem Pin
Michael Ceranski12-Sep-07 2:25
Michael Ceranski12-Sep-07 2:25 
GeneralRe: Great code but I have a problem Pin
nmg19612-Sep-07 2:50
nmg19612-Sep-07 2:50 
GeneralRe: Great code but I have a problem Pin
Michael Ceranski12-Sep-07 4:16
Michael Ceranski12-Sep-07 4:16 
The square brackets are a good idea. I will update the article. I appreciate the feedback.

I did not think about it because I generally would never but spaces in a columns name. It usually causes more problems.
GeneralRe: Great code but I have a problem Pin
nmg19612-Sep-07 4:33
nmg19612-Sep-07 4:33 
Generalworks and works well Pin
Al Ortega31-Jul-07 13:03
Al Ortega31-Jul-07 13:03 
GeneralRe: works and works well Pin
Michael Ceranski1-Aug-07 2:02
Michael Ceranski1-Aug-07 2:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.