Monday, 11 June 2012

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);                
  }
}

No comments:

Post a Comment