/// <summary>
/// Converts a SQL parameter into a MetaObject so that it can be serialized and displayed through a dynamic editor
/// </summary>
/// <param name="parameter">The parameter to convert</param>
/// <returns>A Meta representation of the SQL parameter</returns>
public static DbMetaObject ToMetaObject(this SQLParameterInfo parameter)
{
if (parameter is null)
{
throw new System.ArgumentNullException(nameof(parameter));
}
System.Type PersistenceType = TypeConverter.ToNetType(parameter.DATA_TYPE);
IMetaType thisType = new MetaTypeHolder(PersistenceType);
DbMetaObject toReturn = new DbMetaObject()
{
Property = new DbMetaProperty()
{
Name = parameter.PARAMETER_NAME,
Type = thisType
},
Type = thisType,
Value = parameter.HAS_DEFAULT ? parameter.DEFAULT : null
};
if (PersistenceType == typeof(System.DateTime))
{
IMetaAttribute rangeAttribute = new MetaAttributeHolder(new RangeAttribute(PersistenceType, SqlDateTime.MinValue.ToString(), SqlDateTime.MaxValue.ToString()), false);
List <IMetaAttribute> existingAttributes = toReturn.Property.Attributes.ToList();
existingAttributes.Add(rangeAttribute);
toReturn.Property.Attributes = existingAttributes;
}
return(toReturn);
}
/// <summary>
/// Ensures that data coming from the client is convertable for SQL.
/// Created because HTML5 posts datetime with a "T" in the middle which
/// SQL doesn't like
/// </summary>
/// <param name="ProdecureName">The procedure name that we will be running to gather parameter information</param>
/// <param name="parameters">The parameter string list to ensure compatability</param>
public IEnumerable <SqlParameter> FormatSqlParameters(string ProdecureName, IEnumerable <string> parameters)
{
List <SQLParameterInfo> procParams = this.GetParameters(ProdecureName);
for (int i = 0; i < parameters.Count(); i++)
{
SQLParameterInfo procParam = procParams.ElementAt(i);
yield return(new SqlParameter()
{
Value = FormatParameter(procParam, parameters.ElementAt(i))?.ToString(),
DbType = TypeConverter.ToDbType(procParam.DATA_TYPE),
ParameterName = procParam.PARAMETER_NAME,
SqlDbType = procParam.DATA_TYPE
});
}
}
/// <summary>
/// Retrieves Parameter information for a given stored procedure
/// </summary>
/// <param name="Name">The name of the procedure to retrieve parameter information for</param>
/// <returns>A List of SQLParameterInfo representing the parameter information</returns>
public List <SQLParameterInfo> GetParameters(string Name)
{
using (DataTable dt = this.ExecuteToTable("select * from information_schema.parameters where SPECIFIC_NAME = @0", Name))
{
List <SQLParameterInfo> parameters = new List <SQLParameterInfo>();
foreach (DataRow dr in dt.Rows)
{
SQLParameterInfo thisParam = new SQLParameterInfo(dr);
using (DataTable dti = this.ExecuteToTable("exec [Tools\\_GetParamDefault] @0, @1", Name, thisParam.PARAMETER_NAME))
{
thisParam.DEFAULT = dti.GetSingle <string>().Trim('\'');
}
parameters.Add(thisParam);
}
return(parameters.OrderBy(p => p.ORDINAL_POSITION).ToList());
}
}
