c# - 有没有更好的方法来获取表结构？

是否有更好的方法来使用不同的SQL查询来获取？

还添加了代码段（尽管与我的问题无关）。


  从INFORMATION_SCHEMA.COLUMNS中选择*，其中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;
}

最佳答案

具有多个条件的WHERE语句需要AND / OR来连接这两个条件

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

而且，可以使用参数化查询来代替使用REPLACE设置字符串值（如果输入是最终用户直接键入的，则为a dangerous practice）。

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);
   .....