DataSet from Stored Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm sure this has been answerd before somewhere and i'm just not
searching on the right keywords, so could someone point me in the
right direction?

I'm using .NET 1.1 and the Enterprise Library June 2005 to build a web
application. The application has numerous drop down list controls for
the user to make their selections through and I thought it would be a
good idea to base these on DataTables held within a DataSet populated
from a Stored Procedure. (If this concept is wrong then stop me at
this point and educate me please!)

The problem I seem to be having is that I can't get the SP to populate
the DataSet.

He're some of my attempts:
Dim db As Database = DatabaseFactory.CreateDatabase("VideoClub")
Dim ds As DataSet = New DataSet
Dim dbc As DBCommandWrapper

' Attempt 1
dbc = db.GetStoredProcCommandWrapper("udpLists")
Call db.LoadDataSet(dbc, ds, "Genre") ' Failed

' Attempt 2
dbc = db.GetSqlStringCommandWrapper("SELECT * FROM tblMyTable")
Call db.LoadDataSet(dbc, ds, "Genre") ' Success

' Attempt 3
dbc = db.GetStoredProcCommandWrapper("udpLists")
ds = db.ExecuteDataSet(dbc) ' Failed

Return ds

Each attempt was run on its own and only the second version returned
any data. Unfortunately it uses hardcoded SQL and I don't like that
idea as I prefer to leave all data selection to SQL Server via SP's.
(again, if i'm off the mark here then please let me know!).

So assuming you've not already corrected me, how can I achieve my aim
of populating a DataSet from a SP? Any suggestions?

Cheers,

<M>
 
There was a bug in the earlier versions of the library you should check for.
The line is something like (not exactly, but something like):

tableName += 1;

I am not sure it is still present by the 2005 library, but it was something
I remarked on early on that kept popping up in new builds. With the bug, you
end up with table mappings like so:

Table
Table1
Table11
Table111

The actual auto named tables are:

Table
Table1
Table2
Table3

Not sure if this is the problem, as I have not perused through the 2005
library, but it is one thing I would look at.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
 
Gregory,

Thanks for the tip. Unfortunately my problem is that I don't actually
get any DataTables in my DataSet, let alone misnamed ones. Following
an extensive trawl through the web I think that my 'Attempt 3' should
be the one that works as Microsofts own examples use this method, but
it's still not working for me.

Any more tips?

<M>
 
Yes, but you aren't going to like it. I have never endorsed the Enterprise
Library as it over-complicates something that's really rather simple to
code. I would use straight ADO.NET code to do this. The DataAdapter Fill
method can manage the connection state and build the table(s) for you. Yes,
you have to setup the Parameters collection on your own but that is not that
hard to do--the code is clearly understood and heavily documented.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
William,

Thanks. Previous solutions that i've built have used the method you've
suggested and I guess that's the way i'll have to go. I thought that
the EL was considered 'best practice' these days (it's been a few
yesrs since I last developed using .NET!), hence why I was attempting
something new in including it in my solution.

Cheers,

<M>
 
Hi M,

I successfully use the EL in various versions now (old and new). Gregory is
right to make a judgment on the compexity of adding it to everything - some
apps don't really benefit from it - but I've become comfortable with it and
find many of the routines very useful in keeping my code easier to read and
maintain.

I think you're issue has been related to mixing up usage of the commands a
bit.

Try this:
Dim db As Database = DatabaseFactory.CreateDatabase("VideoClub")
Dim ds As DataSet = New DataSet
Dim dbc As DBCommandWrapper
Click to expand...

dim myTables() as string
'Add your table names for your various lists to the string array

dbc = db.GetStoredProcCommand("udpLists")
db.LoadDataSet(dbc, ds, myTables)
Return ds
Click to expand...

The third parameter of LoadDataSet can be either one table name, or a list
of table names supplied as a string array.

Hope that helps.

Alec
 
Back
Top