UDF from VB.NET

[Uri Dimant]

Hello
I'd like to call UDF with parameter from vb.net that retruns datatable , any
ideas?

I have been already trying
Public Function ExecuteUDFReturnDT(ByVal SPName As String, ByRef ParamArr As
spParam()) As DataTable

Dim cmd As New SqlCommand

Dim dt As New DataTable

Dim da As New SqlDataAdapter

Try

Call CheckConnection()

If isConnectionOpen = False Then

Dim ConException As New Exception

Throw ConException

End If

cmd = New SqlCommand(SPName, CN)

cmd.CommandType = CommandType.Text

'

Call AttachParamsToUDF(cmd, ParamArr)

da.SelectCommand = cmd

da.Fill(dt)

Return dt

Catch ex As Exception

Throw ex

Finally

If CN.State <> ConnectionState.Closed Then

CN.Close()

End If

End Try

End Function



Private Function AttachParamsToUDF(ByVal command As SqlCommand, ByRef
ParametersArr As spParam()) As SqlCommand

Dim i As Integer

Dim SpParam As New SqlParameter

Try

For i = 0 To UBound(ParametersArr) - 1 '.Length - 1

command.Parameters.AddWithValue(ParametersArr(i).PName,
ParametersArr(i).PValue)

command.Parameters(ParametersArr(i).PName).DbType =
ParametersArr(i).PDataType

Next

Return command

Catch ex As Exception

Throw ex

End Try

End Function

Thanks

 

[GhostInAK]

Hello Uri,

You call a UDF the same way you call it from Query Analyzer.

SELECT dbo.FunctionName(param1, ParamN)

-Boo