コード例 #1
0
        protected List<SchemaRoutine> GetSources(string owner, string type)
        {
            using (DbConnectionInfo cn = Db.CreateConnection())
            {
                using (DbCommand cm = cn.CreateCommand())
                {
                    string sql = String.Format(@"SELECT * FROM ALL_SOURCE
                        WHERE (OWNER={0} OR {0}='NULL')
                        AND TYPE={1}
                        ORDER BY OWNER,NAME,LINE",
                         Db.GetParameterMarker("p_owner"),
                         Db.GetParameterMarker("p_type"));

                    cm.CommandText = sql;
                    cm.Parameters.Add(Db.CreateParameter("p_owner", owner.IfNull("NULL").ToUpper()));
                    cm.Parameters.Add(Db.CreateParameter("p_type", type));
                    DataTable dt = Db.ExecQuery(cm);

                    DataRowCollection drc = dt.Rows;
                    StringBuilder sb = new StringBuilder();
                    string owner1 = null;
                    string name1 = null;

                    List<SchemaRoutine> list = new List<SchemaRoutine>();

                    for (int i = 0; i < drc.Count; i++)
                    {
                        if (!drc[i]["OWNER"].Equals(owner1) || !drc[i]["NAME"].Equals(name1))
                        {
                            if (!String.IsNullOrEmpty(name1))
                            {
                                SchemaRoutine item = new SchemaRoutine();
                                item.Owner = owner1;
                                item.Name = name1;
                                item.Definition = sb.ToString();
                                list.Add(item);
                            }
                            owner1 = drc[i]["OWNER"].IfNull(String.Empty);
                            name1 = drc[i]["NAME"].IfNull(String.Empty);
                            sb.Remove(0, sb.Length);
                        }
                        sb.Append(drc[i]["TEXT"].IfNull(String.Empty));
                    }

                    if (drc.Count > 0)
                    {
                        SchemaRoutine item = new SchemaRoutine();
                        item.Owner = owner1;
                        item.Name = name1;
                        item.Definition = sb.ToString();
                        list.Add(item);
                    }

                    return list;
                }
            }
        }
コード例 #2
0
        protected List<SchemaRoutine> GetRoutines(string owner, string type)
        {
            using (DbConnectionInfo cn = Db.CreateConnection())
            {
                using (DbCommand cm = cn.CreateCommand())
                {
                    string sql = String.Format(@"SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
                        WHERE ROUTINE_CATALOG={0} AND ROUTINE_TYPE={1} AND (ROUTINE_SCHEMA={2} OR {2}='NULL')",
                        Db.GetParameterMarker("database"),
                        Db.GetParameterMarker("type"),
                        Db.GetParameterMarker("owner"));

                    cm.CommandText = sql;
                    cm.Parameters.Add(Db.CreateParameter("database", cn.Database));
                    cm.Parameters.Add(Db.CreateParameter("type", type));
                    cm.Parameters.Add(Db.CreateParameter("owner", owner.IfNull("NULL")));
                    DataTable dt = Db.ExecQuery(cm);
                    DataRowCollection drc = dt.Rows;
                    List<SchemaRoutine> list = new List<SchemaRoutine>(drc.Count);
                    for (int i = 0; i < drc.Count; i++)
                    {
                        SchemaRoutine item = new SchemaRoutine();
                        item.Owner = drc[i]["ROUTINE_SCHEMA"].IfNull(String.Empty);
                        item.Name = drc[i]["ROUTINE_NAME"].IfNull(String.Empty);
                        item.Definition = drc[i]["ROUTINE_DEFINITION"].IfNull(String.Empty);
                        list.Add(item);
                    }
                    return list;
                }
            }
        }