/// <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);
}
/// <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);
if (!IsDBA)
{
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)
{
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);
}
