|
因为有利的开发环境，让我有机会来折腾一下ADO中的SqlBulkCopy这个功能。因为曾经公司做过库迁移，把数据从MSSQL2005迁到ORACLE下，当时本人用C#写了一个迁移工具，因当时对.NET操作数据库只是一般的熟练,没能发挥它的最佳性能和方法,写出来的工具在导数据的时候显示有点慢,后来改用BCP来导,BCP做数据导入还是非常快。时过几个月，心想自己再优化一下算法和方法，让数据迁移更快更方便，于是今天就先折腾一下.NET中的BCP批量导入功能. 测试环境: 1、WINDOWS SERVER 2003 +MSSQL2005（服务器） 2、LINUX+ORACLE 10G（服务器） 3、本人电脑配置：Pentium D 2.8G+2G内存 4、 本人操作系统：WINDOWS XP+VS2005+.NET FRAMEWORK2.0 5、从ORACLE数据库中导一张82万的数据到MSSQL2005中? 程序代码: view plaincopy to clipboardprint?
using System;??
using System.Data.SqlClient;??
using System.Data.OracleClient;??
?
class Program??
?
{??
??? static void Main()??
?
??? {??
??????? string connectionString = GetOracleConnectionString();??
?
?????? try?
?
??????? {??
?
??????? using (OracleConnection sourceConnection = new OracleConnection(connectionString))??
?
??????? {??
?
??????????? sourceConnection.Open();??
?
??????????? OracleCommand commandRowCount = new OracleCommand( "SELECT COUNT(*) FROM 表",sourceConnection);??
?
??????????? long countStart = System.Convert.ToInt32(??
?
??????????? commandRowCount.ExecuteScalar());??
?
??????????? Console.WriteLine("Starting row count = {0}",countStart);??
?
??????? DateTime Dt1=DateTime.Now;??
?
??????????? Console.WriteLine("Start Reading Data....");??
?
??????????? string Sqltxt="select * from 表";??
?
??????????? OracleCommand commandSourceData = new OracleCommand(Sqltxt,sourceConnection);??
?
??????????? OracleDataReader reader =??
?
??????????? commandSourceData.ExecuteReader();??
?
?????? string desconnectionString=GetMsConnectionString();??
?
??????????? Console.WriteLine("Start Moveing Data....");??
?
??????????? using (SqlConnection destinationConnection = new SqlConnection(desconnectionString))??
?
??????????? {??
?
??????????????? destinationConnection.Open();??
?
??????????????? using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))??
?
??????????????? {??
?
?????????????????? bulkCopy.BulkCopyTimeout=5000000;??
?
?
??????????????????? bulkCopy.DestinationTableName ="dbo.表";??
?
??????????????????? try?
?
??????????????????? {??
?
??????????????????????? bulkCopy.WriteToServer(reader);??
?
??????????????????? }??
?
??????????????????? catch (Exception ex)??
?
??????????????????? {??
?
??????????????????????? Console.WriteLine(ex.Message);??
?
??????????????????? }??
?
??????????????????? finally?
?
??????????????????? {??
?
??????????????????????? reader.Close();??
?
??????????????????? }??
?
?
??????????????? }??
?
??????? DateTime Dt2=DateTime.Now;??
?
??????? TimeSpan CountTime=Dt2.Subtract(Dt1);??
?
??????????????? long countEnd = System.Convert.ToInt32(??
?
??????????????? commandRowCount.ExecuteScalar());??
?
??????????????? Console.WriteLine("Ending row count = {0}",countEnd);??
?
??????????????? Console.WriteLine("{0} rows were added.",countEnd - countStart);??
?
??????????????? Console.WriteLine("用时:"+CountTime.Minutes.ToString()+"分"+CountTime.Seconds.ToString()+"秒"+CountTime.Milliseconds.ToString()+"毫秒");??
?
??????????????? Console.WriteLine("Press Enter to finish.");??
?
??????????????? Console.ReadLine();??
?
??????????? }??
?
??????? }??
?
??? }??
?
??? catch(Exception ex)??
?
??? {??
?
??????? Console.Write(ex.ToString());??
?
??? }??
?
? }??
?
??? private static string GetMsConnectionString()??
?
??? {??
??????? return "uid=用户名;password=密码;database=库;server=192.168.X.X//sql2005";??
?
??? }??
?
??? private static string GetOracleConnectionString()??
?
??? {??
?
??????? return "Data Source=库;User ID=用户名;Password=密码";??
?
??? }??
?
}?
using System;
using System.Data.SqlClient;
using System.Data.OracleClient;
class Program
{
??? static void Main()
??? {
??????? string connectionString = GetOracleConnectionString();
?????? try
??????? {
??????? using (OracleConnection sourceConnection = new OracleConnection(connectionString))
??????? {
??????????? sourceConnection.Open();
??????????? OracleCommand commandRowCount = new OracleCommand( "SELECT COUNT(*) FROM 表",sourceConnection);
??????????? long countStart = System.Convert.ToInt32(
??????????? commandRowCount.ExecuteScalar());
??????????? Console.WriteLine("Starting row count = {0}",countStart);
???? DateTime Dt1=DateTime.Now;
??????????? Console.WriteLine("Start Reading Data....");
??????????? string Sqltxt="select * from 表";
??????????? OracleCommand commandSourceData = new OracleCommand(Sqltxt,sourceConnection);
??????????? OracleDataReader reader =
??????????? commandSourceData.ExecuteReader();
??? string desconnectionString=GetMsConnectionString();
??????????? Console.WriteLine("Start Moveing Data....");
??????????? using (SqlConnection destinationConnection = new SqlConnection(desconnectionString))
??????????? {
??????????????? destinationConnection.Open();
??????????????? using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
??????????????? {
?????????????? ??? bulkCopy.BulkCopyTimeout=5000000;
??????????????????? bulkCopy.DestinationTableName ="dbo.表";
??????????????????? try
??????????????????? {
??????????????????????? bulkCopy.WriteToServer(reader);
??????????????????? }
??????????????????? catch (Exception ex)
??????????????????? {
??????????????????????? Console.WriteLine(ex.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? reader.Close();
??????????????????? }
??????????????? }
??DateTime Dt2=DateTime.Now;
??TimeSpan CountTime=Dt2.Subtract(Dt1);
??????????????? long countEnd = System.Convert.ToInt32(
??????????????? commandRowCount.ExecuteScalar());
??????????????? Console.WriteLine("Ending row count = {0}",countEnd);
??????????????? Console.WriteLine("{0} rows were added.",countEnd - countStart);
??????????????? Console.WriteLine("用时:"+CountTime.Minutes.ToString()+"分"+CountTime.Seconds.ToString()+"秒"+CountTime.Milliseconds.ToString()+"毫秒");
??????????????? Console.WriteLine("Press Enter to finish.");
??????????????? Console.ReadLine();
??????????? }
??????? }
??? }
??? catch(Exception ex)
??? {
??? ?Console.Write(ex.ToString());
??? }
? }
??? private static string GetMsConnectionString()
??? {
??????? return "uid=用户名;password=密码;database=库;server=192.168.X.X//sql2005";
??? }
??? private static string GetOracleConnectionString()
??? {
??????? return "Data Source=库;User ID=用户名;Password=密码";
??? }
}经测试通过 从ORACLE中导一张82万条数据的表到SQL2005中只花了2分钟多一点，当时我我非常惊讶，就连BCP导入也没这么快过，让我惊叹不已。 数据源可以是任意，但目标数据库一定要是MSSQL，这让我感到有点不爽，因为ORACLE.DATA.ORACLECLIENT命名空间没有这个类,也就是说你可以 从任何源数据导到MSSQL,这或许是微软的一种战略,让别人从其它数据库切换到MS数据库快更方便吧!
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
