C# 迁移Oracle数据库视图

     本文主要介绍如何用C#实现 oracle视图如何从一个数据库迁移到另一个数据中：

     

      oracle数据库视图的详细介绍参照：oracle 视图信息在数据库中的存储详解。

   

迁移步骤：

    1、首先根据视图之间的依赖关系，确定迁移的先后顺序。

    2、迁移视图结构的创建语句。

    3、迁移视图的注释信息。

    4、迁移视图的列注释信息。

迁移注意事项：

    OracleCommand执行的SQL 语句中不能有 ";" ，否则会报错。所以不能将视图结构语句、视图的注释 和视图列的注释放在一条SQL语句中执行。

详细代码如下：

 public class MigrationView
    {
        #region 属性字段

        //数据库连接管理
        private DataBaseHelper _dataBaseHelper;

        //源数据库连接
        private OracleConnection _oracleSourceConn;

        //目标数据库连接
        private OracleConnection _oracleTargetConn;

        #endregion

        #region 构造函数

        public  MigrationView()
        {
            _dataBaseHelper = new DataBaseHelper();
        }

        #endregion


        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="connString">连接字符换</param>
        /// <returns>连接结果</returns>
        public string TestOracelConn(string connString)
        {
            try
            {
                OracleConnection oracleSourceConnTest = _dataBaseHelper.CreateOracleConn(connString);
                return "连接成功！";
            }
            catch (Exception ex)
            {
                return string.Format("连接失败，原因：" + ex.Message);
            }
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <param name="sourceConnString">源数据库连接</param>
        /// <param name="targetConnString">目标数据库连接</param>
        public void InitConnection(string sourceConnString, string targetConnString)
        {
            try
            {
                _oracleSourceConn = _dataBaseHelper.CreateOracleConn(sourceConnString);
                _oracleTargetConn = _dataBaseHelper.CreateOracleConn(targetConnString);
            }
            catch (Exception ex)
            {
                throw new Exception("数据库连接失败，原因：" + ex.Message);
            }
        }


        #region 迁移视图

        /// <summary>
        /// 开始迁移视图
        /// </summary>
        public void StartMigrationView()
        {
            CheckConnectionState(); //检查数据库连接
            try
            {
                DataTable dtViewStructure = _dataBaseHelper.FindDataTable("select * from user_views", _oracleSourceConn);
                if (dtViewStructure == null || dtViewStructure.Rows.Count == 0)
                    return;
                Dictionary<string, List<string>> viewRelation = GetReferenced(dtViewStructure);
                List<string> viewSort = SortView(viewRelation);

                DataTable dtViewTableComments = _dataBaseHelper.FindDataTable("select *  from user_tab_comments where Table_Type='VIEW'", _oracleSourceConn);

                foreach (string viewName in viewSort)
                {
                    try
                    {
                        DataRow[] drs = dtViewStructure.Select(string.Format("VIEW_NAME ='{0}'", viewName.ToUpper()));
                        if (drs == null || drs.Count() < 1)
                            continue;

                        string structureString = drs[0]["TEXT"].ToString();
                        string createViewString = string.Format("CREATE OR REPLACE VIEW {0} AS {1}", viewName, structureString);
                        _dataBaseHelper.ExcuteSql(createViewString, _oracleTargetConn);

                        MigrationViewTableComments(dtViewTableComments, viewName);
                        MigrationViewColumnComments(viewName);
                    }
                    catch (Exception ex)
                    {
                        //若源数据库中的视图编译不成功！则无法完成迁移。
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("迁移视图信息失败，原因-> {0}", ex.Message));
            }

        }

        //获取视图之间的依赖关系
        private Dictionary<string, List<string>> GetReferenced(DataTable dtViewStructure)
        {
            try
            {
                DataTable referencedDT = _dataBaseHelper.FindDataTable("select  *  from USER_DEPENDENCIES  where referenced_type ='VIEW'", _oracleSourceConn);

                Dictionary<string, List<string>> viewReferenceds = new Dictionary<string, List<string>>();
                foreach (DataRow dr in dtViewStructure.Rows)
                {
                    string viewName = dr["VIEW_NAME"].ToString();
                    if (referencedDT == null || referencedDT.Rows.Count == 0)
                    {
                        viewReferenceds.Add(viewName, new List<string>());
                    }
                    else
                    {
                        string filterString = string.Format("Name = '{0}'", viewName);
                        DataRow[] drs = referencedDT.Select(filterString);
                        if (drs == null || drs.Count() == 0)
                        {
                            viewReferenceds.Add(viewName, new List<string>());
                        }
                        else
                        {
                            List<string> referenceds = new List<string>();
                            foreach (DataRow item in drs)
                                referenceds.Add(item["referenced_name"].ToString());
                            viewReferenceds.Add(viewName, referenceds);
                        }
                    }
                }

                return viewReferenceds;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("获取视图依赖关系失败，原因-> {0}", ex.Message));
            }
        }

        //获取视图迁移的先后顺序
        private List<string> SortView(Dictionary<string, List<string>> viewRelations)
        {
            try
            {
                List<string> returnValue = new List<string>();

                foreach (KeyValuePair<string, List<string>> item in viewRelations)  //获取没有依赖的数据
                {
                    if (item.Value.Count == 0)
                        returnValue.Add(item.Key);
                }

                foreach (string item in returnValue)  //依赖关系中移出没有依赖的项
                    viewRelations.Remove(item);

                foreach (KeyValuePair<string, List<string>> dictionary in viewRelations)  //重新梳理依赖关系
                {
                    foreach (string value in returnValue)
                    {
                        if (dictionary.Value.Contains(value))
                            dictionary.Value.Remove(value);
                    }
                }

                if (returnValue.Count == 0 && viewRelations.Count != 0)
                    throw new Exception("有相互依赖的视图，无法迁移视图！");
                else if (viewRelations.Count == 0)
                    return returnValue;
                else
                {
                    returnValue.AddRange(SortView(viewRelations));
                    return returnValue;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("视图迁移排序失败，原因-> {0}", ex.Message));
            }
        }

        //迁移视图的注释
        private void MigrationViewTableComments(DataTable dtViewTableComments, string viewName)
        {
            try
            {
                DataRow[] drs = dtViewTableComments.Select("table_name ='" + viewName + "'");
                if (drs == null || drs.Count() == 0)
                    return;

                string tableComments = string.Format("comment on table {0} is '{1}'", drs[0]["TABLE_NAME"].ToString(), drs[0]["COMMENTS"].ToString());
                _dataBaseHelper.ExcuteSql(tableComments, _oracleTargetConn);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("迁移视图 {0} 注释信息失败，原因-> {1}", viewName, ex.Message));
            }
        }

        //迁移某张视图的列注释
        private void MigrationViewColumnComments(string viewName)
        {
            try
            {
                string sqlStrig =string.Format("select * from user_col_comments  where table_Name ='{0}'", viewName.ToUpper());
                DataTable dtViewColumnComments = _dataBaseHelper.FindDataTable(sqlStrig, _oracleSourceConn);
                if (dtViewColumnComments == null || dtViewColumnComments.Rows.Count == 0)
                    return;

                foreach (DataRow dr in dtViewColumnComments.Rows)
                {
                    string columnComment = string.Format("comment on column {0}.{1} is '{2}'", viewName, dr["COLUMN_NAME"].ToString(), dr["COMMENTS"].ToString());
                    _dataBaseHelper.ExcuteSql(columnComment, _oracleTargetConn);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("迁移视图 {0} 列注释信息失败，原因-> {1}", viewName, ex.Message));
            }
        }

        //检查数据库连接
        private void CheckConnectionState()
        {
            if (_oracleSourceConn == null)
                throw new Exception("源数据库未初始化连接");

            if (_oracleTargetConn == null)
                throw new Exception("目标数据库未初始化连接");

            if (_oracleSourceConn.State != ConnectionState.Open)
                throw new Exception("源数据库连接未打开");

            if (_oracleTargetConn.State != ConnectionState.Open)
                throw new Exception("目标数据库连接未打开");

        }

        #endregion

    }

