How can I pass DataTable to view as JSON/XML code?

Question

I am new to the .NET environment and trying to write a simple MVC application to read student data and display it to the end user. I have connected to a database using SQLOLEDB, the code of which I have pasted below. The data obtained from the query was stored in a variable of the type DataTable. Now I want to see the content of the query result in the form of a JSON output for which I have a faintest idea that I have to create a new controller. But I am not able to proceed beyond this point.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace Database.model
{
public class student
{
    public int id { get; set; }
    public string name { get; set; }
    private string age { get; set; }

    public DataTable GETSQLServerData()
    {
        //Connect
        var _connectionobject = new SqlConnection();
        _connectionobject.ConnectionString = @"Provider=SQLOLEDB;Data Source=PHYSICS\SQLEXPRESS;Persist Security Info=true;Initial Catalog=master;Integrated Security=True; provider = SQLOLEDB;";
        _connectionobject.Open();
        //Command
        var _commandObject = new SqlCommand();
        _commandObject.CommandText = "select * from dbo.tblStudent";
        //Execute 
        _commandObject.ExecuteReader();
        //Data
        var _dataReader = _commandObject.ExecuteReader();

        DataTable obj2 = new DataTable();
        obj2.Load(_dataReader);
        _connectionobject.Close();
        return obj2;


    }

}
}                          

I would be really grateful if anyone could help me in this regard

Answer 1

You can convert the datatable object into a POCO object

How do I convert a datatable into a POCO object in Asp.Net MVC?

then return that POCO object back to the browser.

The best practice would be to create a class that will hold the student data and return that class object instead of the data table like so.

// you student model class
public class Student
{
    // public properties of student...
}

In your data access class populate this student object list and return to the MVC action method.

//then in your MVC action method 

IEnumerable<Student> students = GETSQLServerData();
return this.Json(students , JsonRequestBehavior.AllowGet);

A few points about your code:

1- Avoid using sql statement in your C# code, switch to stored procedure.

2- Use Data Model layer and create Student class to define student model.

3- Use Data acccess layer to call SQL Stored proc

4- Inject dependencies to avoid tightly coupled classes.

Hope this helps!