ASP.Net c# -基于条件设置MySQL查询和参数

本文关键字：设置 MySQL 查询 参数 条件 Net 于条件 ASP | 更新日期: 2023-09-27 18:15:32

如何根据条件设置MySQL查询和参数?

我想要基于'questionSource'的不同查询，如下所示。

但是，在下面的代码中，'cmd'在当前上下文中不存在。

或者，我可以为每个条件提供两个不同的函数，并根据需要调用必要的函数，但我想一定有一种方法可以在连接中包含条件。

//validation checks
else
{
    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    MySqlConnection conn = new MySqlConnection(connStr);
    string questionSource = Session["QuestionSource"].ToString();
    string cmdText = "";
    if (questionSource.Equals("S"))
    {
        cmdText += @"SELECT COUNT(*) FROM questions Q
                    JOIN users U
                    ON Q.author_id=U.user_id
                    WHERE approved='Y'
                    AND role=1
                    AND module_id=@ModuleID";
        MySqlCommand cmd = new MySqlCommand(cmdText, conn);
        cmd.Parameters.Add("@ModuleID", MySqlDbType.Int32);
        cmd.Parameters["@ModuleID"].Value = Convert.ToInt32(Session["TestModuleID"]);
    }
    else if (questionSource.Equals("U"))
    {
        cmdText += "SELECT COUNT(*) FROM questions WHERE approved='Y' AND module_id=@ModuleID AND author_id=@AuthorID;";
        MySqlCommand cmd = new MySqlCommand(cmdText, conn);
        cmd.Parameters.Add("@ModuleID", MySqlDbType.Int32);
        cmd.Parameters["@ModuleID"].Value = Convert.ToInt32(Session["TestModuleID"]);
        cmd.Parameters.Add("@AuthorID", MySqlDbType.Int32);
        cmd.Parameters["@AuthorID"].Value = Convert.ToInt32(Session["UserID"]);
    }
    int noOfQuestionsAvailable = 0;
    int noOfQuestionsWanted = Convert.ToInt32(ddlNoOfQuestions.SelectedValue);
    try
    {
        conn.Open();
        noOfQuestionsAvailable = Convert.ToInt32(cmd.ExecuteScalar());
        if (noOfQuestionsAvailable < noOfQuestionsWanted)
        {
            lblError.Text = "There are not enough questions available to create a test.";
        }
        else
        {
            Session["TestName"] = txtName.Text;
            Session["NoOfQuestions"] = ddlNoOfQuestions.SelectedValue;
            Session["QuestionSource"] = rblQuestionSource.SelectedValue;
            Session["TestModuleID"] = ddlModules.SelectedValue;
            Response.Redirect("~/create_test_b.aspx");
        }
    }
    catch
    {
        lblError.Text = "Database connection error - failed to get module details.";
    }
    finally
    {
        conn.Close();
    }
}

ASP.Net c# -基于条件设置MySQL查询和参数

在if之前声明cmd 

MySqlCommand cmd = new MySqlCommand("",connStr);

和

cmd.CommandText=cmdText;

其他建议:添加

cmd.Parameters.Add("@ModuleID", MySqlDbType.Int32);
cmd.Parameters["@ModuleID"].Value = Convert.ToInt32(Session["TestModuleID"]);

总是在if之前，因为它在if和else部分的使用方式相同

你只需要将cmd的声明移出if块:

//validation checks
else
{
    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    MySqlConnection conn = new MySqlConnection(connStr);
    string questionSource = Session["QuestionSource"].ToString();
    string cmdText = "";
    MySqlCommand cmd; // <-- here
    if (questionSource.Equals("S"))
    {
        cmdText += @"SELECT COUNT(*) FROM questions Q
                    JOIN users U
                    ON Q.author_id=U.user_id
                    WHERE approved='Y'
                    AND role=1
                    AND module_id=@ModuleID";
        cmd = new MySqlCommand(cmdText, conn); // remove MySqlCommand  here
        cmd.Parameters.Add("@ModuleID", MySqlDbType.Int32);
        cmd.Parameters["@ModuleID"].Value = Convert.ToInt32(Session["TestModuleID"]);
    }
    else if (questionSource.Equals("U"))
    {
        cmdText += "SELECT COUNT(*) FROM questions WHERE approved='Y' AND module_id=@ModuleID AND author_id=@AuthorID;";
        cmd = new MySqlCommand(cmdText, conn); // remove MySqlCommand  here
        cmd.Parameters.Add("@ModuleID", MySqlDbType.Int32);
        cmd.Parameters["@ModuleID"].Value = Convert.ToInt32(Session["TestModuleID"]);
        cmd.Parameters.Add("@AuthorID", MySqlDbType.Int32);
        cmd.Parameters["@AuthorID"].Value = Convert.ToInt32(Session["UserID"]);
    }
    int noOfQuestionsAvailable = 0;
    int noOfQuestionsWanted = Convert.ToInt32(ddlNoOfQuestions.SelectedValue);
    try
    {
        conn.Open();
        noOfQuestionsAvailable = Convert.ToInt32(cmd.ExecuteScalar());
        if (noOfQuestionsAvailable < noOfQuestionsWanted)
        {
            lblError.Text = "There are not enough questions available to create a test.";
        }
        else
        {
            Session["TestName"] = txtName.Text;
            Session["NoOfQuestions"] = ddlNoOfQuestions.SelectedValue;
            Session["QuestionSource"] = rblQuestionSource.SelectedValue;
            Session["TestModuleID"] = ddlModules.SelectedValue;
            Response.Redirect("~/create_test_b.aspx");
        }
    }
    catch
    {
        lblError.Text = "Database connection error - failed to get module details.";
    }
    finally
    {
        conn.Close();
    }
}

只需将MySqlCommand的声明移出if/else块，以便您可以在执行命令

的最后尝试中使用它
//validation checks
else
{
    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    using(MySqlConnection conn = new MySqlConnection(connStr))
    using(MySqlCommand cmd = conn.CreateCommand())
    {
         // Don't need to associate the command to the connection
         // Already done by the CreateCommand above, just need to set
         // the parameters and the command text
         if (questionSource.Equals("S"))
         {
                cmdText = @"....."
                cmd.CommandText = cmdText;
                ....
         }
         else if (questionSource.Equals("U"))
         {
                cmdText = "........."
                cmd.CommandText = cmdText;
                ....
         } 
         try
         {
              conn.Open();
              noOfQuestionsAvailable = Convert.ToInt32(cmd.ExecuteScalar());
               ....
          }
    }
}

还要注意，您应该使用using语句来确保您的连接和命令被正确关闭和处置。

相关文章：