/// <summary>
/// Execute a Query and return a Data Reader, make sure you close it
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static async Task <DataSet> QueryAsDataSetAsync(string sql, List <SqlParameter> parmList)
{
DataSet dataset = new DataSet();
using (var conn = await MarsDb.ConnectionAsync())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
foreach (var parm in parmList)
{
cmd.Parameters.Add(parm);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
}
}
return(dataset);
}
/// <summary>
/// Execute a stored procedure and have it return the result set as an objects
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static async Task <IEnumerable <T> > Procedure <T>(string sql, List <SqlParameter> parmList) where T : new()
{
var retList = new List <T>();
using (var conn = await MarsDb.ConnectionAsync())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
foreach (var parm in parmList)
{
cmd.Parameters.Add(parm);
}
using (var reader = await cmd.ExecuteReaderAsync())
{
retList = reader.GetObjects <T>().ToList();
}
}
}
return(retList);
}