从数据库中获取一个数据单元格

本文关键字：一个 数据 单元格 数据库 获取 | 更新日期: 2023-09-27 18:36:34

我想从我的数据库中获取数据。表名是包含用户 ID、用户名和密码的 MsUser。有几行。

表格描述 ：我有从登录，需要用户名和密码才能登录。登录后，交易表单打开，在该表单中，用户ID将使用标签显示，基于已登录用户名的用户ID。

这是我用来匹配用户名和密码的代码对不起，如果我的英语不好。非常感谢您的帮助，真的。

  private void btnLogin_Click(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=E:'ProgII Project'MoneyManager'MoneyManager'MsUser.mdf;Integrated Security=True;User Instance=True");
        SqlDataAdapter sda = new SqlDataAdapter("Select Count(*) From MsUser where username='"+txtUsername.Text+"' and password='"+txtPassword.Text+"'",cn);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        if (dt.Rows[0][0].ToString()=="1")
            {
                this.Hide();
            }
        else
        {
            MessageBox.Show("your id or password is wrong");
        }
    }

从数据库中获取一个数据单元格

如果你只返回一行，一列（如SELECT COUNT(*)），请使用 SqlCommand 及其 .ExecuteScalar（） 方法 - 无需仅针对单个值而产生 DataTable 的所有开销！

使用类似这样的东西：

private void btnLogin_Click(object sender, EventArgs e)
{
    // define query - and **ALWAYS** use parameters!
    string query = "SELECT COUNT(*) FROM dbo.MsUser WHERE username = @UserName AND password = @password);";
    // set up connection and command
    using (SqlConnection cn = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=E:'ProgII Project'MoneyManager'MoneyManager'MsUser.mdf;Integrated Security=True;User Instance=True"))
    using (SqlCommand cmd = new SqlCommand(query, cn))
    {
        // define parameters and provide values
        cmd.Parameters.Add("@username", SqlDbType.VarChar, 100).Value = txtUserName.Text;
        cmd.Parameters.Add("@password", SqlDbType.VarChar, 100).Value = txtPassword.Text;
        // open connection, execute command, close connection
        cn.Open();
        var result = cmd.ExecuteScalar();
        cn.Close();
        // if result is not null and can be converted to an int....
        if(result != null)
        {
            int userCount;
            if(int.TryParse(result.ToString(), out userCount))
            {
                // OK, you have a good value - if it's > 0, your user entry exists....
                if (userCount > 0)
                {
                    // success - user exists with password
                }
                else 
                {
                    // no success - no such entry
                }
            }
            else
            {
               // you didn't get a numeric value......
            }
        }
        else
        {
            // you didn't get any value......
        }
    }
}

另外：请确保您永远不要在数据库表中以纯文本形式存储密码！

更新：如果需要检索UserId（而不仅仅是计数），请使用如下所示的查询：

string query = "SELECT UserId FROM dbo.MsUser WHERE username = @UserName AND password = @password);";

然后检查您是否返回了有效的UserId（不确定可能是什么数据类型......

var result = cmd.ExecuteScalar();
// checking if we got something (or null)
if (result != null)
{
    string userId = result.ToString();
}
else
{
     // we didn't get any "UserId" back -> invalid combination of "username" and "password"
}

这可能会对您有所帮助，但请注意 Sql 注入
更新了代码以防止 SQL 注入。

private void btnLogin_Click(object sender, EventArgs e)
{
        SqlConnection cn = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=E:'ProgII Project'MoneyManager'MoneyManager'MsUser.mdf;Integrated Security=True;User Instance=True");
        SqlCommand cmd1 = new SqlCommand("Select Count(*) From MsUser where username = @username and password = @passowrd", cn);
        cmd1.Parameters.AddWithValue("@username", idBox.Text.Trim());
        cmd1.Parameters.AddWithValue("@passowrd", passwordBox.Text.Trim());            
        int returnValue = Convert.ToInt32(cmd1.ExecuteScalar());
        if (returnValue == 1)
        {
             this.Hide();
        }
        else
        {
            MessageBox.Show("your id or password is wrong");
        }
 }

假设您的用户表包含每个用户名-密码组合的唯一记录，

private void btnLogin_Click(object sender, EventArgs e)
    {
        string requiredUserId;
        SqlConnection cn = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=E:'ProgII Project'MoneyManager'MoneyManager'MsUser.mdf;Integrated Security=True;User Instance=True");
        SqlDataAdapter sda = new SqlDataAdapter("Select Count(userID),UserId From MsUser where username='"+txtUsername.Text+"' and password='"+txtPassword.Text+"' Group By userID",cn);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        if (dt.Rows[0][0].ToString()=="1")
            {
                this.Hide();
                requiredUserId=dt.Rows[0][1].ToString(); //Use this requiredUserId in your next form!!
            }
        else
        {
            MessageBox.Show("your id or password is wrong");
        }
    }
相关文章：