How much of the functionality of SQL Server is mirrored in ADO.Net

[Guest]

Ok, bear with me. This is a bit lengthy. This roughly explains a real-life
legacy data structure problem I’m having.

Assume a SQL Server 2000 database with 2 tables, Table1 and Table2.

Table1 has fields id1 (Integer, primary key, but NOT an identity field) and
content (varchar 10).

Table2 has fields id1 (integer), id2 (integer, identity field) and content
(varchar 10). The primary key of Table2 is id1/id2.

The 2 tables are joined by a relationship in which table1/id1 is the foreign
key of table2/id1. Also the options are set to cascade updates and deletes.

Now I go into SQL Server Enterprise Manager and:
1. Create a Table1 record with an id1 = 1
2. Create a Table2 record with an id1 = 1
3. I then go back into Table1 and change id1 to a value of 2. The change is
immediately reflected in the Table2 record. That’s exactly what I expected
and wanted.

Now the big question – can I get ADO to do the same thing in memory?

Now I create a Winforms app in VB/Dot Net 2.0. I add a dataset (xsd) to the
project and drag tables 1 and 2 onto the designer simultaneously so that the
relationship comes too. I then create a minimal class inheriting from the
dataset so I can fill Tables 1 and 2 from the server. I add a pair of
DataGridViews to the form to display Tables 1 and 2.

If I update Table1, id1, update to the database and reload the data, the
changes are reflected in Table2 as expected.

What I cannot figure out is how to immediately show the changes in the
Table2 grid without an update. In other words is there a way to make the
system do this?

Am I looking for something that does not exist?

 

[Cowboy \(Gregory A. Beamer\)]

The concept you are talking about here can be done in ADO.NET, but you are
going to have to copy information to a new record, alter the foreign key and
then delete the first. This is also what happens in SQL Server, although you
do not see it. SQL Server works the cascade without your knowledge.

Now, to a more important question: Why?

Primary keys are immutable in most properly designed systtems. Once a
primary key is assigned, it should not be altered. If the primary key also
has human value that has to be altered, then you should add another key and
allow the human alterable bits to be altered at their whim. In every case I
can think of, alteration of primary keys was an indication a fundamental
application/business flaw. Please note that I am accepting that your
application may require primary key changes, but I cannot currently think of
a reason to do this.

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

*************************************************
| Think outside the box!
|
*************************************************

 

[Guest]

First of all, as to 'Why?'. Unfortunately the answer is politics and legacy
data structure. Indeed I may have coded myself into a corner but this is
what the design seems to call for. (Also I'm a new employee here and I
really don't want to tick off management by telling them it's impossible or
insane.)

In the actual application I have 2 DataGridViews on one screen. In the
first there is a combobox that is part of the primary key of the first table.
The second grid displays records from a table whose primary key is mostly
made up of key fields from the first table. Using a bindingsource, the
second grid only displays records related to the currently selected row in
the first grid. So once I change the value of the combobox in the first
grid, any existing child records in the second grid are inaccessable until a
database update is performed.

(To make matters worse the combobox in the first grid is populated from
another table in which it is the primary key. This record has a boolean
field, which if true, means that the record in the first grid cannot have
child records in the second grid, hence I have to code some sort of delete
routine.)

 

[Patrice]

AFAIK you can actually do this automatically using :
http://msdn2.microsoft.com/en-us/library/system.data.foreignkeyconstraint(VS.80).aspx
and check the UpdateRule property in the code sample.

That said I agree with Cowboy that a "true" primary key should never be
updated (basically the idea is that if you can change the PK you can
identify each record at a *particular* time but the record X you have now in
your DB could be something else than the record X that was in your DB 3 days
ago i.e. you can't identify records wihtout refering to when).

Basically the idea is that I say now that this is the record X I don"t have
to say that this is X . If this is a user controlled value you may want to
create a unique index on this colmun and use your own application internal
primary key...

A valid scenario though could be to create client side temporary ids (such
as negavtive value for counters) and update them in cascade when the update
is commited in the DB. Anoher option would be to use a guid.

 

[William Vaughn]

Not to pile on, but I'm with Mr. Beamer. It is a fundamental mistake to
alter a PK. While it's possible to do, the referential and data integrity
issues are manifest. Politics aside, once you incorporate this approach into
your design you pass on a problem that will stick with the application for
the rest of its lifetime.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Guest]

Dear Patrice,

