/// <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); } } }