数据迁移(从 sql server 将数据迁移到 oracle)

可以实现从 sql server 将数据迁移到 oracleusing Power.Common;using Power.Controls.Action;using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.Common;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using XCode.DataAccessLayer;namespace TestDemoSimple{ public partial class FrmTransData : Form { Power.Controls.Action.IEntityAction IEntityAction = new Power.Controls.Action.EntityAction(); public FrmTransData() { InitializeComponent(); } private void btnTransTable_Click(object sender, EventArgs e) { if (cboTargetData.SelectedIndex < 0 || cboSourceData.SelectedIndex < 0) { MessageBox.Show("对不起，请选择数据源"); return; } Item tmpItem = (Item)cboSourceData.Items[cboSourceData.SelectedIndex]; DataBaseEntity source = (DataBaseEntity)tmpItem.Target; tmpItem = (Item)cboTargetData.Items[cboTargetData.SelectedIndex]; DataBaseEntity target = (DataBaseEntity)tmpItem.Target; if (source.DataBaseCode == target.DataBaseCode) { MessageBox.Show("源数据源和目标数据源不能相同"); return; } TransTable(source.DataBaseCode, target.DataBaseCode); MessageBox.Show("同步表结构成功"); } private void TransTable(string dataBaseCode, string objDataBaseCode) { DAL dal = DAL.Create(dataBaseCode); DAL dalObj = DAL.Create(objDataBaseCode); NegativeSetting set = new NegativeSetting(); set.NoDelete = true; set.CreateSql = true; set.CheckOnly = true; try { StringBuilder sqlBuilder = new StringBuilder(); // sqlBuilder.AppendLine("declare cnt number;"); sqlBuilder.AppendLine(""); // sqlBuilder.AppendLine("begin "); List<IDataTable> lstTables = dal.Tables; int iIndex = 0; foreach (IDataTable table in lstTables) { if (table.IsView == true) continue; iIndex ; IDataTable[] tables = new XTable[1]; tables[0] = table; // sqlBuilder.AppendLine("select count(*) into cnt from user_tables where table_name='" table.TableName "';"); // sqlBuilder.AppendLine("if cnt>0 then "); string sSql = dalObj.SetTables(set, tables).ToString(); sqlBuilder.AppendLine(sSql); // sqlBuilder.AppendLine(" end if;"); sqlBuilder.AppendLine(); sqlBuilder.AppendLine(); } sqlBuilder.AppendLine("end; "); string sFilePath = AppDomain.CurrentDomain.BaseDirectory "/CreateSqlForOracle.sql"; System.IO.PathHelper.StringToFile(sqlBuilder.ToString(), sFilePath); return; } finally { dal.Session.AutoClose(); dalObj.Session.AutoClose(); } } private void btnTransRecord_Click(object sender, EventArgs e) { if (cboTargetData.SelectedIndex < 0 || cboSourceData.SelectedIndex < 0) { MessageBox.Show("对不起，请选择数据源"); return; } Item tmpItem = (Item)cboSourceData.Items[cboSourceData.SelectedIndex]; DataBaseEntity source = (DataBaseEntity)tmpItem.Target; tmpItem = (Item)cboTargetData.Items[cboTargetData.SelectedIndex]; DataBaseEntity target = (DataBaseEntity)tmpItem.Target; if (source.DataBaseCode == target.DataBaseCode) { MessageBox.Show("源数据源和目标数据源不能相同"); return; } TransRecord(source.DataBaseCode, target.DataBaseCode); MessageBox.Show("同步记录成功"); } private void TransRecord(string dataBaseCode, string objDataBaseCode) { DAL dal = DAL.Create(dataBaseCode); DAL dalObj = DAL.Create(objDataBaseCode); try { string sSql = ""; List<IDataTable> lstTables = dal.Tables; int iIndex = 0; List<string> lstTT = new List<string>(); foreach (IDataTable table in lstTables) { if (lstTT.Contains(table.TableName) == true) continue; lstTT.Add(table.TableName); try { sSql = "Select * from " table.TableName; long iCount = dalObj.Session.QueryCount(sSql); if (iCount > 0) continue; sSql = "Select * from " table.TableName; DataTable dtTable = dal.Session.Query(sSql, null).Tables[0]; using (var trans = new XCode.EntityTransaction(dalObj.Session)) { iCount = dtTable.Rows.Count; int iTmpIndex = 0; int iErrCount = 0; foreach (DataRow row in dtTable.Rows) { iTmpIndex ; DbParameter[] dps = null; sSql = InsertSQL(table, row, dalObj, ref dps); try { dalObj.Execute(sSql, CommandType.Text, dps); } catch(System.Exception exxx) { iErrCount ; continue; } } trans.Commit(); } } catch (System.Exception e) { string serr = e.Message; } iIndex ; } return; } finally { dal.Session.AutoClose(); dalObj.Session.AutoClose(); } } private string InsertSQL(IDataTable table, DataRow row, DAL dal, ref DbParameter[] parameters) { var sbNames = new StringBuilder(); var sbValues = new StringBuilder(); //sbParams = new StringBuilder(); var dps = new List<DbParameter>(); // 只读列没有插入操作 foreach (var fi in table.Columns) { var value = row[fi.ColumnName]; if (fi.IsAutoInsert == true) continue; sbNames.AppendSeparate(", "); sbNames.Append(fi.ColumnName); sbValues.AppendSeparate(", "); dps.Add(CreateParameter(sbValues, dal, fi, value)); } if (sbNames.Length <= 0) return null; if (dps.Count > 0) parameters = dps.ToArray(); return String.Format("Insert Into {0}({1}) Values({2})", table.TableName, sbNames, sbValues); } static DbParameter CreateParameter(StringBuilder sb, DAL dal, IDataColumn fi, Object value) { var paraname = fi.ColumnName; sb.Append(":" paraname); var dp = dal.Session.CreateCommand().CreateParameter(); dp.ParameterName = paraname; if (fi.DataType == typeof(Guid)) dp.DbType = DbType.String; if (fi.DataType == typeof(DateTime)) dp.DbType = DbType.DateTime; dp.IsNullable = fi.Nullable; if (fi.Nullable == false) { if (value == null || string.IsNullOrEmpty(value.ToString()) == true) dp.Value = ""; } if (value != null) { if (fi.DataType == typeof(Guid)) dp.Value = value.ToString(); else if (fi.DataType == typeof(Boolean)) { dp.Value = (value.ToString().ToLower() == "true") ? 1 : 0; } else dp.Value = value; } return dp; } private void SetSource() { List<DataBaseEntity> lst = IEntityAction.LoadDataSource(); foreach (var tmp in lst) { var item = new Item(tmp.DataBaseCode, tmp.DataBaseCode, tmp.DataBaseName, "", tmp); cboSourceData.Items.Add(item); cboTargetData.Items.Add(item); } } private void FrmTransData_Load(object sender, EventArgs e) { SetSource(); } }}