ADO.NET 通用复制表数据函数

• 下载源代码 - 1.6 Kb

引言

您是否曾经想要将数据从生产系统移动到开发环境，而无需使用 DTS 或 SSIS 的开销？这个通用函数允许您通过一个简单的方法调用在任何两个 ADO.NET 提供程序之间复制数据。由于其简单性和扩展到更复杂的转换的能力，只需进行很少的修改，我已经多次使用此函数代替 DTS 作业。

CopyTable 方法

CopyTable 方法接受 4 个参数

1. 源数据库连接。 这是您要从中复制数据的连接。
2. 目标数据库连接。 这是您要将数据复制到的连接。
3. 源 SQL 语句。 使用 “select *” 选择所有字段。 如果要过滤复制到源表的列，则可以指定这些列。
4. 目标表名。 这是将数据复制到的表的名称。 目前，该代码假定您在复制数据之前已经在目标数据库上生成了表。 由于每个数据库系统的语法差异，很难自动生成表 DDL。

/// <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();
    }
}

如何使用代码

从 Northwind 数据库复制 Products 表

//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");

通过一些想象力，您可以发现此方法的许多用途。 例如，我经常将源数据库中的多个表连接起来，并使用复杂查询的结果来创建一个“临时工作表”来运行报表。 要创建此临时工作区，只需将复杂的 SQL 语句作为 sourceSQL 参数传递，在目标数据库中创建一个表以容纳结果集，然后运行 CopyTable 函数来加载数据。

关注点

您可能已经注意到，有一段代码是 SQL Server 特定的。 这主要与小于 1/1/1753 的日期时间值有关。 遗憾的是，我无法想出一种以抽象方式处理此问题的方法。 如果您不使用 SQL Server，您可能需要删除该段代码。 您也可以用 if 语句包装此代码块，并检查目标连接是否为 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;
        }
    }
}

我目前还没有必要为此应用程序制作用户界面，因为我通常将其用于“幕后”操作。 但是，稍微做一些工作，您就可以为此应用程序创建一个 UI，该 UI 只是将参数传递给 CopyTable 方法，并为最终用户显示进度条。

历史

• 2007 年 7 月 24 日 -- 发布原始版本
• 2007 年 7 月 25 日 -- 更新了代码，删除了对 Program.Log 的调用，并将其替换为 System.Diagnostics.Debug.WriteLine