reconcile db-changes back to dataset

[Suranga]

Hello!

How do I read back values into a DataTable within a Dataset after an
"insert" or "update" (via SqlDataAdapter.Update() method) to the
database? After "inserts" or "updates", table columns may change, some
via triggers, and others like "timestamp", and these changes do not
automatically propogate back to the dataset.

For instance, let's say we have a SQL Server table as below;

| UserID | Fname | Lname | Timestamp |

SQL Server's "timestamp" type gets automatically updated everytime a
record is updated, (I use them for optimistic concurrency handling.)

If I update a record ( say Fname), "timestamp" gets a new value
automatically in the table. How do I get this new value back to the
dataset?

Thanks

 

[Cor Ligthert [MVP]]

Suranga,

In my opinion by clearing your dataset and filling it again.
You are not sure if other people have made changes which you need as well.

Just my thought,

Cor

 

[Suranga]

Cor,

Thanks for the reply. I agree that it is a good idea to refresh data
from the database if there is a possiblity that someone else might
update the same set of data.

On occasions where such incidents are not applicable, do we still have
to retrieve the whole dataset from the database?

Kind regards
Suranga

 

[Rahul Arora]

Hi Suranga...

In my view...Yes...you have to refill your dataset/datatable to get the
latest values from the database even in the single user environment.
Because your stored procedures/Triggers where fires at the update
statement may change the contents of the table in the database. Hence,
in my view it is always a best practice to Clear the dataset and Fill
it again to avoid any discrepencies in data.

Rahul Arora

 

[Jesús López]

You can include a refresh statement in your UpdateCommand and InsertCommand
to retrieve the recently updated or inserted record. You must then set the
appropriate value to UpdatedRowSource property of the command.

As you know SqlDataAdapter.Update method executes the UpdateCommand for each
changed row within the datatable . If UpdateCommand returns one or more
records and UpdatedRowSource is Both or FirstReturnedRecord, then
SqlDataAdapter.Update uses the first returned record to refresh the DataRow
in DataTable.

If you are using stored procedures to update and insert rows into the
database, then you can also use output parameters to refresh the recently
update or inserted record. In this case you must set UpdatedRowSource to
Both or OutputParameters.

Let me give you an example.

Given the table:

create table Users
(
UserID int identity(1,1) not null primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null,
Version rowversion
)

You could use the following insert command:

insert into Users(FirstName, LastName) Values (@FirstName, @LastName);
select Scope_Identity() As UserID, Version from Users where UserID =
Scope_Identity()

Or you may perfer to use this one:

insert into Users(FirstName, LastName) Values (@FirstName, @LastName);
select UserID, FirstName, LastName, Version from Users where UserID =
Scope_Identity()

You could use the following update command that refreshes the version column
(the timestamp column) and uses this column to control concurrency:

update Users Set FirstName = @FirstName, LastName = @LastName
where UserID = @Original_UserID And version = @Original_version;
select version from Users where UserID = @Original_UserID

Or you may prefer to refresh all columns:

update Users Set FirstName = @FirstName, LastName = @LastName
where UserID = @Original_UserID And version = @Original_version;
select UserID, FirstName, LastName, Version from Users where UserID =
@Original_UserID


You can also use stored procedures and output parameters:

InsertCommand:

create procedure AddUser
@UserID int output,
@FirstName varchar(50),
@LastName varchar(50),
@Version rowversion output
as
insert into Users(FirstName, LastName) Values(@FirstName, @LastName)
select @UserID = Scope_Identity(), @Version = @Version
from Users
where UserID = Scope_Identity()


UpdateCommand:

create procedure ModifyUser
@Original_UserID int,
@FirstName varchar(50),
@LastName varchar(50),
@Original_Version rowversion,
@Version rowversion output
as
Update Users Set FirstName = @FirstName, LastName = @LastName
where UserID = @Original_UserID And Version = @Original_Version
select @Version = Version
from Users
where UserID = @Original_userID

Regards from Madrid (Spain)

Jesús López
VB MVP

 

[Jesús López]

I disagree.

You may know whether the table has a trigger or not. If you know the table
has a trigger, you may know whether the trigger modifies the table or not.
If you don't know it, you can ask.

I think the best practice is refreshing the dataset only when necessary.
There are many cases where it is not necessary to refresh the dataset. If
you refresh the dataset in these cases your are writting more code than
necessary and you are loading the server and network more than necessary.

Regards from Madrid (Spain)

Jesús López
VB MVP

 

[Suranga]

Jesús López,

Thanks for the example, I included a refresh statement in the update
command, and it solved my problem.

Cheers
Suranga