使用带有 C# 变量的 MySQL 查询 [英] Using a MySQL query with variables from C#

问题描述

在 MySQL Workbench 中，MySQL 查询

In MySQL Workbench the MySQL query

SET @rownum=-1;
SELECT @rownum:=@rownum+1 AS row_num
FROM someTable

返回一个表，其中 row_num 的值从 0 开始，每行增加 1:

returns a table where the value of row_num starts at 0 and goes up by 1 for each row:

+---------+
| row_num |
+---------+
| 0       |
+---------+
| 1       |
+---------+
| 2       |

  ....

我正在尝试从 C# 执行相同的查询.

I am trying to execute the same query from C#.

string connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" +
    "UID=" + uid + ";" + "PASSWORD=" + password + ";" + "ALLOW USER VARIABLES = true;";

MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand command;
MySqlDataAdapter adapter = new MySqlDataAdapter();

connection.Open();
command = connection.CreateCommand();

command.CommandText = "SELECT @rownum := @rownum + 1 AS row_num FROM someTable";
command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;
adapter.SelectCommand = command;

DataTable table = new DataTable();
adapter.Fill(table);

上面的最后一行导致以下 MySqlException

The last line of the above results in the following MySqlException

您的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本以使用正确的语法附近 ':= -1 + 1 AS row_num FROM someTable'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= -1 + 1 AS row_num FROM someTable'

为什么查询在 C# 中不起作用?

Why does the query not work from C#?

推荐答案

删除这些参数行:

command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;

并使用此查询:

command.CommandText = "SET @rownum=-1;SELECT @rownum := @rownum + 1 AS row_num FROM someTable";

您的做法是将@rownum"替换为 -1.所以你最终会得到一个这样的查询:

The way your are doing is both "@rownum" will be replaced with -1. So you end up with a query like this:

SELECT -1:= -1 + 1 AS row_num FROM someTable

这篇关于使用带有 C# 变量的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！