/// <summary>
/// Retrieve a single record from a DB table. Any subsequent rows are ignored.
/// Will throw formatted exception upon error.
/// Note: field/property name must be the same as the database column name (case-insensitive).
/// </summary>
/// <typeparam name="T">Typeof class object to return.</typeparam>
/// <param name="connectionString"></param>
/// <param name="query">parameterized query format string (see string.Format()) or stored procedure</param>
/// <param name="args">format replacement args or stored procedure parameters</param>
/// <returns>Typed class containing the retrieved results. null valuetype values get converted into their associated default value (aka Int32 type == 0, strings == "")</returns>
public static T ExecuteQuerySingleRow <T>(string connectionString, string query, params object[] args) where T : class, new()
{
SqlCommand cmd = null;
SqlDataReader reader = null;
try
{
cmd = CreateSqlCmd(connectionString, query, args);
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
var fields = FieldProp.GetProperties(reader, typeof(T), STRICT);
if (reader.Read())
{
T item = new T();
for (int i = 0; i < fields.Length; i++)
{
if (fields[i].Type == null)
{
continue; //matching field not in query
}
fields[i].SetValue(item, ConvertTo(fields[i].Type, reader.GetValue(i)));
}
return(item);
}
}
catch (Exception ex)
{
throw ex.AppendMessage(string.Format("ExecuteQuerySingleRow<{0}>(\"{1}\",\"{2}\",{{{3}}})", typeof(T).Name, HideCSPwd(connectionString), (query ?? string.Empty).Trim(), ParamsToString(args)));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
return(null);
}
/// <summary>
/// Retrieve a table of data from a Sql query statement.
/// Will throw formatted exception upon error.
/// Note: field/property name must be the same as the database column name (case-insensitive).
/// </summary>
/// <typeparam name="T">
/// Typeof array element. If the array element is a primitive type, DateTime, or Guid
/// but not string, only the first column in the query is used. String does not have a
/// default constructor!
/// </typeparam>
/// <param name="connectionString"></param>
/// <param name="query">parameterized query format string (see string.Format()) or stored procedure</param>
/// <param name="args">format replacement args or stored procedure parameters</param>
/// <returns>Generic list of typed class objects containing the retrieved results. null valuetype values get converted into their associated default value (aka Int32 type == 0, strings == "")</returns>
public static List <T> ExecuteQuery <T>(string connectionString, string query, params object[] args)
{
SqlCommand cmd = null;
SqlDataReader reader = null;
List <T> list = new List <T>();
Type t = typeof(T);
//Is the return type a simple list of values?
bool isPrimitive = t.IsPrimitive || t == typeof(string) || t == typeof(Guid) || t == typeof(Enum);
try
{
cmd = CreateSqlCmd(connectionString, query, args);
reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
if (isPrimitive)
{
while (reader.Read())
{
list.Add((T)ConvertTo(t, reader.GetValue(0))); //we ignore all the rest of the columns.
}
return(list);
}
var fields = FieldProp.GetProperties(reader, typeof(T), STRICT);
//Find typeparam parameterless instance constructor. It may be public or private.
var ci = t.GetConstructor(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance, null, Type.EmptyTypes, null);
var constructorArgs = new object[] { }; //for efficiency
while (reader.Read())
{
var item = (T)ci.Invoke(constructorArgs);
for (int i = 0; i < fields.Length; i++)
{
if (fields[i].Type == null)
{
continue; //matching field not in query
}
fields[i].SetValue(item, ConvertTo(fields[i].Type, reader.GetValue(i)));
}
list.Add(item);
}
}
catch (Exception ex)
{
throw ex.AppendMessage(string.Format("ExecuteQuery<{0}>(\"{1}\",\"{2}\",{{{3}}})", typeof(T).Name, HideCSPwd(connectionString), QueryForException(query), ParamsToString(args)));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
return(list);
}