public List <ProcedureParamEntity> GetProcedureParams(string connStr, string databaseName, string schemaName, string procedureName)
{
var sql = "SELECT * FROM information_schema.PARAMETERS where parameter_name is not null and SPECIFIC_SCHEMA=@0 ";
if (string.IsNullOrEmpty(procedureName) == false)
{
sql += "and SPECIFIC_NAME=@1 ";
}
var helper = SqlHelperFactory.OpenDatabase(connStr, _provider.GetProviderFactory(), SqlType.MySql);
var dt = helper.ExecuteDataTable(sql, databaseName, procedureName);
helper.Dispose();
List <ProcedureParamEntity> procedureEntities = new List <ProcedureParamEntity>();
for (int i = 0; i < dt.Rows.Count; i++)
{
var row = dt.Rows[i];
var entity = new ProcedureParamEntity {
DatabaseName = databaseName,
ProcedureName = row["SPECIFIC_NAME"].ToString(),
ParamName = row["PARAMETER_NAME"].ToString(),
Type = row["DATA_TYPE"].ToString(),
Length = row["CHARACTER_MAXIMUM_LENGTH"].ToString(),
Precision = row["NUMERIC_PRECISION"].ToString(),
Scale = row["NUMERIC_SCALE"].ToString(),
IsOutput = row["PARAMETER_MODE"].ToString().Contains("OUT"),
};
procedureEntities.Add(entity);
}
return(procedureEntities);
}
public List <ProcedureParamEntity> GetProcedureParams(string connStr, string databaseName, string schemaName, string procedureName)
{
var sql = @"SELECT t.oid, n.nspname, t.typname FROM pg_type t LEFT JOIN pg_namespace n ON t.typnamespace = n.oid";
var sql2 = @"SELECT p.proargnames,p.proargtypes,p.proargmodes AS parammodes,p.proallargtypes AS paramalltypes
from pg_proc p
LEFT JOIN pg_namespace ns ON ns.oid = p.pronamespace
WHERE p.prokind='p' and ns.nspname=@0 and p.proname=@1";
var helper = SqlHelperFactory.OpenDatabase(connStr, _provider.GetProviderFactory(), SqlType.PostgreSQL);
helper.ChangeDatabase(databaseName);
var types = helper.Select <PostgreSQLType>(sql);
Dictionary <uint, string> tempTypes = new Dictionary <uint, string>();
foreach (var item in types)
{
tempTypes[(uint)item.oid] = item.typname;
}
types = null;
var dt = helper.ExecuteDataTable(sql2, schemaName, procedureName);
//var procedureParams = helper.FirstOrDefault<PostgreSQLProcedureParam>(sql2, schemaName, procedureName);
helper.Dispose();
if (dt.Rows.Count == 0)
{
return(new List <ProcedureParamEntity>());
}
var row = dt.Rows[0];
if (((uint[])row["proargtypes"]).Length == 0)
{
return(new List <ProcedureParamEntity>());
}
var proargtypes = (uint[])row["proargtypes"];
var proargnames = (string[])row["proargnames"];
if (row["paramalltypes"] is not System.DBNull)
{
proargtypes = (uint[])row["paramalltypes"];
}
char[] parammodes = null;
if (row["parammodes"] is not System.DBNull)
{
parammodes = (char[])row["parammodes"];
}
List <ProcedureParamEntity> result = new List <ProcedureParamEntity>();
for (int i = 0; i < proargnames.Length; i++)
{
var name = proargnames[i];
var typeId = proargtypes[i];
var type = tempTypes[typeId];
bool isOutput = false;
if (parammodes != null)
{
var t = parammodes[i];
if (t == 'o')
{
isOutput = true;
}
}
ProcedureParamEntity entity = new ProcedureParamEntity()
{
DatabaseName = databaseName,
SchemaName = schemaName,
ProcedureName = procedureName,
Type = type,
ParamName = name,
IsOutput = isOutput
};
result.Add(entity);
}
return(result);
}