none
reading multiple result sets returned by stored proc in C# RRS feed

  • Question

  •  

    I have a stored proc that returns 2 result sets and I would like to use the results in my web app. A thread can have multiple posts. One ThreadName returned from syl_Threads can have multiple Post in syl_Posts. When I try to use the datareader I get an invalid cast exeption for the PostID. Stored Proc and C# code are below.

     

    ALTER PROCEDURE [dbo].[syl_ThreadPosts_GetAllByThreadID]

    @ThreadID int

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    BEGIN TRY

     

    SELECT [ThreadName]

    FROM syl_Threads

    WHERE [ThreadID] = @ThreadID

     

    SELECT [PostID],

    [PostTypeID],

    [LanguageID],

    [PostAccessID],

    [UserID],

    [ThreadID],

    [PostParentID],

    FROM [syl_Posts]

    WHERE [ThreadID] = @ThreadID

    RETURN

    END TRY

    BEGIN CATCH

    --Execute LogError_Insert SP

    EXECUTE [dbo].[syl_LogError_Insert];

    --Being in a Catch Block indicates failure.

    --Force RETURN to -1 for consistency (other return values are generated, such as -6).

    RETURN -1

    END CATCH

    END

     

    public static Collection<ThreadPostInfo> GetThreadAndPostsByThreadID(int threadID)

    {

    Collection<ThreadPostInfo> threadPosts = new Collection<ThreadPostInfo>();

    //Name of Stored Procedure.

    string sqlCommand = "syl_ThreadPosts_GetAllByThreadID";

    //Creates a SQL statement or stored procedure to execute against a data source.

    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    //Create a parameter.

    db.AddInParameter(dbCommand, "ThreadID", DbType.Int32, threadID);

    //Invoke a SQL command.

    using (IDataReader dataReader = db.ExecuteReader(dbCommand))

    {

    ThreadPostInfo threadPost = null;

    while (dataReader != null && dataReader.Read())

    {

    threadPost = new ThreadPostInfo(dataReader);

    //Add an post to the threadPost collection.

    threadPosts.Add(threadPost);

    }

    }

    //Return a generic collection of a Thread and Posts by ThreadID.

    return threadPosts;

    }

    Thursday, April 3, 2008 11:50 PM

Answers

All replies

  • In my opinion, it is better to use dataset rather than datareader,

    dataset have datatable that you can manipulate, for example

     

    Code Snippet

    if(ds.tables.count > 0)

    {

    //loop through the first table of the dataset

    foreach(DataRow dr in ds.Tables[0].Rows)

    {

    ThreadPostInfo threadPost = new ThreadPostInfo();

    threadPost.SomeProperty = dr[index].ToString();

    threadPost.SomeProperty = dr[index].ToString();

    threadPosts.Add(threadPost);

    }

    // if there is another table from dataset

    if(ds.Tables.count > 1)

    {

    foreach(Datarow dr1 in ds.Tables[1].Rows)

    {

    ThreadPostInfo threadPost = new ThreadPostInfo();

    threadPost.SomeProperty = dr[index].ToString();

    threadPost.SomeProperty = dr[index].ToString();

    threadPosts.Add(threadPost);

    }

    }

    return theadPosts;

    }

     

     

    hope this will help ...

    Friday, April 4, 2008 2:29 AM
  • you can use dataset..

     

    Code Snippet

    if(ds.Tables.Count > 0)

    {

    foreach(Datarow dr in ds.Tables[0].Rows)

    {

    ThreadPostInfo threadPost = new ThreadPostInfo();

    threadPost.SomeProperty = dr[index].ToString();

    threadPost.SomeProperty = dr[index].ToString();

    threadPosts.Add(threadPost);

    }

    if ds.tables.Count > 1)

    {

    foreach(Datarow dr1 in ds.Tables[1].Rows)

    {

    ThreadPostInfo threadPost = new ThreadPostInfo();

    threadPost.SomeProperty = dr1[index].ToString();

    threadPost.SomeProperty = dr1[index].ToString();

    threadPosts.Add(threadPost);

    }

    }

    return threadPosts;

    }

     

     

     

    i hope this will help.
    Friday, April 4, 2008 2:31 AM
  • I believe you might be looking for IDataReader.NextResult() method.

     

    http://msdn2.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx

    Friday, April 4, 2008 3:19 AM
  •  ecasabuena wrote:

    In my opinion, it is better to use dataset rather than datareader

     

    For completeness, DataTable etc is a pretty lousy option when (as in this example) you are loading into a proper typed object model. It is redundant, and serves no real purpose. It also forces you to load all the data first, rather than dealing with rows as they arrive, which can be a memory hog for bulk processing.

    Friday, April 4, 2008 6:37 AM
  • yo can use sqldatareder and call the result using sqlReader.NextResult();

                    while (sqlReader.Read())
                    {
                        MessageBox.Show ("From first SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));
                    }

                    sqlReader.NextResult();

                    while (sqlReader.Read())
                    {
                        MessageBox.Show("From second SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));
                    }

    full source

    http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm

    lev.

    Saturday, May 4, 2013 10:24 AM