ASP.Net c# -向MySQL查询传递变量

本文关键字：查询 变量 MySQL Net ASP | 更新日期: 2023-09-27 18:16:36

所以我想用MySQL表中的数据创建一个线形图，我已经设法使用下面的代码绘制了一个。

然而，我想传递一个变量'moduleID'到MySQL查询，我已经这样做了，但是，我不确定这是否是最合适的方式。我是否应该传递一个参数，如果是，我该怎么做?

protected void chart(int moduleID)
{
    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    MySqlConnection conn = new MySqlConnection(connStr);
    string comm = "SELECT * FROM scores WHERE module_id=" + moduleID.ToString();
    MySqlDataAdapter dataAdapter = new MySqlDataAdapter(comm, conn);
    DataSet ds = new DataSet();
    Chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
    Chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;
    Chart1.ChartAreas["ChartArea1"].AxisX.Minimum = 1;
    Chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.Enabled = false;
    Chart1.ChartAreas["ChartArea1"].AxisX.Title = "time";
    Chart1.ChartAreas["ChartArea1"].AxisY.Minimum = 0;
    Chart1.ChartAreas["ChartArea1"].AxisY.Maximum = 100;
    Chart1.ChartAreas["ChartArea1"].AxisY.Title = "%";
    Chart1.ChartAreas["ChartArea1"].AxisY.TextOrientation = TextOrientation.Horizontal;
    try
    {
        conn.Open();
        dataAdapter.Fill(ds);
        Chart1.DataSource = ds;
        Chart1.Series["Series1"].YValueMembers = "score";
        Chart1.DataBind();
    }
    catch
    {
        lblError.Text = "Database connection error. Unable to obtain data at the moment.";
    }
    finally
    {
        conn.Close();
    }
}

ASP.Net c# -向MySQL查询传递变量

你是对的。将字符串连接起来形成查询很容易导致SQL注入。使用如下参数:

string comm = "SELECT * FROM scores WHERE module_id=@module_id";
MySqlCommand mySqlCommand = new MySqlCommand(comm,conn);
mySqlCommand.Parameters.Add(new MySqlParameter("@module_id", module_id));
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(mySqlCommand);

您还应该将连接和命令对象包含在using语句中。这将确保资源得到妥善处置。

空的catch也很少有用。你应该先捕获特定的异常，然后捕获对象中的基本异常Exception。使用该对象记录异常信息或在错误消息中显示。这将为您调试应用程序提供帮助。

Step1:创建存储过程

CREATE PROCEDURE SelectScore
(@moduleID NCHAR(50))AS
SELECT * FROM scores WHERE module_id=@moduleID

Step2:从代码

调用存储过程
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr )) {
conn.Open();
// 1.  create a command object identifying the stored procedure
SqlCommand cmd  = new SqlCommand("SelectScore", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@moduleID ", moduleID ));
// execute the command
using (SqlDataReader rdr = cmd.ExecuteReader()) {
    // iterate through results, printing each to console
    while (rdr.Read())
    {
        ..
    }
}
}
相关文章：