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.