C# 从 OleDbCommand 返回值

Return value from OleDbCommand

提问by Cocoa Dev

sqlQuery = "SELECT [ID] from [users] WHERE CallerName=@CallerName";

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
cmd = new OleDbCommand(sqlQuery, conn);
cmd.CommandText = sqlQuery;
cmd.Parameters.Add("@CallerName", OleDbType.VarChar).Value = labelProblemDate.Text.Trim();
cmd.Parameters["@CallerName"].Value = name;
cmd.ExecuteNonQuery();          
conn.Close();

I was told that this is how to read data from a SELECT query using Parameters but it's not working. I think I did something wrong.

有人告诉我，这是使用参数从 SELECT 查询中读取数据的方法，但它不起作用。我想我做错了什么。

I am using WinForms and Microsoft Access 2007

我正在使用 WinForms 和 Microsoft Access 2007

采纳答案by jp2code

It looks like you have your answer, but I wanted to point out a few things from your example code:

看起来你有你的答案，但我想从你的示例代码中指出一些事情：

sqlQuery = "SELECT [ID] from [users] WHERE CallerName=@CallerName";

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
cmd = new OleDbCommand(sqlQuery, conn);
cmd.CommandText = sqlQuery;
cmd.Parameters.Add("@CallerName", OleDbType.VarChar).Value = labelProblemDate.Text.Trim();
cmd.Parameters["@CallerName"].Value = name;
cmd.ExecuteNonQuery();
conn.Close();

First, note that your SQL Query is using Microsoft SQL syntax and that Microsoft Access prefers a slightly different syntax. Instead of wrapping your column names in square brackets, use the tilde mark:

首先，请注意您的 SQL 查询使用的是 Microsoft SQL 语法，而 Microsoft Access 更喜欢稍微不同的语法。不要将列名括在方括号中，而是使用波浪号：

sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=@CallerName";

Next, in your SQL Query, be aware that Microsoft Access does not accept named parameters. Your SQL text above using @CallerNamewill execute with no problem, but all the OleDb object will see is this:

接下来，在您的 SQL 查询中，请注意 Microsoft Access 不接受命名参数。上面 using 的 SQL 文本@CallerName将毫无问题地执行，但所有 OleDb 对象将看到的是：

sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=?";

If, at some point later on, you decide to go with Stored Procedures instead of text SQL, remember to call Prepare()on your OleDbCommandafter adding your parameters and before executing the command.

如果在某一点以后，你决定去与存储过程而不是文字SQL，记得打电话准备（）在您OleDbCommand将您的参数后执行命令前。

If you have multiple parameters, ensure that you add these parameters to your OleDbCommandin the same order that you called them in your SQL text. OleDb does not care what you name them, but you can use them for yourself, to aid you; it is NOT used in the query. @CallerNamewill make no attempt to match up with anything in your SQL text.

如果您有多个参数，请确保OleDbCommand按照您在 SQL 文本中调用它们的相同顺序将这些参数添加到您的参数中。OleDb 不在乎你给它们起什么名字，但你可以自己使用它们，以帮助你；它不在查询中使用。@CallerName不会尝试匹配 SQL 文本中的任何内容。

Next, I wanted to look at your usage of the OleDbParameteritem. In the two lines below, you are adding one (1) parameter to your OleDbCommandwith the value labelProblemDate.Text.Trim()and in the very next line you are re-assigningthat same parameter's value to a variable (that is unknown to us) called name. It does no good for you to declare the parameter with one value then re-assign it to something else.

接下来，我想看看你对这个OleDbParameter项目的使用情况。在下面的两行中，您将一 (1) 个参数添加到您OleDbCommand的值labelProblemDate.Text.Trim() 中，并在下一行中将相同参数的值重新分配给变量（我们不知道） ) 称为name。用一个值声明参数然后将其重新分配给其他值对您没有好处。

You could have used the modified snippet below and gotten the same results (remember to add the size field, as shown below and specified in your database):

