使用 SQL 2008 和 C# 4.0 中的存储过程时，我无法从 Select 语句中检索 OUTPUT 信息和返回信息。我一直收到“对象引用未设置为对象的实例”的错误提示。当我执行 ExecuteScalar() 方法时，我可以获得行数，但是没有数据。我在网上找到了一些示例，它们看起来像我正在做的事情，所以我认为我可能忽略了一些简单的东西。谢谢。
存储过程
USE [PhoneDb]
GO
/****** Object: StoredProcedure [dbo].[TestPagingProcedure] Script Date: 06/16/2011 08:39:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TestPagingProcedure]
-- Add the parameters for the stored procedure here
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @first_id UNIQUEIDENTIFIER
DECLARE @startRow int
SET @startRowIndex = (@startRowIndex - 1) * @maximumRows
IF @startRowIndex = 0
SET @startRowIndex = 1
SET ROWCOUNT @startRowIndex
SELECT @first_id = ExtensionGUID FROM ExtItem ORDER BY ExtensionGUID
PRINT @first_id
SET ROWCOUNT @maximumRows
SELECT
ExtensionGUID, AesExt, AesHashPassword, ToNumber, AgentExt, Name, JpgImageName, BigImageName, WbmpImageName
FROM ExtItem WHERE
ExtensionGUID >= @first_id
ORDER BY ExtensionGUID
SET ROWCOUNT 0
-- GEt the total rows
SELECT @totalRows = COUNT(ExtensionGUID) FROM ExtItem
END
C# 代码
public bool GetPagedResults(string startRowIndex, string maxRows, ref double totalRowsReturned)
{
bool IsSuccess = false;
string clearPassword = "";
Log.WriteLine("GetExtList : ENTERED GETEXTITEM: ", Log.DEBUG_LEVEL.VERBOSE);
SqlConnection MyConnection = null;
EnDecrypt hasher = null;
try
{
if (SQLLookup.DatabaseString == "")
{
Log.WriteLine("GetPagedResults : SQLLookup.DatabaseString is empty:", Log.DEBUG_LEVEL.VERBOSE);
SQLLookup.SQLFinder();
Log.WriteLine("GetPagedResults : SQL FINDER RUN: SQLLookup.DatabaseString:'" + SQLLookup.DatabaseString + "'", Log.DEBUG_LEVEL.VERBOSE);
}
Log.WriteLine("GetPagedResults: SQL Server '" + SQLLookup.DatabaseString + "'", Log.DEBUG_LEVEL.VERBOSE);
_extItemList.Clear(); // Keep new records from just being appended to existing list.
hasher = new EnDecrypt("SetMyKey", "SaltGenerator");
// Create a Connection to SQL Server
MyConnection = new SqlConnection(@"Data Source= " + SQLLookup.DatabaseString + @"; Initial Catalog=PhoneDb;Integrated Security=True");
SqlCommand myCommand = new SqlCommand("TestPagingProcedure", MyConnection);
myCommand.CommandType = CommandType.StoredProcedure;
/* ASSIGN PARAMETERS */
myCommand.Parameters.Add(new SqlParameter("@startRowIndex", startRowIndex));
myCommand.Parameters.Add(new SqlParameter("@maximumRows", maxRows));
myCommand.Parameters.Add("@totalRows", SqlDbType.Int, 4);
myCommand.Parameters["@totalRows"].Direction = ParameterDirection.Output;
Log.WriteLine("GetPagedResults:3 After try ", Log.DEBUG_LEVEL.VERBOSE);
Log.WriteLine("GetPagedResults:3 startRowIndex = " + startRowIndex + " maxRows = " + maxRows, Log.DEBUG_LEVEL.VERBOSE);
MyConnection.Open();
SqlDataReader Reader = myCommand.ExecuteReader();
Log.WriteLine("GetPagedResults BEFORE WHILE LOOP", Log.DEBUG_LEVEL.VERBOSE);
while (Reader.Read())
{
/* BUILD EXT ITEM*/
ExtItem extItem = new ExtItem();
if (Reader.IsDBNull(0) || Reader.GetGuid(0) == Guid.Empty)
extItem.ExtensionGUID = Guid.Empty;
else
extItem.ExtensionGUID = Reader.GetGuid(0);
if (Reader.IsDBNull(1) || Reader.GetString(1) == "")
extItem.AesExt = "No value";
else
extItem.AesExt = Reader.GetString(1);
/* ADD ITEM TO LIST */
AddItem(extItem);
//Log.WriteLine("GetExtList extItem: " + extItem.ToString(), Log.DEBUG_LEVEL.VERBOSE);
}
// get the total rows
Log.WriteLine("GetPagedResults: New Total number of pages: " + (int)myCommand.Parameters[2].Value, Log.DEBUG_LEVEL.TERSE);
// totalRowsReturned = myCommand.Parameters["@totalRows"];
IsSuccess = true;
MyConnection.Close();
Log.WriteLine("GetPagedResults: RETURNING:", Log.DEBUG_LEVEL.VERBOSE);
}
catch (Exception ex)
{
Log.WriteLine("GetPagedResults: Unable to retrieve Extension list. Caught Exception " + ex.Message,
Log.DEBUG_LEVEL.TERSE);
IsSuccess = false;
}
MyConnection.Close();
return IsSuccess;
}
SELECT TOP(@maximumRows) ...而不是SET ROWCOUNT。这样查询优化器会知道你只想要前面的几行，并生成一个针对此优化的计划。 - Remus Rusanu