How to convert SQL Server Json into a MVC Model

     Like most of the .NET Developers, you might be using the Entity Framework to create MVC models when you load data from database. Entity Framework is great and make developers life easy for years. But for good reasons, DBAs are not fan of Entity Framework or alike products which let developers query the databases without writing any queries.
   
      SQL Server has been supporting to store/edit JSON objects for some time now. You can create MVC models from JSON objects, probably you are doing that now, if you do front-end development. You can create the required model by using JavaScript in JSON format, and send the JSON object to the server. If your JSON object matches the required MVC model, magic happens and your JSON object becomes MVC Model.

    We should be able to do that when we read data from SQL Server too. All we need is, returned data from SQL Server must be JSON. First I am going to create my MVC model. Here is a sample two models from the project I use for my blog posts. As you can see, I have a model named Sale and it contains a list of Receipts in it.

    Now, I need to query the database and create JSon object to convert into this model. Here is my database tables for Sale and Receipt MVC Models.

    I am going to join these two tables and convert the result into JSON object. The Table Aliases I used in my query matches my MVC model names.

SELECT Sale.SaleId, Sale.CustomerName,
 Sale.PaymentTypeId, Sale.ReferenceNumber,  Sale.SoldDt, Sale.SoldBy,
 SaleReceipt.ReceiptId, SaleReceipt.NumberOfProducts, SaleReceipt.TotalPrice, 

SaleReceipt.RegisterId, SaleReceipt.HasSignature
FROM sales as Sale
inner join Receipts as SaleReceipt ON sale.ReceiptId = SaleReceipt.ReceiptId
WHERE sale.SaleId = 1
for json auto, without_array_wrapper

Query returns the following JSON

{
"SaleId":1,
"CustomerName":"Customer Name",
"PaymentTypeId":1,
"ReferenceNumber":"Ref-1",
"SoldDt":"2017-12-26T22:40:04.507",
"SoldBy":"Cashier",
"SaleReceipt":[
 {"ReceiptId":1,
 "NumberOfProducts":1,
 "TotalPrice":3.0000,
 "RegisterId":-5,
 "HasSignature":true}
 ]
}

      I think we are ready to convert this JSON object into MVC Model. To do that, I am going to use Newtonsoft.Json Nuget package.

     Also, Since I am not using Entity Framework to pull data from database. I can use an old friend "Stored Procedure" to query the database. You don't need to do this way, But in my feature posts, I am going to talk about how to cache these MVC models in SQL Server, and stored procedures are going to help us to do that. 

public class SaleRepository
    {
        public Sale LoadSaleSummary(int saleid)
        {
            using (var sqlconn = new SqlConnection(ConfigurationManager.

  ConnectionStrings["Local"].ConnectionString))
            {
                var sqlcomm = new SqlCommand("dbo.LoadSaleSummary", sqlconn)
                {
                    CommandType = CommandType.StoredProcedure
                };
                sqlcomm.Parameters.Add(new SqlParameter("SaleId",saleid));
                var dset = new DataSet();
                var adap = new SqlDataAdapter(sqlcomm);
                adap.Fill(dset);
                var converted = JsonConvert.DeserializeObject(

   dset.Tables[0].Rows[0][0].ToString());
                return converted;
            }
        }
    }

When I stop the application in the last line of the code, here what I see.

There we go, I created my MVC model directly from a stored procedure without using Entity Framework. From this point, I can still use Linq to query this object. In my next posts, I am going to show you how to cache this MVC model in SQL Server so next time you call the same Sale, SQL Server will return it much faster from cache.