How to display dynemic tables and display all rows and column after select a particular tables in mvc

Hi friends,Today I am explaining how to bind all tables in drop down list and when select particular tables then display all rows and column related that table.Here we will not create any tables and database you can use your own database and table.So let us see step by step.

First Create MVC Application for perform this demo.

After create project go in web.config file and set connection string 

<add name="constr" connectionString="Data Source=PC;Initial Catalog=ProjectDB;User Id=sa;Password=123;" providerName="System.Data.SqlClient" />

Add a controller 

Right click of project->Add->Controller

write below code in controller

  public List<string> BindTables()

        {

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

         

            int count = 0;

            using (SqlConnection con = new SqlConnection(constr))

            {

                try

                {

                   

                    con.Open();

                    DataTable dt = con.GetSchema("Tables");

                    list.Add("Select project name");

                

                    for (int i = 0; i < dt.Rows.Count; i++)

                    {

                     

                        string tbl = dt.Rows[i]["TABLE_NAME"].ToString();

                            list.Add(tbl);

                    }

                }

                catch (Exception ex)

                {

                    ex.ToString();

                    throw;

                }

            }

            return list;

        }

  public ActionResult AllReport()

        {

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

            listProject = BindTables();

            ViewBag.MyTables = listProject;

            return View();

        }

Add a view 

Right click on action method -> Add view 

<div class="container">

    <div class="row">

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

            @Html.DropDownList("tables", new SelectList(ViewBag.MyTables), new { id = "ddlProject", @class = "form-control" })

        </div>

      

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

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

        </div>

    </div>

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

</div>

<div class="container">

 

    <table id="myTable" width="500" class="table">

     

        </table>

</div>

Add jquery code for sending the request 

<script>

    $(document).ready(function () {

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

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

            var project = $("#ddlProject option:selected").val();

        

            if (project === "Select project name")

            {

                $("#spnMsg").text("Please select project").css('color', 'red');

            }

            else

            {

                debugger;

                $.ajax({

                    url: '/ControllerName/BindTables',

                    type: 'GET',

                    dataType: 'json',

                    data: { projectName: project },

                    success: function (data) {

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

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

                    },

                    error: function () {

                        alert('Error!');

                    }

                });

            }

          

        });

    });

</script>

Write tow store proc one for header name and other for rows and column

      CREARTE proc [dbo].[USP_SelectTableColumn]

         (

@ProjectName varchar(100)

)

as

begin

SELECT *

  FROM INFORMATION_SCHEMA.COLUMNS

 WHERE table_catalog = 'DatabaseName'

   AND table_name = @ProjectName

   end

   CREATE proc [dbo].[USP_SelectProject]

(

@ProjectName varchar(100)

)

as

begin

DECLARE @TABLE NVARCHAR(100)

SET @TABLE =@ProjectName

EXEC('SELECT * FROM ' + @TABLE)

   end

Add BindTables method in controller

      public JsonResult BindTables(string projectName)

        {

            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("@ProjectName", projectName);

                    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_SelectProject", con);

                    cmdName.CommandType = CommandType.StoredProcedure;

                    cmdName.Parameters.AddWithValue("@ProjectName", projectName);

                    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 Json(body, JsonRequestBehavior.AllowGet);

        }

Final output