Solved

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

Posted on 2004-09-03
17
826 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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