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

Question

有没有更好的方法来使用不同的 SQL 查询来获取数据？

还添加了代码片段(虽然与我的问题并不相关)。

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;
}

Answer 1

具有多个条件的 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);
   .....