使用Parameters.AddWithValue传递数据库名称失败 [英] Using Parameters.AddWithValue to pass database name fails

问题描述

我一直在尝试执行登录查询.我认为此功能的主要问题是Parameters.AddWithValue部分，但我并不真正了解出什么问题.

I've been trying to perform a login query. I think my main problem with this function is the Parameters.AddWithValue portion, but don't really understand what is wrong.

以下代码在运行时返回错误:

Following code returns an error when ran:

必须声明表变量"@database"

Must declare the table variable "@database"

代码:

 public static bool clsFuncLogin(string USER, string PASS, 
         string conStr, string strDatabase)
{
    SqlConnection conn = new SqlConnection(
       ConfigurationManager.ConnectionStrings[conStr].ConnectionString);
    conn.Open();


    using (SqlCommand StrQuer = 
         new SqlCommand("SELECT COUNT(*) FROM @database "+ 
            "WHERE Username = @userid AND Password = @password", conn))
    {
        StrQuer.Parameters.AddWithValue("@userid", USER);
        StrQuer.Parameters.AddWithValue("@password", PASS);
        StrQuer.Parameters.AddWithValue("@database", strDatabase);
        int DataQuery = Convert.ToInt32(StrQuer.ExecuteScalar().ToString());
        if (DataQuery == 1)
        {
            System.Web.HttpContext.Current.Session["User"] = USER;
            System.Web.HttpContext.Current.Session["Pass"] = PASS;
            System.Web.HttpContext.Current.Session["loggedIn"] = "True";

            return true;

        }
        else if (DataQuery > 1)
        {
           //to tell if a double is created in the db
           //probably to be removed

            System.Web.HttpContext.Current.Session["Double"] = USER;
            return false;
        }
        else 
        {
            return false;

        }
    }
}

我也以

"SELECT COUNT(*) FROM" + strDatabase + " WHERE Username = " + USER + 
        " AND Password = " + PASS;

但是我被告知那是不好的做法.有什么建议吗?

but I was told that that is bad practice. Any advice?

推荐答案

我从未见过将表名作为参数传递，并且基于其他帖子(此)，我认为这不容易实现...至少不能通过SqlCommand.Parameters来实现.

I've never seen table names passed as a parameter, and based on other posts (this and this for example), I don't think it can be easily done... at least, not via SqlCommand.Parameters.

听起来只有两个表-管理员和普通用户.或者，您可以将布尔值传递给该方法，例如isAdmin，然后根据用户是管理员还是普通用户进行两次查询.

It sounds like there's only two tables - admins and regular users. As an alternative, you could just pass a bool to the method, like isAdmin, then have two queries based on whether the user is an admin or a regular user.

public static bool clsFuncLogin(string user, string pass, string conStr, bool isAdmin)
{
    ...

    var query = isAdmin
        ? "SELECT COUNT(*) FROM ADMIN_TABLE WHERE Username = @userid AND Password = @password"
        : "SELECT COUNT(*) FROM REGULAR_TABLE WHERE Username = @userid AND Password = @password";

    using (var sqlCommand = new SqlCommand(query, conn))
    {
        sqlCommand.Parameters.AddWithValue("@userid", user);
        sqlCommand.Parameters.AddWithValue("@password", pass);

        ...
        ...

这篇关于使用Parameters.AddWithValue传递数据库名称失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！