Executing Scalar-Valued Function from VB.NET


Author
Message
StarkMike
StarkMike
Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)Advanced StrataFrame User (738 reputation)
Group: Forum Members
Posts: 436, Visits: 944
How do I execute a sql server scalar-valued function from vb.net? Is it just like a stored procedure?
Reply
Greg McGuffey
Greg McGuffey
Strategic Support Team Member (4.8K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
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 BigGrin I know the enum for the Parameters.Add is wrong, but you should get the idea.
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...





Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search