/// <summary>
/// Executes a query to a data table with optional parameters.
/// </summary>
/// <param name="Query">The query text to execute</param>
/// <param name="args">The ordered values of any "@i" formatted parameters to replace in the query</param>
/// <returns>A data table representing the results of the query</returns>
public DataTable ExecuteToTable(string Query, params object[] args)
{
using (TransientCommand command = this.CommandBuilder.Build(Query, args))
{
DataTable dt = new DataTable();
using (SqlDataAdapter da = command.GetDataAdapter())
{
da.Fill(dt);
}
return(dt);
}
}
/// <summary>
/// Executes a query to a List of complex objects, binding each row to an instance by property name
/// </summary>
/// <param name="query">The name of the procedure to execute</param>
/// <returns>An IEnumerable of object representing the first value of each row </returns>
public IEnumerable <T> ExecuteToComplexList <T>(string query) where T : class
{
Dictionary <string, PropertyInfo> cachedProps = typeof(T).GetProperties().ToDictionary(k => k.Name, v => v, StringComparer.OrdinalIgnoreCase);
ConstructorInfo chosenConstructor = null;
List <ConstructorInfo> constructors = typeof(T).GetConstructors().ToList();
foreach (ConstructorInfo c in constructors.OrderByDescending(c => c.GetParameters().Length))
{
bool picked = true;
foreach (ParameterInfo pi in c.GetParameters())
{
if (!cachedProps.TryGetValue(pi.Name, out PropertyInfo _))
{
picked = false;
}
}
if (picked)
{
chosenConstructor = c;
break;
}
}
using (TransientCommand Command = this.CommandBuilder.Build(query))
{
foreach (IDictionary <string, object> row in Command.GetReader().GetRows())
{
Dictionary <string, object> newObjDict = new Dictionary <string, object>(StringComparer.OrdinalIgnoreCase);
foreach (string cName in row.Keys)
{
if (cachedProps.TryGetValue(cName, out PropertyInfo pi))
{
newObjDict.Add(cName, row[cName]);
}
}
List <object> parameters = new List <object>();
foreach (ParameterInfo pi in chosenConstructor.GetParameters())
{
parameters.Add(newObjDict[pi.Name]);
newObjDict.Remove(pi.Name);
}
T toReturn = Activator.CreateInstance(typeof(T), parameters.ToArray()) as T;
foreach (PropertyInfo pi in cachedProps.Select(v => v.Value))
{
if (newObjDict.TryGetValue(pi.Name, out object val) && !(val is DBNull))
{
if (val is string sv && pi.PropertyType != typeof(string))
{
val = sv.Convert(pi.PropertyType);
}
pi.SetValue(toReturn, val);
}
}
yield return(toReturn);
}
}
}
