CODEWITHSUNDEEP
c#sqlstored-proceduressqldatareader
JPK
JPK

Reputation: 1364

SQLDataReader with an empty Read after ExecuteReader

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?

Upvotes: 1

Views: 2327

Answers (3)

Hassan
Hassan

Reputation: 5430

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 = "";

Upvotes: 2

st4hoo
st4hoo

Reputation: 2204

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

Upvotes: 1

Mohamed
Mohamed

Reputation: 520

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();

Upvotes: 1

Related Questions