Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL OUTPUT Stored Procedures not working with ExecuteReader

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;
    }
like image 524
gcoleman0828 Avatar asked Jun 16 '11 15:06

gcoleman0828


People also ask

How do I get data from ExecuteReader?

To retrieve data using a DataReader, create an instance of the Command object, and then create a DataReader by calling Command. ExecuteReader to retrieve rows from a data source.

How can we return XML data from stored procedure in SQL Server?

1) Duplicate the stored procedure and add the FOR XML to the return statement(not pretty but works). 2) Have a second stored procedure call the non-XML version and just return the results using FOR XML . 3) Build the XML in the SELECT statement of the stored procedure.

What is ExecuteReader ()?

ExecuteReader() Sends the CommandText to the Connection and builds a SqlDataReader. ExecuteReader(CommandBehavior) Sends the CommandText to the Connection, and builds a SqlDataReader using one of the CommandBehavior values.


1 Answers

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

like image 192
Johan Buret Avatar answered Sep 20 '22 14:09

Johan Buret