Bloggin in the UK RSS 2.0
 Wednesday, September 07, 2005

When I first started using Typed Datasets I fell into a couple of pitfalls that took me a day to figure out. To prevent some poor soul from suffering like I did here are some notes.

I work almost exclusivly with Stored Procedures, and i'm assuming you already know how to create a typed dataset. I don't like 'myDataSet' type examples so I have create a real world one, this dataset returns a result set with all the addresses associated with a customers proposal so is inventively called ProposalAddresses.

VB.Net 2003 Example Code:




'Declare an instance of the typed dataset you have already created
Dim dsProposalAddress As New ProposalAddresses

'Create a command to pass to the Get SqlDataAdapter function
'We need a SqlDataAdapter in order to populate our Typed Dataset,
'we need a SqlCommand object to tell our SqlDataAdapter where to
'get its data and what parameters to pass to the database.
Dim Command As New SqlClient.SqlCommand("sp_AddressSummary")
'Set the commands properties
Command.CommandType = CommandType.StoredProcedure
'Okay so you will have to create your own SQLConnection Object before reaching this point.
Command.Connection = MyConn
'If your stored procedure takes any parameters create them here.
Dim Proposal As New SqlClient.SqlParameter
Proposal.ParameterName = "@ProposalID"
Proposal.SqlDbType = SqlDbType.Int
Proposal.Value = ProposalID
'Attach your parameter to your command
Command.Parameters.Add(Proposal)
'===============================
'This line creates your SqlDataAdapter using the SqlCommand Object you have just created.
Dim daProposalAddresses As New SqlDataAdapter(Command)

'!! IMPORTANT: With Typed Datasets you must specify
'the additional parameter TableName when calling .Fill or Table(0)
'will not contain any rows and your DS members will not hold any data
daProposalAddresses.Fill(dsProposalAddress, dsProposalAddress.Tables(0).TableName)

'== Test what has been returned in your dataset ==
'This line returns from Row(0) the value in the FullAddress Column
MsgBox(dsProposalAddress.sp_AddressSummary(0).FullAddress)
'This line returns from Row(1) the value in the FullAddress Column
MsgBox(dsProposalAddress.sp_AddressSummary(1).FullAddress)



This code is to demonstrate how to retrieve data from a typed dataset, if you are going to be doing a lot of data access in your application you should consider creating a class that will hide away the above routines and reduce the amount of code you need to write.

Wednesday, September 07, 2005 11:05:08 AM (GMT Standard Time, UTC+00:00)  #    Comments [2] -

Monday, August 07, 2006 9:55:17 PM (GMT Standard Time, UTC+00:00)
Charlie - I've been programming against untyped datasets in VB.net for a while now and decided to finally take on typed datasets. This has been a helpful...and very well documented example that put things into place. I work for myself so finding well documented examples that are easily understood are very valuable to me. Nice work!
Tuesday, August 08, 2006 7:43:00 AM (GMT Standard Time, UTC+00:00)
Hi Jack, thanks for the kudos, typed datasets are quite cool, I have only one complaint if you alter the stored procedure your TD is based on and forget to regenerate it you get a very abstract error message at runtime.
Charlie Barker
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):

Live Comment Preview
Archive
<March 2010>
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2010
Charlie Barker
Sign In
Statistics
Total Posts: 163
This Year: 5
This Month: 1
This Week: 1
Comments: 76
Themes
Pick a theme:
All Content © 2010, Charlie Barker
DasBlog theme 'Business' created by Christoph De Baene (delarou)