none
How to call and get TableValued function of SQL Server (2005) in VB.net RRS feed

  • Question

  •  

    Please tell me how to call a table valued function of SQL Server (2005) and get its result stored in a Data.DataTable in VB.net

     

    Thanks

     

    Tuesday, July 1, 2008 7:13 PM

Answers

  • Here is a simple example:

     

    Code Snippet

     

    ' Open connection to local SQL Server (pubs).

    Dim conn As New SqlConnection("server=.;database=pubs;integrated security=sspi")

    Dim cmd As New SqlCommand()

    conn.Open()

     

    ' Create a command object to drop and re-create TVF.

    cmd.Connection = conn

    cmd.CommandText = "drop function foo"

    try

    cmd.ExecuteNonQuery()

    catch

    ' Skip exception here, just want to drop foo.

    end try

     

    ' Create our TVF function foo that returns simple result.

    cmd.CommandText = "create function foo() returns table as return (select 123 as f1, 456 as f2)"

    cmd.ExecuteNonQuery()

     

    ' Now call our TVF and fill our dataset.

    ' NOTE select * from foo(), this is the key, must call function using select syntax.

    dim ds as New DataSet()

    dim da as New SqlDataAdapter("select * from foo()",conn)

    da.Fill(ds)

    ' Ok, nothing up my sleeve, dump dataset, should see 123, 456...

    MessageBox.Show(ds.GetXml())

     

     

     

     

    Monday, July 7, 2008 6:21 PM

All replies

  • You could call function same way as you call stored procedures. Here is sample of calling stored procedures

     

    http://support.microsoft.com/kb/308049/en-us

     

    In a case of function CommandType still would be StoredProcedure. If that does not work for you, you would need to post more details here about what you do and what is not workinmg and error message (if you get one)

     

    Wednesday, July 2, 2008 10:00 AM
    Moderator
  • Thanks VMazur,

     

    There are 4 methods and 3 are described. I am interested in frist (which is not described)

    once return rows are stored in table in dataset (method 1) I can easly manuplate and keep

    for some time it rather then reading it forward only (method 2)

     

    Please tell me how can I use first method (store output into a table of a dataset, without reading

    forward only )

     

    Thanks

     

     

    Wednesday, July 2, 2008 4:21 PM
  • You need to use SqlDataAdpater in this case.It allows to fil DataTable or dataSet with the data from the database. You code should look something like

     

        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter

        'Create a connection to the SQL Server.
        MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

        'Create a DataAdapter, and then provide the name of the stored procedure.
        MyDataAdapter = New SqlDataAdapter("GetAuthorsByLastName", MyConnection)

        'Set the command type as StoredProcedure.
        MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

        'Create and add a parameter to Parameters collection for the stored procedure.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@au_lname", _
       SqlDbType.VarChar, 40))

        'Assign the search value to the parameter.
        MyDataAdapter.SelectCommand.Parameters("@au_lname").Value = Trim(txtLastName.Text)

        'Create and add an output parameter to Parameters collection.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", _
        SqlDbType.Int, 4))

        'Set the direction for the parameter. This parameter returns the Rows returned.
        MyDataAdapter.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output

        DS = New DataSet() 'Create a new DataSet to hold the records.
        MyDataAdapter.Fill(DS, "AuthorsByLastName") 'Fill the DataSet with the rows returned.

     

    Thursday, July 3, 2008 9:53 AM
    Moderator
  • Hello VMazur,

     

    In my table valued function, there is not any parameter. The name of TVF is TVF, I am using following code :

     

    Code Snippet

    Private Sub GetResult(ByRef pSQLConnection As SqlClient.SqlConnection)

    Dim da As New SqlClient.SqlDataAdapter("TVF", pSQLConnection)

    da.SelectCommand.CommandType = CommandType.StoredProcedure

    Dim ds As New DataSet

    da.Fill(ds, "Result")

    End Sub

     

     

     

    It is giving an error:

    --------

    The request for procedure 'TVF' failed because 'TVF' is a table valued function object.

    ---------

     

    at last line of GetResult procedure [ da.Fill(ds),"Result")  ]

     

     

    Thanks

     

    Thursday, July 3, 2008 5:16 PM
  • Here is a simple example:

     

    Code Snippet

     

    ' Open connection to local SQL Server (pubs).

    Dim conn As New SqlConnection("server=.;database=pubs;integrated security=sspi")

    Dim cmd As New SqlCommand()

    conn.Open()

     

    ' Create a command object to drop and re-create TVF.

    cmd.Connection = conn

    cmd.CommandText = "drop function foo"

    try

    cmd.ExecuteNonQuery()

    catch

    ' Skip exception here, just want to drop foo.

    end try

     

    ' Create our TVF function foo that returns simple result.

    cmd.CommandText = "create function foo() returns table as return (select 123 as f1, 456 as f2)"

    cmd.ExecuteNonQuery()

     

    ' Now call our TVF and fill our dataset.

    ' NOTE select * from foo(), this is the key, must call function using select syntax.

    dim ds as New DataSet()

    dim da as New SqlDataAdapter("select * from foo()",conn)

    da.Fill(ds)

    ' Ok, nothing up my sleeve, dump dataset, should see 123, 456...

    MessageBox.Show(ds.GetXml())

     

     

     

     

    Monday, July 7, 2008 6:21 PM