Get column value

[vovan]

I'm creating DataSet, then 2 DataTables, then DataRelation between those
DataTables.
I populate DataTables with DataAdapters. Data from each table is displayed
in Grids.
For display everything works fine - if I change the current record in the
Parent grid then the Child grid displays records related to the Parent.
In order to update any record in both Parent and Child underlying
datasources (SQL Server tables) I need to know ID of the record.
What is the proper way to get an ID (it exists in the DataTable, in
BindingSource)? I just do not know how to get it? The only way I found (I
don't like it) is to bind ID field to TextBox control and then when I need
ID I get it from the bound TextBox.

Thank you
vovan

 

[Cor Ligthert [MVP]]

Vovan,

I cannot find a nice sample at the moment, however you need the datarowstate
to get the changed rows per table in a copy of your dataset

Those you update to your database in the sequence
childs deleted
parents deleted
parents added
childs added
parents updated
childs updated

However you use for that the dataadapter and not the executenonquery as I
get the idea you want to do.

If everything goes right than you have to do an acceptchanges at the end.

Quickly written in this messages so watch typos or whatever.

Cor

 

[vovan]

Thank you Cor
First of all, I need to use ExecuteNoneQuery because I'm working on upgrade
of VB6 application which uses tons of existing Stored Procedures and I was
thinking that using existing SPs will decrease the time of developing.
The second reason is I couldn't find any sample which uses your approach and
SPs at the same time. Everything I found so far with Update/Insert/Delete
operations was with SQL codes in the body of the program with some question
marks in the bodies of statements. Unfortunately I couldn't understand those
codes.

vovan

 

[Cor Ligthert [MVP]]

Vovan,

It is almost impossible to show samples with stored procedures.

However an text procedure is good written nowhere different from a stored
procedure while such a procedure is just by copy and paste to bring to a
stored procedure and than the only thing that has be changed is the adding
of the property

http://msdn2.microsoft.com/en-us/library/system.data.commandtype(vs.80).aspx

Cor

 

[vovan]

As I know from my VB6/SQL Server experience there is a huge difference in
performance between SPs and build in code.
The same body of some procedures and the code imbedded into VB application.
The time of execution for an SP is a portion of a second, the code from VB
takes 10 seconds. It's due to the need to compile the embedded code first.
SP is already compiled.
Maybe it works differently with VB2005? I don't know.

vovan

 

[Cor Ligthert [MVP]]

Vovan,

There is only one SQL database server that uses compiled procedures. That is
the IBM DB2. Neither Oracle, Microsoft of whatever other does that (quoting
Frans Bouma for the non Microsoft versions).

SP's have in common a slightly better performance because that they don't
have to be loaded everytime, but don't think than in seconds, just parts of
seconds.

There are some advantages for the SP but that is for the oposite as well.
However, in common it are fairly tails.

Cor

 

[vovan]

I don't know, Cor.
I'm telling you what I met. Initially my application was written to work
with Access. Later Access was upgraded to SQL Server. No SPs were used right
after that. In a couple of years some screens begun to work pretty slow.
Using Debug.Print Now I found all buttle necks, I mean places in the code
where the application spends a significant time. Most of them were
populating recordsets via embedded SQL statements.
I was surprized with the result when I replaced the most slow part with SP.
Although I did not change statements logic (often I just moved the code into
the body of SP, added parameters etc), the speed encreased dramatically.
Maybe in theory SQL Server doesn't compile SP. But the result of using SPs
doesn't make me go back to embedded code.

vovan

 

[RobinS]

Are you talking about identity columns? You have identity columns in your
parent table, and it is stored in the child table as well? How are you
doing your updates? Are you updating the parent first, and then the child
table? Do you have cascading changes turned on from the parent to the
child?

Robin S.