C#获取sql表列的值并将其放入变量中。

Question

我有一个用户登录菜单。我想根据用户的级别重定向用户。级别数据在SQL表中。我想根据用户名从表中获取级别数据，并将其分配给一个变量。

protected void btnDefault_Click(object sender, EventArgs e)
{
//filter entered text
string strUserName = Tools.checkSQLInjection(txtUserName.Text).Trim();
string strPassword = Tools.checkSQLInjection(txtPassword.Text);
string strError = "";

//Get Dealer Level Value
SqlCommand command = new SqlCommand("SELECT dealerLvl FROM Users where email='" + strUserName + "'");
string strDealerLvl = "dealerLvl".ToString();
int intDealerLvl;
bool isParsable = Int32.TryParse(strDealerLvl, out intDealerLvl);

if (strDealerLvl == "1")
  {  Response.Redirect("/dealers/dashboard"); }
else if (strDealerLvl == "2")
  {  Response.Redirect("/dealers/dashboard-2"); }

Answer 1

您似乎没有检查密码，但这可能是稍后才会出现的。

执行此操作的有效代码存根将如下所示：

DataTable MyTable = new DataTable();
int intDealerLvl = 0;

using (SqlCommand cmdSQL = new SqlCommand("SELECT dealerLv1 FROM Users where email = @meail", 
          new SqlConnection(My.Settings.test3ConnectionString)))
{
    cmdSQL.Parameters.Add("@email", SqlDbType.NVarChar).Value = strUserName;
    cmdSQL.Connection.Open();
    MyTable.Load(cmdSQL.ExecuteReader);
}

if (MyTable.Rows.Count > 0)
    intDealerLvl = MyTable.Rows(0)(0);

switch (intDealerLvl)
{
    case 1:
        {
            Response.Redirect("/dealers/dashboard");
            break;
        }

    case 2:
        {
            Response.Redirect("/dealers/dashboard-2"); 
            break;
        }

    default:
        {
            // no level found - where to go??
            break;
        }
}

但是，不清楚您是否应该检查密码，如果是，那么我们当然会使用以下代码：

DataTable MyTable = new DataTable();
string strSQL;
strSQL = "SELECT dealerLv1 FROM Users where email = @Email and Password = @Pass";

using (SqlCommand cmdSQL = new SqlCommand(strSQL, 
          new SqlConnection(My.Settings.test3ConnectionString)))
{
cmdSQL.Parameters.Add("@email", SqlDbType.NVarChar).Value = strUserName;
cmdSQL.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = strPassword;
cmdSQL.Connection.Open();
MyTable.Load(cmdSQL.ExecuteReader);

if (MyTable.Rows.Count > 0)
    intDealerLvl = MyTable.Rows(0)(0);

Answer 2

            using(SqlCommand command = new SqlCommand("SELECT dealerLvl FROM Users where email= @strUserName", connection))
            {
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@strUserName", strUserName);
                DataSet ds = new DataSet();
                using(SqlDataAdapter da = new SqlDataAdapter(command))
                    da.Fill(ds);
        
                //Get the result of the first row        
                DataRow dr = ds.Tables[0].Rows[0];
        
               //Get the value of the column in the first row        
               string strDealerLvl = dr["dealerLvl"].ToString();    
            }