Share via

How to retrieve the any table from SQL DB and display dynamically in one page in asp.net core

phani sekhar 1 Reputation point
2023-04-02T16:30:01.65+00:00

Requirements are as below. 1. In single page of asp.net core, need to display any table data dynamically like jtable from jQuery plugin... With generic code with ado.net or web api orAPI or entity framework core

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
725 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,383 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,626 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Chen Li - MSFT 1,221 Reputation points
    2023-04-07T08:08:10.6933333+00:00

    Hi @phani sekhar,

    Do you mean to output the corresponding table data by the name of the table without a model?

    You can create SqlCommand object(Need to reference System.Data.SqlClient) and then use DataSet and use SqlDataAdapter to operate database. Below is my test code:

    Controller:

    public IActionResult Index()
{
    return View();
}
[HttpGet]
public DataTable GetData(string Table)
{
    string connectionString = "Server=(localdb)\\mssqllocaldb;Database=TestDbOne;Trusted_Connection=True;MultipleActiveResultSets=true";
    SqlConnection sqlconn = new SqlConnection(connectionString);
    try
    {
        sqlconn.Open();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.ToString());
    }
    SqlCommand sqlcmd = new SqlCommand("SELECT * FROM "+Table, sqlconn);
    SqlDataAdapter sda = new SqlDataAdapter();
    sda.SelectCommand = sqlcmd;
    DataSet ds = new DataSet();
    sda.Fill(ds, "table1");
    DataTable dataTable = ds.Tables["table1"];
    sqlconn.Close();
    return dataTable;
}

    Index.cshtml:

    <div>
    //You can create input box or selectlist to pass the table name
    <button onclick="GetData('MyTable')">Click Here to Get Data!</button>
</div>
<div id="table"></div>

<script>
    function GetData(value)
    {
        $.ajax({
            type: 'Get',
            url: '/Home/GetData?Table='+value,
            success: function (result) {
                var keys = Object.keys(result[0]);
                var html = "<table class='table'><thread><tr>";

                for(var key in keys)
                {
                    html+= "<th>"+keys[key]+"</th>";
                }
                html+="</tr></thread><tbody>";

                for(var res in result)
                {
                    html+="<tr>";
                    var values = Object.values(result[res]);
                    for(var val in values)
                    {
                        html+= "<td>"+ values[val] +"</td>";
                    }
                    html+="</tr>";
                }
                html+= "</tbody></table>";
                $("#table").append(html);
            },
            error: function () {
                alert('Failed to receive the Data');
                console.log('Failed ');
            }
        })
    }
</script>

    Test Result: User's image

    Is this what you want?

    If the answer is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,

    Chen Li

  2. Sushama Agrawal 1 Reputation point
    2024-05-27T05:27:02.3433333+00:00

    Here are the steps you need to do:

    Step 1: Set Up Your ASP.NET Core Project

    Create a new ASP.NET Core project

    Install EF Core Packages

    Configure the connection string in appsettings.json

    Step 2: Create Your EF Core Model

    Define a generic model class

    Create your DbContext

    Configure DbContext in Startup.cs

    Step 3: Create an API Controller to Fetch Data

    Create a controller to return data

    Step 4: Set Up jTable and AJAX in Your View

    • Include jTable and jQuery scripts in your view (_Layout.cshtml or specific view) 
        <head>
      <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
      <link 			href="https://cdnjs.cloudflare.com/ajax/libs/jtable/2.6.0/themes/lightcolor/blue/jtable.min.css" rel="stylesheet" />
      <script src="https://cdnjs.cloudflare.com/ajax/libs/jtable/2.6.0/jquery.jtable.min.js"></script>
  </head>
    • Create the view to display data (Index.cshtml) 
        @model IEnumerable<DynamicTableApp.Models.YourEntity>
  <div id="jtable-container"></div>
  <script type="text/javascript">
      $(document).ready(function () {
          $('#jtable-container').jtable({
              title: 'Table of Entities',
              actions: {
                  listAction: '/api/Data/YourEntities'
              },
              fields: {
                  Id: {
                      key: true,
                      create: false,
                      edit: false,
                      list: true
                  },
                  Name: {
                      title: 'Name',
                      width: '40%'
                  },
                  Value: {
                      title: 'Value',
                      width: '20%'
                  }
              }
          });
          $('#jtable-container').jtable('load');
      });
  </script>