Is there a better way to get table structure?

Question

Is there a better way to fetch using a different SQL query?

Have also added the code snippet (though not really related to my question).

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '$schema_name$', TABLE_NAME='$table_name$';

public TableStructure GetTableStructure(string TableName, MySqlConnection Connection)
{
    if (Connection == null)
        throw new ArgumentNullException("Sql Connection should be initialized.");

    string sqlQuery = @"select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '$schema_name$', TABLE_NAME='$table_name$'";
    sqlQuery = sqlQuery.Replace("$table_name$", TableName);
    sqlQuery = sqlQuery.Replace("$schema_name$", SchemaName);

    TableStructure tableStructure = null;
    try
    {
        using (MySqlCommand sqlCmd = new MySqlCommand(sqlQuery, Connection))
        {
            if (Connection.State == ConnectionState.Closed)
                Connection.Open();

            using (MySqlDataReader dr = sqlCmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    ...
                    ...
                    //tableStructure = TableStructure.GetTableStructureFromDataReader(TableName, dr);
                }
            }
        }
    }
    catch (Exception)
    {
        //TODO
        throw new Exception("Error occured while obtaining tables list");
    }
    return tableStructure;
}

You may wish to use prepared/parameterized statements instead of manually working with queries as strings. Look at the `Parameters.AddWithValue()` functions in the `MySqlCommand` class. This question can give you an idea of how it's done: http://stackoverflow.com/questions/13580993/c-sharp-mysqlcommand-command-parameters-add-is-obsolete — Bacon Bits, Apr 02 '14 at 18:26
so in terms of obtaining the table structure details (like column name, column data type, ect)...there is no better way to fetch it? — ShaQ.Blogs, Apr 02 '14 at 18:44
There's `SHOW COLUMNS` and the other [`SHOW` commands](https://dev.mysql.com/doc/refman/5.0/en/show.html) specific to MySQL, but `INFORMATION_SCHEMA` is the SQL standard way. — Bacon Bits, Apr 02 '14 at 19:23

score 3 · Accepted Answer · edited May 23 '17 at 12:20

3

A WHERE statement with multiple conditions requires an AND / OR to join the two conditions

string sqlQuery = @"select * from INFORMATION_SCHEMA.COLUMNS 
                  where TABLE_SCHEMA = '$schema_name$' AND TABLE_NAME='$table_name$'";

And instead of using a REPLACE to set your string values (a dangerous practice if the input are typed directly by your end user) you could use a parameterized query

string sqlQuery = @"select * from INFORMATION_SCHEMA.COLUMNS 
                  where TABLE_SCHEMA = @schema AND TABLE_NAME=@table";

using (MySqlCommand sqlCmd = new MySqlCommand(sqlQuery, Connection))
{
    sqlCmd.Parameters.AddWithValue("@schema", SchemaName);       
    sqlCmd.Parameters.AddWithValue("@table", TableName);
   .....

edited May 23 '17 at 12:20

Community

1
1

answered Apr 02 '14 at 18:21

Steve

213,761
22
232
286

so in terms of obtaining the table structure details (like column name, column data type, ect)...there is no better way to fetch it? – ShaQ.Blogs Apr 02 '14 at 18:43
As far as I know no, there is the method [GetSchema](http://dev.mysql.com/doc/connector-net/en/connector-net-programming-getschema.html) of the connection but, in my opinion is more complex. And by the way [INFORMATION_SCHEMA](http://en.wikipedia.org/wiki/Information_Schema) is a standard ISO – Steve Apr 02 '14 at 18:56

Is there a better way to get table structure?

1 Answers1