public List <T> ExecuteReaderToList <T>(QueryStatement <T> queryCommand)
{
var list = new List <T>();
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var cmd = new SqlCommand(queryCommand.SqlQuery, conn))
{
var reader = cmd.ExecuteReader();
var columnNames = (from row in reader.GetSchemaTable().AsEnumerable()
select row.Field <string>("ColumnName")).ToList();
var itemType = typeof(T);
while (reader.Read())
{
var item = Activator.CreateInstance <T>();
(from columnName in columnNames select columnName).
ToList().ForEach(columnName =>
{
var property = itemType.GetProperty(columnName);
if (property != null)
{
property.SetValue(item, reader[columnName]);
}
});
list.Add(item);
}
}
}
return(list);
}
// TODO move all query statement building into the QueryStatement class
private QueryStatement <T> GetQueryStatement()
{
var genericType = this.GetType().GetGenericArguments()[0];
// TODO cache the entity property type information???
// TODO need to handle collections and reference properties
// use the properties of the generic type as the select list
var properties = genericType.GetProperties();
var propertyNames = (from property in properties
where property.CanWrite
select property.Name).ToList();
var selectList = string.Join(", ", propertyNames);
// use the generic type name as the "from"
var fromClause = genericType.Name;
// convert the where predicates to a where clause
var whereClause = string.Empty;
if (_whereExpressions.Count > 0)
{
whereClause = BuildWhereClause(_whereExpressions);
}
// build the query
var select = new StringBuilder();
select.AppendFormat("select {0}{1}", selectList, Environment.NewLine);
select.AppendFormat("from {0}", fromClause);
if (!string.IsNullOrWhiteSpace(whereClause))
{
select.Append(Environment.NewLine);
select.AppendFormat("where {0}", whereClause);
}
// setup the query command
var queryStatement = new QueryStatement();
queryStatement.SqlQuery = select.ToString();
// TODO setup the query command parameters
this.QueryStatement = queryStatement;
return(queryStatement);
}
public T ExecuteReaderToSingle <T>(QueryStatement <T> queryCommand)
{
var item = default(T);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var cmd = new SqlCommand(queryCommand.SqlQuery, conn))
{
var reader = cmd.ExecuteReader();
var columnNames = (from row in reader.GetSchemaTable().AsEnumerable()
select row.Field <string>("ColumnName")).ToList();
var itemType = typeof(T);
var count = 0;
while (reader.Read())
{
if (count > 0)
{
throw new ApplicationException("More than one item was returned for the query: " +
queryCommand.SqlQuery);
}
item = Activator.CreateInstance <T>();
(from columnName in columnNames select columnName).
ToList().ForEach(columnName =>
{
var property = itemType.GetProperty(columnName);
if (property != null)
{
property.SetValue(item, reader[columnName]);
}
});
count++;
}
}
}
return(item);
}
