Freigeben über


How to retrieve stored procedure return values from TableAdapter

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

  • Anonymous
    August 08, 2006
    Good
  • Anonymous
    August 20, 2006
    I have reviewed your example and ask if you could explain how one does this in a web application. I have been pulling out my hair trying to fix this. I have been stuck on this for an entire day. My sql query works fine when tested even using the table adapter "preview data". However, when I try to pull the result into a variable to be displayed in a textbox or any object for that matter on a web form, I experience the problems you detailed in your example. I would greatly appreciate a "translation" of your solution as it relates to a web form. Thank you for sharing your knowledge.
  • Anonymous
    August 21, 2006
    Phil,

    Are you having problem using Typed Dataset in your web application or is the problem specific to accessing return values from stored procedures in your web application?  If you are having trouble using Typed Dataset in generation, there's a great tutorial where you can learn how to build 3-tier application in ASP.NET.  Please check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspnettut/html/aspnet_tutorial01_dataaccesslayer_vb.asp and following series articles.

    - YoungJoo
  • Anonymous
    August 23, 2006
    The comment has been removed
  • Anonymous
    September 10, 2006
    I have read your explaination to retrieve the procedure return value from TableAdapter...I've done almost the same, but in my DataSet.cs (where I should write that partial class of TableAdapter), It does not find any definition for CommandCollection and I can't find why!!
    In DataSet.Designer.cs, InitCommandCollection(), I found the CommandCollection Index I need to use and it's there!
    I would be very glad if you can give me some hint or clue, which where I can find the answer of the error, saying there is no definition for CommandCollection! and so in my form, saying there is no definition for the method I wrote in that partial class of DataSet Designer..!
    Thank you again.
  • Anonymous
    September 23, 2006
    The comment has been removed
  • Anonymous
    September 25, 2006
    I too have been searching for this solution. Your article provides a good method to retrieve the return code. The only thing that concerns me is accessing the command collection by an index. It looks like the Typed Dataset adds commands to the collection alphabetically with the exception of the default method. If I refer to the command by it's index, and latter add a method to the adapter, the index of my command object could change, right? Is there a different/better way to refer to it without the subtle danger of methods getting reordered in the command collection?Thanks!
  • Anonymous
    September 25, 2006
    Hi!Do you have any workarounds, when using QueriesTableAdapter and not the TableAdapter?In this case, the generated class file doesnt have the stored procedure's CommandCollection[x].Parameters[y].Value property, so i cannot create a proper GetReturnValue object like the one in your solution.
  • Anonymous
    September 27, 2006
    TasksDataset.cs

    In response to the above error -- you should cast the parameter class as the return value.  Here is what works:

    [ C# ]

    namespace WindowsApplication1.TasksDatasetTableAdapters {

       public partial class tbTasksTableAdapter

       {

           public object GetReturnValue(int commandIndex)

           {

               return ((System.Data.SqlClient.SqlParameter)this.CommandCollection[commandIndex].Parameters[0]).Value;
           }

       }

    }

  • Anonymous
    October 02, 2006
    Lothan, Could you please copy and paste your partial class code?
  • YoungJoo
  • Anonymous
    October 14, 2006
    The comment has been removed

  • Anonymous
    December 13, 2006
    OK, select scope_identity is all well and fine for sql server, how do you go about getting the new pk value when using sqlce? Since batch statements are not allowed (and there is not scope_identity function in sqlce even if you could batch). I really don't want to have to ride an event to issue another statement to sqlce to get the new value using @@identity.

  • Anonymous
    July 20, 2007
    I am particularly interested in Ben's question about the Procedure Index.  Is there a more symbolic way to accomplish the same thing? Or alternatively could one use a For Next loop comparing the Command.Text to the known procedure name. For each obj in theCommands if obj.CommandText = "dbo.sp_InsertTask" then   blah, blah   return end if next Return Error

  • Anonymous
    August 09, 2007
    I was on this problem for nearly 2 days. consider my situation when you get stuck for inserting a record for that long. I think asp.net guys should put this in their articles section. Thanks again ...

  • Anonymous
    April 20, 2008
    PingBack from http://julio.dishtvnews.com/tableadaptersqlvariable.html

  • Anonymous
    May 10, 2008
    PingBack from http://lillie.freemedianewsnetwork.info/smartretrieve.html

  • Anonymous
    June 08, 2009
    PingBack from http://hairgrowthproducts.info/story.php?id=5475

  • Anonymous
    June 16, 2009
    PingBack from http://fixmycrediteasily.info/story.php?id=17155