Esempio n. 1
0
        public SOCommand GetSP(string spName)
        {
            List <SOCommand> list = this.CommandList;
            SOCommand        sp   = null;

            foreach (SOCommand item in list)
            {
                if (item.Name == spName)
                {
                    sp = item;
                    break;
                }
            }

            return(sp);
        }
Esempio n. 2
0
        /// <summary>
        /// 获取存储过程的Sql脚本
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override string GetCommandSqlText(SOCommand command)
        {
            string cmdText = string.Format(@"select TEXT from dba_source where type='PROCEDURE' and owner='{0}' and name='{1}' order by line",command.Database.Name, command.Name);

            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
            StringBuilder sb = new StringBuilder();
            foreach (DataRow row in dt.Rows)
            {
                sb.AppendLine(row["TEXT"].ToString());
            }

            return sb.ToString();
        }
Esempio n. 3
0
        /// <summary>
        /// 获取存储过程参数列表
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override List<SOCommandParameter> GetCommandParameterList(SOCommand command)
        {
            string cmdText = string.Format(@"select * from all_arguments where owner='{0}' and package_name={1} order by position", command.Database.Name, command.Name);

            List<SOCommandParameter> columnList = new List<SOCommandParameter>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                ParameterDirection direction = ParameterDirection.ReturnValue;
                string pmode = row["IN_OUT"].ToString();
                if (pmode == "IN") direction = ParameterDirection.Input;
                if (pmode == "OUT") direction = ParameterDirection.Output;
                if (pmode == "IN/OUT") direction = ParameterDirection.InputOutput;

                SOCommandParameter param = new SOCommandParameter
                {
                    Parent = command,
                    Name = row["ARGUMENT_NAME"].ToString(),
                    Direction = direction,
                    NativeType = row["DATA_TYPE"].ToString(),
                    Length = ConvertUtil.ToInt32(row["CHAR_LENGTH"], 0),
                    Precision = ConvertUtil.ToInt32(row["DATA_PRECISION"], 0),
                    Scale = ConvertUtil.ToInt32(row["DATA_SCALE"], 0),
                };

                param.DataType = this.GetDbType(param.NativeType);
                columnList.Add(param);
            }

            return columnList;
        }
Esempio n. 4
0
        /// <summary>
        /// 获取存储过程列表
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public override List<SOCommand> GetCommandList(SODatabase db)
        {
            string cmdText = string.Format(@"select PROCEDURE_NAME from dba_procedures where owner='{0}'", db.Name);

            List<SOCommand> commandList = new List<SOCommand>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                SOCommand command = new SOCommand { Parent = db, Name = row["PROCEDURE_NAME"].ToString(), Comment = row["PROCEDURE_NAME"].ToString() };
                commandList.Add(command);
            }

            return commandList;
        }
Esempio n. 5
0
 /// <summary>
 /// 获取存储过程的Sql脚本
 /// </summary>
 /// <param name="command"></param>
 /// <returns></returns>
 public virtual string GetCommandSqlText(SOCommand command)
 {
     return "该方法目前还没有实现";
 }
Esempio n. 6
0
        /// <summary>
        /// 获取存储过程参数列表
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public virtual List<SOCommandParameter> GetCommandParameterList(SOCommand command)
        {
            List<SOCommandParameter> list = new List<SOCommandParameter>();

            string[] restrictions = new string[4];
            restrictions[0] = command.Database.Name;
            restrictions[2] = command.Name;
            DataTable dt = GetSchema(MetaDataCollectionName_Parameters, restrictions);

            //todo:转换数据

            return list;
        }
Esempio n. 7
0
        /// <summary>
        /// 获取存储过程列表
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public virtual List<SOCommand> GetCommandList(SODatabase db)
        {
            List<SOCommand> list = new List<SOCommand>();

            string[] restrictions = new string[4];
            restrictions[0] = db.Name;
            DataTable dt = GetSchema(MetaDataCollectionName_Procedures, restrictions);

            foreach (DataRow dr in dt.Rows)
            {
                SOCommand cmd = new SOCommand();
                cmd.Name = dr["routine_name"].ToString();
                cmd.Comment = cmd.Name;
                cmd.Parent = db;

                list.Add(cmd);
            }

            return list;
        }
Esempio n. 8
0
 /// <summary>
 /// 获取存储过程的Sql脚本
 /// </summary>
 /// <param name="command"></param>
 /// <returns></returns>
 public override string GetCommandSqlText(SOCommand command)
 {
     throw new NotImplementedException();
 }
Esempio n. 9
0
 public override List<SOCommandParameter> GetCommandParameterList(SOCommand command)
 {
     return new List<SOCommandParameter>();
 }
Esempio n. 10
0
 public DbSPViewer(SOCommand sp)
 {
     InitializeComponent();
     currentSP = sp;
 }
Esempio n. 11
0
        /// <summary>
        /// 获取存储过程的Sql脚本
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override string GetCommandSqlText(SOCommand command)
        {
            string cmdText = string.Format(@"USE [{1}];SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES 
                WHERE routine_schema='{0}' AND routine_type='PROCEDURE' AND routine_catalog='{1}' AND routine_name='{2}';",
                                                    command.Owner ?? "dbo", command.Database.Name, command.Name);

            string text = this.DbProvider.ExecuteScalar(System.Data.CommandType.Text, cmdText).ToString();
            return text;
        }
