private static void ApplyDefaultParameters(StoredProcedureInfo procedure, String definition)
{
// HACK: detecting default parameters (mssql not provide it)
String commentPattern = "(--[^\r\n]*[\r\n])";
String definitionPattern = "(PROCEDURE|PROC)\\s+(.*)\\sAS\\s";
// Remove comment
definition = Regex.Replace(definition, commentPattern, "", RegexOptions.CultureInvariant);
definition = definition.Trim();
// Capture parameters
Match m = Regex.Match(definition, definitionPattern, RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.Singleline);
if (m.Success)
{
String group2 = m.Groups[2].Value;
int firstAtCharacter = group2.IndexOf('@');
if (firstAtCharacter >= 0)
{
if (firstAtCharacter > 0)
{
group2 = group2.Substring(firstAtCharacter);
}
group2 = Regex.Replace(group2, "\\s+", " ").Trim();
String[] parameterDefinitions = group2.Split(',');
foreach (String parameterDefinition in parameterDefinitions)
{
String parameterDefinitionCleaned = parameterDefinition.Trim();
String parameterName = parameterDefinitionCleaned.Substring(0, parameterDefinitionCleaned.IndexOf(' ')).Trim();
bool hasDefaultValue = parameterDefinitionCleaned.Contains("=");
StoredProcedureParameterInfo parameterInfo = procedure.GetParameterByName(parameterName);
if (parameterInfo == null)
{
parameterInfo = new StoredProcedureParameterInfo();
parameterInfo.Name = parameterName;
}
parameterInfo.HasDefault = hasDefaultValue;
}
}
}
}
public static List <StoredProcedureInfo> GetStoredProcedures(SqlConnection connection)
{
const String QUERY_PROCEDURES = @"SELECT p.[object_id], p.[name], s.[name] AS [schema_name], m.[definition] FROM [sys].[procedures] p INNER JOIN [sys].[schemas] s ON s.[schema_id] = p.[schema_id] LEFT JOIN [sys].[sql_modules] m ON m.[object_id] = p.[object_id] ORDER BY p.[name]";
const String QUERY_PROCEDURE_PARAMETERS = @"SELECT p.[name], p.[is_output], t.[name] FROM [sys].[parameters] p INNER JOIN [sys].[types] t on t.[user_type_id] = p.[user_type_id] WHERE p.[object_id] = @ObjectID ORDER BY p.[parameter_id]";
const String QUERY_PARAMETER_OBJECTID = "@ObjectID";
List <StoredProcedureInfo> procedures = new List <StoredProcedureInfo>();
using (SqlCommand tableCommand = new SqlCommand(QUERY_PROCEDURES, connection))
{
using (SqlDataReader reader = tableCommand.ExecuteReader())
{
while (reader.Read())
{
StoredProcedureInfo storedProcedureInfo = new StoredProcedureInfo();
storedProcedureInfo.ObjectID = reader.GetInt32(0);
storedProcedureInfo.Name = reader.GetString(1);
storedProcedureInfo.Schema = reader.GetString(2);
String definition = reader.GetString(3);
if (!String.IsNullOrWhiteSpace(definition))
{
ApplyDefaultParameters(storedProcedureInfo, definition);
}
procedures.Add(storedProcedureInfo);
}
}
}
foreach (var p in procedures)
{
using (SqlCommand parameterCommand = new SqlCommand(QUERY_PROCEDURE_PARAMETERS, connection))
{
parameterCommand.Parameters.AddWithValue(QUERY_PARAMETER_OBJECTID, p.ObjectID);
using (SqlDataReader reader = parameterCommand.ExecuteReader())
{
while (reader.Read())
{
StoredProcedureParameterInfo parameterInfo = p.GetParameterByName(reader.GetString(0));
if (parameterInfo == null)
{
parameterInfo = new StoredProcedureParameterInfo();
p.Parameters.Add(parameterInfo);
}
parameterInfo.Name = reader.GetString(0).Replace("@", "");
parameterInfo.IsOutput = reader.GetBoolean(1);
parameterInfo.DbType = SqlDbTypeFrom(reader.GetString(2));
parameterInfo.Type = TypeFrom(parameterInfo.DbType);
parameterInfo.MappingMethodName = MappingMethod(parameterInfo.DbType);
parameterInfo.FullTypeName = TypeFullName(parameterInfo.Type, parameterInfo.HasDefault);
parameterInfo.LocalVariableName = ToLocalVariableName(parameterInfo.Name);
parameterInfo.ParameterName = ToParameterName(parameterInfo.Name);
parameterInfo.LocalParameterVariableName = ToLocalParameterName(parameterInfo.Name);
}
}
}
}
return(procedures);
}
