How can I identify the column names and datatypes of a datatable in MySQL database using c#

Question

User-1313756816 posted

I want to be able to query a mysql database...knowing the tablename...but not knowing the column in that table or their datatypes and size restrictions and be able to retrieve that information... 

How can I do that in c#?

Answer 1

User665793701 posted

Dear Prysson,

Try this sql from your c# code

"SHOW COLUMNS FROM mytable FROM mydb";

then iterate the returned data and fetch your desired column name and data type.

Regards,

Answer 2

User-1313756816 posted

Thanks for the Query.  Works like a charm.

Anyone who may not know how to evaluate the data in c# as its returned from the query the following will work

MySqlConnection msql = new MySqlConnection();
msql.ConnectionString = "connectionstringinfo";

MySqlDataAdapter myAdapter = new MySqlDataAdapter("SHOW COLUMNS FROM mytable", msql);

DataSet ds = new DataSet();
ds.Tables.Add(dt);
myAdapter.Fill(ds.Tables[0]);

DataTable dt = ds.Table[0];

 then parse through the datatable columns and you will have yoru column names and data types.