ejones ejones - 1 month ago 16
C# Question

TableAdapter always returns null for scalar function in SQL Server 2008

I encountered a weird behavior that I could narrow down in a simple sample project.

I am trying to translate the corresponding visual studio terms from German to English

The components included:
- Visual Studio 2013 Express
- C#
- MSSQL Server 2008 R2
- .NET Framework 4.5
- using DataSet and a Query

The sample function is very simple:

CREATE FUNCTION [dbo].[fTest]
( )
RETURNS INT
AS
BEGIN
RETURN 42
END


Then I included a DataSet and included a Query through the TableAdapter Configuration Assistant using an "existing stored procedure" leaving it with the default settings. Visual Studio has a "Data Preview" Feature on the context menu. There I will receive the correct result value.

Now adding some simple code:

using functionTest.DataSet1TableAdapters;
using System;

namespace functionTest
{
class Program
{
static void Main(string[] args)
{
QueriesTableAdapter qta = new QueriesTableAdapter();
object result = qta.fTest();
int? resI = (int?)result;
Console.WriteLine("result: " + resI);
}
}
}


Strangely result will always be null.

EDIT:

the generated TableAdapter Code:

namespace functionTest.DataSet1TableAdapters {


/// <summary>
///Represents the connection and commands used to retrieve and save data.
///</summary>
[global::System.ComponentModel.DesignerCategoryAttribute("code")]
[global::System.ComponentModel.ToolboxItem(true)]
[global::System.ComponentModel.DataObjectAttribute(true)]
[global::System.ComponentModel.DesignerAttribute("Microsoft.VSDesigner.DataSource.Design.TableAdapterDesigner, Microsoft.VSDesigner" +
", Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")]
[global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
public partial class QueriesTableAdapter : global::System.ComponentModel.Component {

private global::System.Data.IDbCommand[] _commandCollection;

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
[global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
protected global::System.Data.IDbCommand[] CommandCollection {
get {
if ((this._commandCollection == null)) {
this.InitCommandCollection();
}
return this._commandCollection;
}
}

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
[global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
private void InitCommandCollection() {
this._commandCollection = new global::System.Data.IDbCommand[1];
this._commandCollection[0] = new global::System.Data.SqlClient.SqlCommand();
((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[0])).Connection = new global::System.Data.SqlClient.SqlConnection(global::functionTest.Properties.Settings.Default.connectionString);
((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[0])).CommandText = "dbo.fTest";
((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[0])).CommandType = global::System.Data.CommandType.StoredProcedure;
((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[0])).Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RETURN_VALUE", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue, 10, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
}

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
[global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
[global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
public virtual object fTest() {
global::System.Data.SqlClient.SqlCommand command = ((global::System.Data.SqlClient.SqlCommand)(this.CommandCollection[0]));
global::System.Data.ConnectionState previousConnectionState = command.Connection.State;
if (((command.Connection.State & global::System.Data.ConnectionState.Open)
!= global::System.Data.ConnectionState.Open)) {
command.Connection.Open();
}
object returnValue;
try {
returnValue = command.ExecuteScalar();
}
finally {
if ((previousConnectionState == global::System.Data.ConnectionState.Closed)) {
command.Connection.Close();
}
}
if (((returnValue == null)
|| (returnValue.GetType() == typeof(global::System.DBNull)))) {
return null;
}
else {
return ((object)(returnValue));
}
}
}
}


EDIT:
another Hint: The internal CommandCollection contains the valid result. I veried this in the debugger and also by exposing the CommandCollection and accessing it:

IDataParameterCollection test = qta._commandCollection[0].Parameters;
SqlParameter param = (SqlParameter) test[0];
int? res = (int?) param.Value;

Answer

It seems, the object returned from the scalar valued function is always null using command.executescalar().

So the returnvalue cannot be retrieved this way. I think it is a MS problem because this is certainly the obvious way of doing it.

A work around is to edit the generated code above as follows:

replace

returnValue = command.ExecuteScalar();

with

command.ExecuteScalar();
returnValue = command.Parameters["@RETURN_VALUE"].Value;

Of course the "@RETURN_VALUE" has to match the name assigned in your tableadapter.

This worked for me... hope it helps.

If you edit the generated code