/// <summary>
/// Converts a List of SQL parameters into MetaObjects so that they can be serialized and displayed through a dynamic editor
/// </summary>
/// <param name="parameters">A List of Sql parameters to serialize</param>
/// <param name="c">The optional MetaConstructor to use as a start, for caching</param>
/// <returns>A MetaObject representing a collection of the serialized parameters</returns>
public static DbMetaObject ToMetaObject(this IEnumerable <SQLParameterInfo> parameters, MetaConstructor c = null)
{
if (parameters is null)
{
throw new System.ArgumentNullException(nameof(parameters));
}
DbMetaObject metaObject = new DbMetaObject();
c = c ?? new MetaConstructor(new MetaConstructorSettings()
{
AttributeIncludeSettings = AttributeIncludeSetting.All
});
c.ClaimOwnership(metaObject);
foreach (SQLParameterInfo thisParam in parameters)
{
DbMetaObject prop = thisParam.ToMetaObject();
metaObject.Properties.Add(prop);
}
metaObject.Type = new DbMetaType("SqlStoredProc", metaObject.Properties)
{
CoreType = CoreType.Reference
};
return(metaObject);
}
/// <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);
}
public ActionResult RunStoredProcedure(string Name, string json, int count = 20, int page = 0)
{
List <SQLParameterInfo> parameters = this.ReportingDatabase.GetParameters(Name);
DbMetaObject toRender = parameters.ToMetaObject();
StoredProcedureDisplayModel model = new StoredProcedureDisplayModel
{
Name = Name,
Parameters = toRender,
Optimized = parameters.Any(p => p.PARAMETER_NAME == "count") && parameters.Any(p => p.PARAMETER_NAME == "page")
};
if (parameters.Any())
{
JObject jObject = JObject.Parse(json);
foreach (JProperty jtok in jObject.Properties())
{
model.Parameters[jtok.Name].Value = jtok.Value.ToString();
}
}
model.Results = new PagedListContainer <IMetaObject>()
{
Count = count,
Page = page
};
if (model.Parameters.HasProperty("@page") && model.Parameters.HasProperty("@count"))
{
model.Parameters["@page"].Value = $"{page}";
model.Parameters["@count"].Value = $"{count}";
model.Results.Items.AddRange(this.ReportingDatabase.ExecuteStoredProcedureToTable(Name, model.Parameters.ToSqlParameters()).ToMetaObject().Cast <IMetaObject>());
}
else
{
List <IMetaObject> results = this.ReportingDatabase.ExecuteStoredProcedureToTable(Name, model.Parameters.ToSqlParameters()).ToMetaObject().Cast <IMetaObject>().ToList();
model.Results.TotalCount = results.Count;
model.Results.Items.AddRange(results.Skip(page * count).Take(count));
}
return(this.View(model));
}
/// <summary>
/// Casts a DataTable into list of MetaObjects
/// </summary>
/// <param name="dt">The data table to be used as a source</param>
/// <returns>A list of MetaObjects representing the data</returns>
public static List <DbMetaObject> ToMetaObject(this DataTable dt)
{
if (dt is null)
{
throw new System.ArgumentNullException(nameof(dt));
}
List <DbMetaObject> MetaRows = new List <DbMetaObject>();
foreach (DataRow dr in dt.Rows)
{
DbMetaObject row = new DbMetaObject();
foreach (DataColumn dc in dt.Columns)
{
DbMetaObject item = new DbMetaObject();
MetaTypeHolder objectType = new MetaTypeHolder(dc.DataType);
item.Type = objectType;
item.Property = new DbMetaProperty()
{
Type = objectType,
Name = dc.ColumnName
};
item.Value = dr[dc]?.ToString();
row.Properties.Add(item);
}
row.Type = new DbMetaType("SqlRow", row.Properties)
{
CoreType = CoreType.Reference
};
MetaRows.Add(row);
}
return(MetaRows);
}
public ActionResult StoredProcedure(string Name)
{
List <SQLParameterInfo> parameters = this.ReportingDatabase.GetParameters(Name);
DbMetaObject toRender = parameters.ToMetaObject();
foreach (SQLParameterInfo thisParam in parameters)
{
ParameterInfo DBInstance = this.ReportingParameterRepository.GetByProcedureAndName(Name, thisParam.PARAMETER_NAME);
System.Type netType = TypeConverter.ToNetType(thisParam.DATA_TYPE);
if (DBInstance != null)
{
if (DBInstance.Default != null)
{
toRender[thisParam.PARAMETER_NAME].Value = this.GetParamConstraint(DBInstance.Default, netType);
}
if (DBInstance.MinValue is null)
{
throw new NullReferenceException("DbInstance MinValue can not be null");
}
if (DBInstance.MaxValue is null)
{
throw new NullReferenceException("DbInstance MaxValue can not be null");
}
if (DBInstance.MinValue.Enabled || DBInstance.MaxValue.Enabled)
{
string?Min = null;
string?Max = null;
if (DBInstance.MinValue.Enabled)
{
Min = this.GetParamConstraint(DBInstance.MinValue, netType);
}
if (DBInstance.MaxValue.Enabled)
{
Max = this.GetParamConstraint(DBInstance.MaxValue, netType);
}
RangeAttribute range = new RangeAttribute(netType, Min, Max);
MetaConstructor c = new MetaConstructor();
DbMetaObject instance = new DbMetaObject()
{
Properties = new List <DbMetaObject>()
{
new DbMetaObject()
{
Value = range.Minimum.ToString(),
Property = new DbMetaProperty()
}
}
};
MetaAttributeHolder toAdd = new MetaAttributeHolder(range, false);
instance.Type = toAdd.Type;
List <IMetaAttribute> existingAttributes = toRender[thisParam.PARAMETER_NAME].Property.Attributes.ToList();
existingAttributes.Add(toAdd);
toRender[thisParam.PARAMETER_NAME].Property.Attributes = existingAttributes;
}
}
}
StoredProcedureDisplayModel model = new StoredProcedureDisplayModel
{
Name = Name,
Parameters = toRender,
Optimized = parameters.Any(p => p.PARAMETER_NAME == "count") && parameters.Any(p => p.PARAMETER_NAME == "page")
};
return(this.View(model));
}
