How to fill a dataset from an inline function ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to read a table into a dataset using an SQL inline function (which
returns a table).

Can anyone tell me how to do it ... have been trying all sorts of
combinations with no luck.
 
Sarah said:
I want to read a table into a dataset using an SQL inline function (which
returns a table).

Can anyone tell me how to do it ... have been trying all sorts of
combinations with no luck.
Click to expand...

The sql that you use should be something like...

select * from :functionName

I believe...haven't done this in a few years though...try it out :) Worth a
shot..

Mythran
 
Thanks for the reply Mythran but that's not quite the problem .... I
understand the SQL parts its the VB end that I'm having probs with.

I can call stored procedures with output parameters fine but can't figure
out how to get the TABLE from the UDF. I cant seem to set up the right
combination of dataadapter/commands etc to get it to work.

Can anyone help ?
 
Sarah said:
Thanks for the reply Mythran but that's not quite the problem .... I
understand the SQL parts its the VB end that I'm having probs with.

I can call stored procedures with output parameters fine but can't figure
out how to get the TABLE from the UDF. I cant seem to set up the right
combination of dataadapter/commands etc to get it to work.

Can anyone help ?
Click to expand...

Off top of my head...

Dim ds As DataSet
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim adap As SqlDataAdapter

conn = New SqlConnection("MyConnectionStringHere")

Try
conn.Open()
cmd = New SqlCommand("SELECT * FROM :MyFunction", conn)
adap = New SqlDataAdapter(cmd)
ds = New DataSet()

adap.Fill(ds, "tblMyFunction")
Finally
conn.Dispose()
End Try

I believe this will work.

Mythran
 
It should fill it correctly. Be sure not to pass a table name to Fill when you
fill and expect more than 1 table. I don't know what the results would be if you
did that.

Mythran
 
I'm not quite sure as I do not rely on udf's for anything. If I can't do it in a
sp or trigger, then I'm probably doing it wrong.

:P Just my way of doing it I guess.

Mythran
 
I'm still getting an error: Missing SourceTable mapping: 'Table' ... I don't
suppose you know what that means do you ? This is driving me crazy.

Thanks for all your help anyway

Sarah
 
Sarah said:
I'm still getting an error: Missing SourceTable mapping: 'Table' ... I
don't
suppose you know what that means do you ? This is driving me crazy.
Click to expand...

Fill your dataTables one at a time with SqlDataAdapter.Fill(DataTable), and
code your select commands to only return one result set.

David
 
This would mean reading in one heck of a lot of data/tables! The reason I'm
using an SQL user-defined function is so I can process and filter the results
so I get one table of results back rather than a massive amount of data in
all the separate/related tables. Should I not be doing this ?
 
Sarah said:
This would mean reading in one heck of a lot of data/tables! The reason
I'm
using an SQL user-defined function is so I can process and filter the
results
so I get one table of results back rather than a massive amount of data in
all the separate/related tables. Should I not be doing this ?
Click to expand...

That's fine. But there's no real performance benifit to returning multiple
result sets in one command or several. You are moving the same amount of
data, and returning resultsets already requires several server round trips
per resultset.

David
 
Back
Top