C# ASP 项目使用 MySql 5.5 - 参数查询语法

Question

我的一些查询使用不同的参数化查询语法，我似乎无法计算出 C#、MySql 5.5 的参数化查询的语法。

例如，语法是什么:

SELECT * FROM tablename WHERE (column1 = @param1) AND (column2 = @param2)

@parametername 是声明它们的正确方法吗？之后我只需要:

sqlquery.Parameters.AddWithValue("@param1", 'apples');
sqlquery.Parameters.AddWithValue("@param2", 'oranges');

当使用在 select 子句中使用子查询和别名的查询时，where 子句中需要不同的参数语法吗？

谢谢。

---

编辑:

好的，这是我遇到问题的查询:

SELECT postcode, suburb, streetname, categorycode, DATE_FORMAT(dateRecorded, '%d/%m/%Y') AS 'Date Recorded', DATE_FORMAT(dateLastModified, '%d/%m/%Y') AS 'Date Last Modified', status FROM incidentdetails WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status)

我正在添加参数:

    sqlFillRelated.Parameters.AddWithValue("@postcode", int.Parse(PostcodeTxtBox.Text.ToString()));
    sqlFillRelated.Parameters.AddWithValue("@suburb", SuburbTxtBox.Text.ToString());
    sqlFillRelated.Parameters.AddWithValue("@categorycode", IncidentTypeDropList.Text.ToString());
    sqlFillRelated.Parameters.AddWithValue("@status", "Open");

如果我删除 WHERE 子句，查询将按预期返回表，但似乎没有提取这些参数，因为它总是返回一个空集。我可以准确地复制查询并通过服务器资源管理器毫无问题地运行它，手动添加参数。

    string sqlFILL = "SELECT postcode, suburb, streetname, categorycode, DATE_FORMAT(dateRecorded, '%d/%m/%Y') AS 'Date Recorded', DATE_FORMAT(dateLastModified, '%d/%m/%Y') AS 'Date Last Modified', status FROM incidentdetails WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status)";
    string sql = "SELECT COUNT(*) FROM incidentdetails WHERE (postcode = @postcode) AND (suburb = @suburb) AND (categorycode = @categorycode) AND (status = @status)";
    MySqlConnection mycon = new MySqlConnection(sqlconnection);
    mycon.Open();
    MySqlCommand selectRelatedCmd = new MySqlCommand(sql, mycon);
    MySqlCommand sqlFillRelated = new MySqlCommand(sqlFILL, mycon);
    int matches = 0;
    selectRelatedCmd.Parameters.AddWithValue("@postcode", int.Parse(PostcodeTxtBox.Text.ToString()));
    selectRelatedCmd.Parameters.AddWithValue("@suburb", SuburbTxtBox.Text.ToString());
    selectRelatedCmd.Parameters.AddWithValue("@categorycode",IncidentTypeDropList.Text.ToString());
    selectRelatedCmd.Parameters.AddWithValue("@status", "Open");
    sqlFillRelated.Parameters.AddWithValue("@postcode", int.Parse(PostcodeTxtBox.Text.ToString()));
    sqlFillRelated.Parameters.AddWithValue("@suburb", SuburbTxtBox.Text.ToString());
    sqlFillRelated.Parameters.AddWithValue("@categorycode", IncidentTypeDropList.Text.ToString());
    sqlFillRelated.Parameters.AddWithValue("@status", "Open");


    matches = int.Parse(selectRelatedCmd.ExecuteScalar().ToString());
    if (matches == 0)
    {
        matchingIncidentPanel.Visible = false;  
    }
    else if (matches >= 1)
    {
        matchingIncidentPanel.Visible = true;
    }

    MySqlDataAdapter da = new MySqlDataAdapter(sqlFILL, mycon);
    DataTable table = new DataTable();
    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
    da.Fill(table);
    g.DataSource = table;
    g.DataBind();

    mycon.Close();

Answer 1

MySQL 连接器使用 ?而不是@来标记参数名称。

像这样尝试:

SELECT * FROM tablename WHERE (column1 = ?param1) AND (column2 = ?param2)

MySQL documentation

关于子查询——我知道至少在 MsSQL 中将参数传递给子查询存在一些问题(我认为这根本不可能)所以这可能也适用于 MySQL，但这纯粹是猜测。