How to fill a dataset from an inline function ?

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.

Mythran
Fri Aug 20 10:33:22 CDT 2004

"Sarah" <Sarah@discussions.microsoft.com> wrote in message
news:17B29A7D-567F-41CB-95E1-C14C66A6FF17@microsoft.com...
> 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.

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

Sarah
Fri Aug 20 11:45:05 CDT 2004

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 ?

Mythran
Mon Aug 23 10:32:58 CDT 2004


"Sarah" <Sarah@discussions.microsoft.com> wrote in message
news:51CE4603-F57F-46A6-875E-F28C8C9D6F64@microsoft.com...
> 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 ?

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

Sarah
Fri Sep 03 14:49:11 CDT 2004

Also, how do you do the Fill if the udf references more than one table ?

"Mythran" wrote:

>
> "Sarah" <Sarah@discussions.microsoft.com> wrote in message
> news:51CE4603-F57F-46A6-875E-F28C8C9D6F64@microsoft.com...
> > 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 ?
>
> 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
>
>
>

Mythran
Fri Sep 03 15:05:28 CDT 2004

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

"Sarah" <Sarah@discussions.microsoft.com> wrote in message
news:F5802709-F1F0-4C8C-BF21-7B7B68D3AA0E@microsoft.com...
> Also, how do you do the Fill if the udf references more than one table ?
>
> "Mythran" wrote:
>
> >
> > "Sarah" <Sarah@discussions.microsoft.com> wrote in message
> > news:51CE4603-F57F-46A6-875E-F28C8C9D6F64@microsoft.com...
> > > 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 ?
> >
> > 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
> >
> >
> >

Mythran
Fri Sep 03 15:06:16 CDT 2004

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

"Sarah" <Sarah@discussions.microsoft.com> wrote in message
news:E7C5C2B9-2266-4D5B-8EA8-C9C75E9E73CE@microsoft.com...
> Many thanks Mythran - sorry for late reply - was on hols.
>
> Your suggestion works, but the thing that confused me was I expected the
> Command.Type to be StoredProcedure and to set up parameters via sqlparameters
> object whereas you have used a text string and the default Command.Type=Text.
> Is there no support for treating udf's like stored procedures ?
>
> Thanks again
>
> Sarah
>
> "Mythran" wrote:
>
> >
> > "Sarah" <Sarah@discussions.microsoft.com> wrote in message
> > news:51CE4603-F57F-46A6-875E-F28C8C9D6F64@microsoft.com...
> > > 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 ?
> >
> > 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
> >
> >
> >

Sarah
Fri Sep 03 15:35:04 CDT 2004

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

"Mythran" wrote:

> 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
>
> "Sarah" <Sarah@discussions.microsoft.com> wrote in message
> news:F5802709-F1F0-4C8C-BF21-7B7B68D3AA0E@microsoft.com...
> > Also, how do you do the Fill if the udf references more than one table ?
> >
> > "Mythran" wrote:
> >
> > >
> > > "Sarah" <Sarah@discussions.microsoft.com> wrote in message
> > > news:51CE4603-F57F-46A6-875E-F28C8C9D6F64@microsoft.com...
> > > > 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 ?
> > >
> > > 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
> > >
> > >
> > >
>
>
>

David
Fri Sep 03 15:48:07 CDT 2004


"Sarah" <Sarah@discussions.microsoft.com> wrote in message
news:7AB8D1D3-45E9-42B8-A276-64E53234EB18@microsoft.com...
> 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.
>

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

David

Sarah
Fri Sep 03 16:01:03 CDT 2004

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 ?

"David Browne" wrote:

>
> "Sarah" <Sarah@discussions.microsoft.com> wrote in message
> news:7AB8D1D3-45E9-42B8-A276-64E53234EB18@microsoft.com...
> > 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.
> >
>
> Fill your dataTables one at a time with SqlDataAdapter.Fill(DataTable), and
> code your select commands to only return one result set.
>
> David
>
>
>

David
Sat Sep 04 09:08:44 CDT 2004


"Sarah" <Sarah@discussions.microsoft.com> wrote in message
news:F476FE17-30D5-49CA-9173-3749A6D3F611@microsoft.com...
> 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 ?
>

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