/// <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);
}
private static FieldProp[] InternalGetPropertiesForgiving(IDataReader reader, Type t)
{
//Create a clean and smaller memberinfo search list.
var members = new Dictionary <string, MemberInfo>(StringComparer.InvariantCultureIgnoreCase);
foreach (var m in t.GetMembers(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic))
{
if (m.MemberType == MemberTypes.Field || m.MemberType == MemberTypes.Property)
{
string name = m.Attribute <ColumnAttribute>();
if (!name.IsNullOrEmpty())
{
members[name] = m;
}
members[m.Name] = m;
}
}
FieldProp[] fields = new FieldProp[reader.FieldCount];
for (int i = 0; i < fields.Length; i++)
{
string name = reader.GetName(i);
MemberInfo mi = null;
members.TryGetValue(name, out mi);
if (mi == null)
{
continue;
}
fields[i].Name = name;
if (mi.MemberType == MemberTypes.Field)
{
FieldInfo fi = (FieldInfo)mi;
fields[i].Type = fi.FieldType;
fields[i].GetValue = fi.GetValue;
fields[i].SetValue = fi.SetValue;
}
else
{
PropertyInfo pi = (PropertyInfo)mi;
fields[i].Type = pi.PropertyType;
if (pi.CanRead)
{
fields[i].GetValue = pi.GetValue;
}
else
{
fields[i].GetValue = delegate(object obj) { return(ConvertTo(pi.PropertyType, null)); };
}
if (pi.CanWrite)
{
fields[i].SetValue = pi.SetValue;
}
else
{
fields[i].SetValue = delegate(object obj, object value) { };
}
}
}
return(fields);
}
/// <summary>
/// Strict like EF. Data model properties must match the Sql query column properties exactly.
/// Data model properties may have the [NotMapped] or [Column("queryColumnName")] attributes
/// to match the EF logic.
/// </summary>
/// <param name="reader">Datareader to read properties from</param>
/// <param name="t">NET Type to read into</param>
/// <returns>Array of class member properties that match IDataReader fields</returns>
private static FieldProp[] InternalGetPropertiesStrict(IDataReader reader, Type t)
{
List <string> orphanedQueryColumns = null;
List <string> typeMismatch = null;
//Create a clean and smaller memberinfo search list.
var members = new Dictionary <string, PropertyInfo>(StringComparer.InvariantCulture);
foreach (var m in t.GetProperties().Where(x => x.CanRead && x.CanWrite))
{
var name = m.Attribute <NotMappedAttribute>();
if (name != null)
{
continue;
}
name = m.Attribute <ColumnAttribute>();
members[!name.IsNullOrEmpty() ? name : m.Name] = m;
}
FieldProp[] fields = new FieldProp[reader.FieldCount];
for (int i = 0; i < fields.Length; i++)
{
string name = reader.GetName(i);
PropertyInfo pi = null;
members.TryGetValue(name, out pi);
//Matching property does not exist for query column
if (pi == null)
{
if (orphanedQueryColumns == null)
{
orphanedQueryColumns = new List <string>();
}
orphanedQueryColumns.Add(name);
continue;
}
//Property type does not match query column type
var tt = pi.PropertyType;
if (tt.IsGenericType && tt.GetGenericTypeDefinition() == typeof(System.Nullable <>) && tt.GenericTypeArguments.Length > 0)
{
tt = tt.GenericTypeArguments[0];
}
if (reader.GetFieldType(i) != tt)
{
if (typeMismatch == null)
{
typeMismatch = new List <string>();
}
typeMismatch.Add(name);
members.Remove(name);
continue;
}
//Property and query column match!
fields[i].Name = name;
fields[i].Type = pi.PropertyType; //leave as nullable type so ConvertTo() knows how to handle it properly.
fields[i].GetValue = pi.GetValue;
fields[i].SetValue = pi.SetValue;
members.Remove(name);
}
//Get bad columns from strict matching. Format nice message and throw
StringBuilder sb = null;
if (orphanedQueryColumns != null)
{
sb = new StringBuilder();
sb.Append("Query columns: ");
sb.Append(string.Join(", ", orphanedQueryColumns));
sb.Append(", do not have a matching case-sensitive, read/writeable '");
sb.Append(t.Name);
sb.Append("' properties.");
}
if (typeMismatch != null)
{
if (sb == null)
{
sb = new StringBuilder();
}
else
{
sb.AppendLine();
}
sb.Append("Data model '");
sb.Append(t.Name);
sb.Append("' properties: ");
sb.Append(string.Join(", ", typeMismatch));
sb.Append(", do not have the same value type as the matching Sql query columns.");
}
if (members.Count > 0)
{
if (sb == null)
{
sb = new StringBuilder();
}
else
{
sb.AppendLine();
}
sb.Append("Data model '");
sb.Append(t.Name);
sb.Append("' case-sensitive properties: ");
sb.Append(string.Join(", ", members.Keys.ToArray()));
sb.Append(", do not exist in the Sql query column headings.");
}
if (sb != null)
{
throw new MissingMemberException(sb.ToString());
}
return(fields);
}
/// <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);
}
