DataContext ExecuteQuery extension method returning anonymous objects based on template
This is type safe version of ExecuteQuery method using anonymous object as a template
public static class DataContextExtensions{public static IEnumerable<T> ExecuteQuery<T>(this DataContext ctx, string query, T template, DbParameter[] parameters = null) where T : class{using (DbCommand cmd = ctx.Connection.CreateCommand())
{cmd.CommandText = query;if (parameters != null) cmd.Parameters.AddRange(parameters);ctx.Connection.Open();using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{while (rdr.Read())
{object[] values = new object[rdr.FieldCount];for (int i = 0; i < rdr.FieldCount; i++){if(!rdr.IsDBNull(i))
{if (rdr.GetFieldType(i) == typeof(Decimal)) //special case if you need it?{values[i] = Convert.ToDouble(rdr.GetDecimal(i));}else
{values[i] = rdr.GetValue(i);}}else
{values[i] = null;
}}var row = (T)Activator.CreateInstance(template.GetType(), values);yield return row;
}}}}}
And the test
[Test]public void TestDataContextAnonymous(){var connString = @"Persist Security Info=False; Integrated Security=SSPI; Server={0}; Database={1}; Connect Timeout={2}; Application Name={3}";
var dataContext = new DataContext(new SqlConnection(connString));var result = dataContext.ExecuteQuery("SELECT TOP 10 Column1, Column2, Column3 FROM Table1 WHERE Column1 = @Column1Value",
new {
Column1 = (int?)null,Column2 = (string)null,Column3 = (double?)null},new DbParameter[]
{DBUtils.CreateSqlParameter("Column1Value", SqlDbType.Int, 13),
});foreach (var row in result){Console.WriteLine("{0},{1},{2}", row.Column1, row.Column2, row.Column3);
}}
Comments
Post a Comment