Using Sql tables as elements in front end

Question

Hello people. I want to do a a tool where I can generate a report, and I was wondering if I can use the tables that I have in my database as elements in my front end, like I have the database "Person", "State" and "City"(just an example), and these tables are going to be radio buttons or other kind of element in my front-end, this way I can do a dynamic search. Does anyone know a way to do it ?

Thanks in advance

Update 02/08/2022

To explain better, I want to use the tables as literally elements in front-end, like I have the table "Person", somehow I gonna use it as a radio button or another type of element on front-end. Supposing it's a radio button when I click on it shows the columns of the table, in case I want to filter a specific column from the table.

So like I have 5 tables, I'm gonna have 5 radio buttons, if I add a table later, I'm don't want to add it in the front-end manually, I want that it add automatically, like a dynamic thing.

Answer 1

dynamic web UI count on have defined meta data for the UI. you will want to define this meta data:

public class Column
{
     public string Name {get; set;}
}
public class Table
{
     public string Name {get; set;}
     public List<Column> Columns {get set;} = new List<Column>()
}

var tables = ...

//in the html (using razor)

@for (var table in Model.Tables)
{
     <h1>Database @table.Name</h1>
     <ul>
     @for (var col in table.Columns)
     {
       <li>@col.Nam</li>
     }
     </ul>
} 

Answer 2

Hi @Jonathan ,
What's your meanings? If you want to show sql table on the page directly,I think,this is not possible. You must create a server control in the front page and then bind your database. You could do select in the page.

Best regards,
Yijing Sun

---

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.

Answer 3

According to your updated post, I assume you are looking for a SQL query that returns the tables in a database.

SELECT TABLE_NAME   
FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES   
WHERE TABLE_TYPE = 'BASE TABLE'  

TABLES (Transact-SQL)

Keep in mind, you tagged this question as Web Forms and Web Pages so forum members assume you are asking how to create a web page. Working with data is covered in every beginning level tutorial Web Forms tutorial. As far as I can tell, this is more of a SQL fundamentals question.

Answer 4

Yeah, like adding controls dynamically. Like the example I gave,

I'm a little confused by this response as you have not provided a single code example in this thread. This is why forum members are having a hard time understanding the use case.

Keep in mind, adding controls dynamically to Web Forms is a usually a poor design choice with little upside. Dynamic controls are not included Web Forms' state management which commonly confuses Web Forms developers. I recommend the template based MVC or Razor Pages which give developers better control over rendering HTML.

Below is a basic Web Forms dynamic control example. As you can see the only difference is the control is added to the control tree dynamically from the code behind rather than adding the control to the page markup (designer.cs file).

Markup

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <asp:Panel ID="Panel1" runat="server"></asp:Panel>
</asp:Content>

Code behind

public partial class _default : System.Web.UI.Page
    {
        private readonly string ConnectionString;


        public _default()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        protected void Page_Init(object sender, EventArgs e)
        {
            List<string> databases = GetDatabases();
            PopulateDatabaseRadioButtonList(databases);
        }
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void PopulateDatabaseRadioButtonList(List<string> databases)
        {
            RadioButtonList rbl = new RadioButtonList();
            Panel panel = this.Panel1;
            panel.Controls.Add(rbl);
            rbl.DataSource = databases;
            rbl.DataBind();
        }

        protected List<string> GetDatabases()
        {
            List<string> databases = new List<string>();
            string sql = @" SELECT TABLE_NAME 
                            FROM DemoDB.INFORMATION_SCHEMA.TABLES 
                            WHERE TABLE_TYPE = 'BASE TABLE'";

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    databases.Add((string)reader[0]);
                }
            }
            return databases;
        }
    }

Answer 5

And I'd like to know too, if I can catch the columns of the tables .

The follow query fetches the tables within a database and the columns within a table.

SELECT t.TABLE_CATALOG, t.TABLE_NAME, c.COLUMN_NAME
FROM DemoDB.INFORMATION_SCHEMA.TABLES AS t
    JOIN DemoDB.INFORMATION_SCHEMA.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE = 'BASE TABLE'

As the developer you get create any UI you like. If you are having a problem and need assistance then share code that reproduces the issue. Explain the expected results and the actual results.