SOURCE http://support.microsoft.com/zh-cn/kb/317095
Profile
This step-by-step article uses the Microsoft SQL Server
timestamp field to reduce the amount of data that is sent to the server to detect update conflicts. By default, the
CommandBuilder objects (
sqlclientcommandbuilder and
oledbcommandbuilder) Generates a collision detection based on all field values. by using custom
UpdateCommand, you can specify which columns to use.
Create a SQL Server tableto create a test table and insert some test records, use the following script:
CREATE TABLE [tblQ317095] ([ID] int IDENTITY () not NULL, [chardata] varchar (TEN) not Null,[timestampcol] Timestamp NUL L) ALTER TABLE [tblQ317095] with NOCHECK ADD CONSTRAINT [pk_tblq317095] PRIMARY KEY ([ID]) INSERT into tblQ317095 ([Chardata ] VALUES (' AAA ') insert into tblQ317095 ([chardata]) The values (' BBB ') insert into tblQ317095 ([chardata]) VALUES (' CCC ')
Create a Visual c#.net application
- Create a new Visual c#.net Windows application.
- From the Windows Forms tab in the Toolbox, add the following controls to the form:
- Adds a button control as cmdupdate.
- txtID, and txtchardata,txttimestamp add three text box controls. (Set txtID and txttimestamp as read-only to show that these fields cannot be updated).
- Add a DataGrid control.
- Use statements are used on the system and System.Data.SqlClient namespaces so that you do not have to qualify them for later declarations in these namespaces in your code. The following code is added to the General Declarations section of Form1:
Using system.data;using System.Data.SqlClient;
- Add the following declaration to the class:
SqlConnection con = new SqlConnection (); SqlDataAdapter da;dataset ds = new DataSet (); SqlCommand daupdatecommand;datatable DT;
- Form_Load event:
Con. ConnectionString = "server= (local);D atabase=pubs; Trusted_connection=yes; "; Con. Open ();d a = new SqlDataAdapter ("select * from tblQ317095 ORDER by ID", con);d Aupdatecommand = new SqlCommand ("UPDATE tblQ3 17095 SET chardata = @pCharData WHERE Timestampcol = @pTimeStamp ", Da. SelectCommand.Connection);//this is the field, which is Updating.daUpdateCommand.Parameters.Add (new SqlParameter ("@ Pchardata ", SqlDbType.VarChar, ten);d aupdatecommand.parameters[" @pCharData "]. SourceVersion = datarowversion.current;daupdatecommand.parameters["@pCharData"]. SourceColumn = "Chardata";//use the TimeStamp to locate your ROW.DAUPDATECOMMAND.PARAMETERS.ADD (new SqlParameter ("@ Ptimestamp ", sqldbtype.binary));d aupdatecommand.parameters[" @pTimeStamp "]. sourceversion=datarowversion.original;daupdatecommand.parameters["@pTimeStamp"]. SourceColumn = "Timestampcol";d A. UpdateCommand = Daupdatecommand;//fetch The Data.da.FillSchema (ds, SchemaType.Source, "tblQ317095");d A. Fill (ds, "tblQ317095");d T=ds. tables["tblQ317095"];//show the data in the Textboxes.txtID.Text = dt. Rows[0][0]. ToString (); Txtchardata.text =dt. ROWS[0][1]. ToString (); txttimestamp.text= dt. ROWS[0][2]. ToString ();
- Modify the connection string property (in the first line of the code in step 5) to correspond to your SQL Server connection information. Make sure that you connect to the database that is running the SQL script to create the test table.
- Add the following code to the Cmdupdate_click event:
Dt. rows[0]["Chardata"] = Txtchardata.text;try{da. Update (DT); MessageBox.Show ("Update was successful"); catch (System.Data.DBConcurrencyException dbexception) {MessageBox.Show (dbException.Message.ToString ()));D Ataset Dsmodified;dsmodified = ds. GetChanges (datarowstate.modified);d Atagrid1.datasource = dsmodified.tables[0];d atagrid1.captiontext = "Modified Rows ";d S. RejectChanges ();} catch (Exception genexception) {MessageBox.Show (genexception.message);} Con. Close ();
c#.net Application for testing
- To run the application (extract data in the Form_Load event), on the Debug menu, click Start .
- In Query Analyzer (or Enterprise Manager), update the chardata field in the record #1 to the new value.
- Return to the application and change the chardata to a different value.
- Click the button on the form. be aware that you receive an exception.
Ado. NET timestamp use