StrataFrame Forum » .NET Forums » General .NET Discussion » Executing Scalar-Valued Function from VB.NET

Executing Scalar-Valued Function from VB.NET

StarkMike — Fri, 20 Jul 2007 19:57:30 GMT

How do I execute a sql server scalar-valued function from vb.net? Is it just like a stored procedure?

RE: Executing Scalar-Valued Function from VB.NET

Greg McGuffey — Fri, 20 Jul 2007 19:57:30 GMT

[quote][b]Ben Chase (07/20/2007)[/b][hr]Greg's solution also works with more than just scalar values... you can use it with full record sets as well.[/quote]

I'm not following you Ben. How would you use ExecuteScalar with a "full record sets"? Doesn't it just return a single value?

RE: Executing Scalar-Valued Function from VB.NET

Greg McGuffey — Fri, 20 Jul 2007 19:55:54 GMT

[quote][b]StarkMike (07/20/2007)[/b][hr]Thats awesome! Thanks Greg... that worked. ;-)[/quote]

:D Glad you got it working!

RE: Executing Scalar-Valued Function from VB.NET

StrataFrame Team — Fri, 20 Jul 2007 08:48:13 GMT

Greg's solution also works with more than just scalar values... you can use it with full record sets as well.

RE: Executing Scalar-Valued Function from VB.NET

StrataFrame Team — Fri, 20 Jul 2007 08:47:34 GMT

Yep, Greg's solution works (just remember to either set the Connection property on the command or call ExecuteScalar(cmd) through a business object) ;) However, if you ever need to return a value from a stored procedure (or multiple values), you can also use output parameters. After you call ExecuteScalar() or ExecuteNonQuery(), the parameters labeled as Direction = Output will have the values assigned by the server. Just FYI.

RE: Executing Scalar-Valued Function from VB.NET

StarkMike — Fri, 20 Jul 2007 07:22:45 GMT

Thats awesome! Thanks Greg... that worked. ;-)

RE: Executing Scalar-Valued Function from VB.NET

Greg McGuffey — Thu, 19 Jul 2007 14:49:59 GMT

Ah...well I hope my previous example helps someone else then.

That is a very good question. I've never tried that before, calling a function from client code. My guess would be that you'd just use the above code, but for the CommandText use something like:

[codesnippet]cmd.CommandText = "Select fn_GetCustomersByCounty(@county)"[/codesnippet]

where fn_GetCustomersByCounty is your scalar SQL function. That should work.

RE: Executing Scalar-Valued Function from VB.NET

StarkMike — Thu, 19 Jul 2007 14:18:52 GMT

Thanks Greg. What I mean is if I create a scalar valued function in SQL Server... how would I execute that function and have its result returned to me in VB? :cool:

RE: Executing Scalar-Valued Function from VB.NET

Greg McGuffey — Thu, 19 Jul 2007 12:50:22 GMT

[quote]How do I execute a sql server scalar-valued function from vb.net? Is it just like a stored procedure?[/quote]

If I understand you correctly, you want to do something like pass in a PK for a table and get the value of one column or get the count of rows that match some criterion. I.e. get a single value back from SQL Server. You'd do this in the appropriate BO. Here is an example

[codesnippet]Public Sub GetCustomerCountByCounty(county As String) As Integer
Dim count As Integer
Using cmd As New SqlCommand()
cmd.CommandText = "Select Count(*) From Customers Where County = @county"
cmd.Parameters.Add("@county",varchar,50)
cmd.Parameters("@county").Value = county
Dim retObj As Object = cmd.ExecuteScalar(cmd)
If retObj IsNot Nothing AndAlso retObj IsNot DBNull.Value Then
count = CType(retObj,Integer)
End If
End Using
Return count
End Sub[/codesnippet]

Take note that I return the value from SQL to an Object, not an integer. In this case, it would likely be safe-ish to return to an integer, as count() return an integer. But if this had been a column, then the return could be Nothing (null is C#) if no record was found, or it could be DBNull.Value if the record was found, but the column was NULL.

I just typed this in, so there may be some errors. I use intellisense a lot :D I know the enum for the Parameters.Add is wrong, but you should get the idea.