Warm tip: This article is reproduced from stackoverflow.com, please click
c# mysql sql

How can I display all my sql tables in a ListBox

发布于 2020-03-27 10:30:34

I'm trying to receive information from my DB. I need to list all my table names. I guess I'm not close to the execution at all. I'm trying with this but not working, .sql is my connectionstring

private void ListBox1_SelectedIndexChanged(object sender, System.EventArgs e)
{ 
    listBox1.Text= (SQLConnection.sql = "SELECT* FROM INFORMATION_SCHEMA.TABLES");
}
Questioner
user11612444
Viewed
83
Zalhera 2019-07-03 23:59

If you have defined a DbContext to access your Database you can do the following. (Assuming you're using Entity Framework).

using(var ctx = new YourDbContex())
{
   var tableNames = ctx.Database.SqlQuery<string>("SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES as t WHERE t.TABLE_TYPE = 'BASE TABLE' AND t.TABLE_SCHEMA = 'yourDatabase'").ToList() ;  
   listBox1.Text = String.Join(", ", tableNames);  
}

This SqlQuery will get you all Tables that you have defined in 'yourDatabase' and sets the Text to "table1, table2, ..."

If you don't want to access your Database with a DbContext you can do it with the SqlCommandClass. With sql being your Connection String.

var command = sql.CreateCommand() ;
command.CommandText = "SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES as t WHERE t.TABLE_TYPE = 'BASE TABLE' AND t.TABLE_SCHEMA = 'yourDatabaseName'";  
List<string> tableNames = new List<string>() ;

sql.Open() ;

using(var reader = command.ExecuteReader())
{
  while(reader.Read())
  {
    tableNames.Add(reader.GetString(0)) ;
  }
}

sql.Close();

listBox1.Text = String.Join(", ", tableNames) ;