Dataset timestamp fails to update

[Brian Korenaga]

Have tried a couple of different methods to update dataset timestamp
after SQL 2000 update (batch query and sproc output parameter).

I've tried both methods using ADO .NET by David Sceppa (Chapter 11) as
an example. I've examined the timestamp before and after the update
and it is unchanged.

1. I created DataAdapter through IDE.
2. I know using the batch query method that SQL is passing back the
new timestamp (using Query Analyzer).
3. I've tried both separate and combined parameters for the timestamp
(i.e. oldtimestamp as input, newtimestamp as output or timestamp as
input/output).
4. I've assigned the parameters to the timestamp column in the
datatable (DataSource) and have tried both using Current and then
Original as SourceVersion.
5. Dataset will update once (Data changes in SQL, but SQL generated
timestamp doesn't get back to the dataset).

Abbreviated stored procedure (using separate parameters):

Create Procedure UpdateHousehold
@BuildingId uniqueidentifier,
..
..
@OldTStamp timestamp,
@NewTStamp timestamp OUTPUT
AS
UPDATE Household ...
WHERE (BuildingId = @BuildingId) AND (TStamp = @OldTStamp);
If @@Rowcount = 1
Select @NewTStamp = TStamp From Household Where BuildingId =
@BuildingId

IDE Generated code

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@OldTStamp",
System.Data.SqlDbType.VarBinary, 8,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "TStamp", System.Data.DataRowVersion.Original,
null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@NewTStamp",
System.Data.SqlDbType.VarBinary, 8,
System.Data.ParameterDirection.Output, false, ((System.Byte)(0)),
((System.Byte)(0)), "TStamp", System.Data.DataRowVersion.Original,
null));
this.sqlUpdateCommand1.UpdatedRowSource =
System.Data.UpdateRowSource.OutputParameters;

I know other people have go this to work, but I am at a total loss.
Any body have any ideas?

Thanks,
Brian

 

[David Sceppa]

Brian,

I created a small sample that uses a timestamp field and can
submit updates successfully using either INSERT / UPDATE / DELETE
queries or stored procedures. I've included the sample code.
(Sorry about any word wrap problems.) You should only need to
change the initial connection string. Try running the sample to
verify that the code works for you.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.



