Web API and Stored Procedures I am building a web service that gets data via Stored Procedures from a db and provides the result as JSON. The solution is built as a MVC 4 Web API project. I have to retrieve the data via Stored Procedures for several reasons (security, SQLAnywhere db, etc).

It is a relatively small scale project so I have opted for not defining a Model in the Web Service layer to save work (and possibly performance). There will likely be models in the front end application (which will be a MVC 4 Web Application).

The reasons why I am including an extra layer in the form of a Web Service is to a) add extra security, and b) that there will likely be a iPhone app for part of the application which then can utilize the same web service as the site.

It is working as I want it to, so what I want feedback on is


What do you think of the general approach to build a MVC 4 Web API solution without explicit models?
Do you have any comments on how to improve the details of the implementation?


One potential thought I have had is to skip the dataset and make a direct call to the Stored Procedure and loop over a datareader, which could potentially be faster. The other potential change is to loop over the datatable and build up the JSON manually instead of using the serializer.

What I like about the current solution is that it can be done on very few lines of code and that I do not have to manually name each JSON object since it is inherited from the datatable within the dataset. 

I define a dataset called dsProducts that is loaded with a TableAdapter.

    // GET api/products
    public string Get()
    {

        string result = "";

        dsProductTableAdapters.GetProductsTableAdapter taTemp = new dsProductsTableAdapters.GetProductsTableAdapter();
        dsProducts dsTemp = new dsProducts();

        try {
            taTemp.Fill(dsTemp.GetProducts);
            result = jsonHelper.convertJSONString(dsTemp.GetProducts);
        } catch (Exception ex) {
            result = "{\"error\":\"1\"}";
        }

        return result;

    }


The convertJSONString and its help functions are defined as:

    public static string convertJSONString(DataTable table)
    {
        JavaScriptSerializer serializer = new JavaScriptSerializer();
        return serializer.Serialize(table.ToDictionary());
    }

    static Dictionary&lt;string, object&gt; ToDictionary(this DataTable dt)
    {
        return new Dictionary&lt;string, object&gt; {
            { dt.TableName, dt.convertTableToDictionary() }};
    }

    static object convertTableToDictionary(this DataTable dt)
    {
        var columns = dt.Columns.Cast&lt;DataColumn&gt;().ToArray();
        return dt.Rows.Cast&lt;DataRow&gt;().Select(r =&gt; columns.ToDictionary(c =&gt; c.ColumnName, c =&gt; r[c]));
    }
General approach to build Web API without models, doesn't give the full web API capabilities.

E.g: 


Help: You cannot have Web API help generated automatically.
You cannot return error handling scenarios: status, message


If you don't want to create the DTOs (Models), you can use the dynamic types. JSON formatter works well with the dynamic types.