Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

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

Posted on 2004-09-03
17
Medium Priority
?
833 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
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!

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

648 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