Question 88076 in ASP.NET Question posted in
The official documentation can be found here.

Refactoring method for getting Json from a stored procedure in ASP.NET Core

Please, please don’t close or mark this question as duplicate, I have already looked StackOverflow and online but couldn’t find solution.

Below code works great that I receive data from SQL Server via a stored procedure, then assign to a list of book model and return Json:

public IActionResult GetAllBooks()
{
    List<BookViewModel> book = new List<BookViewModel>();

    DataTable dataTable = new DataTable();

    using (SqlConnection sqlConnection = new SqlConnection(_configuration.GetConnectionString("xxx")))
    {
        sqlConnection.Open();

        SqlDataAdapter sqlData = new SqlDataAdapter("proc_GetBookList", sqlConnection);
        sqlData.SelectCommand.CommandType = CommandType.StoredProcedure;

        sqlData.Fill(dataTable);

        foreach (DataRow dr in dataTable.Rows)
        {
            book.Add(new BookViewModel
            {
                Name = dr["Name"].ToString(),
                Stock = Convert.ToInt32(dr["Stock"]),
             });
         }
     }

     return Json(book);
}

But I am trying to find a better way or best practice e.g serialize or any other techniques so that I don’t need to create (View model and Assigning them values) like below. This is small example of only two properties but sometimes I need to map like 20 or more properties, do you guy see any problem in above code? I am new in software development world, any suggestion would be appreciated.

new BookViewModel
{
    Name = dr["Name"].ToString(),
    Stock = Convert.ToInt32(dr["Stock"]),
};
Tags:

3

Answers


  1. 0

    I have used Newtonsoft JSON (NuGet package) for this purpose.

    Example:

    using Newtonsoft.JSON;  
  
public string DataTableToJSONWithJSONNet(DataTable table) {  
   string JSONString = string.Empty;  
   JSONString = JSONConvert.SerializeObject(table);  
   return JSONString;  
}

    You can find this Newtonsoft example and a few other methods here.

    Login or Signup to reply.
  2. 0

    Using a query like you are using is pretty much going to make you use this style of assignment. Switching to Entity Framework to query your DB is going be your best bet, since it will do assignment to objects/classes automatically. But I get that doing so after a project is started can be a PITA or nearly impossible (or a very significantly large amount of work) to do. There’s also a bit of a learning curve, if you’ve never used it before.

    What you can do to make things easier is to create a constructor for your model that takes in a DataRow and assigns the data on a single place.

    public BookViewModel(DataRow dr)
{
    Name = dr["Name"].ToString();
    Stock = Convert.ToInt32(dr["Stock"]);
}

    Then you just call "book.Add(new BookViewModel(dr));" in your foreach loop. This works well if you have to do this in multiple places in your code, so you don’t have to repeat the assignments when you import rows.

    You might also be able to use Reflection to automatically assign the values for you. This also has a bit of a learning curve, but it can make conversions much simpler, when you have it set up.

    Something similar to Reflection is AutoMapper, but that’s not as popular as it used to be.

    I was going to suggest using a JSON package like Newtonsoft or the built in package for C#, but it looks I got beat to that punchline.

    Another option is using Dapper. It’s sort of a half-step between your current system and Entity. It can use SQL or it’s own query language to cast the results directly to a model. This might be the easiest and most straight forward way to refactor your code.

    Dapper and Entity are examples of object relational mappers (ORMs). There are others around you can check out.

    I’ve only listed methods I’ve actually used and there are many other ways to get the same thing done, even without an ORM. They all have their pros and cons, so do your research to figure out what you’re willing to commit to.

    Login or Signup to reply.
  3. 0

    Simply just replace your "return Json(book)" with

    return Ok(book)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search