How to retrieve stored procedure return values from TableAdapter


This entry was originally posted on SmartClientData blog.


If you’ve been wondering why you are not able to access stored procedure return values from TableAdapter, here’s the solution for you.


I will use tbTasks table and spInsertTask stored procedure to demonstrate the solution.  You can see definitions for both below.


CREATE TABLE dbo.tbTasks


(


       intID INT NOT NULL IDENTITY(1,1),


       strName VARCHAR(100) NOT NULL,


       intPriority INT NOT NULL,


       dtDueDate DATETIME NOT NULL


)


—————————————-


CREATE PROCEDURE dbo.spInsertTask


       (


              @strName VARCHAR(200),


              @intPriority INT,


              @dtDueDate DATETIME


       )


AS


 


INSERT INTO tbTasks (strName, intPriority, dtDueDate)


VALUES (@strName, @intPriority, @dtDueDate)


 


RETURN SCOPE_IDENTITY()


GO


 


Notice that tbTasks has an identity column named intID.  Also, stored procedure spInsertTask returns the new identity column value using SCOPE_IDENTITY().  Knowing this new identity value is extremely useful on the client side.


Create a new Typed Dataset called TasksDataset and add tbTasks.  Also, add a new query to tbTasksTableAdapter using spInsertTask stored procedure.  When adding a new query, choose ‘A single value’ option.




At this point, you probably would expect that following code would assign the new identity value returned by spInsertTask stored procedure to returnValue variable.


[ VB ]


Dim taTasks As New TasksDatasetTableAdapters.tbTasksTableAdapter


Dim TaskName As String


Dim TaskPriority As Integer


Dim TaskDueDate As Date


Dim returnValue As Integer


 


TaskName = “Test”


TaskPriority = 1


TaskDueDate = Now()


 


returnValue = taTasks.InsertTask(TaskName, TaskPriority, TaskDueDate)


 


