Datasets - what is the best approach?

[Guest]

(My apologies for posting this on two forums. I have just found out the other
one was the incorrect location)

I am writing a VB.NET 2003 web application to operate on my company's
intranet. It accesses data in an SQL Server database. I have developed a
couple of pages that display data successfully. However, there is one area
that I am having trouble getting a handle on, despite purchasing a couple of
Wrox books.

Up until now, I have used a whole lot of data adapters and datasets to be
able to display my information. The data is stored in a very hierarchical
structured database. I now want to be able to edit, delete and add new
records to most of these tables. The data would be edited in datagrids.

I have seen some information that indicates that having a 'mirror-image' of
the original database stored in a dataset is the way to go, ie this is
retrieved from the database, then modified and then sent back.

My questions are:

Is it best to have a single large dataset mimicking the tables and table
structure in the database?

If so, how is this achieved effectively? I only seem to be able to bring
across a table at a time.

Any help would be greatly appreciated.

Dave.

 

[W.G. Ryan eMVP]

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

(My apologies for posting this on two forums. I have just found out the other
one was the incorrect location)

I am writing a VB.NET 2003 web application to operate on my company's
intranet. It accesses data in an SQL Server database. I have developed a
couple of pages that display data successfully. However, there is one area
that I am having trouble getting a handle on, despite purchasing a couple of
Wrox books.

Up until now, I have used a whole lot of data adapters and datasets to be
able to display my information. The data is stored in a very hierarchical
structured database. I now want to be able to edit, delete and add new
records to most of these tables. The data would be edited in datagrids.

I have seen some information that indicates that having a 'mirror-image' of
the original database stored in a dataset is the way to go, ie this is
retrieved from the database, then modified and then sent back.

My questions are:

Is it best to have a single large dataset mimicking the tables and table
structure in the database?]

---Not sure how you could avoid this if you wanted to. However - you
definitely want to keep your tables as small as possible...

If so, how is this achieved effectively? I only seem to be able to bring
across a table at a time.

Where clauses

Any help would be greatly appreciated.

If you don't need state data - you can use a datareader - but it's not a
panacea. It really depends on the situation. can you tell me a little more
about it?

 

[Guest]

Thanks for the reply.

At the moment, it has a page which is a "Job Register", where the user
selects an "Area" from a dropdownlist, which when chosen then populates
another dropdownlist of "Departments", which when selected populates another
dropdownlist of "Sections". When the Section is chosen, it populates a
datagrid of "Masters", which when chosen populates another datagrid of
"Orders", then it populates "Items" (datagrid), then it populates
"Subscripts" (datagrid).

(The reason I used datagrids is because I can't display more than one field
in a dropdownlist and also because I want the ability to add new, update,
delete records in each of these datagrids)

Once a Subscript has been chosen, a button is selected to "Create an
Estimate", which then takes the user to another page. On this page there is
another hierarchical structure where a dropdownlist is selected, which
populates the next and so on. Once an answer is arrived at, the data can be
added to a "cart" (a datagrid). This page works well, except I am unsure how
to tackle the add new, edit, delete part of the datagrid.

My problems are mainly with the first page mentioned, where I want to add
new, update, delete records in the various datagrids. Because each datagrid
has been populated using an individual dataset, where the SQL is coded based
on the selection of the previous datagrid, I am finding it confusing with how
to then update the database and repopulate the datagrids to reflect the
changes.

Thanks for your help.

Dave.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

(My apologies for posting this on two forums. I have just found out the other
one was the incorrect location)

I am writing a VB.NET 2003 web application to operate on my company's
intranet. It accesses data in an SQL Server database. I have developed a
couple of pages that display data successfully. However, there is one area
that I am having trouble getting a handle on, despite purchasing a couple of
Wrox books.

Up until now, I have used a whole lot of data adapters and datasets to be
able to display my information. The data is stored in a very hierarchical
structured database. I now want to be able to edit, delete and add new
records to most of these tables. The data would be edited in datagrids.

I have seen some information that indicates that having a 'mirror-image' of
the original database stored in a dataset is the way to go, ie this is
retrieved from the database, then modified and then sent back.

My questions are:

Is it best to have a single large dataset mimicking the tables and table
structure in the database?]

---Not sure how you could avoid this if you wanted to. However - you
definitely want to keep your tables as small as possible...

If so, how is this achieved effectively? I only seem to be able to bring
across a table at a time.

Where clauses

Any help would be greatly appreciated.

If you don't need state data - you can use a datareader - but it's not a
panacea. It really depends on the situation. can you tell me a little more
about it?

Dave.

 

[Darious Snell]

A general answer - I am sure you will get more detailed ones as well.

I like to use WHERE clauses in my SQL. As a matter of fact, I love it.
I try to retrieve only the data that is currently relevant to the task at hand.
When I am done with it, I clear my datasets.

How is this accomplished? Look into creating parameter queries.
You will have access to the parameters collection of your select
query through your dataadapter. You basically set the parameters
before executing the query. Then, only the data you need is retrieved.

You can allow a user to edit the data in the dataset and when you are
ready, post the changes back to the database using the hand update/insert/
delete queries stored in the dataadapter.

It is almost always better to retrieve as little data as needed.

Loosely coupled systems with disconnected data sources are
only one way to approach your project. It is usually the correct way.
There may be critical need for direct access to the data with locking
etc.

Also, remember, you don't have to create relationships in your
dataset. You can just use it to store the schema/data for your project
and work with the individual tables. Sometimes you need to create
the relationships. Crystal report projects with master/detail type
reports for instance. In other cases, you can just use one dataset
to store a bunch of disconnected data. You can easiy create
additional elements in your datatable schema and then bind controls
to this "hybrid" dataset.

GL - hope some of this made sense.

 

[Guest]

Thanks for a very straightforward explanation. Well done.

I seem to still be struggling with the best place to declare the DataAdapter
and DataSet and how long the data lasts in them. Will experiment further.

Thanks again.

Dave.