/// <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="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="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;
}