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

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
Mon Oct 29 08:27:38 PDT 2007

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!
|
*************************************************
"B. Chernick" <BChernick@discussions.microsoft.com> wrote in message
news:FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
> 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?

BChernick
Mon Oct 29 08:57:01 PDT 2007

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.)

> 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. :-)

On the other hand I did not thing about deleting and replacing records in
the second grid on combobox change. I will give that a try. Thanks.


"Cowboy (Gregory A. Beamer)" wrote:

> 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!
> |
> *************************************************
> "B. Chernick" <BChernick@discussions.microsoft.com> wrote in message
> news:FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
> > 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?
>
>
>

Patrice
Mon Oct 29 09:05:05 PDT 2007

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.

--
Patrice
"B. Chernick" <BChernick@discussions.microsoft.com> a écrit dans le message
de news: FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
> 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?

William
Mon Oct 29 10:16:03 PDT 2007

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)
-----------------------------------------------------------------------------------------------------------------------

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in
message news:uqNO8AkGIHA.484@TK2MSFTNGP06.phx.gbl...
> 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! |
> *************************************************
> "B. Chernick" <BChernick@discussions.microsoft.com> wrote in message
> news:FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
>> 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?
>
>

BChernick
Mon Oct 29 11:07:02 PDT 2007

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.

"Patrice" wrote:

> 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.
>
> --
> Patrice
> "B. Chernick" <BChernick@discussions.microsoft.com> a écrit dans le message
> de news: FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
> > 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?
>
>
>

Cor
Mon Oct 29 11:13:05 PDT 2007

> 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
Mon Oct 29 11:17:08 PDT 2007

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...

--
Patrice


"B. Chernick" <BChernick@discussions.microsoft.com> a écrit dans le message
de news: 301FC931-4B25-4BB1-81C7-92F16294C865@microsoft.com...
> 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.
>
> "Patrice" wrote:
>
>> 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.
>>
>> --
>> Patrice
>> "B. Chernick" <BChernick@discussions.microsoft.com> a écrit dans le
>> message
>> de news: FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
>> > 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?
>>
>>
>>

BChernick
Mon Oct 29 11:37:00 PDT 2007

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?

"Patrice" wrote:

> 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...
>
> --
> Patrice
>
>
> "B. Chernick" <BChernick@discussions.microsoft.com> a écrit dans le message
> de news: 301FC931-4B25-4BB1-81C7-92F16294C865@microsoft.com...
> > 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.
> >
> > "Patrice" wrote:
> >
> >> 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.
> >>
> >> --
> >> Patrice
> >> "B. Chernick" <BChernick@discussions.microsoft.com> a écrit dans le
> >> message
> >> de news: FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
> >> > 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
Mon Oct 29 14:23:32 PDT 2007

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!
|
*************************************************
"B. Chernick" <BChernick@discussions.microsoft.com> wrote in message
news:BD86A105-31AB-4DEF-BCD2-F2985563986B@microsoft.com...
> 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.)
>
>> 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. :-)
>
> On the other hand I did not thing about deleting and replacing records in
> the second grid on combobox change. I will give that a try. Thanks.
>
>
> "Cowboy (Gregory A. Beamer)" wrote:
>
>> 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 m