是否可以在 .NET MySqlCommand 中使用 MySql 用户定义变量? [英] Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

问题描述

我正在尝试执行当前在 phpMyAdmin 中有效的查询，但在使用 MySqlAdapter 在 .NET 中执行时它不起作用.这是Sql语句.

I'm trying to execute a query that currently works in phpMyAdmin but it does not working when executing it in .NET using the MySqlAdapter. This is the Sql statement.

SELECT @rownum := @rownum +1 rownum, t . *
FROM (
  SELECT @rownum :=0
) r, (
  SELECT DISTINCT
    TYPE FROM `node`
  WHERE TYPE NOT IN ('ad', 'chatroom')
)t     

它使用@rownum 对从我的内部标量查询返回的每个不同的行进行编号.但是如果我在 .NET 中使用它，它会假设 @rownum 是一个参数并抛出异常，因为我没有定义它.

It is using the @rownum to number each distinct row that is returned from my inner scalar query. But if I use it in .NET it's assuming that the @rownum is a parameter and throwing an exception because I didn't define it.

using (var sqlConnection = new MySqlConnection(SOURCE_CONNECTION))
{
    sqlConnection.Open();

    MySqlDataAdapter sqlAdapter = new MySqlDataAdapter(SqlStatement, sqlConnection);

    DataTable table = new DataTable();
    sqlAdapter.Fill(table);
    sqlConnection.Close();

    return table;
}

关于如何解决这个问题的任何想法?或者我可以获得行号的可能方法?

Any ideas for how I could get around this problem? Or possible ways for me to get a line number?

推荐答案

我发现了这个 博客，它告诉您，对于较新版本的 .net 连接器，您必须添加

I found this blog, which tells, that with newer versions of .net Connector you have to add

;Allow User Variables=True

到连接字符串.比较我的 SO 问题 如何我在 .NET MySqlCommand 中使用 MySql 用户定义变量?

to the connection string. Compare my SO question How can I use a MySql User Defined Variable in a .NET MySqlCommand?

这篇关于是否可以在 .NET MySqlCommand 中使用 MySql 用户定义变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！