[C#]


TasksDatasetTableAdapters.tbTasksTableAdapter taCustomers = new WindowsApplication1.TasksDatasetTableAdapters.tbTasksTableAdapter();


String taskName;


int taskPriority;


DateTime taskDueDate;


int returnValue;


 


taskName = “Test”;


taskPriority = 1;


taskDueDate = System.DateTime.Now;


 


returnValue = taCustomers.InsertTask(taskName, taskPriority, taskDueDate);


 


However, running above code results in System.InvalidOperationException during run-time for VB and “Cannot implicitly convert type ‘int?’ to ‘int’.” compile error for C#.  If you look at what actually gets returned by tbTasksTableAdapter.InsertTask() function, you will understand why above code does not work.  You can find the function from the generated Typed Dataset code, TasksDataset.Designer.vb / TasksDataset.Designer.cs in this case.


[ VB ]


Public Overridable Overloads Function InsertTask(…) As System.Nullable(Of Integer)


   


   


    Dim returnValue As Object


    Try


        returnValue = command.ExecuteScalar


    Finally


       


    End Try


    If ((returnValue Is Nothing) _


                OrElse (returnValue.GetType Is GetType(System.DBNull))) Then


        Return New System.Nullable(Of Integer)


    Else


        Return New System.Nullable(Of Integer)(CType(returnValue, Integer))


    End If


End Function


* C# version omitted since there’s no significant difference.


As you can see from above, what gets returned from InsertTask function is actually the return value of System.Data.SqlClient.SqlCommand.ExecuteScalar() which is the first column of the first row in the result set, or a null reference if the result set is empty, not the return value of the stored procedure.  In this case, InsertTask returns null since the stored procedure does not return any result set.


If you choose ‘No value’ option, System.Data.SqlClient.SqlCommand.ExecuteNonQuery() is used instead.  And the return value of ExecuteNonQuery() is the number of rows affected.  Again, this is not the stored procedure return value.


So, how do you retrieve the stored procedure return value?  Although it’s not immediately obvious, there’s an easy way to access it.  Let’s look at the definition of the command object for the stored procedure.  You can see it from tbTasksTableAdapter.InitCommandCollection() in TasksDataset.Designer.vb / TasksDataset.Designer.cs file.


[ VB]


Private Sub InitCommandCollection()


    Me._commandCollection = New System.Data.SqlClient.SqlCommand(1) {}


    Me._commandCollection(0) = New System.Data.SqlClient.SqlCommand


    Me._commandCollection(0).Connection = Me.Connection


    Me._commandCollection(0).CommandText = “SELECT intID, strName, intPriority, dtDueDate FROM dbo.tbTasks”


    Me._commandCollection(0).CommandType = System.Data.CommandType.Text


    Me._commandCollection(1) = New System.Data.SqlClient.SqlCommand


    Me._commandCollection(1).Connection = Me.Connection


    Me._commandCollection(1).CommandText = “dbo.spInsertTask”


    Me._commandCollection(1).CommandType = System.Data.CommandType.StoredProcedure


    Me._commandCollection(1).Parameters.Add(New System.Data.SqlClient.SqlParameter(“@RETURN_VALUE”, System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, 10, 0, Nothing, System.Data.DataRowVersion.Current, false, Nothing, “”, “”, “”))


    Me._commandCollection(1).Parameters.Add(New System.Data.SqlClient.SqlParameter(“@strName”, System.Data.SqlDbType.VarChar, 200, System.Data.ParameterDirection.Input, 0, 0, Nothing, System.Data.DataRowVersion.Current, false, Nothing, “”, “”, “”))


    Me._commandCollection(1).Parameters.Add(New System.Data.SqlClient.SqlParameter(“@intPriority”, System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, 10, 0, Nothing, System.Data.DataRowVersion.Current, false, Nothing, “”, “”, “”))


    Me._commandCollection(1).Parameters.Add(New System.Data.SqlClient.SqlParameter(“@dtDueDate”, System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input, 23, 3, Nothing, System.Data.DataRowVersion.Current, false, Nothing, “”, “”, “”))


End Sub


* C# version omitted since there’s no significant difference.


You can see from above that parameters collection does actually include @RETURN_VALUE parameter.  It’s the first parameter in the collection.  When the stored procedure is executed, return value from the stored procedure is added to this item in the collection.  So, all we need to do is to retrieve this value after executing InsertTask() function.  To do that, I will add some code to the partial class defined in TasksDataset.vb / TasksDataset.cs.


[ VB ]


Namespace TasksDatasetTableAdapters


    Partial Public Class tbTasksTableAdapter


        Public Function GetReturnValue(ByVal commandIndex As Integer) As Object


            Return Me.CommandCollection(commandIndex).Parameters(0).Value


        End Function


    End Class


End Namespace


 


[ C# ]


namespace WindowsApplication1.TasksDatasetTableAdapters {


    public partial class tbTasksTableAdapter


    {


        public object GetReturnValue(int commandIndex)


        {


            return this.CommandCollection[commandIndex].Parameters[0].Value;


        }


    }


}


 


Since Dataset Designer does not generate partial class structure for TableAdapters, you will have to add above code yourself to partial class file.  The commandIndex parameter is the index of the command object in _commandCollection to retrieve return value from.  You can get that information by looking at tbTasksTableAdapter.InitCommandCollection(). Now, let’s modify the code that was not running to use this new function.


[ VB ]


Dim taTasks As New TasksDatasetTableAdapters.tbTasksTableAdapter


Dim TaskName As String


Dim TaskPriority As Integer


Dim TaskDueDate As Date


Dim returnValue As Integer


 


TaskName = “Test”


TaskPriority = 1


TaskDueDate = Now()


 


taTasks.InsertTask(TaskName, TaskPriority, TaskDueDate)


returnValue = taTasks.GetReturnValue(1)


 


[C#]


TasksDatasetTableAdapters.tbTasksTableAdapter taCustomers = new WindowsApplication1.TasksDatasetTableAdapters.tbTasksTableAdapter();


String taskName;


int taskPriority;


DateTime taskDueDate;


int returnValue;


 


taskName = “Test”;


taskPriority = 1;


taskDueDate = System.DateTime.Now;


 


taCustomers.InsertTask(taskName, taskPriority, taskDueDate);


returnValue = (int)taCustomers.GetReturnValue(1);


 


We pass in 1 as a parameter value to GetReturnValue() since our stored procedure is located at index 1 in _commandCollection.  Above code will correctly retrieve return value from the stored procedure which is the new identity value of intID column.  If you have more than one stored procedures that return something, you can retrieve those return values by calling GetReturnValue() with correct index.


Typed Dataset simplifies data access layer development significantly by generating necessary code for you based on the information you provide via Dataset Designer.  Although generated code covers large number of scenarios, I suggest that you take a close look at generated code and find out how you can extend the functionality of default Typed Dataset.  And definitely let us know how we can improve it to make Typed Dataset more powerful and flexible.


Also, don’t forget to let me know if you have better ways to retrieve return values from stored procedures.  What I suggest here is just one solution and I am sure that you have other solutions that might be more elegant than this.


Young Joo


Comments (8)

  1. You’ve been kicked (a good thing) – Trackback from DotNetKicks.com

  2. says:

    Un amigo me comenta que no puede recuperar los valores de retorno para un procedimiento almacenado, utilizando

  3. Robert says:

    It is very well,But if i do it in the insert , update command of the dataset, how can I do?

  4. Shichao Hu says:

    Hi Robert, if you are using store procedures for insert and update, it should be the same approach. If not, can you give us more details on what you are going to archieve? Some example code would help us to understand more.

    Thanks.

  5. Mithilesh S says:

    This was very helpful for me thnx..

    But I want to know is there any way such that I pass SP name and get the return value from that SP..

  6. XiaoyingGuo says:

    Mithilesh,

    May I understand better what kind of task that you are going to achieve? What are you going to pass the SP name to, and what kind of return value do you want to get and from where?

    Thanks!

  7. Mithilesh S says:

    XiaoyingGuo,

    In the example given by Young Joo we are passing index of the Store Procedure[SP] to get the return value from that SP. But in future if more SP’s are added in Typed Dataset Table, then index which we are passing to get the return value have to be changed every time as index will change as we add new SP’s in Table.

    So I want that instead of passing index , if we can pass name of the SP to get the return value .

    I found one way which is as follows :

    I added another method in partial class which returns me the collection of SP’s. After getting the collection I can get index of the particular SP. Then I pass the index to the GetReturnValue method and get the value.

  8. XiaoyingGuo says:

    Hi Mithilesh,

    Thanks for your clarification! Now I get your point. Actually, you are right. Currently, there’s no single API or other way you can do to feed a SP name and get the return value in one step. The way you find is something you can do here. If you find any issue when you are doing that, please feel free to let me know.

    Thanks,

    Xiaoying