Esempio n. 12
0
        /// <summary>
        /// 获取存储过程参数列表
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override List<SOCommandParameter> GetCommandParameterList(SOCommand command)
        {
            string cmdText = string.Format(@"USE [{1}];SELECT routine_definition FROM INFORMATION_SCHEMA.PARAMETERS 
                WHERE SPECIFIC_schema='{0}' AND SPECIFIC_type='PROCEDURE' AND SPECIFIC_catalog='{1}' AND SPECIFIC_name='{2}';",
                                                    command.Owner ?? "dbo", command.Database.Name, command.Name);

            List<SOCommandParameter> columnList = new List<SOCommandParameter>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                ParameterDirection direction = ParameterDirection.ReturnValue;
                string pmode = row["PARAMETER_MODE"].ToString();
                if (pmode == "IN") direction = ParameterDirection.Input;
                if (pmode == "OUT") direction = ParameterDirection.Output;
                if (pmode == "INOUT") direction = ParameterDirection.InputOutput;

                SOCommandParameter param = new SOCommandParameter
                {
                    Parent = command,
                    Name = row["PARAMETER_NAME"].ToString(),
                    Direction = direction,
                    NativeType = row["DATA_TYPE"].ToString().Replace(" identity", ""),
                    Length = ConvertUtil.ToInt32(row["CHARACTER_OCTET_LENGTH"], -1),
                    Precision = ConvertUtil.ToInt32(row["NUMERIC_PRECISION"], -1),
                    Scale = ConvertUtil.ToInt32(row["NUMERIC_SCALE"], -1),
                };

                param.DataType = this.GetDbType(param.NativeType);
                columnList.Add(param);
            }

            return columnList;
        }
Esempio n. 13
0
//        /// <summary>
//        /// 获取视图所拥有的索引列表
//        /// </summary>
//        /// <param name="view"></param>
//        /// <returns></returns>
//        public override List<SOIndex> GetViewIndexList(SOView view)
//        {
//            string cmdText = string.Format(@"SELECT *  
//                FROM INFORMATION_SCHEMA.`constraints` 
//                WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);

//            List<SOIndex> indexList = new List<SOIndex>();
//            List<SOColumn> columnList = GetViewColumnList(view);
//            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

//            foreach (DataRow row in dt.Rows)
//            {
//                SOIndex index = new SOIndex
//                {
//                    Parent = view,
//                    Name = row["constraint_name"].ToString(),
//                    Comment = row["constraint_name"].ToString(),
//                    IsCluster = false,
//                    IsFullText = row["constraint_type"].ToString() == "Full Text",
//                    IsPrimaryKey = row["constraint_type"].ToString() == "PRIMARY KEY",
//                    IsUnique = row["constraint_type"].ToString() == "UNIQUE"
//                };
//                indexList.Add(index);

//                string cmdText2 = string.Format(@"SELECT column_name  
//                FROM INFORMATION_SCHEMA.`statistics` 
//                WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);

//                DataTable dt2 = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText2).Tables[0];
//                index.Columns = new List<SOColumn>();
//                foreach (DataRow row2 in dt2.Rows)
//                {
//                    foreach (SOColumn column in columnList)
//                    {
//                        if (row2[0].ToString() == column.Name) index.Columns.Add(column);
//                    }
//                }
//            }

//            return indexList;
//        }

        /// <summary>
        /// 获取存储过程列表
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public override List<SOCommand> GetCommandList(SODatabase db)
        {
            string cmdText = string.Format(@"use [{0}];SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_catalog='{0}' AND routine_type='PROCEDURE';", db.Name);

            List<SOCommand> commandList = new List<SOCommand>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                SOCommand command = new SOCommand { Parent = db, Name = row["routine_name"].ToString(), Comment = row["routine_name"].ToString() };
                commandList.Add(command);
            }

            return commandList;
        }
Esempio n. 14
0
        /// <summary>
        /// 获取存储过程参数列表
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override List<SOCommandParameter> GetCommandParameterList(SOCommand command)
        {
            //在information_schema数据库中没有找到存储存储过程参数的表,可以考虑从存储过程DDL脚本解析出来
            string cmdText = string.Format(@"use {0};select  a.name,b.name as typename,a.length,a.status from syscolumns a 
                LEFT JOIN systypes b on a.xtype=b.xtype
                where ID in (SELECT id FROM sysobjects as a  WHERE xtype='P' and id = object_id(N'{1}')) ",command.Parent.Database.Name,command.Name);

            List<SOCommandParameter> commandParList = new List<SOCommandParameter>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach(DataRow row in dt.Rows)
            {
                SOCommandParameter comParameter = new SOCommandParameter { 
                Parent=command,
                Name=row["name"].ToString(),
                Length=int.Parse(row["length"].ToString()),
                NativeType = row["typename"].ToString(),
                };
                int status=int.Parse(row["status"].ToString());
                if(status==8)
                {
                    comParameter.Direction = ParameterDirection.Input;
                }else if(status==72)
                {
                    comParameter.Direction = ParameterDirection.Output;
                }
                commandParList.Add(comParameter);
            }
            return commandParList;
        }
 public static SOCommand ToSOCommand(PDProcedure sp)
 {
     SOCommand cmd = new SOCommand();
     return cmd;
 }