数据库操作中,我们有时候需要将数据从一个数据库导入到另一个数据库中。本文我们主要介绍一下SQL Server 2005导入Oracle 10g的C#源码程序,希望能够对您有所帮助。
程序代码的参考如下:
以下是代码片段: //-------------------------------------------------------------------- //AllRightsReserved,Copyright(C)2011,HairihanTECH,Ltd. //-------------------------------------------------------------------- usingSystem.Data; namespaceDotNet.Example { usingDotNet.BaseManager; usingDotNet.DbUtilities; publicclassImportExportData { ///<summary> ///导出数据库到Oralce ///</summary> publicvoidExport() { //this.ExportTable("Items_Area"); //this.ExportTable("Items_AuditStatus"); //this.ExportTable("Items_Degree"); //this.ExportTable("Items_Duty"); //this.ExportTable("Items_Education"); //this.ExportTable("Items_Express"); //this.ExportTable("Items_Links"); //this.ExportTable("Items_MembershipLevels"); //this.ExportTable("Items_Nationality"); //this.ExportTable("Items_NewsCategory"); //this.ExportTable("Items_OnSale"); //this.ExportTable("Items_OrganizeCategory"); //this.ExportTable("Items_Party"); //this.ExportTable("Items_Pattern"); //this.ExportTable("Items_PayCategory"); //this.ExportTable("Items_PostCategory"); //this.ExportTable("Items_RoleCategory"); //this.ExportTable("Items_SalaryItemCategory"); //this.ExportTable("Items_SendCategory"); //this.ExportTable("Items_Sex"); //this.ExportTable("Items_Title"); //this.ExportTable("Items_Units"); //this.ExportTable("Items_UserAuditStates"); //this.ExportTable("Items_Wed"); //this.ExportTable("Items_WorkCategory"); //this.ExportTable("Items_WorkFlowCategories"); //this.ExportTable("Items_WorkingProperty"); //this.ExportTable("Base_Businesscard"); //this.ExportTable("Base_Comment"); //this.ExportTable("Base_Contact"); //this.ExportTable("Base_ContactDetails"); //this.ExportTable("Base_Exception"); //this.ExportTable("Base_File"); //this.ExportTable("Base_Folder"); //this.ExportTable("Base_Items"); //this.ExportTable("Base_Log"); //this.ExportTable("Base_Message"); //this.ExportTable("Base_News"); //this.ExportTable("Base_Organize"); //this.ExportTable("Base_Module","SELECT*FROMBASE_MODULEORDERBYPARENTID,ID"); //this.ExportTable("Base_PermissionItem"); //this.ExportTable("Base_Role"); //this.ExportTable("Base_Staff"); //this.ExportTable("Base_Parameter"); //this.ExportTable("Base_Project"); //this.ExportTable("Base_Permission"); //this.ExportTable("Base_PermissionScope"); this.ExportTable("Base_Sequence"); //this.ExportTable("Base_StaffOrganize"); //this.ExportTable("Base_TableColumns"); //this.ExportTable("Base_User"); //this.ExportTable("Base_UserAddress"); //this.ExportTable("Base_UserOrganize"); //this.ExportTable("Base_UserRole"); //this.ExportTable("Base_StaffOrganize"); //this.ExportTable("Base_WorkFlowActivity"); //this.ExportTable("Base_WorkFlowCurrent"); //this.ExportTable("Base_WorkFlowHistory"); //this.ExportTable("Base_WorkFlowProcess"); System.Console.ReadLine(); } publicvoidExportTable(stringtableName) { ExportTable(tableName,tableName); } ///<summary> ///导出一个表 ///</summary> ///<paramnameparamname="tableName">表名</param> ///<paramnameparamname="table">里面的数据</param> publicvoidExportTable(stringtableName,stringtable) { //这里是获取数据 SqlHelpersqlHelper=newSqlHelper("DataSource=localhost;InitialCatalog=UserCenterV32;IntegratedSecurity=SSPI;"); sqlHelper.Open(); DataTabledataTable=newDataTable(tableName); if(tableName.Equals(table)) { dataTable=sqlHelper.Fill("SELECT*FROM"+table); } else { dataTable=sqlHelper.Fill(table); } sqlHelper.Close(); //这里是插入数据 OracleHelperoracleHelper=newOracleHelper("DataSource=ORACLE11;user=DBO_USERCENTERV32;password=DBO_USERCENTERV32;"); oracleHelper.Open(); oracleHelper.BeginTransaction(); SQLBuildersqlBuilder=newSQLBuilder(oracleHelper); try { //清除表数据 oracleHelper.ExecuteNonQuery("TRUNCATETABLE"+tableName); //创建配套的序列 //oracleHelper.ExecuteNonQuery("createsequenceSEQ_"+tableName+"minvalue1maxvalue999999999999999999999999startwith1incrementby1cache20"); intr=0; for(r=0;r<dataTable.Rows.Count;r++) { sqlBuilder.BeginInsert(tableName); for(inti=0;i<dataTable.Columns.Count;i++) { sqlBuilder.SetValue(dataTable.Columns[i].ColumnName,dataTable.Rows[r][dataTable.Columns[i].ColumnName]); } sqlBuilder.EndInsert(); System.Console.WriteLine("表"+tableName+"已插入第"+r.ToString()+"行"); } System.Console.WriteLine("--表"+tableName+"共插入"+r.ToString()+"行"); oracleHelper.CommitTransaction(); } catch(System.Exceptionexception) { oracleHelper.RollbackTransaction(); System.Console.WriteLine(tableName+"--"+exception.Message); } finally { oracleHelper.Close(); } } } |