Selecting, Inserting and Updating Relational Data (SQLServer/ADO.NET)

  • Thread starter Thread starter Mike Wilson
  • Start date Start date
M

Mike Wilson

Dear Group,

I have a heirarchical set of database tables, say - "order" and "order_type"
and want to display a series of orders in a grid control, and in place of
the order_type foreign key identifier, I would like a dropdown combo box
(lookup from the "order_type" table) to change the type of the order. I also
need an update command button, a delete row button and also an insert new
row button.

I'm sure this is a very common design pattern, although I can't find any
good tutorials anywhere. Can someone point me in the right direction, as I
want to get the design pattern right from the very beginning.

Also a couple of specific questions:

1) If I use a typed dataset with a SQL query to return the rows, and
databind this to the grid control - how can I override/load the order_type
column to load a combobox lookup dropdown?

2) If I use a typed dataset with a SQL query and the relations set, bound to
the grid control - how can I change the WHERE clause dynamically at runtime?
Since I'm building the dataset at designtime, I can't see how I can restrict
the amount of data queried in memory (example: WHERE customer_name = "bob").

I can do all of the above in ADODc, and with careful use of recordsets; but
I'm sure ADO.NET would be faster for this job!

I'm using ADO.NET 2.0, VB2005 (Professional Edtn) and SQL Server Express.

Many thanks for your help!

Mike
 
Have you tried the Add New Item and DataForm wizard?

So you have a project in vb.net then click File then Add New Item then pick
data form wizard. it will do most of the work for you?

Its very easy to connect a datagrid once you have the logical data sources
identified. You can draw the grid and did you want the drop down list
inside grid elements? Or a separate drop down list?
 
Brad Rogers said:
Have you tried the Add New Item and DataForm wizard?

So you have a project in vb.net then click File then Add New Item then
pick
data form wizard. it will do most of the work for you?
Click to expand...

Not yet (didn't realise it was an option) - I will try this..
Its very easy to connect a datagrid once you have the logical data sources
identified. You can draw the grid and did you want the drop down list
inside grid elements? Or a separate drop down list?
Click to expand...

But I thought that a dataset (a typed one anyway) contains multiple tables
as seperate objects and their rows as seperate objects - I want a literal
SET or view which comprises of a whole series of tables (there are about 13
in all). I'm fairly sure that I can manage to put together something that
works using a read only view, but I want to be able to "add row" and "update
row" - which of course needs to handle referential integrity and work with
the database contraints.

I want the drop down list inside the grid elements - such that one of the
columns always displays a drop down list.

Thanks for your help :)

Cheers!

Mike
 
Right, the DataSet is the whole DataBase, everything you need, tables and
all, just that its in memory.

If you add drop down items on columns, Ive heard its tricky to make it work
or may not be intuitive

A typed dataset allows you to reference tables and fields directly, by name,
without having to reference the underlying collection. Typed datasets are a
custom DataSet that derive from the System.Data.DataSet class.

Imports System.Data
'statements
Public Class dsUtility
Inherits DataSet
'statements
End Class

But now youre defining your tables in Access? If you used the Wizard to
make the database viewer, it should import all tables you select? Im still
learning this also, it seems like database theory is just a very old concept
thats been implemented so many ways we have legacy framework things to deal
with, if it were all new? it wouldnt be this way, Im betcha.

Im thinking about trying to learn OLD database concepts in order to make
better sense of it all.

hth
 
Brad Rogers said:
Right, the DataSet is the whole DataBase, everything you need, tables and
all, just that its in memory.
Click to expand...

I have a couple of issues with that.

Issue #1 is that I could load the whole database into memory, but that would
have huge overheads, would it not? Also, if I can't do that - then I need to
create a whole load of datasets to describe the data and relationship
(customer and customer_type, order and order_type for example), so I have
loads of datasets knocking around.
If you add drop down items on columns, Ive heard its tricky to make it
work
or may not be intuitive
Click to expand...

Should be intuitive enough. My dropdown will say, "open" or "closed". I
reckon I should be able to work out how to make a dataset using a
TableAdapter to fill the information set, but I'm not sure about how this
will insert new records - it all doesn't make much sense to me and I can't
find a clear example anywhere. My datagrid isn't going to want to show ALL
the columns of ALL of the tables in the dataset, since that would be messy.
Also when I add a new row I will need to populate all the columns of all the
tables in the dataset so that the data isn't messy with nulls everywhere.
A typed dataset allows you to reference tables and fields directly, by
name,
without having to reference the underlying collection. Typed datasets are
a
custom DataSet that derive from the System.Data.DataSet class.

Imports System.Data
'statements
Public Class dsUtility
Inherits DataSet
'statements
End Class

But now youre defining your tables in Access? If you used the Wizard to
Click to expand...

Nope, SQL Server Express (2005)
make the database viewer, it should import all tables you select? Im
still
Click to expand...

I'm only importing a small subset of tables each time, yes - just about got
the hang of it.
learning this also, it seems like database theory is just a very old
concept
thats been implemented so many ways we have legacy framework things to
deal
with, if it were all new? it wouldnt be this way, Im betcha.
Click to expand...
Im thinking about trying to learn OLD database concepts in order to make
better sense of it all.
Click to expand...

No problem in ADODc - use a recordset and loop through it. The advantage as
I see things is not having to use an @@IDENTITY function and repeated
insert/update statements to update a series of related tables. I'm assuming
the dataset will handle that for me. I hope.

Mike
 
Back
Top