mySQL database

[Potatoman]

Hi there - Using C#, assuming i have set the connection string and connected to the database, how would i retrieve a list of tables with the database? Thanks. PM

[OBonio]

Info on the SQL you will need to run against MySQL :

http://dev.mysql.com/doc/refman/5.0/en/tables- table.html

To run this and get the data, you need a Command object and a DataReader object from whichever data provider you are using.

The DataReader object should have a HasRows property and a Read method which you can use to pull the data.

if(dr.HasRows)
{
while(dr.Read())
{
string tableName = dr["table_name"].Value;
// .. other processing
}
}

If you want to bind to a DataGrid, then you need to use a DataAdapter and a DataSet

Need more info on how you're connecting to MySQL to give more info really.

[Potatoman]

Cheers, OBonio. Just what i was looking for. I connect to mySQL via the .NET connector on their website. I was just wondering how i would load a list of the tables into an array... Any ideas? Thanks.

[OBonio]

If you're happy with the above code and setting up the Command object, just use an ArrayList object to append a new string. Then you can use ToArray(typeof(string)) to return an array of strings from the ArrayList.

I'll download MySQL and get a proof of concept running and post the code.

[OBonio]

...
using MySql.Data;
using MySql.Data.MySqlClient;
...
string[] tableNames = null;
MySqlConnection conn = new MySqlConnection("Server=localhost;Database=test;Uid=kallidus;Pwd=password");
MySqlCommand cmd = new MySqlCommand("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='Test'", conn);
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();

if(dr.HasRows)
{
ArrayList alTableNames = new ArrayList();
while(dr.Read())
alTableNames.Add(dr["table_name"].ToString());
tableNames = (string[])alTableNames.ToArray(typeof(string));
}

if(tableNames!=null)
{
foreach(string tableName in tableNames)
MessageBox.Show(tableName);
}
else
{
MessageBox.Show("No table names returned.");
}

[Potatoman]

OBonio, your a star. Thats brilliant. Cheers for your help, m8. I appreciate it. PM