If you’re working with traditional ADO.NET data types – like the DataTable – in your data access code and wanting to wire up a web service to serve up data, you’ve likely run into some issues. You’re either getting errors due to failed serialization or trying to deal with complex deserialization issues on the other end.

A robust solution would be to create entity types to wrap your data into easily digestible (and serializable) objects. You can roll your own or use an Object/Relational Mapper such as Entity Framework or Telerik’s OpenAccess ORM. Another option would be to use a custom converter. But if you’re after a quick solution, you might just consider converting your ADO.NET objects into other CLR objects that the built-in JavaScriptSerializer knows how to convert, and that are easier to work with in the context of web services. For example, consider the following page method:

[WebMethod]
public static DataTable GetCustomers()
{
    var data = new DataTable("Customers");
    var connectionString = 
        ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString;
    var query = "SELECT * FROM CUSTOMERS";
    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand())
    {
        sqlCommand.CommandType = CommandType.Text;
        sqlCommand.CommandText = query;
        sqlCommand.Connection = sqlConnection;
        var dataAdapter = new SqlDataAdapter(sqlCommand);
        dataAdapter.Fill(data);
    }
    return data;
}

This will result in an InvalidOperationException with the error: “A circular reference was detected while serializing an object of type ‘System.Reflection.Module’”.

Instead you can convert this into a list of Dictionary objects which can be serialized to JSON and easily consumed on the client.

[WebMethod]
public static List<object> GetCustomers()
{
    var data = new DataTable("Customers");
    var connectionString = 
        ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString;
    var query = "SELECT * FROM CUSTOMERS";
    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand())
    {
        sqlCommand.CommandType = CommandType.Text;
        sqlCommand.CommandText = query;
        sqlCommand.Connection = sqlConnection;
        var dataAdapter = new SqlDataAdapter(sqlCommand);
        dataAdapter.Fill(data);
    }

    var results = new List<object>();
    foreach (DataRow row in data.Rows)
    {
        var item = new Dictionary<string, string>();
        foreach (DataColumn column in data.Columns)
        {
            item.Add(column.ColumnName, row[column].ToString());
        }
        results.Add(item);
    }

    return results;
}

A simple solution with quick results…