MySql“选择位置”和 C#

MySql "Select Where" and C#

提问by A.R

How can i read the return value from "Select Where" statement , every time i run no return value appear in the label, and no syntax error.

我如何从“Select Where”语句中读取返回值，每次运行时标签中都没有出现返回值，也没有语法错误。

command.CommandText = "select product_price from product where product_name='"+x+"';";
            connection.Open();
            Reader = command.ExecuteReader();
            while(Reader.Read()){


            Price_label.Content = "" + Reader.GetString(0);

            }
            connection.Close();

采纳答案by Thorsten Dittmar

If the product_pricecolumn is not of type TEXTin MySQL, the Reader.GetString(0)will (depending on how the reader was implemented by Oracle) throw an Exception or return an empty string. I would think the latter is happening.

如果该product_price列不是TEXTMySQL 中的类型，则Reader.GetString(0)will（取决于读取器由 Oracle 实现的方式）抛出异常或返回空字符串。我认为后者正在发生。

Retrieving the value through a DataReaderrequires you to know the data type. You can not simply read a string for every type of field. For example, if the field in the database is an Integer, you need to use GetInt32(...). If it is a DateTimeuse GetDateTime(...). Using GetStringon a DateTimefield won't work.

通过 a 检索值DataReader需要您知道数据类型。您不能简单地为每种类型的字段读取一个字符串。例如，如果数据库中的字段是整数，则需要使用GetInt32(...). 如果是DateTime用GetDateTime(...)。使用GetString上的DateTime场将无法正常工作。

EDIT
This is how I'd write this query:

编辑
这就是我写这个查询的方式：

using (MySqlConnection connection = new MySqlConnection(...))
{
    connection.Open();
    using (MySqlCommand cmd = new MySqlCommand("select product_price from product where product_name='@pname';", connection))
    {
        cmd.Parameters.AddWithValue("@pname", x);
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            StringBuilder sb = new StringBuilder();
            while (reader.Read())
                sb.Append(reader.GetInt32(0).ToString());

            Price_label.Content = sb.ToString();
        }
    }
}

回答by Safari

you have to create a variable of your reader

你必须为你的读者创建一个变量

command.CommandText = "select product_price from product where product_name='"+x+"';";
try {
connection.Open();
SqlReader reader = command.ExecuteReader();
while(reader.Read()){


    Price_label.Content = "" + Reader.GetString(0);

}
} catch (Exception) {}
finally {
connection.Close();
}

回答by Bobby

To append to my comment, your approach has three problems which are not part of your problem:

补充我的评论，你的方法有三个不属于你的问题的问题：

• SQL-Injection, always use parameterized queries.
• Leaking resources, IDisposable-Objects need to be treated properly.
• Bad habits, "" + stringfor casting is...uhhh...not good and not necessary.

• SQL 注入，始终使用参数化查询。
• 泄漏资源，IDisposable-Objects 需要妥善处理。
• 坏习惯，"" + string因为铸造是......呃......不好也没有必要。

So, a more correct version for your code would look like this:

因此，更正确的代码版本如下所示：

// using utilizes the IDisposable-Interface, whcih exists to limit the lifetime
// of certain objects, especially those which use native resources which
// otherwise might be floating around.
using(YourConnectionType connection = new YourConnectionType("connectionstring"))
{
    connection.Open(); // You might want to have this in a try{}catch()-block.

    using(YourCommandType command = connection.CreateCommand())
    {
        command.CommandText = "select product_price from product where product_name=@NAME;";
        command.Parameters.Add("NAME", YourTypes.VarChar);
        command.Parameters[0].Value = x; // For your own sanity sake, rename that variable!

        using(YourReaderType reader = command.ExecuteReader())
        {
            while(reader.Read()) // If you're expecting only one line, change this to if(reader.Read()).
            {
                Price_label.Content = reader.GetString(0);
            }
        }
    }
} // No need to close the conenction explicit, at this point connection.Dispose()
  // will be called, which is the same as connection.Close().

回答by YsfTgrl

You should write @pname without '' otherwise it won't work.

你应该写不带 '' 的 @pname 否则它不会工作。

instead of:

代替：

select product_price from product where product_name='@pname'

从 product_name='@pname' 的产品中选择 product_price

you should write like this:

你应该这样写：

select product_price from product where product_name=@pname

从 product_name=@pname 的产品中选择 product_price