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

Question

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

Answer

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:


    //You can create input box or selectlist to pass the table name

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

Answer

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)
```
  
      
      
      
  
  
```

Create the view to display data (Index.cshtml)

  @model IEnumerable

Share via

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

2 answers

Your answer