|
| | | 
StrataFrame User
 
Group: StrataFrame Users
Last Login: 07/09/2008 2:20:16 PM
Posts: 436, Visits: 944 |
| | How do I execute a sql server scalar-valued function from vb.net? Is it just like a stored procedure? |
| |
| | | StrataFrame VIP
Group: StrataFrame Users
Last Login: Today @ 3:26:36 PM
Posts: 1,193, Visits: 3,031 |
| How do I execute a sql server scalar-valued function from vb.net? Is it just like a stored procedure?
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
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
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  I know the enum for the Parameters.Add is wrong, but you should get the idea. |
| |
| | |
StrataFrame User
Group: StrataFrame Users
Last Login: 07/09/2008 2:20:16 PM
Posts: 436, Visits: 944 |
| 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?  |
| |
| | | StrataFrame VIP
Group: StrataFrame Users
Last Login: Today @ 3:26:36 PM
Posts: 1,193, Visits: 3,031 |
| 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:
cmd.CommandText = "Select fn_GetCustomersByCounty(@county)"
where fn_GetCustomersByCounty is your scalar SQL function. That should work. |
| |
| | |
StrataFrame User
Group: StrataFrame Users
Last Login: 07/09/2008 2:20:16 PM
Posts: 436, Visits: 944 |
| | Thats awesome! Thanks Greg... that worked. ;-) |
| |
| | | 
StrataFrame Developer
Group: StrataFrame Developers
Last Login: 08/01/2008 8:53:41 AM
Posts: 2,671, Visits: 1,879 |
| 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.
www.bungie.net |
| |
| | |
StrataFrame Developer
Group: StrataFrame Developers
Last Login: 08/01/2008 8:53:41 AM
Posts: 2,671, Visits: 1,879 |
| Greg's solution also works with more than just scalar values... you can use it with full record sets as well.
www.bungie.net |
| |
| | | StrataFrame VIP
Group: StrataFrame Users
Last Login: Today @ 3:26:36 PM
Posts: 1,193, Visits: 3,031 |
| StarkMike (07/20/2007)
Thats awesome! Thanks Greg... that worked. ;-)
Glad you got it working! |
| |
| | | StrataFrame VIP
Group: StrataFrame Users
Last Login: Today @ 3:26:36 PM
Posts: 1,193, Visits: 3,031 |
| Ben Chase (07/20/2007)
Greg's solution also works with more than just scalar values... you can use it with full record sets as well.
I'm not following you Ben. How would you use ExecuteScalar with a "full record sets"? Doesn't it just return a single value? |
| |
|
|