dbdirect methods with timestamp columns

[Guest]

I inherited a SQL database for which I am building a .NET C# web front-end.
The tables have a Timestamp column, called "ts", that I thought I might as
well use for optimistic concurrency.

I'm going VS 2005 all the way, so I have TableAdapter as my DAL that
contains DBDirect methods. The TableAdapter is used from my BLL, a class
library. My aspx page contains a GridView that uses an ObjectDataSource to
the BLL.

I've seen Scott Mitchell's wonderful tutorials on the asp.net site that
detail building a BLL to a DLL, etc. His examples don't use Timestamp columns
for updating and deleting, however, and neither do any of the other examples
I could find on the web. That is where my confusion comes in, to wit:

1. The DBDirect methods created for Update and Delete have a parameter "int
original_PK", which I get. But they also have "byte[] IsNull_ts" and a
"byte[] Original_ts" parameters. The Orignal_ts must be what it sounds like,
the Timestamp value that was retrieved when the record was read. But what is
IsNull_ts? I don't understand that naming convention. If it is the "new"
value, I don't get it because Timestamp values are auto-generated by SQL and
I would never update them.

2. How should I handle the Timestamp on the aspx page? I created a bound
column for it and set Visible to false, thinking that I would preserve the
value for Original_ts on the update. But when the GridView calls my BLL
Update, it passes a null value back for ts.

3. Maybe the logic will clear itself up when I understand the IsNull_ts
parameters, but just in case... It seems to me that for both Update and
Delete, I should first read the current data values so I can get the ts, then
compare it to the Original_ts I saved on my aspx page. Only if they are the
same should I issue the update/delete.

Many thanks,
Ray

 

[William \(Bill\) Vaughn]

See my reply to "Identity Column and Dataset relation"

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Guest]

Well, with some digging and playing, I worked out the problems. Here's what I
learned along the way:

1) Bill Howard writes about a bug in VS and its stored proc creation:
http://www.geekzilla.co.uk/View72C81AA0-44A5-4E2C-8903-B1EA7D7643EA.htm
I seem to be able to miss this bug if I use a ConnectionString with
IntegratedSecurity = True. I don't want this in production, but it will work
for development. And it creates the stored procs with no errors and under the
right schema.

2) If you use SQL Statements for the TableAdapter configuration, all is
well. The data type of the IsNull_param is inferred as int or bit because it
is never declared. If you have the TableAdapter create the stored procs (and
who wouldn't for an application?), and you have a timestamp column, and you
turn on Optimistic Concurrency, VS creates the IsNull_timestamp param as the
wrong type of timestamp. You can see this by Previewing the SQL during the
wizard.

3) The way that seems to work to fix this is to let the wizard do it's
thing. Then modify the Update and Delete stored procs to change the type of
the IsNull_timestamp param to bit or int. The TableAdapter generated class
works off the params in the stored proc, so fiddle with the TableAdapter a
bit to get it to regenreate the parameters for the DeleteCommand and the
UpdateCommand. Now the TableAdapter methods have the right params, too.

4) Martin Millar has a great article on optimistic concurrency and timestamps:
http://developer.bracora.com/Articles/Optimistic.aspx
Particularly useful is the code to store the timestamp in a hidden label on
your GridView, etc. You need to store it for the Update and Delete, but VS
sees it as an array of bytes and you'll need a way to convert that for use in
HTML. Martin has the magic, and easy, code for storing and retrieving that
timestamp.

I think that solves all my problems. I'm off and running.

I inherited a SQL database for which I am building a .NET C# web front-end.
The tables have a Timestamp column, called "ts", that I thought I might as
well use for optimistic concurrency.

I'm going VS 2005 all the way, so I have TableAdapter as my DAL that
contains DBDirect methods. The TableAdapter is used from my BLL, a class
library. My aspx page contains a GridView that uses an ObjectDataSource to
the BLL.

I've seen Scott Mitchell's wonderful tutorials on the asp.net site that
detail building a BLL to a DLL, etc. His examples don't use Timestamp columns
for updating and deleting, however, and neither do any of the other examples
I could find on the web. That is where my confusion comes in, to wit:

1. The DBDirect methods created for Update and Delete have a parameter "int
original_PK", which I get. But they also have "byte[] IsNull_ts" and a
"byte[] Original_ts" parameters. The Orignal_ts must be what it sounds like,
the Timestamp value that was retrieved when the record was read. But what is
IsNull_ts? I don't understand that naming convention. If it is the "new"
value, I don't get it because Timestamp values are auto-generated by SQL and
I would never update them.

2. How should I handle the Timestamp on the aspx page? I created a bound
column for it and set Visible to false, thinking that I would preserve the
value for Original_ts on the update. But when the GridView calls my BLL
Update, it passes a null value back for ts.

3. Maybe the logic will clear itself up when I understand the IsNull_ts
parameters, but just in case... It seems to me that for both Update and
Delete, I should first read the current data values so I can get the ts, then
compare it to the Original_ts I saved on my aspx page. Only if they are the
same should I issue the update/delete.

Many thanks,
Ray