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);
}
/// <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();
}
/// <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;
}
/// <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;
}
/// <summary>
/// 获取存储过程的Sql脚本
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public virtual string GetCommandSqlText(SOCommand command)
{
return "该方法目前还没有实现";
}
/// <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;
}
/// <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;
}
/// <summary>
/// 获取存储过程的Sql脚本
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override string GetCommandSqlText(SOCommand command)
{
throw new NotImplementedException();
}
public override List<SOCommandParameter> GetCommandParameterList(SOCommand command)
{
return new List<SOCommandParameter>();
}
public DbSPViewer(SOCommand sp)
{
InitializeComponent();
currentSP = sp;
}
/// <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;
}
/// <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;
}
// /// <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;
}
/// <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;
}