?
Solved

How to fill a dataset from an SQL user-defined function

Posted on 2004-09-03
17
Medium Priority
?
832 Views
Last Modified: 2008-01-09
I have an SQL inline udf which takes 3 parameters and returns a table of results. (It works in SQL query analyser so I know there's no probs on the SQL end). I'm trying to get the resulting table into a dataview:

DbCommand.CommandText = "SELECT * FROM dbo.[udfMyUDF](1,1,65)"
DbDataAdapter.SelectCommand = DbCommand
DbDataSet = New DataSet
DbDataAdapter.Fill(DbDataSet)

The connection, command & adapter all seem to go through fine but when I do the fill I get 0 rows and no table. Can anyone help ?
0
Comment
Question by:Mamine
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
17 Comments
 

Author Comment

by:Mamine
ID: 11977057
Oops sorry forgot to mention the error message, which is: Missing SourceTable mapping: 'Table'
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11977152
wouldnt you want to use executescalar ? your function is returning a value no ?
0
 

Author Comment

by:Mamine
ID: 11977331
I thought executescalar was for simple single value results. Will it cope with a table being returned ? And if so how do I get it into a Dataset ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:natloz
ID: 11977384
DbCommand.TableMappings.Add("Table","tblName")

DbDataSet = New DataSet
DbDataSet.Tables.Add("tblName")
DbDataAdapter.Fill(DbDataSet, "tblName")

0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11977395
it should default to "table1" no ? perhaps because its a table object being returned from the function the dataadapter is going a bit nuts ?
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977398
You shouldn't use Scalar for this because it is an entire Result Set you are seeking.
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977405
All I know...is when I create datasets in this way (Like when importing from Excel...I need to define the TableMappings as shown above.
0
 

Author Comment

by:Mamine
ID: 11977770
In this:

DbCommand.TableMappings.Add("Table","tblName")
DbDataSet = New DataSet
DbDataSet.Tables.Add("tblName")
DbDataAdapter.Fill(DbDataSet, "tblName")

is the tblName the name of the table in the sql database ? If so, I can't use it because the user-defined function is putting together a table from several related tables.... unless there is a way of naming the output table from the udf ?
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977855
No...basically whatever your query returns from DbCommand will be stored in the Table called tblName...in your case the results of your UDF...so you could do...

DbCommand.CommandText = "SELECT * FROM dbo.[udfMyUDF](1,1,65)"
DbCommand.TableMappings.Add("Table","tblUDFResults")
DbDataSet = New DataSet
DbDataSet.Tables.Add("tblUDFResults")
DbDataAdapter.SelectCommand = DbCommand
DbDataAdapter.Fill(DbDataSet, "tblUDFResults")

0
 

Author Comment

by:Mamine
ID: 11977995
that sounded like it might've solved the problem but if I type in:

DbCommand.TableMappings.Add("Table","tblUDFResults")

I get a syntax error: TableMappings is not a member of DbCommand. I looked up TableMappings and MSHelp said it was a member of DataAdapter, so I did this instead:

DbCommand.CommandText = "SELECT * FROM dbo.[udfMyUDF](1,1,65)"
DbDataAdapter.TableMappings.Add("Table", "tblUDFResults")
DbDataSet = New DataSet
DbDataSet.Tables.Add("tblUDFResults")
DbDataAdapter.SelectCommand = DbCommand
DbDataAdapter.Fill(DbDataSet, "tblUDFResults")

But got the same error message: Missing SourceTable mapping: 'tblUDFResults' <groan!>

So near and yet so far !! This is really frustrating!
0
 

Author Comment

by:Mamine
ID: 11978000
Incidentally, what does the first "Table" parameter the TableMappings.Add refer to ?
0
 

Author Comment

by:Mamine
ID: 11978174
Taking another tack ... do I really need a DataAdapter at all ? All I want to do is get the table into a dataset (I don't ever want to update it) ... is there another way to read it in ?
0
 
LVL 4

Expert Comment

by:Javert93
ID: 11978326
Well, if you you want to read the data, you could use an SqlDataReader and populate a DataTable yourself.
0
 

Author Comment

by:Mamine
ID: 11978339
My understanding is that the datareader reads one row at a time - but the udf returns a whole table so I don't see how this will work ..... unless of course you know better ?
0
 

Author Comment

by:Mamine
ID: 11978345
Going back to the mapping problem ... if I generate a typed dataset will that solve it ? (Seems a long way round tho')
0
 
LVL 4

Accepted Solution

by:
Javert93 earned 2000 total points
ID: 11978424
The data reader uses a fast-forward read-only server-side cursor to read the data. Yes, it is true that the cursor only points to one record at a time, but you can "scan" the table using the Read() method. So you could use code similar to the following:

    DbCommand.CommandText = "SELECT * FROM dbo.[udfMyUDF](1,1,65)"
    Dim dr As IDataReader = DbCommand.ExecuteReader()

    Do While dr.Read()
        ' Read the record and add it to the DataTable here
    Loop

It is extremely fast, has much better performance than the DataAdapter (especially when it comes to paging operations), and a data reader gives you the flexability of reading only the records you want, rather than copying everything into a DataSet and then picking what you actually want to use.
0
 
LVL 4

Expert Comment

by:Javert93
ID: 11978428
Oops, forgot to mention that Read() moves the cursor to the next record (not a single column or byte). Check out the documentation in MSDN.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question