数据库访问辅助类代码：

 

 public class DataBaseHelper
    {
        //源数据库连接
        private OracleConnection _oracleSourceConn;

        //目标数据库连接
        private OracleConnection _oracleTargetConn;

        //创建OracleConnection，连接Oracle数据库       
        public OracleConnection CreateOracleConn(string oracleConnectionString)
        {
            OracleConnection oracleConn = new OracleConnection();
            oracleConn.ConnectionString = oracleConnectionString;
            oracleConn.Open();
            return oracleConn;
        }

        //创建OracleCommand命令      
        public OracleCommand CreateOracleCommand(string sql, OracleConnection oracleConn)
        {
            OracleCommand oracleCommand;
            oracleCommand = oracleConn.CreateCommand();
            oracleCommand.CommandText = sql;
            return oracleCommand;
        }

        //执行sql命令
        public void ExcuteSql(string sql, OracleConnection oracleConn)
        {
            OracleCommand oracleCommand = oracleConn.CreateCommand();
            oracleCommand.CommandText = sql;
            oracleCommand.ExecuteNonQuery();
        }

        // 从数据库获得DataTable,只能对DataTable进行查询，而不能执行修改更新操作
        public DataTable FindDataTable(string sql, OracleConnection oracleConn)
        {
            OracleCommand oracleCommand;
            oracleCommand = oracleConn.CreateCommand();
            oracleCommand.CommandText = sql;
            OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
            oracleDataAdapter.SelectCommand = oracleCommand;
            DataSet ds = new DataSet();
            oracleDataAdapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            return dt;
        }
       
    }

相关源代码下载：点击打开链接