Timestamp field

In the project, a timestamp field is required in the database to identify the addition and modification of differential data. Because timestamp in SQL SERVER 2005 databases, the timestamp type that cannot be empty is semantically equivalent to binary (8), the null timestamp type can be equivalent to the varbinary (8) type in semantics, this will change the timestamp type obtained in the C # program to the byte [] type. Therefore, if we need to obtain and use the timestamp from the database, it must be converted.

First, create a test table. The statement is as follows:

Create table [dbo]. [tb_Ts] ([id] [int] IDENTITY (1, 1) not null, [TS] [timestamp] NULL, [text] [nvarchar] (50) NULL) ON [PRIMARY]

Table nameTb_Ts, Only three field IDs, TS and text. The TS field is the timestamp type defined in SQL Server.

First, let's take a look at the definition of timestamp in SQL Server 2005. This definition is excerpted from SQL Server 2005 Books Online (Click here for details ):

Timestamp indicates the data type of the unique binary number automatically generated in the database. Timestamp is usually used to add version stamps to table rows. The storage size is 8 bytes. The timestamp data type is only an incremental number and does not retain the date or time. To record the date or time, use the datetime data type.

Note:

Each database has a counter. When you insert or update a table containing the timestamp column in the database, the counter value increases. This counter is the database timestamp. This can track the relative time in the database, rather than the actual time associated with the clock. A table can have only one timestamp column. Each time you modify or insert a row containing the timestamp column, the incremental database timestamp value is inserted in the timestamp column. This attribute makes the timestamp column not suitable for use as a key, especially not as a primary key. Any update to the row changes the timestamp value to change the key value. If the column belongs to the primary key, the old key value is invalid, and the foreign key that references the old value is no longer valid. If the table is referenced in a dynamic cursor, all updates change the row position of the cursor. If this column belongs to the index key, all updates to the data row will also lead to index updates.

Using the timestamp column in a row can easily determine whether any value in the row has changed since the last read. If the row is changed, the timestamp value is updated. If no changes are made to the row, the timestamp value will be consistent with the timestamp value previously read from the row. To return the current timestamp value of the database, use @ DBTS.

The Transact-SQL timestamp data type is different from the timestamp data type defined in the SQL-2003 standard. The SQL-2003 timestamp data type is equivalent to the Transact-SQL datetime data type.

Rowversion is a synonym of the timestamp data type and acts as a synonym of the data type. In DDL statements, use rowversion instead of timestamp. For more information, see data type synonyms (Transact-SQL ).

How can I use SQL statements to Insert timestamp field values obtained in the program?

We know from the preceding timestamp definition that the value of timestamp is generally automatically added and modified by the database, which is equivalent to the same as the automatic growth identifier (and the field will be automatically updated when the update statement is executed ), therefore, we only query this field. If you want to update this field, the following error message is displayed:The timestamp column cannot be updated.However, this field can be manually added, but it can only be usedDEFALUTField (the default field is a default value of the SQL Service database). If other values are input, an error message is displayed:Explicit values cannot be inserted into the TIMESTAMP column. Use INSERT on the column list to exclude timestamp columns or insert default into the TIMESTAMP column.The SQL statement for adding timestamp is as follows:

Insert into [tb_Ts] ([TS]) VALUES (DEFAULT)

Solve the problem of converting the timestamp type in the database and the byte [] type in C #.

In the program, we found that the timestamp field value in the database is obtained through ADO. NET to the program, and the result type is byte []. Assume that the value of timestamp in the database is0x000000000000000007d6,This is not the case when the value in the. net program is obtained. For example, it will become the array type of byte. There are two solutions: the first one is to convert timestamp directly in the database, which can be converted to a hexadecimal string type or a BIGINT long integer, this is also my recommended method. Another method is to use BitConverter in. NET programs for conversion. There are two methods of code: Method 1 (convert in SQL ):

Select ts, CAST (ts as varbinary (8) AS 'timestamp to hexadecimal string', CONVERT (BIGINT, TS) AS 'timestamp to bigint type' FROM tb_Ts

In this way, we can obtain the hexadecimal string or bigint of timestamp. The final query result is as follows:

In addition, the c # type corresponding to VARBINARY (8) is byte [], so we recommend that you convert it to the bigint type directly, otherwise, the following method 2 will be called in C # (for conversion in the program, call the following method ):

/// <Summary> /// convert the timespan in the database to a hexadecimal string /// </summary> /// <param name = "objTs"> from the database timespan value obtained </param> // <returns> timespan hexadecimal string </returns> public string ConvertToTimeSpanString (object objTs) {byte [] btTsArray = objTs as byte []; string strTimeSpan = "0x" + BitConverter. toString (btTsArray ). replace ("-", ""); return strTimeSpan ;}

Timestamp field