Thanks for the reference. I've managed to make my example work but I'm
still a bit confused.

I started with the assumption that the foreign key constraint already
existed in the xsd. I can see it declared in relations, in the XSD designer
file (or so I thought).

After some frustration trying to extract a existing foreign key constraint,
I gave up and copied the code in the example, dropping the constraint before
the database read (because the fk conflicts with table clears if it already
exists) and recreating the constraint afterwords. Now the grids do exactly
what I want, redisplaying the new keys instantly and updating to the db
correctly.

So do I understand this correctly? Whatever that 'relation' in the designer
may be, it is not a foreign key constraint. Visual Studio XSDs do not
automatically create foreign key constraints. It always has to be done
programmatically?

Thanks.

 

[Cor Ligthert[MVP]]

Not to pile on, but I'm with Mr. Vaughn. (Thanks for [pointing me on an

English phrase Bill, I would have used stack or heap).

However, for me it is even more, today I had a problem where the use of a
GUID instead of an autoidentifier would have made the problem solvable, now
I have to make a workaround.

Cor

 

[Patrice]

Not sure. Your best bet would be likely to check the generated code.

Instead of driopping/creating the constraint you can also just
acativate/deactivate those constraints. Also if tables are cleared in the
correct order iy shiuld likely work...

 

[Guest]

That's just it. So far, I cannot find any reference to a
foreignkeyconstraint in the designer.vb file (if that's what you're referring
to) so I'm guessing that Visual Studio is not automatically creating it, even
though the xsd designer surface is displaying a relationship by that name.
(The foreign key is stored as a 'system.data.datarelation', but as I
mentioned earlier, I haven't been able to find anyway to use that as a
foreignkeyconstraint.)

Could this have something to do with using SQL Server 2000 rather than 2005?

 

[Cowboy \(Gregory A. Beamer\)]

Politics often force decisions that are, otherwise, not a good idea. I have
been there.

You will still have to sync with the database as quickly as possible to make
sure things are up to snuff, but add, link, delete may solve the main
problem.

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

*************************************************
| Think outside the box!
|
*************************************************

 

[Patrice]

In this relation you have a ChildKeyConstraint (that is a
ForeignKeyConstraint) (as well as a ParentKeyConstraint that is a
UniqueConstraint) that should expose UpdateRule, DeleteRule etc...

You may want to rephrase though the exact problem you are trying to solve
now as the introduction was quite general...

Have you tried to change an id in table 1 ? (if I remember cascading is the
default anyway).

 

[Guest]

You may want to rephrase though the exact problem you are trying to solve ...

Actually I have solved the problem by trial and error. I'm just not
completely sure I understand it.

The main problem as I saw it was that I had to display 2 tables in 2 grids
on one screen. Part of the key in the 1st table's record is set by a combo
dropdown in the grid. This same key segment was part of the key for child
records displayed in the second grid. What I was expecting, and what the
boss wanted, was for the key segment in the child records to update instantly
on screen when the parent key value was changed, to display without any
update to the database. This apparently works fine in the database itself.
Part of my confusion, as I mentioned earlier was that I was not sure the
relationship displayed in the xsd was working.

This was my solution: To call this code after every load of datatables.
(Incidentally I also have to drop it before loading data. Otherwise I have
constraint problems, apparently due to the default table clear before fill in
the dataset. But why am I not having the same problem with the relationship
declared through SQL?)

Dim parentColumns(2) As DataColumn
Dim childColumns(2) As DataColumn
Dim fkeyConstraint As ForeignKeyConstraint

parentColumns(0) = Me.Tables("flowcomp").Columns("flow")
childColumns(0) = Me.Tables("fflow").Columns("flow")

parentColumns(1) = Me.Tables("flowcomp").Columns("sid")
childColumns(1) = Me.Tables("fflow").Columns("sid")

parentColumns(2) = Me.Tables("flowcomp").Columns("grp")
childColumns(2) = Me.Tables("fflow").Columns("grp")

fkeyConstraint = New ForeignKeyConstraint( _
"FK_fflow_flowcomp", parentColumns, childColumns)
fkeyConstraint.DeleteRule = Rule.Cascade
fkeyConstraint.UpdateRule = Rule.Cascade

fkeyConstraint.AcceptRejectRule = AcceptRejectRule.Cascade
Me.Tables("fflow").Constraints.Add(fkeyConstraint)
Me.EnforceConstraints = True