RowState attribute in DataTable, datatablerowstate

RowState attribute in DataTable, datatablerowstate

This attribute is an enumerative type of a read-only attribute. It has five values: Detached, Unchanged, Added, Deleteed, Modified,

// First state: the value of Detached is 1: The row has been created, but it is not part of any System. Data. DataRowCollection. System. Data. DataRow is in this status immediately after being created, before being added to the collection, or after being removed from the collection. (Yes, not delete)
// Status 2: The value of Unchanged is 2. The row has not changed since System. Data. DataRow. AcceptChanges was called last time.
// Status 3: The value of Added is 4: The row has been Added to System. Data. DataRowCollection, but System. Data. DataRow. AcceptChanges has not been called.
// Status 4: The value of Deleted is 8: The row has been Deleted through System. Data. DataRow's System. Data. DataRow. Delete method.
// Status 5: The Modified value is 16: The row has been Modified, but System. Data. DataRow. AcceptChanges has not been called.

This status flag plays a major role. It is used to translate T-SQL statements by SqlCommandBuilder (but only for a single table). Of course, a primary key is required. If there is no primary key in the data table, the following error is reported: "For SelectCommand that does not return any key column information, dynamic SQL generation of UpdateCommand is not supported."

After the DataTable calls the AcceptChange () methodAddedAndModifiedAll rows becomeUnchanged, AndDeletedThe row is also deleted.

Therefore, this AcceptChange () must be called only after DataAdapter calls the Update () method. Otherwise, SqlCommandBuilder cannot find the modified row. In this way, the table in DataSet is modified, but it cannot be modified synchronously when it is updated to the database.

Here is an example to illustrate the problem:

-- Create database student; use student; create table student (sname varchar (10) not null, sno int not null, sage int not null, ssex varchar (2) not null); alter table student add constraint PK_sno primary key (sno), constraint CK_ssex check (ssex = 'male' or ssex = 'female '), constraint CK_sage check (sage> 8 and sage <40) insert into student values ('zhang san', 103, 23, 'mal'); insert into student values ('Li si ', 104, 24, 'male'); insert into student values ('wang wu', 105, 25, 'male'); insert into student values ('zhao liu', 106, 26, 'male'); insert into student values ('zhu 7', 107, 27, 'male'); select * from student;

Code in C:

Public static void AdapterAndSqlCommand () {// Step 1: Obtain database configuration information String connStr = ConfigurationManager. connectionStrings ["connStr"]. toString (); // Step 2: Construct the SqlCommand query statement SqlCommand command = new SqlCommand ("select * from student;"); command. connection = new SqlConnection (connStr); // Step 3: Create SqlDataAdapter adapter = new SqlDataAdapter (command); // Step 4: create DataSet and DataTable DataSet dataSet = new DataSet (); DataTable dataTable = new DataTable (); // Step 5: Fill the data adapter. fill (dataTable); dataSet. tables. add (dataTable); // modify the name dataTable in the first row of data. rows [0] ["sname"] = "Xiaohong"; Console. writeLine ("RowState attribute of the first row before the AcceptChanges method is called:" + dataTable. rows [0]. rowState); // The RowState attribute of the row before the AcceptChanges method is called. // Note: I am the dataTable OF THE AcceptChanges method called before Update. acceptChanges (); Console. writeLine ("RowState attribute of the first row after the AcceptChanges method is called:" + dataTable. rows [0]. rowState); // The RowState attribute SqlCommandBuilder scb = new SqlCommandBuilder (adapter) of the row after the AcceptChanges method is called. adapter. update (dataTable );// I did not specifically write dataTable in this place. getChanges (). If it is written, this error cannot be displayed. An empty parameter exception is reported in this row. If you do not know whether to call the AcceptChanges method in advance, it is hard to find that the error is AcceptChanges, because the error is not the line of AcceptChanges, but the line of Update.// Note: I called it after Update // dataTable. acceptChanges (); // The following is a data foreach (datatable table in dataSet. tables) {foreach (DataRow row in table. rows) {Console. writeLine (row [0] + "," + row [1] + "," + row [2] + "," + row [3]) ;}}

(For the red characters in the above Code, dataTable. GetChanges () is an optimization for the Program)

Console output after running:

Obviously, RowState changes from Modified to Unchanged before and after the call. Let's see if John has changed to a red color in the database?

Some people will start to swear. Why is James still there? I modified it, and I saved it (that is, the AcceptChanges method is called). According to the print output on the console, the data in the memory is indeed changed. Why is the database not changed?

That's because the SqlCommandBuilder I mentioned above generates SQL statements based on the row state RowState and primary key, but calling the AcceptChanges method will change RowState [AllAddedAndModifiedAll rows becomeUnchanged, AndDeletedThe row is also deleted .], SqlCommandBuilder cannot generate SQL statements for this row, and the database will not modify the data.

The correct method is to call the AcceptChanges method of the DataTable after the Update method of the DataAdapter is called, so that the data in the memory can be consistent with the data in the database.

Comment out the first able. AcceptChanges (); and open the second.

Public static void AdapterAndSqlCommand () {// Step 1: Obtain database configuration information String connStr = ConfigurationManager. connectionStrings ["connStr"]. toString (); // Step 2: Construct the SqlCommand query statement SqlCommand command = new SqlCommand ("select * from student;"); command. connection = new SqlConnection (connStr); // Step 3: Create SqlDataAdapter adapter = new SqlDataAdapter (command); // Step 4: create DataSet and DataTable DataSet dataSet = new DataSet (); DataTable dataTable = new DataTable (); // Step 5: Fill the data adapter. fill (dataTable); dataSet. tables. add (dataTable); // modify the name dataTable in the first row of data. rows [0] ["sname"] = "Xiaohong"; Console. writeLine ("RowState attribute of the first row before the AcceptChanges method is called:" + dataTable. rows [0]. rowState); // The RowState attribute of the row before the AcceptChanges method is called. // Note: I am the AcceptChanges method called before Update. // dataTable. acceptChanges (); // Console. writeLine ("dataTable. rows [0] [sname]: "+ dataTable. rows [0] ["sname"]); SqlCommandBuilder scb = new SqlCommandBuilder (adapter); adapter. update (dataTable); // Note: I am the dataTable called after Update. acceptChanges (); Console. writeLine ("RowState attribute of the first row after the AcceptChanges method is called:" + dataTable. rows [0]. rowState); // The RowState attribute of the row after the AcceptChanges method is called // The following is a data foreach (datatable table in dataSet) in the output DataTable. tables) {foreach (DataRow row in table. rows) {Console. writeLine (row [0] + "," + row [1] + "," + row [2] + "," + row [3]) ;}}

Now the data in the data is synchronized: