SQL Server functions and TableAdapters

[John3]

Hi there,

I have an SQL function (not stored procedure) that returns a single
variable. The function works and I can preview its results in VS. However,
it always returns null when I invoke it in code through my TableAdapter. I'm
reasonably sure it's caused by the underlying call to
"SqlCommand.ExecuteScalar()" which returns "the first column of the first
row in the result set, or a null reference ... if the result set is empty".
Since I'm returning a single variable only, does anyone know how to get it
working so that the latter call to "SqlCommand.ExecuteScalar()" will return
it. Thanks very much.

 

[Erland Sommarskog]

I have an SQL function (not stored procedure) that returns a single
variable. The function works and I can preview its results in VS.
However, it always returns null when I invoke it in code through my
TableAdapter. I'm reasonably sure it's caused by the underlying call to
"SqlCommand.ExecuteScalar()" which returns "the first column of the
first row in the result set, or a null reference ... if the result set
is empty". Since I'm returning a single variable only, does anyone know
how to get it working so that the latter call to
"SqlCommand.ExecuteScalar()" will return it. Thanks very much.

So what it is your CommandText? It's difficult to tell what's going
when we don't see the code.




--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[John3]

So what it is your CommandText? It's difficult to tell what's going

when we don't see the code.

Thanks for the feedback. The code is auto-generated by the Visual Studio
designer (using their wizard) . You can see this below. I simply call it but
I but don't think that's the problem. Note that the "command" variable is
simply the name of my SQL function. "command.Parameters[0].ParameterName" is
set to "@RETURN_VALUE" which may be useful to know. The "returnValue" comes
back null which is the problem. I'm reasonably sure it's because my function
returns a simple variable, given what I mentioned in my first post (I tried
returning it using a SELECT statement but the problem persists). Maybe this
would work using a stored procedure instead of a function since I do that
all the time without issue (though I'm not returning a simple variable in
those cases). Note that my function works since I've tested it and I can
also call it in Visual Studio when I wire this all up in the first place
(using a "preview" button in the wizard). Any ideas would be helpful since
I've searched for hours now with no luck. Thanks again.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public virtual object CalcLegDistance(int legId, bool miles)
{
global::System.Data.SqlClient.SqlCommand command =
this.CommandCollection[1];
command.Parameters[1].Value = ((int)(legId));
command.Parameters[2].Value = ((bool)(miles));
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));
}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 

[John3]

I tried this in a stored procedure instead of a function and it works fine
(by issuing "SELECT @MyVariable" as the final line). I'd like to keep it in
a function if possible however so clearly there's some issue with the way
the data is being returned. Any insight you can provide would be welcome.
Thanks again.

 

[Michael Coles]

What are the contents of this.CommandCollection?

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

So what it is your CommandText? It's difficult to tell what's going
when we don't see the code.

Thanks for the feedback. The code is auto-generated by the Visual Studio
designer (using their wizard) . You can see this below. I simply call it
but I but don't think that's the problem. Note that the "command" variable
is simply the name of my SQL function.
"command.Parameters[0].ParameterName" is set to "@RETURN_VALUE" which may
be useful to know. The "returnValue" comes back null which is the problem.
I'm reasonably sure it's because my function returns a simple variable,
given what I mentioned in my first post (I tried returning it using a
SELECT statement but the problem persists). Maybe this would work using a
stored procedure instead of a function since I do that all the time
without issue (though I'm not returning a simple variable in those cases).
Note that my function works since I've tested it and I can also call it in
Visual Studio when I wire this all up in the first place (using a
"preview" button in the wizard). Any ideas would be helpful since I've
searched for hours now with no luck. Thanks again.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public virtual object CalcLegDistance(int legId, bool miles)
{
global::System.Data.SqlClient.SqlCommand command =
this.CommandCollection[1];
command.Parameters[1].Value = ((int)(legId));
command.Parameters[2].Value = ((bool)(miles));
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));
}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 

[Erland Sommarskog]

Thanks for the feedback. The code is auto-generated by the Visual Studio
designer (using their wizard) . You can see this below. I simply call it
but I but don't think that's the problem. Note that the "command"
variable is simply the name of my SQL function.
"command.Parameters[0].ParameterName" is set to "@RETURN_VALUE" which
may be useful to know. The "returnValue" comes back null which is the
problem.

ExecuteScalar returns the first column of the first result set that
batch produces. But your batch is:

EXEC @RETURN_VALUE = yourfunc @p1, @p2

and does not produce a result set. However, you should find the return
value in command.Parameters[0].Value.

If you want to use return value from ExecuteScalar, you would have
to change the batch to

SELECT dbo.yourfunc(@p1, @p2)

and change the command type to CommandType.Text, and not use
CommandType.StoredProcedure which I assume that you use now.




--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[John3]

Thanks for the feedback. The code is auto-generated by the Visual Studio

designer (using their wizard) . You can see this below. I simply call it
but I but don't think that's the problem. Note that the "command"
variable is simply the name of my SQL function.
"command.Parameters[0].ParameterName" is set to "@RETURN_VALUE" which
may be useful to know. The "returnValue" comes back null which is the
problem.

ExecuteScalar returns the first column of the first result set that
batch produces. But your batch is:

EXEC @RETURN_VALUE = yourfunc @p1, @p2

and does not produce a result set. However, you should find the return
value in command.Parameters[0].Value.

Yes, you're right. That works. The correct value comes back in
Parameters[0]. Ok, I'm seeing how this works now. The question is, do I need
to code this myself. I normally wire everything up on the "DataSet" design
surface in Visual Studio (using the TableAdapter wizard). I don't see how to
automate the call however after doing some experiments. Do you know if it's
possible or am I stuck rolling this myself. Thanks very much.

 

[Erland Sommarskog]

Yes, you're right. That works. The correct value comes back in
Parameters[0]. Ok, I'm seeing how this works now. The question is, do I
need to code this myself. I normally wire everything up on the "DataSet"
design surface in Visual Studio (using the TableAdapter wizard). I don't
see how to automate the call however after doing some experiments. Do
you know if it's possible or am I stuck rolling this myself. Thanks very
much.

Now you are asking me about something I know very little about, that is
Visual Studio. My speciality is in SQL Server and I've learnt the call
API in ADO .Net to get an understanding of it. I only program in C#
occasionally, and when I do I write all code on my own (or peek at
what I did last time). Generally, I dislike having something to generate
my database access code, because I feel that I lose control over it.
But at least VS has the good taste to generate code that you can change
on your own.

But if you want to know if VS can do this better for you, you need to
ask in a Visual Studio forum. Or maybe someone from the C# newsgroup
can fill in.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[John3]

Yes, you're right. That works. The correct value comes back in

Parameters[0]. Ok, I'm seeing how this works now. The question is, do I
need to code this myself. I normally wire everything up on the "DataSet"
design surface in Visual Studio (using the TableAdapter wizard). I don't
see how to automate the call however after doing some experiments. Do
you know if it's possible or am I stuck rolling this myself. Thanks very
much.

Now you are asking me about something I know very little about, that is
Visual Studio. My speciality is in SQL Server and I've learnt the call
API in ADO .Net to get an understanding of it. I only program in C#
occasionally, and when I do I write all code on my own (or peek at
what I did last time). Generally, I dislike having something to generate
my database access code, because I feel that I lose control over it.
But at least VS has the good taste to generate code that you can change
on your own.

But if you want to know if VS can do this better for you, you need to
ask in a Visual Studio forum. Or maybe someone from the C# newsgroup
can fill in.

Ok, thanks for the feedback. I realize it's a SQL Server NG (also
cross-posted to C#) but most SQL Server developers are probably working with
Visual Studio anyway. For those that are, I think it's much cleaner and
safer to use the "TableAdapter" wizard that's already built in
(notwithstanding this particular issue). It's reliable, cleaner, easier, and
much more maintainable than do-it-yourself code (also sets a common MSFT
standard that everyone can follow and generally saves a lot of time rolling
it yourself). Given this issue particular however, it's clearly not an
exhaustive system yet (by MSFT) but still highly configurable just the same
(you can integrate your code into their existing system which I've now
done). Thanks for everyone's help (appreciated).

 

[John3]

Now you are asking me about something I know very little about, that is

Visual Studio. My speciality is in SQL Server and I've learnt the call
API in ADO .Net to get an understanding of it. I only program in C#
occasionally, and when I do I write all code on my own (or peek at
what I did last time). Generally, I dislike having something to generate
my database access code, because I feel that I lose control over it.
But at least VS has the good taste to generate code that you can change
on your own.

But if you want to know if VS can do this better for you, you need to
ask in a Visual Studio forum. Or maybe someone from the C# newsgroup
can fill in.

(Sorry, this was already posted to microsoft.public.dotnet.languages.csharp)

Ok, thanks for the feedback. I realize it's a SQL Server NG (also
cross-posted to C#) but most SQL Server developers are probably working with
Visual Studio anyway. For those that are, I think it's much cleaner and
safer to use the "TableAdapter" wizard that's already built in
(notwithstanding this particular issue). It's reliable, cleaner, easier, and
much more maintainable than do-it-yourself code (also sets a common MSFT
standard that everyone can follow and generally saves a lot of time rolling
it yourself). Given this particular issue however, it's clearly not a
complete system yet (by MSFT) but still highly configurable just the same
(you can integrate your code into their existing system which I've now
done). Thanks for everyone's help (appreciated).