How to display data from local db tables

Question

How to display data from local db tables

There is a Windows Forms C# application for working with a local database. There is a database of 4 tables. The application has a SELECT tab that displays data from all tables in the window. In this case, the application outputs data from only 1 table (Product). And I need to display all the tables! Unfortunately, there is not enough knowledge to write code or modify an existing one. I will be happy to hear any thoughts on this. Screenshots and a code snippet are attached.

private async void Form1_Load(object sender, EventArgs e)
{
   string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DataBaseMusicShop.mdf;Integrated Security=True";
   sqlConnection = new SqlConnection(connectionString);
   await sqlConnection.OpenAsync();

   SqlDataReader sqlReader = null;
   SqlCommand command = new SqlCommand("SELECT * FROM [Product]", sqlConnection);

   try
   {
       sqlReader = await command.ExecuteReaderAsync();
       while (await sqlReader.ReadAsync())
       {
           listBox1.Items.Add(Convert.ToString(sqlReader["id_product"]) + "  " + Convert.ToString(sqlReader["p_firm"]) + "  " + Convert.ToString(sqlReader["p_type"]) + "  " + Convert.ToString(sqlReader["p_price"]));
       }
   }
   catch (Exception ex)
   {
       MessageBox.Show(ex.Message.ToString(), ex.Source.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
   }
   finally
   {
        if (sqlReader != null)
                    sqlReader.Close();
   }
}

0

c#база-данных winforms visual-studio-2017 sql-server-localdb

Author: Александр Муксимов, 2019-06-12

Source

1 answers

score 0 · Accepted Answer

To get the names of all the tables without writing them in the code, you can make a query to the database schema. To do this, there is a method DbConnection. GetSchema. Provider ADO.NET for SQL Server, it provides a schema Tables containing the table names.

The simplest code for the output of all DB tables in listboxes will look something like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Threading.Tasks;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {        
        public Form1()
        {
            InitializeComponent();            
        }        

        public static IEnumerable<string> QueryTableNames(DbConnection con)
        {            
            //получение списка всех таблиц в БД
            DataTable dtSchema = con.GetSchema("Tables");

            foreach (DataRow row in dtSchema.Rows)
            {
                if ((string)row["TABLE_TYPE"] == "BASE TABLE")
                {
                    yield return (string)row["TABLE_NAME"];
                }
            }
        }

        public static async Task<DataTable> QueryTable(DbCommand cmd,string name)
        {             
            //получение таблицы
            DbDataReader rd = await cmd.ExecuteReaderAsync();
            using (rd)
            {
                DataTable dt = new DataTable();
                await Task.Run(() => { dt.Load(rd); });                                
                dt.TableName = name;
                return dt;
            }            
        }

        public static void DisplayTables(Control control, IEnumerable<DataTable> tables)
        {
            //вывод набора таблиц в указанном элементе управления сontrol

            //создадим FlowLayoutPanel для группировки элементов в столбик
            FlowLayoutPanel pan = new FlowLayoutPanel();            
            pan.FlowDirection = FlowDirection.TopDown;            
            pan.Dock = DockStyle.Fill;

            foreach (DataTable t in tables)
            {
                //создадим GroupBox с именем таблицы
                GroupBox gb = new GroupBox();
                gb.Text = "Table [" + t.TableName+"]";
                gb.Width = 400;
                gb.Height = 150;

                //создадим ListBox для отображения данных таблицы
                ListBox lb = new ListBox();                
                lb.Dock = DockStyle.Fill;

                foreach (DataRow row in t.Rows)
                {                    
                    lb.Items.Add(String.Join(" ", row.ItemArray));
                }

                gb.Controls.Add(lb);
                pan.Controls.Add(gb);
            }

            control.Controls.Clear();
            control.Controls.Add(pan);
        }

        public async Task DisplayBase()
        {
            string connstr = @"...";
            SqlConnection con = new SqlConnection(connstr);

            using (con)
            {
                await con.OpenAsync();

                //загружаем схему
                IEnumerable<string> table_names = QueryTableNames(con);
                List<DataTable> tables = new List<DataTable>(table_names.Count());
                SqlCommand cmd;

                //загружаем данные
                foreach (string t in table_names)
                {
                    cmd = new SqlCommand("SELECT * FROM [" + t + "]", con);
                    tables.Add(await QueryTable(cmd, t));
                }

                //отображаем данные в элементе panel1
                panel1.SuspendLayout();
                DisplayTables(panel1, tables);
                panel1.ResumeLayout();
            }
        }

        private async void Form1_Load(object sender, EventArgs e)
        {                                    
            await DisplayBase();              
        }              
    }  
}