SQL OUTPUT存储过程不与工作的ExecuteReader [英] SQL OUTPUT Stored Procedures not working with ExecuteReader

问题描述

在使用2008年SQL和C＃4.0的存储过程，我无法检索输出信息，并从SELECT语句返回的信息。我不断收到未设置为一个对象的实例对象引用。当我做的ExecuteScalar（）我得到的行，而不是数据。发现几个例子在那里，他们看起来像我在做什么，所以我想我失去了一些东西在我面前简单。谢谢你。

存储过程

<预类= 郎咸平-SQL prettyprint-覆盖> 使用[PhoneDb]
GO
/ ******对象：StoredProcedure的[DBO] [TestPagingProcedure]脚本日期：06 /二千零十一分之一十六8时39分03秒****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [ 。DBO] [TestPagingProcedure]
- 添加参数存储过程在这里
@startRowIndex INT，
@maximumRows INT，
@totalRows INT输出


AS
BEGIN
- SET NOCOUNT ON加入防止额外的结果集从
- 与SELECT语句的干扰。
SET NOCOUNT ON;

DECLARE @first_id UNIQUEIDENTIFIER
DECLARE @startRow INT

设置@startRowIndex =（@startRowIndex - 1）* @maximumRows

如果@startRowIndex = 0
设置@startRowIndex = 1

SET ROWCOUNT @startRowIndex

选择@first_id = ExtensionGUID从ExtItem ORDER BY ExtensionGUID

打印@first_id

SET ROWCOUNT @maximumRows

选择
ExtensionGUID，AesExt，AesHashPassword，ToNumber，AgentExt，名称，JpgImageName，BigImageName，WbmpImageName
从ExtItem WHERE
ExtensionGUID> = @first_id
ORDER BY ExtensionGUID

SET ROWCOUNT 0

- 获取总行

选择@totalRows = COUNT（ExtensionGUID）FROM ExtItem

端


C＃代码

 公共BOOL GetPagedResults（字符串startRowIndex，串maxRows进行，裁判双重totalRowsReturned）
 {
布尔IsSuccess = FALSE; 
串clearPassword =; 
 Log.WriteLine（GetExtList：输入的GETEXTITEM：Log.DEBUG_LEVEL.VERBOSE）; 
的SqlConnection MyConnection的= NULL; 
 EnDecrypt散列器= NULL; 
 
试
 {
如果（SQLLookup.DatabaseString ==）
 {
 Log.WriteLine（GetPagedResults：SQLLookup.DatabaseString是空的： Log.DEBUG_LEVEL.VERBOSE）; 
 SQLLookup.SQLFinder（）; 
 Log.WriteLine（GetPagedResults：SQL FINDER RUN：SQLLookup.DatabaseString：'+ SQLLookup.DatabaseString +'，Log.DEBUG_LEVEL.VERBOSE）; 
} 
 
 Log.WriteLine（GetPagedResults：SQL服务器'+ SQLLookup.DatabaseString +'，Log.DEBUG_LEVEL.VERBOSE）; 
 
 _extItemList.Clear（）; //从只是被追加到现有列表保存新记录。 
 
散列器=新EnDecrypt（SetMyKey，SaltGenerator）; 
 
 //创建一个连接到SQL Server 
 MyConnection的=新的SqlConnection（@数据源=+ SQLLookup.DatabaseString + @;初始目录= PhoneDb;集成安全性=真） ; 
 
的SqlCommand myCommand =新的SqlCommand（TestPagingProcedure，MyConnection的）; 
 myCommand.CommandType = CommandType.StoredProcedure; 
 
 / *分配参数* / 
 myCommand.Parameters.Add（新的SqlParameter（@ startRowIndex，startRowIndex））; 
 myCommand.Parameters.Add（新的SqlParameter（@ maximumRowsmaxRows进行））; 
 myCommand.Parameters.Add（@ totalRows，SqlDbType.Int，4）; 
 myCommand.Parameters [@ totalRows]方向= ParameterDirection.Output。 
 
 
 Log.WriteLine（GetPagedResults：3尝试后，Log.DEBUG_LEVEL.VERBOSE）; 
 Log.WriteLine（GetPagedResults：3 startRowIndex =+ startRowIndex +maxRows进行=+ maxRows进行，Log.DEBUG_LEVEL.VERBOSE）; 
 MyConnection.Open（）; 
 SqlDataReader的读卡器= myCommand.ExecuteReader（）; 
 
 Log.WriteLine（GetPagedResults BEFORE WHILE LOOP，Log.DEBUG_LEVEL.VERBOSE）; 
，而（Reader.Read（））
 {
 / * BUILD EXT项目* / 
 ExtItem extItem =新ExtItem（）; 
如果（Reader.IsDBNull（0）|| Reader.GetGuid（0）== Guid.Empty）
 extItem.ExtensionGUID = Guid.Empty; 
，否则
 extItem.ExtensionGUID = Reader.GetGuid（0）; 
 
如果（Reader.IsDBNull（1）|| Reader.GetString（1）==）
 extItem.AesExt =没有价值; 
，否则
 extItem.AesExt = Reader.GetString（1）; 
 
 
 / *添加项目列出* / 
的AddItem（extItem）; 
 
 //Log.WriteLine(\"GetExtList extItem：+ extItem.ToString（），Log.DEBUG_LEVEL.VERBOSE）; 
} 
 
 //获取总行
 Log.WriteLine（GetPagedResults：页新总数：+（INT）myCommand.Parameters [2] .value的， Log.DEBUG_LEVEL.TERSE）; 
 // totalRowsReturned = myCommand.Parameters [@ totalRows]; 
 
 IsSuccess = TRUE; 
 
 MyConnection.Close（）; 
 Log.WriteLine（GetPagedResults：复古，Log.DEBUG_LEVEL.VERBOSE）; 
} 
 
赶上（异常前）
 {
 Log.WriteLine（GetPagedResults：无法检索扩展列表捕获的异常。+ ex.Message，
 Log.DEBUG_LEVEL.TERSE）; 
 IsSuccess = FALSE; 
} 
 
 MyConnection.Close（）; 
 
返回IsSuccess; 
} 
  

解决方案

据，的 http://msdn.microsoft.com/en-us/library/ms971497 ，你必须在你面前关闭DataReader处理输出参数。

When using a Stored procedure on SQL 2008 and C# 4.0, I am unable to retrieve OUTPUT information and return info from a Select statement. I keep getting "Object reference not set to an instance of an object.". When i do ExecuteScalar() i get the rows, but not the data. Found a few examples out there and they look like what i'm doing, so i think i'm missing something simple in front of me. Thanks.

Stored procedure

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# Code

 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;
    }

解决方案

According to, http://msdn.microsoft.com/en-us/library/ms971497, you must close the datareader before you process the output parameters.

这篇关于SQL OUTPUT存储过程不与工作的ExecuteReader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！