public List <RawStoredProcedure> ReadStoredProcs(bool includeSynonyms)
{
if (DatabaseReaderPlugin != null)
{
return(DatabaseReaderPlugin.ReadStoredProcs());
}
var result = new List <RawStoredProcedure>();
using (var conn = _factory.CreateConnection())
{
if (conn == null)
{
return(result);
}
conn.ConnectionString = Settings.ConnectionString;
conn.Open();
var storedProcedureSQL = StoredProcedureSQL();
if (string.IsNullOrEmpty(storedProcedureSQL))
{
return(result);
}
var cmd = GetCmd(conn);
if (cmd == null)
{
return(result);
}
if (includeSynonyms)
{
cmd.CommandText = SynonymStoredProcedureSQLSetup() + storedProcedureSQL + SynonymStoredProcedureSQL() + SpecialQueryFlags();
}
else
{
cmd.CommandText = storedProcedureSQL + SpecialQueryFlags();
}
using (var rdr = cmd.ExecuteReader())
{
var lastName = string.Empty;
var emptyParamNumber = 1;
while (rdr.Read())
{
var rawDataType = rdr["DATA_TYPE"];
var schema = rdr["SPECIFIC_SCHEMA"].ToString().Trim();
var name = rdr["SPECIFIC_NAME"].ToString().Trim();
var routineType = rdr["ROUTINE_TYPE"].ToString().Trim().ToLower();
var returnDataType = rdr["RETURN_DATA_TYPE"].ToString().Trim().ToLower();
var dataType = rawDataType.ToString().Trim().ToLower();
var parameterMode = rdr["PARAMETER_MODE"].ToString().Trim().ToLower();
if (name != lastName)
{
lastName = name;
emptyParamNumber = 1;
}
var isTableValuedFunction = (routineType == "function" && returnDataType == "table");
var isScalarValuedFunction = (routineType == "function" && returnDataType != "table");
var isStoredProcedure = (routineType == "procedure");
StoredProcedureParameter parameter = null;
if (rawDataType != DBNull.Value)
{
parameter = new StoredProcedureParameter
{
Ordinal = ChangeType <int>(rdr["ORDINAL_POSITION"]),
Name = rdr["PARAMETER_NAME"].ToString().Trim(),
SqlDbType = GetStoredProcedureParameterDbType(dataType),
ReturnSqlDbType = GetStoredProcedureParameterDbType(returnDataType),
PropertyType = GetPropertyType(dataType),
ReturnPropertyType = GetPropertyType(returnDataType),
DateTimePrecision = ChangeType <short>(rdr["DATETIME_PRECISION"]),
MaxLength = ChangeType <int>(rdr["CHARACTER_MAXIMUM_LENGTH"]),
Precision = ChangeType <byte>(rdr["NUMERIC_PRECISION"]),
Scale = ChangeType <int>(rdr["NUMERIC_SCALE"]),
UserDefinedTypeName = rdr["USER_DEFINED_TYPE"].ToString().Trim(),
IsSpatial = SpatialTypes.Contains(dataType)
};
if (string.IsNullOrEmpty(parameter.Name))
{
parameter.Name = "p" + emptyParamNumber++;
}
switch (parameterMode)
{
case "in":
parameter.Mode = StoredProcedureParameterMode.In;
break;
case "out":
parameter.Mode = StoredProcedureParameterMode.Out;
break;
default:
parameter.Mode = StoredProcedureParameterMode.InOut;
break;
}
var clean = CleanUp(parameter.Name.Replace("@", string.Empty));
if (!string.IsNullOrEmpty(clean))
{
parameter.NameHumanCase = Inflector.MakeInitialLower((Settings.UsePascalCase ? Inflector.ToTitleCase(clean) : clean).Replace(" ", ""));
if (ReservedKeywords.Contains(parameter.NameHumanCase))
{
parameter.NameHumanCase = "@" + parameter.NameHumanCase;
}
}
}
var rsp = new RawStoredProcedure(schema, name, isTableValuedFunction, isScalarValuedFunction, isStoredProcedure, parameter);
result.Add(rsp);
}
}
}
return(result);
}