Solved

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

Posted on 2004-09-03
17
820 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
  • 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now