static void Main(string[] args)
{
string strConn = "Data Source=(local);Initial
Catalog=Northwind;Trusted_Connection=Yes;";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();

PrepDb(cn);
SqlDataAdapter da = CreateDataAdapter(cn, true);
DataTable tbl = CreateDataTableSchema();

try
{
AddRows(tbl);
ShowContents("Before insert", tbl);
da.Update(tbl);
ShowContents("After insert", tbl);

ModifyRows(tbl);
ShowContents("Before update", tbl);
da.Update(tbl);
ShowContents("After update", tbl);

DeleteRows(tbl);
ShowContents("Before delete", tbl);
da.Update(tbl);
ShowContents("After delete", tbl);
Console.WriteLine("Success!");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

static void ShowContents(string Message, DataTable tbl)
{
Console.WriteLine(Message);
foreach (DataRow row in tbl.Rows)
{
string strOutput = "";
if (row.RowState == DataRowState.Deleted)
{
strOutput = string.Format("\tID: {0}\tOtherColumn:
{1}\tTimestampColumn: ", row["ID", DataRowVersion.Original],
row["OtherColumn", DataRowVersion.Original]);
if (row.IsNull(row.Table.Columns["TimestampColumn"],
DataRowVersion.Original))
strOutput += "<Null>";
else
strOutput += FormatTimestamp((byte[])
row["TimestampColumn", DataRowVersion.Original]);
}
else
{
strOutput = string.Format("\tID: {0}\tOtherColumn:
{1}\tTimestampColumn: ", row["ID"], row["OtherColumn"]);
if (row.IsNull("TimestampColumn"))
strOutput += "<Null>";
else
strOutput += FormatTimestamp((byte[])
row["TimestampColumn"]);
}
Console.WriteLine(strOutput);
}
Console.WriteLine();
}

static string FormatTimestamp(byte[] ts)
{
string strOutput = "0x";
foreach (byte b in ts)
{
strOutput += b.ToString("X2");
}
return strOutput;
}

static void AddRows(DataTable tbl)
{
for (int intCounter = 1; intCounter <= 3; intCounter++)
{
DataRow row = tbl.NewRow();
row["OtherColumn"] = "New " + intCounter;
tbl.Rows.Add(row);
}
}

static void ModifyRows(DataTable tbl)
{
foreach (DataRow row in tbl.Rows)
row["OtherColumn"] = ((string)
row["OtherColumn"]).Replace("New", "Modified");
}

static void DeleteRows(DataTable tbl)
{
foreach (DataRow row in tbl.Rows)
row.Delete();
}

static DataTable CreateDataTableSchema()
{
DataTable tbl = new DataTable("Korenaga");
DataColumn col = tbl.Columns.Add("ID", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = -1;
col.AutoIncrementStep = -1;
tbl.PrimaryKey = new DataColumn[] {col};
tbl.Columns.Add("OtherColumn", typeof(string));
tbl.Columns.Add("TimestampColumn", typeof(byte[]));

return tbl;
}

static SqlDataAdapter CreateDataAdapter(SqlConnection cn, bool
UseStoredProcs)
{
string strSQL = "SELECT ID, OtherColumn, TimestampColumn FROM
Korenaga";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
SqlCommand cmd;

if (UseStoredProcs)
{
strSQL = "Korenaga_Insert";
cmd = new SqlCommand(strSQL, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int, 0, "ID").Direction
= ParameterDirection.Output;
cmd.Parameters.Add("@OtherColumn", SqlDbType.VarChar, 255,
"OtherColumn");
cmd.Parameters.Add("@TimestampColumn", SqlDbType.Timestamp,
0, "TimestampColumn").Direction = ParameterDirection.Output;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
da.InsertCommand = cmd;

strSQL = "Korenaga_Update";
cmd = new SqlCommand(strSQL, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
cmd.Parameters.Add("@OtherColumn", SqlDbType.VarChar, 255,
"OtherColumn");
cmd.Parameters.Add("@TimestampColumn", SqlDbType.Timestamp,
0, "TimestampColumn").Direction = ParameterDirection.InputOutput;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
da.UpdateCommand = cmd;

strSQL = "Korenaga_Delete";
cmd = new SqlCommand(strSQL, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
cmd.Parameters.Add("@TimestampColumn", SqlDbType.Timestamp,
0, "TimestampColumn");
cmd.UpdatedRowSource = UpdateRowSource.None;
da.DeleteCommand = cmd;
}
else
{
strSQL = "INSERT INTO Korenaga (OtherColumn) VALUES
(@OtherColumn);" +
"SELECT @ID = ID, @TimestampColumn = TimestampColumn
FROM Korenaga WHERE ID = SCOPE_IDENTITY()";
cmd = new SqlCommand(strSQL, cn);
cmd.Parameters.Add("@ID", SqlDbType.Int, 0, "ID").Direction
= ParameterDirection.Output;
cmd.Parameters.Add("@OtherColumn", SqlDbType.VarChar, 255,
"OtherColumn");
cmd.Parameters.Add("@TimestampColumn", SqlDbType.Timestamp,
0, "TimestampColumn").Direction = ParameterDirection.Output;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
da.InsertCommand = cmd;

strSQL = "UPDATE Korenaga SET OtherColumn = @OtherColumn " +
" WHERE ID = @ID AND TimestampColumn =
@TimestampColumn; " +
"IF @@ROWCOUNT = 1 " +
" SELECT @TimestampColumn = TimestampColumn FROM
Korenaga WHERE ID = @ID";
cmd = new SqlCommand(strSQL, cn);
cmd.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
cmd.Parameters.Add("@OtherColumn", SqlDbType.VarChar, 255,
"OtherColumn");
cmd.Parameters.Add("@TimestampColumn", SqlDbType.Timestamp,
0, "TimestampColumn").Direction = ParameterDirection.InputOutput;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
da.UpdateCommand = cmd;

strSQL = "DELETE Korenaga WHERE ID = @ID AND
TimestampColumn = @TimestampColumn";
cmd = new SqlCommand(strSQL, cn);
cmd.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
cmd.Parameters.Add("@TimestampColumn", SqlDbType.Timestamp,
0, "TimestampColumn");
cmd.UpdatedRowSource = UpdateRowSource.None;
da.DeleteCommand = cmd;
}

return da;
}

static void PrepDb(SqlConnection cn)
{
string strSQL;

ExecuteNonQuery(cn, "DROP TABLE Korenaga_Log", true);
ExecuteNonQuery(cn, "DROP PROCEDURE Korenaga_Delete", true);
ExecuteNonQuery(cn, "DROP PROCEDURE Korenaga_Update", true);
ExecuteNonQuery(cn, "DROP PROCEDURE Korenaga_Insert", true);
ExecuteNonQuery(cn, "DROP TABLE Korenaga", true);


strSQL = "CREATE TABLE Korenaga (ID int identity PRIMARY KEY,
OtherColumn varchar(255), TimestampColumn timestamp)";
ExecuteNonQuery(cn, strSQL, false);

strSQL = "CREATE PROCEDURE Korenaga_Insert " +
" (@ID int OUTPUT, @OtherColumn varchar(255),
@TimestampColumn timestamp OUTPUT) " +
"AS " +
" INSERT INTO Korenaga (OtherColumn) VALUES
(@OtherColumn) " +
" SELECT @ID = ID, @TimestampColumn = TimestampColumn
FROM Korenaga WHERE ID = SCOPE_IDENTITY()";
ExecuteNonQuery(cn, strSQL, false);

strSQL = "CREATE PROCEDURE Korenaga_Update " +
" (@ID int, @OtherColumn varchar(255), @TimestampColumn
timestamp OUTPUT) " +
"AS " +
" UPDATE Korenaga SET OtherColumn = @OtherColumn " +
" WHERE ID = @ID AND TimestampColumn =
@TimestampColumn " +
" IF @@ROWCOUNT = 1 " +
" SELECT @TimestampColumn = TimestampColumn FROM
Korenaga WHERE ID = @ID";
ExecuteNonQuery(cn, strSQL, false);

strSQL = "CREATE PROCEDURE Korenaga_Delete " +
" (@ID int, @TimestampColumn timestamp) " +
"AS " +
" DELETE Korenaga WHERE ID = @ID AND TimestampColumn =
@TimestampColumn";
ExecuteNonQuery(cn, strSQL, false);

}

static void ExecuteNonQuery(SqlConnection cn, string strSQL,
bool ContinueOnFail)
{
using (SqlCommand cmd = new SqlCommand(strSQL, cn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (!ContinueOnFail)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
}
}