Best way sending ExecuteReader result over http

Question

What is the best way, sending the result of ADO.NETs ExecuteReader over http to an client (the client pulls the data of course).

I don't want to load the result into an List<Dictionary<string, object>> serialize it and so on, becuase this seems to produce a lot of over head.

Is there a method, to send the data as a binary format over http?

Thank you!

Answer 1

Best way in my opinion is building a custom class with the table fields as class members, then send plain array with each row as a new instance:

public class Car
{
    public string Model { get; set; }
    public int Price { get; set; }
}

public void Foo()
{
    string strSQL = "Select * From Cars";

    List<Car> cars = new List<Car>();
    //...initialize connection, Command, etc...
    while (reader.Read())
    {
        cars.Add(new Car {
            Model = reader["Model"] + "", 
            Price = (int)reader["Price"]
        });
    }
    //...send cars.ToArray() over to client...
}

This way you send only the data you need, with zero overhead.

If you want to make it flexible and support any table structure, you'll need some more classes. Most basic thing I can think of, are such classes:

public class BasicField
{
    public string Name { get; set; }
    public object Value { get; set; }
}

public class BasicRow
{
    public BasicField[] Fields { get; set; }
}

public class BasicTable
{
    public BasicRow[] Rows { get; set; }

    public static BasicTable Parse(DataTable table)
    {
        string[] fieldNames = table.Columns.OfType<DataColumn>().ToList().ConvertAll(c => c.Caption).ToArray();
        List<BasicRow> basicRows = table.Rows.OfType<DataRow>().ToList().ConvertAll(dataRow =>
        {
            List<BasicField> fields = new List<BasicField>();
            for (int i = 0; i < dataRow.ItemArray.Length; i++)
                fields.Add(new BasicField
                {
                    Name = fieldNames[i], 
                    Value = dataRow.ItemArray[i]
                });
            return new BasicRow
            {
                Fields = fields.ToArray()
            };
        });
        return new BasicTable
        {
            Rows = basicRows.ToArray()
        };
    }
}

Then to use it, such code is required:

BasicTable basicTable;
string strSQL = "Select * From Cars";
using (DataTable table = new DataTable())
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, connection))
    {
        adapter.Fill(table);
    }
    basicTable = BasicTable.Parse(table);
}

And you can send basicTable over to the client, being made from minimal primitive members, it should have minimum overhead.