带有存储过程和参数的 SQL SELECT? [英] SQL SELECT With Stored Procedure and Parameters?

问题描述

我已经编写了大量基于存储过程的 SQL 插入的 Web 服务，但我还没有真正使用过任何 SELECTS.

I've been writing a lot of web services with SQL inserts based on a stored procedure, and I haven't really worked with any SELECTS.

我想到的SELECT很简单.

SELECT COUNT(AD_SID) As ReturnCount FROM AD_Authorization
WHERE AD_SID = @userSID

但是，根据我当前的 INSERT 代码，我无法弄清楚如何将其转换为 SELECT 并返回 ReturnCount 的值>... 你能帮忙吗?这是我的 INSERT 代码:

However, I can't figure out based on my current INSERT code how to make that into a SELECT and return the value of ReturnCount... Can you help? Here is my INSERT code:

string ConnString = "Data Source=Removed";
string SqlString = "spInsertProgress";

using (OleDbConnection conn = new OleDbConnection(ConnString))
{
   using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("attachment_guid", smGuid.ToString());
      cmd.Parameters.AddWithValue("attachment_percentcomplete", fileProgress);
      conn.Open();
      cmd.ExecuteNonQuery();
      conn.Close();
   }
}

推荐答案

这里是你出错的地方:

cmd.ExecuteNonQuery();

您正在执行查询.

您需要ExecuteReader 或ExecuteScalar 代替.ExecuteReader 用于结果集(几行/几列)，ExecuteScalar 当查询返回单个结果(它返回object，所以结果需要转换为正确的类型).

You need to ExecuteReader or ExecuteScalar instead. ExecuteReader is used for a result set (several rows/columns), ExecuteScalar when the query returns a single result (it returns object, so the result needs to be cast to the correct type).

var result = (int)cmd.ExecuteScalar();

results 变量现在将包含 OledbDataReader 或带有 SELECT 结果的值.您可以迭代结果(对于读者)或标量值(对于标量).

The results variable will now hold a OledbDataReader or a value with the results of the SELECT. You can iterate over the results (for a reader), or the scalar value (for a scalar).

这篇关于带有存储过程和参数的 SQL SELECT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！