How to display a table based on listbox selection in MVC

Question

0.00/5 (No votes)

See more:

List box containing column names(column name changing dynamically based on another list box).Now how to display table format for list box columns in mvc4.

What I have tried:

I tried in asp.net c# but i need in mvc,here drop down select item is table name.This same code i need in mvc.

C#

protected void Button2_Click(object sender, EventArgs e)
  {

      if (ListBox1.Items.Count > 0)
      {
          List<string> listbfrom = new List<string>();


          for (int i = 0; i < ListBox1.Items.Count; i++)
              if (ListBox1.Items[i].Selected)
                  listbfrom.Add(ListBox1.Items[i].Text);

          // string csv = string.Join(",", listbfrom);
          //string csv = string.Format("'{0}'", string.Join("','", listbfrom));
          string csv = string.Join(",", listbfrom.ToArray());
          string csv1 = DropDownList1.SelectedItem.Text;

          con.Open();

          string str = "SELECT "+csv+ " FROM  "+csv1;

          using (SqlCommand com = new SqlCommand(str, con))
          {
             // com.Parameters.AddWithValue("@listbfrom", csv);
            //  com.Parameters.AddWithValue("@tabfrom", DropDownList1.SelectedItem.Text);
              DataSet dsBooking = new DataSet();
              SqlDataAdapter dap = new SqlDataAdapter(com);

              dap.Fill(dsBooking);
              con.Close();


              GridView1.DataSource = dsBooking;
              GridView1.DataBind();
          }





          /* protected void Button2_Click(object sender, EventArgs e)
           {

               //ListBox1_SelectedIndexChanged(sender, e);
               if (ListBox1.Items.Count > 0)
               {

                   DataTable dt = new DataTable();


                   DataSet dsBooking = new DataSet();

                   for (int i = 0; i < ListBox1.Items.Count; i++)
                   {
                       if (ListBox1.Items[i].Selected)
                       {
                           string Listbfrom = ListBox1.Items[i].Text;







                           string str = "SELECT  " + Listbfrom + "  FROM  " + DropDownList1.SelectedItem.Text;

                           SqlCommand com = new SqlCommand(str, con);



                           SqlDataAdapter dap = new SqlDataAdapter(com);

                           dap.Fill(dsBooking);









                       }

                   }
                   GridView1.DataSource = dsBooking;

                   GridView1.DataBind();

               }



           }*/

          /* protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
           {
               if (ListBox1.Items.Count > 0)
               {

                   DataTable dt = new DataTable();

                   DataSet ds = new DataSet();

                   for (int i = 0; i < ListBox1.Items.Count; i++)
                   {
                       if (ListBox1.Items[i].Selected)
                       {
                           string Listbfrom = ListBox1.Items[i].Text;





                           con.Open();

                           string str = "SELECT  " + Listbfrom + "  FROM  " + DropDownList1.SelectedItem.Text;

                           SqlCommand com = new SqlCommand(str, con);

                           DataSet dsBooking = new DataSet();

                           SqlDataAdapter dap = new SqlDataAdapter(com);

                           dap.Fill(dsBooking);

                           con.Close();


                           {

                               GridView1.DataSource = dsBooking;

                               GridView1.DataBind();

                           }

                       }

                   }

               }

           }
   */
      }
  }

Posted 20-Jun-17 19:37pm

GrpSMK

Updated 20-Jun-17 21:38pm

v2

Add a Solution

Comments

Karthik_Mahalingam 21-Jun-17 1:48am

use jquery ajax

GrpSMK 21-Jun-17 1:54am

can you please help me with sample code

Karthik_Mahalingam 21-Jun-17 2:37am

Always use Reply button, to post Comments/query to the user, so that the user gets notified and responds to your text.

GrpSMK 21-Jun-17 2:46am

ok sure,help me

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-06-20T21:38:00

Controller:

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace mvc.Controllers
{
    public class HomeController : Controller
    {
        string constr = "your conneciton string";
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult GetAllTableNames() {

            SqlConnection con = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("select name from sys.tables " , con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            var data = dt.AsEnumerable().Select(k => k["name"].ToString());
            return Json(data);
             
        }
        public ActionResult GetAllColumnNames(string tbl)
        {

            SqlConnection con = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tbl " , con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.Parameters.AddWithValue("@tbl", tbl);
            DataTable dt = new DataTable();
            da.Fill(dt);
            var data = dt.AsEnumerable().Select(k => k["COLUMN_NAME"].ToString());
            return Json(data);
             
        }


        public ActionResult GetTableData(string tableName, string columnNames)
        {
            
                SqlConnection con = new SqlConnection(constr);
                SqlCommand cmd = new SqlCommand("select " +  columnNames + " from  " + tableName, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                string data = JsonConvert.SerializeObject(dt);
                return Json(data);
               
        }
        
        
    }
}

CSHTML

<script src="~/Scripts/jquery-1.10.2.js"></script>
<script>
    $(function () {

        $.ajax({
            url:  '@Url.Action("GetAllTableNames", "home")',
            data: {},
            type: 'post',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (data) {
                var ddl = $('#ddlTableNames');
                var options = []; 
                for (var i = 0, len = data.length; i < len; ++i)  
                    options.push('<option value="' + data[i] + '">' + data[i] + '</option>'); 
                ddl.append(options.join(''));
            },
            error: function (a, b, c) {
                console.log(a, b, c);
            }
        });

    });

    function populateColumns(ddl){
        var table = ddl.value;  
        $.ajax({
            url: '@Url.Action("GetAllColumnNames", "home")',
            data: JSON.stringify({'tbl':table}),
            type: 'post',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (data) {
                var ddl = $('#ddlColumnNames');
                ddl.empty();
                var options = [];
                for (var i = 0, len = data.length; i < len; ++i)
                    options.push('<option value="' + data[i] + '">' + data[i] + '</option>');
                ddl.append(options.join(''));
            },
            error: function (a, b, c) {
                console.log(a, b, c);
            }
        });
    }

    function getdata() {
        var table = $('#ddlTableNames').val();
        var columns = $('#ddlColumnNames').val().join(',');
        debugger;

         
        $.ajax({
            url: '@Url.Action("GetTableData", "home")',
            data: JSON.stringify({ 'tableName': table, 'columnNames':columns }),
            type: 'post',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (data) {
                debugger;
                var json = JSON.parse(data);

                generateTable(json)
            },
            error: function (a, b, c) {
                console.log(a, b, c);
            }
        });
    }

    

    function generateTable(json) {

        var $table = $('#tblDynamic');
        $table.find('thead').empty()
        $table.find('tbody').empty()
        if (json && json.length > 0) {
            var header = json[0];
            var columns = [];
            for (var col in header) {
                columns.push('<th>' + col + '</th>');
            }
            $table.find('thead').append('<tr>' + columns.join('') + '</tr>');
            var rows = [];
            for (var i = 0; i < json.length; i++) {
                var row = json[i];
                var tds = [];
                for (var col in row) {
                    tds.push('<td>' + row[col] + '</td>');
                }
                rows.push('<tr>' + tds.join() + '</tr>');
            }
            $table.find('tbody').append(rows.join(''));
        }

    }

</script>

<select id="ddlTableNames" onchange="populateColumns(this)">   
</select> <br />
<select id="ddlColumnNames"  multiple></select>
<button onclick="getdata()" >get data</button>

<table id="tblDynamic">
    <thead></thead>
    <tbody></tbody>
</table>

Note: Take care of validations