How does the SqlCommandBuilder class build T-SQL statements? sqlcommandbuilder

How does the SqlCommandBuilder class build T-SQL statements? sqlcommandbuilder

By default, you have read the [RowState attribute in DataTable] blog.

When using SqlCommandBuilder, it is easy to create a SqlCommandBuilder object and set its DataAdapter attribute. But in fact, the SqlCommandBuilder object has done a lot for us, that is, building the T-SQL command, so that our database can be synchronized with our operations. When we call the Update () method of the SqlDataAdapter instance, The SqlCommandBuilder instance will traverse the rows we have modified or added, build different T-SQL commands Based on the row RowState flag.

Next, let's take a look at what SQL commands the SqlCommandBuilder instance has built:

Old rule code first

-- 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; delete student;

C # code:

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); // change the name in the first row to Xiaohong and change its gender to female dataTable. rows [0] ["sname"] = "Xiaohong"; dataTable. rows [0] ["ssex"] = ""; // Delete the second row of Data able. rows [1]. delete (); // create a new row and add it to the table to dataTable. rows. add (new object [] {"James", 108, 18, "male"}); // create a SqlCommandBuilder object, bind a SqlDataAdapter object SqlCommandBuilder scb = new SqlCommandBuilder (adapter); // print and output the SQL command Console for adding, deleting, modifying, and modifying SqlCommandBuilder objects. writeLine ("Insert command for SqlCommandBuilder instance:" + scb. getInsertCommand (). commandText); Console. writeLine ("Delete command of SqlCommandBuilder instance:" + scb. getDeleteCommand (). commandText); Console. writeLine ("SqlCommandBuilder instance Update command:" + scb. getUpdateCommand (). commandText); // synchronize the changed rows to the database adapter. update (dataTable. getChanges (); // save and modify the 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]) ;}}

The output result of running the print operation is as follows: the data changes in the memory meet the expectation.

What about the database? View:

It is also normal and has been synchronized to the database. Modified, deleted, and added.

However, we can see that the SQL commands printed on the console are opposite, but how can we replace local variables and when?

Not complete ===================================================

[Click here to return to the home page]