ExamGuru IT/.Net Guru : How to fill a dataset from an inline function ? on microsoft.public.dotnet.framework.adonet

.NETGURU

How to fill a dataset from an inline function ?

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Post a new message to this list...

Sarah (VIP)

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.

Reply to this message...

Mythran

"Sarah" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

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

Reply to this message...

Sarah (VIP)

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 ?

Reply to this message...

Mythran

"Sarah" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

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

Reply to this message...

Sarah (VIP)

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:

[Original message clipped]

Reply to this message...

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

"Sarah" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Sarah (VIP)

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

"Mythran" wrote:

[Original message clipped]

Reply to this message...

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

"Sarah" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Sarah (VIP)

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:

[Original message clipped]

Reply to this message...

David Browne

"Sarah" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

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

David

Reply to this message...

Sarah (VIP)

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:

[Original message clipped]

Reply to this message...

David Browne

"Sarah" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

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

Reply to this message...

	System.Data.DataSet
	System.Data.DataTable
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataAdapter
	System.Web.UI.MobileControls.Command

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification