C#和Sql Server Reader问题 [英] C# and Sql Server Reader Issue

问题描述

嘿.我已经编写了一些代码来从SQL Server中的数据库中读取登录信息.到目前为止，它仍然有效，但是有一行无效的是ExecuteReader.有关以下评论的更多信息.

Hey. I have written some code for reading a logon from a database in SQL server. So far it works however there is one line that wont work which is the ExecuteReader. More on the comment below.

        private void btnLogon_Click(object sender, EventArgs e)
        {
            try
            {

                SqlConnection dbConnection = new SqlConnection("Data Source=(local); Initial Catalog = User; Integrated Security = True; Pooling = False");
                dbConnection.Open();

                SqlCommand sqlCommand = new SqlCommand("SELECT User, Password FROM [dbo].[User]");
                sqlCommand.Connection = dbConnection;

                SqlDataReader row;

                row = sqlCommand.ExecuteReader(CommandBehavior.SingleResult);

                while (row.Read())
                {
                    if (tbxUserID.Text == row.GetString(0) && tbxPassword.Text == row.GetString(1))
// Basically the issue is here. ''button1'' dosent become enabled and its all down to the code above. Is this the code or SQL? Would much appreciate an explination or help. Thanks :)
                    {
                        button1.Enabled = true;
                    }
                    else
                    {
                        button1.Text = ("True");
                    }

                }

                dbConnection.Close();

                sqlCommand.Dispose();

                row.Close();
            }

            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "Database Problem", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }

推荐答案

好，让我们剖析您的代码.您正在执行

Well let us dissect your code. You are executing

SELECT User, Password FROM [dbo].[User]

这意味着它将返回所有用户及其各自的密码.然后，通过执行
将选择范围限制为单个

Which means it will return all users and their respective password. Then you limit your choice to single one by executing

sqlCommand.ExecuteReader(CommandBehavior.SingleResult);

当达到if条件时，是什么让您认为读取的单个记录将与您的输入相匹配?

尝试将您的SQL查询更改为

When your reach if condition what makes you think the single record your read is going to match what is your input?

Try to change your sql query to

"SELECT User, Password FROM [dbo].[User] where User = ''" + tbxUserID.Text + "'' AND Password = ''" + tbxPassword.Text + "''"

您的代码崩溃了吗?尝试输入一些调试消息.也许使用MessageBox.Show();

尝试在while循环之前检查HasRows属性.然后在循环中尝试显示第1列和第2列的值，看它们是否符合您的期望...

Does your code crash? try putting in some debug messages. Perhaps using MessageBox.Show();

trying checking the HasRows property before the while loop. then in the loop try showing the values of columns 1 and 2 to see if they are what you expect...

MessageBox.Show(row.HasRows.ToString());
while(row.Read())
{
   MessageBox.show(row.GetString(0) + " " + row.GetString(1));

您期望的值是什么?

您还应该考虑在SQL查询中使用WHERE子句以搜索特定的用户名和密码-然后您可以根据结果是否为"HasRows"来启用按钮

are the values what you expect?

you should also consider using a WHERE clause in your sql query in order to search for the specific username and password - then you can enable the button based on if the result ''HasRows''

button1.Enabled = row.HasRows;

使用文本框值创建SQL where子句时，请不要忘记使用命令参数(如果需要，请搜索Google)(请参阅SQL注入)

重要提示:在键入以下代码时，我意识到您的问题很可能是您在ExecuteReader调用中设置CommandBehavior.SingleResult的事实，这意味着您仅返回第一个结果.改成...

don''t forget to use command parameters (search Google if needed) when you use textbox values to create an SQL where clause (see SQL Injection)

IMPORTANT: While typing the below code I realised your problem is probably the fact that you are setting CommandBehavior.SingleResult in your ExecuteReader call that means you are only returning the first result. change this to...

row = sqlCommand.ExecuteReader();

建议的代码...

Suggested code...

SqlConnection connection; 

try{
     connection = new SqlConnection("...");//should get this from a config file
     SqlCommand cmd = new SqlCommand("SELECT ID FROM [dbo].[User] WHERE User = @0 AND Password = @1");//Consider using Username in stead of User in database
     cmd.Parameters.Add("@0", OleDbType.Char).Value = tbxUserID.Text;
     cmd.Parameters.Add("@1", OleDbType.Char).Value = tbxPassword.Text;

     SqlDataReader reader = cmd.ExecuteReader();

     button1.Enabled = reader.HasRows;

}catch(Exception ex){
     MessageBox.Show(ex.Message, "Database Problem", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally{
     if(connection != null)
          connection.Close();
}

尝试一下

if (tbxUserID.Text.Trim() == row.GetString(0).Trim() && tbxPassword.Text.Trim() == row.GetString(1).Trim())

这篇关于C#和Sql Server Reader问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！