SQLDataReader with an empty Read after ExecuteReader

Question

Please help correct my thinking, I suspect it is either an obvious mistake or a misunderstanding in how to read from a stored procedure.

I am trying to get c# to call a stored procedure and return multiple rows. It works fine for IDs with a single instance if I remove the reader and use a

res = cmd.ExecuteNonQuery();

but in the code below the while part is just skipped and I cannot see why. I have been playing with the code for a while and not having any luck. The field count is zero but there is data in the table?

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    try
    {
        using (SqlCommand cmd = new SqlCommand("dbo.ReadFromArchive", conn))
        {
            cmd.CommandTimeout = 1000; //This should be the max timeout.
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArchiveID", SqlDbType.Char).Value = AD.ArchiveID;

            cmd.Parameters.Add("@DocsKEY", SqlDbType.Char, 36).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@DocsDateKey", SqlDbType.DateTime).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@DocumentType", SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;                  
            cmd.Parameters.Add("@OperationType", SqlDbType.VarChar, 30).Direction = ParameterDirection.Output;                   

            try
            {                                                        
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    AD.AddDocKey((string)cmd.Parameters["@DocsKEY"].Value);
                    AD.AddDateKey((DateTime)cmd.Parameters["@DocsDateKey"].Value);
                    AD.AddDocument((string)cmd.Parameters["@DocumentType"].Value);
                    AD.AddOpType((string)cmd.Parameters["@OperationType"].Value);
                }

                AD.RTKD = res;
                AD.RTMSG = "";
            }
            catch (Exception ex)
            {
                AD.RTMSG = ex.ToString();
                Logger.LogError("1. Error with executing dbo.ReadFromArchive Stored Procedure", ex);
            }
        }
        conn.Close();
    }

    catch (Exception ex)
    {
        AD.RTMSG = ex.ToString();
        Logger.LogError("Error setting up dbo.ReadFromArchive Stored Procedure :", ex);
    }
}

return AD;

The Stored Procedure

ALTER PROCEDURE [dbo].[ReadFromArchive]
(

@ArchiveID      CHAR(36)        ,
@DocsKEY        CHAR(36)        OUT,
@DocsDateKey    DATETIME        OUT,
@DocumentType   VARCHAR(100)    OUT,
@OperationType  VARCHAR(30)     OUT

)
AS
BEGIN

Select @DocsKEY         = DocsKEY       from     dbo.ArchiveData where  ArchiveID = @ArchiveID          
Select @DocsDateKey     = DocsDateKey   from     dbo.ArchiveData where  ArchiveID = @ArchiveID              
Select @DocumentType    = DocumentType  from     dbo.ArchiveData where  ArchiveID = @ArchiveID 
Select @OperationType   = OperationType from     dbo.ArchiveData where  ArchiveID = @ArchiveID 

END

The key I give it(ArchiveID) returns two seperate values when I query the SQL directly without the stored procedure. If I remove the reader read I get a single value out without problem. Now I need to make it iterate because the database will have many values per ID.

No error is thrown it just doesn't give any data back. My understanding is the reader should loop over every row, why is it not? How do I fix this?

why do you want to go for four selects? instead you could take it in a table and return the same to C# — Mohamed
– Mohamed, Commented May 26, 2014 at 11:50
Is SP moddification possible? Do any other components/systems depend on it? — st4hoo
– st4hoo, Commented May 26, 2014 at 12:23
Yes you can modify the SP I am the original author and currently it is only used one other place in the code. I need return one, return many, and return all that match two parameters. The primary key is two paramaters ArchiveID and DocumentType. — JPK
– JPK, Commented May 26, 2014 at 12:58

Hassan Nisar · Accepted Answer · 2014-05-26 12:28:05Z

2

You are doing it wrong, you are not reading data from the reader:

It should be something like that in the while loop:

AD.AddDocKey(reader["DocsKEY"].ToString());

UPDATE:

I haven't noticed OUT parameter in the Store Procedure.

Here is MSDN reference on Using a DataReader to Process the RETURN Value and OUTPUT Parameters.

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
reader.Close();

AD.AddDocKey((string)cmd.Parameters["@DocsKEY"].Value);
AD.AddDateKey((DateTime)cmd.Parameters["@DocsDateKey"].Value);
AD.AddDocument((string)cmd.Parameters["@DocumentType"].Value);
AD.AddOpType((string)cmd.Parameters["@OperationType"].Value);
AD.RTKD = res;
AD.RTMSG = "";

edited May 26, 2014 at 12:28

answered May 26, 2014 at 11:48

Hassan Nisar

5,4502 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hassan Nisar Over a year ago

No. I haven't noticed the OUT parameter in SP.

Mohamed · Accepted Answer · 2014-05-26 11:54:35Z

1

Modify you SP

ALTER PROCEDURE [dbo].[ReadFromArchive]
(

@ArchiveID      CHAR(36)   

)
AS
BEGIN

Select DocsKEY, DocsDateKey,DocumentType   ,OperationType  from     dbo.ArchiveData where  ArchiveID = @ArchiveID   

END

And then you can fetch the columns inside the while loop as follows

reader["reader"].ToString();

answered May 26, 2014 at 11:54

Mohamed

5303 silver badges14 bronze badges

8 Comments

Me.Name Over a year ago

@st4hoo He's right though isn't he? In the current setup with out parameters only one value per parameter is returned. The OP wants to read multiple rows returned from a SP, which requires a different setup, such as this, where the parameters become columns

Mohamed Over a year ago

If the database has many values per Id then you can not take them in a @variable, can you?

st4hoo Over a year ago

@Me.Name: Maybe he is right. It depends on answer to the question: "Is SP modiffication possible?".

Me.Name Over a year ago

@st4hoo Agreed, but if not, his query of Now I need to make it iterate because the database will have many values per ID. is not possible with the current sp and modification of the code to using a reader superfluous.

Me.Name Over a year ago

@JonathanP The out parameter will return one value, which is also demonstrated by the need to close the reader before the values can be read: they are the last return values and not per returned row. Although in theory it would be possible to concatenate the values, return those in the parameter, and split them again in code, that would require altering the stored procedure, in which case imho it's far easer to follow Mohameds example and have the stored procedure return a table, which can be read in a reader (or DataTable)

|

st4hoo · Accepted Answer · 2014-05-26 12:02:00Z

1

You can't get output parameters value before closing SqlDataReader. Here is corresponding KB.

answered May 26, 2014 at 12:02

st4hoo

2,21218 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQLDataReader with an empty Read after ExecuteReader

3 Answers 3

1 Comment

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related