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;
}
}
}
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;
}
}
}