FYI TableAdapter and Stored Procedure / Function Return Values

[cjard]

For a long time, some people have been baffled by the fact that when you wire up a TableAdapter to a (in my case Oracle) Function (a Stored Procedure that returns a value), and you tell it "it's a scalar call", calling the code that VS makes for you DOESN'T return the value that is returned by the database function.

Sure, the dataset designer puts a parameter into the collection for you, calls it return value, marks it as a return value, but you don't get access to its value.

This chap, who works for Microsoft, proposed one solution:

Only Passionate People Win : TableAdapter's RETURN_VALUE mystery

Where basically, after we run the function, we go digging through the command's parameters, looking for the return value, and then pull its value out. It's nice and easy thanks to partial classes, but it's an unnecessary headache to work around what would seem to be a deficiency in the dataset designer

The basic problem I had with this approach was that I was SURE I had correctly linked a tableadapter to a function, and got its return value without any of this messing around.

I recently had time to investigate, after struggling with VS2008 for hours and getting nowhere, I went back to VS2005 which (probably by consequence of the weird way you have to get an oracle stored procedure into a tableadapter in the first place) I could actually make work correctly.


Here is a line from the XSD file generated by VS2005's dataset designer:

<DbSource ConnectionRef="ConnectionString (Settings)" 
DbObjectName="GPC.RAZE_GPC" [B]DbObjectType="Function"[/B] 
GenerateShortCommands="True" GeneratorSourceName="ScalarQuery" 
MethodsParameterType="CLR" Modifier="Public" Name="ScalarQuery" 
QueryType="NoData" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
UseOptimisticConcurrency="True" UserGetMethodName="GetDataBy" 
UserSourceName="ScalarQuery">

It describes a tableadapter query that links to a database function that returns a string

And here is the same line, generated by VS 2008's dataset designer when linking to that exact same database function:

<DbSource ConnectionRef="ConnectionString (Web.config)" 
DbObjectName="1.COMMON.PREF_GET."COMMON#1#PREF_GET".GPC.COMMON.PREF_GET" 
[B]DbObjectType="StoredProcedure"[/B] GenerateShortCommands="true" 
GeneratorSourceName="ScalarQuery" MethodsParameterType="CLR" 
Modifier="Public" Name="ScalarQuery" QueryType="NoData" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
 UseOptimisticConcurrency="true" UserGetMethodName="GetDataBy16" UserSourceName="ScalarQuery">

For some reason, VS2008 (+Oracle ODP Client, in case that makes a difference) puts a value of StoredProcedure in the attribute, whereas my VS2005(+Microsoft Oracle Client) puts Function in there..


VS2008's dataset custom tool generates code (dataset1.designer.cs) that looks like this:
(Sorry it's in C# - i believe that it'll suffice for illustrative purposes though)

int returnValue;
try {
  returnValue = command.ExecuteNonQuery();
}
finally {
  if ((previousConnectionState ==global::System.Data.ConnectionState.Closed)) {
    command.Connection.Close();
  }
}
return returnValue;

It returns a return value that is a number result; this would look more at home on an UPDATE query if you wanted to know how many rows you had updated.. It's certainly not our string based return value.

If we edit the DbObjectType="StoredProcedure" to instead be DbObjectType="Function" we now get the generator to make some nice code like:

try {
  command.ExecuteNonQuery();
}
finally {
  if ((previousConnectionState == global::System.Data.ConnectionState.Closed)) {
    command.Connection.Close();
  }
}
if (((command.Parameters[0].Value == null) || (command.Parameters[0].Value.GetType() == typeof(global::System.DBNull)))) {
  return null;
}
else {
  return ((string)(command.Parameters[0].Value));
}

i.e. the custom tool that turns our dataset XSD into code, puts code in to dig out the return value and hand it back to us..



So now in VS2008 I have the following arcane procedure to add a function to a dataset so I can easily get its return value:

If the dataset is empty of any tables, drag ANY random table into the dataset designer, from the server explorer - this works around a "Attempt to read or write protected memory" error that always appears if you try to add an Oracle anything to a dataset that is empty. You can delete your random table later

* Right click the surface and choose New Query
* Sort out the connection string (if needed)
* Choose USE AN EXISTING STORED PROCEDURE
* Choose the function
* Choose "No value - a typed.... blah ... which doesn't return data" (this is totally a red herring; you should ignore completely the content of this page, as it does NOT do what it says on the tin. Pick No Value and move on)
* Correct the name of it (When I use VS2008+ODP it always screws up the name of my function: schema.func becomes SCHEMA#0#FUNC. Correct it to some nioce name you want in your .net code)
* Finish the wizard
* Save the dataset XSD
* RIght click the XSD in Solution Explorer and choose Open With... XML Editor
* Find the DbSource tag, and edit the DbObjetType from "StoredProcedure" to "Function"
* Save the XSD again

If you get bitten by this, you could vote it up:
https://connect.microsoft.com/Visua...e-functions-results-in-broken-code-generation
If you get bitten by this on SQL Server and this post helps solve it too, you should definitely vote it up, adding comment! Cheers

 

[JohnH]

Here are the illustrated VB.Net codes, they were automatically converted by an online translator, I believe that it'll suffice for illustrative purposes though.

Dim returnValue As Integer
Try 
    returnValue = command.ExecuteNonQuery
Finally
    If (previousConnectionState = System.Data.ConnectionState.Closed) Then
        command.Connection.Close
    End If
End Try
Return returnValue

Try 
    command.ExecuteNonQuery
Finally
    If (previousConnectionState = System.Data.ConnectionState.Closed) Then
        command.Connection.Close
    End If
End Try
If ((command.Parameters(0).Value Is Nothing)  _
            OrElse (command.Parameters(0).Value.GetType = GetType(System.DBNull))) Then
    Return Nothing
Else
    Return CType(command.Parameters(0).Value,String)
End If

 

[MMOCK_]

...
* Choose "No value - a typed.... blah ... which doesn't return data" (this is totally a red herring; you should ignore completely the content of this page, as it does NOT do what it says on the tin. Pick No Value and move on)
...

My xsd was correctly generated knowing what I wanted to call was function and not a stored procedure. But I was not getting back data. When I said "no value" per your recommendation, the data I expected was returned to me! LOL, thanks for this help!