使用 ADO.NET 2.0 SqlBulkCopy 在不同环境之间迁移数据的应用程序

• 下载源代码文件 - 28.7 Kb

引言

通常，出于测试或审计目的，需要在不同环境之间迁移数据。例如，可以将生产数据移动到测试环境，并且测试人员可以使用此数据。这可以清楚地了解程序在实际数据量上的行为。使用此实用程序，可以轻松迁移数据。我使用 ADO.NET 2.0 的 SqlBulkCopy 进行批量复制数据。此托管类提供了与 BCP 实用程序类似的功能。该程序已成功用于大量数据。我大约花费了 1 小时来加载 8GB 的数据。更准确地说，加载 110 万条记录，耗时 4 分钟 30 秒。它支持 SQL 2005 和 SQL 2000 服务器。可用功能有

• 可以选择特定的表来迁移数据
• 删除所选表中的现有数据
• 报告数据加载的进度
• 记录每个表的加载或删除操作的结果
• 可以在进程中间取消；应用程序在完成当前表后停止加载数据。

解决方案演练

此解决方案包含一个 C# Windows Forms 项目。这是一个非常简单的项目。Windows Form 完成所有工作。它使用 BackgroundWorker 组件来报告进度。源和目标连接在应用程序配置文件中定义。如果在表中删除或加载数据时发生任何异常，应用程序将记录该异常并继续处理下一个表。

此应用程序中的两个主要功能是 DeleteData 和 LoadData。

DeleteData

此功能涉及以下任务

• 禁用所有表上的约束。
• 禁用所有表上的触发器。
• 从表中删除数据（根据表上的外键约束使用 DELETE 或 TRUNCATE 语句）。
• 记录结果。
• 启用所有表上的约束。
• 启用所有表上的触发器。

private void DeleteData()
{
    using (SqlConnection conn = new SqlConnection(strDestConn))
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandTimeout = 0;
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            //Disable Constraints on all tables before deleting data
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                cmd.CommandText = "ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] NOCHECK CONSTRAINT ALL";
                cmd.CommandText += " ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] DISABLE TRIGGER ALL";
                cmd.ExecuteNonQuery();
            }

            //Delete data in selected tables and log results
            string strLogFile = "DeleteLog" + 
                   DateTime.Now.ToString("MMddyyyyHHmm") + ".txt";
            using (StreamWriter sw = new StreamWriter(strLogFile, false))
            {
                sw.AutoFlush = true;
                foreach (object dr in lstInclude.Items)
                {
                    try
                    {
                        cmd.CommandText = 
                         "SELECT OBJECTPROPERTY ( object_id('" + 
                         dr.ToString() + "'),'TableHasForeignRef')";
                        int intref = Convert.ToInt32(cmd.ExecuteScalar());
                        if (intref == 1)
                            cmd.CommandText = "DELETE FROM " + dr.ToString();
                        else
                            cmd.CommandText = "TRUNCATE TABLE " + 
                                              dr.ToString();
                        cmd.ExecuteNonQuery();

                        sw.WriteLine("Data deleted successfully from " + 
                                     dr.ToString() + " at " + 
                                     DateTime.Now.ToString());
                        sw.WriteLine();
                    }
                    catch (Exception ex)
                    {
                        sw.WriteLine("Error while deleting data" + 
                                     " in table " + dr.ToString() + 
                                     ".Error is " + ex.Message);
                        sw.WriteLine();
                    }
                }
            }

            //Enable Constraints on all tables
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                cmd.CommandText = "ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] CHECK CONSTRAINT ALL";
                cmd.CommandText += " ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] ENABLE TRIGGER ALL";
                cmd.ExecuteNonQuery();
            }
        }                
    }
}

LoadData

此功能涉及以下任务

• 使用 SQL 数据读取器从源表加载数据。
• 使用 KeepIdentity 和 TableLock 选项创建 SqlBulkCopy。对表加锁可以使加载过程更快。默认情况下，SqlBulkCopy 在加载数据时禁用表上的约束和触发器。
• 映射源表和目标表的列。如果两个环境之间的列序号位置不同，则这是必要的。
• 在映射列时消除计算列。
• 通过将数据读取器传递给 SqlBulkCopy 来将数据批量插入到表中。

private void LoadData(string pSourceConn,string pDestConn,string pTable)
{
    using (SqlConnection srcConn = new SqlConnection(pSourceConn))
    {
        string[] strTable = pTable.Split('.');
        //Get data from source table
        srcConn.Open();                
        SqlCommand srcCommand = new SqlCommand("SELECT * FROM " + 
                                pTable, srcConn);
        srcCommand.CommandTimeout = 0;
        SqlDataReader sqldr = srcCommand.ExecuteReader();
        SqlBulkCopy sqlbcp = new SqlBulkCopy(pDestConn,
                             SqlBulkCopyOptions.KeepIdentity | 
                             SqlBulkCopyOptions.TableLock);

        //Map Columns
        SqlConnection destConn = new SqlConnection(pDestConn);
        destConn.Open();
        SqlCommand cmd1 = new SqlCommand("SELECT COLUMN_NAME," + 
                         "COLUMNPROPERTY(OBJECT_ID('" + 
                         strTable[0] +"." +strTable[1]+
                         "'),COLUMN_NAME,'IsComputed')AS 'IsComputed' " + 
                         "FROM INFORMATION_SCHEMA.COLUMNS " + 
                         "WHERE TABLE_SCHEMA = '" + 
                         strTable[0] + "' AND TABLE_NAME = '" + 
                         strTable[1] + "'", destConn);
        SqlDataReader drcolumns = cmd1.ExecuteReader();
        while (drcolumns.Read())
        {
            if(drcolumns.GetInt32(1) != 1)
            sqlbcp.ColumnMappings.Add(drcolumns.GetString(0), 
                                      drcolumns.GetString(0));
        }
        drcolumns.Close();

        //Load Data
        sqlbcp.BulkCopyTimeout = 0;                
        sqlbcp.DestinationTableName = pTable;
        sqlbcp.WriteToServer(sqldr);
        sqldr.Close();
    }
 }

结论

就这些了。我希望此应用程序对迁移您的数据有所帮助。如果您想从不同的数据源（例如：XML 文件、平面文件）加载数据，可以轻松自定义此功能。感谢您的阅读。请就问题或改进方法向我提供反馈。