How to get Remaining outputs from stored procedure with multiple returns in C#

Question

I am calling a stored procedure from my desktop app and that stored procedure returns multiple results.

But when I store the result into a DataTable, only the very 1st results is filled.
Can someone help me out with this.

The stored procedure that I used is:

ALTER PROCEDURE [dbo].[InsertIntoUserLogsAndImageArchive]
    @UserName NVARCHAR(50), 
    @CompUserName NVARCHAR(50), 
    @CompName NVARCHAR(50), 
    @DateTime NVARCHAR(50),
    @EAN NVARCHAR(50), 
    @Path NVARCHAR(50),
    @FileName NVARCHAR(50),
    @BatchId NVARCHAR(50)
AS
BEGIN
    DECLARE @ULId NVARCHAR(50), @AId NVARCHAR(50)

    INSERT INTO [UserLogs] (UserName, CompUserName, CompName, DateTime) OUTPUT INSERTED.ULId --into @ULId 
    VALUES (@UserName, @CompUserName, @CompName, @DateTime);

    SELECT @ULId = SCOPE_IDENTITY();

    INSERT INTO [ImagesArchive] (EAN, Path, FileName, BatchId, ULId) 
    OUTPUT INSERTED.AId 
    VALUES (@EAN, @Path, @FileName, @BatchId, @ULId);

    SELECT @AId = SCOPE_IDENTITY();

    SELECT ULId, AId 
    FROM ImagesArchive 
    WHERE AId = @AId;
END

And the code snippet to receive data is:

public DataTable ExecuteQuery2(string storedProcedureName, List<SqlParameter> param)
{
    DataTable dt = new DataTable();

    using (SqlConnection cn = GetConnection(""))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;
            cmd.Parameters.AddRange(param.ToArray());                    

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
    }
    return dt;
}

Executing the stored procedure returns following:

I want to get the very last result in my DataTable instead of the 1st one. Although I have other options but I really want to go with this.

Please help!

Possible duplicate of [How do I return multiple result sets with SqlCommand?](https://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand) — GSerg, Aug 05 '19 at 16:37
The return value from the same query will always return same results. Which means either you are querying a different database or the AId in the WHERE is wrong. Like in one place you are using an string and the other place an integer. — jdweng, Aug 05 '19 at 16:41

CarCar · Answer 1 · 2019-08-05T16:52:45.360

2

If you can use a datareader

datatable1.Load(reader)
reader.NextResult();
datatable2.Load(reader)

to get your next table of data.

This should allow you to hit each result set in turn.

edited Aug 05 '19 at 16:52

answered Aug 05 '19 at 16:43

CarCar

680
4
13

And use `DataTable.Load` to load a DataTable from each result. https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.load?view=netframework-4.8 – David Browne - Microsoft Aug 05 '19 at 16:46

score 2 · Accepted Answer · edited Aug 17 '19 at 07:35

Instead of datatable you may use dataset. So you updated code in c# will be

public DataSet ExecuteQuery2(string storedProcedureName, List<SqlParameter> param)
{
    DataSet ds = new DataSet();
    using (SqlConnection cn = GetConnection(""))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;
            cmd.Parameters.AddRange(param.ToArray());                    

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds);
            }
        }
    }
    return ds;
}

By this way you may return all of the records return from your procedure.

Or

If you want to send particular DataTable, then you may update like this.

public DataTable ExecuteQuery2(string storedProcedureName, List<SqlParameter> param)
{
    DataSet ds = new DataSet();

    using (SqlConnection cn = GetConnection(""))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;
            cmd.Parameters.AddRange(param.ToArray());                    

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds);
            }
        }
    }

    return ds.Tables[0];   //// here instead of 0 you may pass the table number you want to return
}

I surely missed that one. Your solution solves my issue. Thanks! — AbhiAbzs, Aug 06 '19 at 06:20

How to get Remaining outputs from stored procedure with multiple returns in C#

2 Answers2