您可以使用下面修改后的代码段并获得相同的结果（请记住添加大小字段，如下所示并在您的数据库中指定）：

cmd.Parameters.Add("@CallerName", OleDbType.VarChar, 255).Value = labelProblemDate.Text.Trim();
// cmd.Parameters["@CallerName"].Value = name;

Similarly, your OleDbCommandis being created with your sqlQueryparameter, so specifying your command's CommandTextproperty is unnecessary:

同样，您OleDbCommand是使用sqlQuery参数创建的，因此CommandText无需指定命令的属性：

cmd = new OleDbCommand(sqlQuery, conn);
//cmd.CommandText = sqlQuery;

Finally, as others have said, if you want to query your data as your SQL statement suggest, you must read the data in as opposed to calling ExecuteNonQuery()(notice it is called Non Query).

最后，正如其他人所说，如果您想按照 SQL 语句的建议查询数据，则必须读取数据而不是调用ExecuteNonQuery()（注意它称为Non Query）。

To sum it up, I have written it out here:

总结一下，我写在这里：

sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=?";
int result = 0;
OleDbConnection conn = new OleDbConnection(connectionString);
try {
  conn.Open();
  var cmd = new OleDbCommand(sqlQuery, conn);
  //cmd.CommandText = sqlQuery; This command was specified by your initializer
  cmd.Parameters.Add("?", OleDbType.VarChar, 255).Value = labelProblemDate.Text.Trim();
  //cmd.Parameters["@CallerName"].Value = name; Possible bug here
  using (OleDbDataReader reader = cmd.ExecuteReader())
  {
    if(reader.HasRows)
    {
      reader.Read();
      result = reader.GetInt32(0);
    }
  }
} finally {
  conn.Close();
}
return result;

Always put the Close in a finallyblock in case your program throws an error for any reason. This prevents your application from crashing and leaving the file open. A usingclause, I have found, does not necessarily close a connection when it is done (like they are supposed to do).

始终将 Close 放在一个finally块中，以防您的程序因任何原因引发错误。这可以防止您的应用程序崩溃并使文件保持打开状态。using我发现，一个子句在完成时不一定关闭连接（就像他们应该做的那样）。

I hope this helps. I'm refreshing my knowledge of OleDbat the moment, and wanted to point out a few things.

我希望这有帮助。我正在刷新我目前的知识OleDb，并想指出一些事情。

回答by phoog

If a query returns one value, you can use ExecuteScalarto retrieve the value. ExecuteNonQuerydoesn't return a value from your database; rather, it's intended for use with UPDATEstatements and the like, and it returns the number of rows affected by the statement.

如果查询返回一个值，您可以使用它ExecuteScalar来检索该值。 ExecuteNonQuery不会从您的数据库返回值；相反，它旨在用于UPDATE语句等，并返回受语句影响的行数。

You probably know this, but in general, SELECT queries can return more than one row (and more than one column), so to "read data from a SELECT query", you use ExecuteReaderto get a DbDataReader.

你可能知道这一点，但总的来说，SELECT查询可以“从一个SELECT查询读取数据”返回多行（和超过一列），所以，你用ExecuteReader得到DbDataReader。

回答by Steve

ExecuteNonQuery doesn't return data, only the rows affected by your command
You need to use ExecuteReader with a OleDbDataReader

ExecuteNonQuery 不返回数据，仅返回受命令影响的行
您需要将 ExecuteReader 与 OleDbDataReader 一起使用

OleDbDataReader reader = cmd.ExecuteReader();           
if(reader.HasRows)
{
    reader.Read();
    var result = reader.GetInt32(0);
}

回答by Syed

To Read Data and Load it into DataTable:

读取数据并将其加载到 DataTable 中：

OleDataReader rdr = (OleDataReader) cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr);

To Read Scalor Value Data and Load it into Variable:

读取标量值数据并将其加载到变量中：

int result = (int)cmd.ExecuteScalar(); //Assume scalar value to be return is int

I hope it helps

我希望它有帮助