如何查询Mysql数据库以返回布尔结果 [英] How do I query a Mysql database to return a boolean result

问题描述

我需要查询mysql数据库以检查表中是否存在特定字符串，然后执行某些操作。这就是我所尝试的

i need to query a mysql database to check if a particular string exists in a table then perform some operation afterwards. this is what i have tried

protected void toNokReg_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connection);
                MySqlCommand cmd = new MySqlCommand("select number from table.alladmin where number = '" + number.Text + "'", conn);
                MySqlDataReader read;
                conn.Open();
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    if (read.GetString("number").Equals(""))
                    {
                        insertStaffDetails();
                    }
                    else
                    {
                        Label23.Text = "Incorrect Mobile Number";
                    }
                }
                read.Close();
                conn.Close();
            }
            catch (MySqlException ex)
            {
                Label23.ForeColor = System.Drawing.Color.Red;
                //Label23.Text = "MySql: Failed Upload, Please Try Agin";
                Label23.Text = ex.Message + "\n" + ex.StackTrace;
            }
            catch (Exception)
            {
                Label23.ForeColor = System.Drawing.Color.Red;
                Label23.Text = "Failed Upload, Please Try Agin";
            }
        }

上面的代码是一个按钮，表号包含varchar类型中存储的各种手机号码。

i want它检查当前在具有id号的文本框中的号码是否已经在数据库中，如果不是它应该执行

insertStaffDetails（）;

else，it应该显示错误，但它不起作用我最好的错误猜测来自while语句。我需要你的帮助，非常感谢

the code above is in an event of a button, the table number comprises various mobile numbers stored in the varchar type.
i want it to check if the number currently in a textbox with id number is already in the database, if it isn't it should perform
insertStaffDetails();
else, it should display an error, but it isn't working my best guess of error is from the while statement. Please i need your assistance, thanks a lot

推荐答案

试试这个：

Try this:

MySqlConnection conn = new MySqlConnection(connection);
MySqlCommand cmd = new MySqlCommand("select count(*) from table.alladmin where number = '" + number.Text + "'", conn);
conn.Open();
int a=(int)cmd.ExecuteScalar();
conn.Close();
if (a>0) { //do your staff];

编辑

在别人告诉你之前，编写你的sql命令连接字符串绝对不是一个好主意，它会让你暴露给SQL注入。

Edit
Before someone else tell you,it's NEVER a good idea to compose your sql command concatenating strings, it exposes you to SQL Injection.

我认为以前的建议评论都是正确的。



我会稍微重构代码如下：

I think the suggestions in previous comment are all correct.

I would just refactor the code a bit as following:

public class SomeDataAccessClass
{
	public bool IsPhoneNumberPresent(string number)
	{	
		bool returnVal = false;
		using(MySqlConnection conn = new MySqlConnection(connection))
		{
			using (SqlCommand cmd = con.CreateCommand())
            {
				cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select count(*)from table.alladmin where number = @number";
				
				SqlParameter param = new SqlParameter("@number", number);
				cmd.Add(param);
				
				conn.Open();
				
				int result = (int)cmd.ExecuteScalar();
				returnVal = result >= 1;
			}
		}
	
		return returnVal;
	}
}

仍然不完美，但我可以忍受这段代码。我希望你能理解这些代码。如果没有，请询​​问。

Still Not perfect but I could live with this code. I hope you will understand the code. If not, ask.

这篇关于如何查询Mysql数据库以返回布尔结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！