how to display dynamically tables data from a database in MVC

Hi, Today in this article, I am going to perform display tables records from a database i.e when entering a query as a table name in the text box from UI from any database then it should be populated records on UI and also dynamically generate rows and columns. I used SQL, ADO .Net and MVC Let us see step by step

1. Create database and tables and then create two store procedure one for columns name and another for data

alter proc [dbo].[USP_SelectTableColumn]

         (

@Query varchar(max)

)

as

begin

SELECT *

  FROM INFORMATION_SCHEMA.COLUMNS

 WHERE table_catalog = 'AngularDB'

   AND table_name = @Query

   end

 create proc [dbo].[USP_SelectQuery]

(

@queryTable varchar(100)

)

as

begin

DECLARE @TABLE NVARCHAR(100)

SET @TABLE =@queryTable

EXEC('SELECT * FROM ' + @TABLE)

   end

2. Now create an MVC application and add a class in the model folder i.e DataFetcher.cs

and write below code  

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

namespace BindSQLData.Models

{

    public class DataFetcher

    {

        public string query { get; set; }

        public DataFetcher(string strQuery)

        {

            this.query = strQuery;

        }

        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        public string BindSqlData()

        {

            DataTable td = new DataTable();

            DataSet ds = new DataSet();

            DataSet dsRows = new DataSet();

            string body = "";

            List<string> lstColumn = new List<string>();

            try

            {

                using (SqlConnection con = new SqlConnection(constr))

                {

                    con.Open();

                    SqlCommand cmdColumn = new SqlCommand("USP_SelectTableColumn", con);

                    cmdColumn.CommandType = CommandType.StoredProcedure;

                    cmdColumn.Parameters.AddWithValue("@Query", this.query);

                    SqlDataAdapter da = new SqlDataAdapter(cmdColumn);

                    da.Fill(ds);

                    if (ds.Tables[0].Rows.Count > 0)

                    {

                        body = "<tr class='btn-primary'>";

                        for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)

                        {

                            body += "<th>" + ds.Tables[0].Rows[i]["COLUMN_NAME"].ToString() + "</th>";

                        }

                        body += "</tr>";

                    }

                    SqlCommand cmdName = new SqlCommand("USP_SelectQuery", con);

                    cmdName.CommandType = CommandType.StoredProcedure;

                    cmdName.Parameters.AddWithValue("@queryTable", this.query);

                    SqlDataAdapter daRow = new SqlDataAdapter(cmdName);

                    daRow.Fill(dsRows);

                    if (dsRows.Tables[0].Rows.Count > 0)

                    {

                        for (int i = 0; i < dsRows.Tables[0].Rows.Count; i++)

                        {

                            body += "<tr>";

                            for (int j = 0; j < ds.Tables[0].Rows.Count - 1; j++)

                            {

                                body += "<td>" + dsRows.Tables[0].Rows[i][j].ToString() + "</td>";

                            }

                            body += "</tr>";

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                ex.ToString();

                throw;

            }

            return body;

        }

    }

}

3. Add a controller and write below code

using BindSQLData.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace BindSQLData.Controllers

{

    public class DemoController : Controller

    {

      

        // GET: Demo

        public ActionResult GenerateData()

        {

            return View();

        }

        public JsonResult BindTable(string query)

        {

            DataFetcher objData = new DataFetcher(query);

            string body = "";

            body = objData.BindSqlData();

            return Json(body, JsonRequestBehavior.AllowGet);

        }

    }

}

4. Right-click on GenerateData() method and add a view and write below code

@{

    ViewBag.Title = "GenerateData";

}

<br />

<div class="container">

    <div class="row">

        <div class="col-md-3">

          <input type="text" id="txtQuery" class="form-control" />

        </div>

        <div class="col-md-3">

            <input type="button" id="btnSubmit" class="btn btn-primary" value="Submit" />

        </div>

    </div>

    <div>

        <span id="spnMsg" style="color:red"></span>

    </div>

    <br />

    <table id="myTable" width="700" class="table"></table>

</div>

<script src="~/Scripts/jquery-1.10.2.js"></script>

<script>

    $(document).ready(function () {

        $("#btnSubmit").click(function () {

            $("#myTable").empty();

            var query = $("#txtQuery").val();

            if (query === "")

            {

                $("#spnMsg").text("Please enter the query").css('color', 'red');

            }

            else

            {

                $.ajax({

                    url: '/Demo/BindTable',

                    type: 'GET',

                    dataType: 'json',

                    data: { query: query },

                    success: function (data) {

                        $("#myTable").append(data);

                    },

                    error: function () {

                        alert('Error!');

                    }

                });

            }

        });

    });

</script>

 5. Run the project and check the output