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

【实例简介】可以实现从 sql server 将数据迁移到 oracle

【实例截图】

【核心代码】

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