How to get `List<string>` of table names from MySQL db?

Question

How can I get List<string> of all table names contained in a MySQL database?

I want to load a complete database into a DataSet but from my understanding it seems that MySqlDataAdapter.Fill() only operates on single tables, correct? That is what I want to use the table string collection for.

EDIT:

I look for the correct query: The following returns 59 different items despite the database only holding 3 tables:

    MySqlCommand command = new MySqlCommand("SELECT table_name FROM information_schema.tables where table_type = 'BASE TABLE'", connection);
    var result = command.ExecuteReader();

I look for C# code to parse the result of the query into a List<string>.

@ArunKillu, why is that the case. It completely depends on its content. I have one database setup with several settings/properties tables that are loaded at startup of the app. — Matt
– Matt, Commented Jan 10, 2013 at 5:59

CloudyMarble · Accepted Answer · 2013-01-10 06:42:22Z

7

Use Entity fraemwork, add your schema to the dbcontext then you can make something like:

var tableNames = context.MetadataWorkspace.GetItems(DataSpace.SSpace)
                        .Select(t => t.Name)
                        .ToList();

EDIT:

Alternativly you can read the tablen names using a normal sql query (for example using Show tables) and parse these into a list like:

List<String> Tablenames = new List<String>();

using(SqlConnection connection = new SqlConnection("conn_string"))
{
    string query = "show tables from YourDB";
    SqlCommand command = new SqlCommand(query, connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Tablenames.Add(reader.GetString(0));
        }         
    }
}

edited Jan 10, 2013 at 6:42

answered Jan 10, 2013 at 6:21

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Over a year ago

sorry but I like to do without entity framework. But thank you for the suggestion.

CloudyMarble Over a year ago

then you will have to use a normal sql query and parse the results into the list.

Matt Over a year ago

the whole point of my question was exactly how to parse the results to the list :-) (please see my second point)

Matt Over a year ago

great, thanks that answers my question. By the way, is there a way in .Net to ease the parsing for more complicated queries? Would linq2sql help or any other ideas?

CloudyMarble Over a year ago

Sure Linq is very powerfull in this, but im not really familiar with linq2sql, i can only recommend you considering to use entity framework since it provides you allready dbsets on which you can run linq queries.

|

Chenxiao · Accepted Answer · 2014-05-20 12:17:13Z

Download and install Connector/Net first.This is necessary to use MySQL in C# .MySQL for Visual Studio is not necessary,but I recommend you to install it.It can help you designing MySQL databases in Visual Studio.
Add reference to MySql.Data
Add using MySql.Data.MySqlClient; in your code.

Declare a function like this:

public List<string> MySqlCollectionQuery(MySqlConnection connection, string cmd)
{
    List<string> QueryResult = new List<string>();
    MySqlCommand cmdName = new MySqlCommand(cmd, connection);
    MySqlDataReader reader = cmdName.ExecuteReader();
    while (reader.Read())
    {
        QueryResult.Add(reader.GetString(0));
    }
    reader.Close();
    return QueryResult;
}

Then create a MySql connection and call this function:

string connStr = string.Format("user={0};password={1};database={2}",
                                username,password,database);
List<string>TableNames = new List<string>();//Stores table names in List<string> form
using(MySqlConnection Conn = new MySqlConnection(connStr))
{
    Conn.Open();
    string cmdStr = "show tables";
    TableNames = MySqlCollectionQuery(Conn,cmdStr);
}

I did not put this in a try ... catch block,but it is always a good practice to do that.

Collectives™ on Stack Overflow

How to get `List<string>` of table names from MySQL db?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related