-1

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!

BendEg
  • 20,098
  • 17
  • 57
  • 131
  • You can use a BinaryFormatter ( https://msdn.microsoft.com/en-us/library/system.runtime.serialization.formatters.binary.binaryformatter(v=vs.110).aspx ) to serialize a List or DataTable in a binary format. – Sam Axe Feb 16 '15 at 07:30
  • I will take a look at the BinaryFormatter. But is there way, to go without serialization? – BendEg Feb 16 '15 at 08:05
  • Another option would be to just allow the client to execute queries direct against the database. – Sam Axe Feb 16 '15 at 08:24
  • Thats the way we are working currently, but we want to implement a new abstraction level, because we need to deal with mobile clients, windows clients... Another reason is, that our controllers in the http endpoint has special user access rights, so our security concept is build on top of it. – BendEg Feb 16 '15 at 08:26
  • @Dan-o the BinaryFormatter seems to be not very effective: http://www.maxondev.com/serialization-performance-comparison-c-net-formats-frameworks-xmldatacontractserializer-xmlserializer-binaryformatter-json-newtonsoft-servicestack-text/ – BendEg Feb 16 '15 at 08:32

1 Answers1

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.

Shadow The GPT Wizard
  • 66,030
  • 26
  • 140
  • 208