I Learnt Today…

2011-10-07

Timestamp is a value that is incremented by the database whenever an insert or update operation is performed.

Even though the name Timestamp could be a bit misleading, this value has no relevance to a clock related time. This only shows a linear progression of time.

For an example , it is something like your database saying it has been two update or insert commands since your last visit.

You can see this value by referring to @@DBTS  [ select @@DBTS ]

How can this be helpful at all?

Well, in simple terms, this helps to keep track of versions of records.

For an example, assume a scenario where you have to fetch a record from a table, manipulate the data and write it back.

What if the record gets changed (from another database call) after you fetched data? You will manipulate the old data and update the record without knowing somebody has updated the record in between your fetching and updating commands.

How can timestamp help?

You can add a column to your table (you can call it “Version”) and set its data type to TimeStamp. Then whenever you update or insert a record to this table, this column will record the database timestamp after that transaction.

So before writing your manipulated data, you can check if the timestamp value remains the same as what you read at the beginning of the transaction.

Following example demonstrates how timestamp can be used to monitor versions:

The Person table used for this example has a column called “Version” which is of type Timestamp.

First simply query the Version column of the table for the person called “Robert”

After an update to the same record, we will check the version (timestamp) of the same record:

As you can see, the timestamp value for the record has been automatically updated.

Note: Timestamp columns are automatically updated by the database engine so you do not have to specify value when either inserting or updating a row of a table which has a timestamp (version) column.

So to insert a record to a table with a timestamp column simply omit the timestamp column from your insert statement.

i.e. – Person table has following columns [Id, Given_Name, Family_Name, Age, Version]

Insert statement would be:

Insert into Person(Id, Given_Name, Family_Name, Age)
Values(001, "Robert", "Nox", 78);

The database will take care of the timestamp (version) column.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

2008-12-04

Today I had to take care of a problem of a certain company (CIS) that I consult. They had a problem of keeping the values modified in a template field of a gridview when the user moves from one page of the grid to another.

Template fields allow us to add custom columns into a datagrid or gridview. For an instance, we can add a grid column which has a textbox in each cell.

When the gridview has several pages, the gridview holds only the rows that are displayed in its Rows (collection) property. So we cannot access the values in other pages using the gridview.Rows property.

Then I thought that I could access the whole collection of rows by referring to the DataSource property of the gridview. This is a good idea because even though the gridview only shows the rows on the current page for display purposes, the data source property of the gridview holds the whole collection of rows in it.

ASP.Net clears the data source of any control at post backs. This is done to optimize performance of communications. In addition, the state of each control is stored in ViewState so it is not necessary to keep the datasource between postbacks.

The problem is worse now because I could not use both datasource and the direct row collection from the gridview.

Of course we can use either cache or session to keep the datasouce.

Session["DataSource"] = dt;  // store our table in the session

Then to synchronize (update) the datasource which was stored in the session we have to:

• Go to each row in the gridview
• Get the matching data row from the data table
• Update the fields of the data table row

Since we have enabled paging for the gridview, we have to do this when ever user changes the page he/she is viewing to preserve his/her modifications.

So we need to write our code in the PageIndexChanging event handler of the GridView.

In my example I have two columns of the grid view called “ID” and “Name” and the name is the only template column I have used so I am only updating that column in the stored data table.

Code:

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
// get the datatable from the
DataTable dTable = (DataTable)Session["dataSource"];

// now we will iterate through all rows of the grid
// then get the matching row from the data table (datasource of our grid)
// and append the updated data (by the user) to the selected data row
foreach (GridViewRow grv in this.GridView1.Rows)
{
// get the matching row from the data table
DataRow dRow = dTable.Rows.Find(this.GridView1.DataKeys[grv.RowIndex].Value);

// set values of updated columns : here I have only let the user to edit "Name" column
dRow["Name"] = ((TextBox)grv.FindControl("txtName")).Text;
}

// go to the next page of the grid
this.GridView1.PageIndex = e.NewPageIndex;
this.GridView1.DataSource = (DataTable)Session["DataSource"];
this.GridView1.DataBind();

// show the whole collection of data in the second grid (used only to display)
this.GridView2.DataSource = (DataTable)Session["DataSource"];
this.GridView2.DataBind();
}

I have used a different gridview called GridView2 to show the whole data source without paging.

So don’t misunderstand the use of GridView2 in the last section of the code. It’s used only do display the whole set of data (without paging).

I cannot upload my code into this blog because it seems like zip files are not supported. If you like to take a glance of my code just add a comment so I’ll send the code to you via email.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek