/// <summary>
/// Get all parameter names of a Stored Procedure
/// </summary>
/// <returns></returns>
public async Task <IReadOnlyCollection <SPParameter> > GetSPParameters(DBObjectName storedProcedure, DbConnection con)
{
var cachedValue = await cache.TryGetValue(storedProcedure);
if (cachedValue != null)
{
return(cachedValue);
}
string command = @"SELECT PARAMETER_NAME, DATA_TYPE, USER_DEFINED_TYPE_SCHEMA,
USER_DEFINED_TYPE_NAME, PARAMETER_MODE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.parameters
WHERE SPECIFIC_NAME = @SPName AND SPECIFIC_SCHEMA=isnull(@Schema, schema_NAME()) AND PARAMETER_NAME<>''";
await con.AssureOpenAsync();
DbCommand cmd = con.CreateCommand();
cmd.CommandText = command;
cmd.AddCommandParameter("@SPName", storedProcedure.Name)
.AddCommandParameter("@Schema", storedProcedure.Schema);
List <SPParameter> resultL = new List <SPParameter>();
using (var reader = await cmd.ExecuteReaderAsync())
{
if (reader.HasRows)
{
while (reader.Read())
{
var name = reader.GetString(0);
name = name.StartsWith("@") ? name.Substring(1) : name;
string type = reader.GetString(1);
(string userDefinedSchema, string userDefinedName) = (reader.GetNString(2) !, reader.GetNString(3) !);
DBObjectName?userDefinedType = type == "table type" ?
new DBObjectName(userDefinedSchema, userDefinedName) : null;
string parameterMode = reader.GetString(4);
System.Data.ParameterDirection?parameterDirection = parameterMode.Equals("IN", System.StringComparison.CurrentCultureIgnoreCase)
? System.Data.ParameterDirection.Input:
parameterMode.Equals("OUT", System.StringComparison.CurrentCultureIgnoreCase) ? System.Data.ParameterDirection.Output:
parameterMode.Equals("INOUT", System.StringComparison.CurrentCultureIgnoreCase) ? System.Data.ParameterDirection.InputOutput
: (System.Data.ParameterDirection?)null;
if (parameterDirection == null)
{
logger.LogWarning($"Cannot parse Parameter mode {parameterMode} of {name} of {storedProcedure}");
}
int?maxLength = reader.GetNInt32(5);
resultL.Add(new SPParameter(name, type, userDefinedType, parameterDirection ?? System.Data.ParameterDirection.Input, maxLength));
}
}
}
var result = resultL.ToArray();
await cache.TryAdd(storedProcedure.ToString(), result);
return(result);
}
public static string FilterCondition <TEntity>(DbCommand command, IEnumerable <SearchyCondition> conditions = null)
{
var entityType = typeof(TEntity);
var where = string.Empty;
if (conditions == null || conditions.Count() == 0)
{
return("");
}
int index = 0;
foreach (var condition in conditions.Where(x => x.Filters != null && x.Filters.Count != 0))
{
if (where == string.Empty)
{
where = " WHERE (";
}
else
{
where = $"{where} or ( ";
}
foreach (var filter in condition.Filters)
{
index += 1;
var filterColName = GetColumnInfo(entityType, filter.Field).ColumnNameEscaped(command.Connection.SqlProviderName());
var filterColumnParameter = GetColumnInfo(entityType, filter.Field).ColumnName;
var parameter = command.AddCommandParameter(filterColumnParameter + index.ToString());
switch (filter.Rule)
{
case SearchyRule.EqualsTo:
where = $"{where} ({filterColName}={parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.LessThan:
where = $"{where} ({filterColName}<{parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.LessThanOrEquals:
where = $"{where} ({filterColName}<={parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.GreaterThan:
where = $"{where} ({filterColName}>{parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.GreaterThanOrEquals:
where = $"{where} ({filterColName}>={parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.NotEqualsTo:
where = $"{where} ({filterColName}<>{parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.StartsWith:
where = $"{where} ({filterColName} like {parameter.ParameterName}) AND ";
parameter.Value = string.Concat(filter.Value, "%");
break;
case SearchyRule.Contains:
where = $"{where} ({filterColName} like {parameter.ParameterName}) AND ";
parameter.Value = string.Concat("%", filter.Value, "%");
break;
//case SearchyRule.EqualsToList:
// {
// var _ListType = _e.Value.GetType();
// var _ItemType = _ListType.GetGenericArguments();
// var _GenericListType = typeof(List<>);
// var _GenericList = _GenericListType.MakeGenericType(_ItemType);
// if (_GenericList != _ListType)
// throw new Exception(string.Format("The value for the filter {0} is not a generic list", _filtercolname));
// var _Values = new StringBuilder();
// if (_ItemType.Contains(Type.GetType("System.String")) || _ItemType.Contains(Type.GetType("System.Guid")))
// {
// foreach (var _Value in _e.Value)
// _Values.Append(string.Concat("'", _Value.ToString().Replace("'", "''"), "'", ","));
// }
// else
// foreach (var _Value in _e.Value)
// _Values.Append(string.Concat(_Value, ","));
// _whereclause = string.Format(_whereclause + " ({0} IN ({1})) AND ", _filtercolname, _Values.ToString().TrimEnd(new char[] { ',' }));
// break;
// }
}
}
where = where.Remove(where.Length - 5);
where = $"{where})";
}
return(where);
}