Solved

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

Posted on 2004-09-03
17
827 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
Technology Partners: 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 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sending receiving text messages in vb.net 15 62
FInd Image Control Gridview 3 36
Find date of 2nd Thursday of each month 3 33
Copying from a network share 3 26
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 …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 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