In the. NET environment for database development, looking for a component that can automatically generate Transact-SQL, I believe everyone will think of the sqlcommandbuilder class. It allows you to modify data in dataset and generate Transact-SQL statements for a single table, saving developers the trouble of writing a lot of transact-SQL statements. Similar operations also exist in the past ado, but in ADO. net, the coupling of such operations is reduced. However, I will tell you later that this coupling degree is not low enough. First, sqlcommandbuilder must be used with sqldataadaper. In use, we must also pay attention to the following two conditions: 1. The selectcommand attribute of sqldataadaper must be set; 2. the fields returned by the selectcommand attribute must contain the primary key field or fields with unique constraints. Sqlcommandbuilder generates Transact-SQL statements based on selectcommand. When we realize this, we should stop and look at its circle of friends. The following UML diagram shows the social network of sqlcommandbuilder.
From the UML view above, it is not difficult to see that sqlcommandbuilder has a direct connection with sqldataadapter and has indirect connections with dataset, sqlcommand, and sqlconnection objects. Its Design still has a high Coupling Degree. The following code shows how sqlcommandbuilder works together with its friends. String strconn = "connection string ";
Using (system. Data. sqlclient. sqlconnection con = new system. Data. sqlclient. sqlconnection (strconn ))
...{
Con. open (); // open the database connection
System. Data. sqlclient. sqlcommand cmd = con. createcommand (); // create a sqlcommand object on the current connection
Cmd. commandtext = "select * from table where query condition"; ①
Cmd. commandtype = commandtype. text;
System. Data. sqlclient. sqldataadapter SDA = new system. Data. sqlclient. sqldataadapter (CMD );
// Create a sqldataadapter object and set its selectcommand attribute
System. Data. sqlclient. sqlcommandbuilder sb = new system. Data. sqlclient. sqlcommandbuilder (SDA );
// Establish Association
Dataset DST = new dataset ();
SDA. Fill (DST, "table"); // read data to the "table" of dataset through the sqlcommand object.
// Modify the data of the "table" in DST
SDA. Update (DST, "table"); // write the data changes in the "table" to the database.
}
Obviously, the transact-SQL code generated by sqlcommandbuilder comes from ①. I think this code will soon give readers some inspiration to know how to apply the sqlcommnandbuilder object, but think carefully, this application has great limitations. First of all, in general, the data we operate on the client does not come from the same table, but from multiple tables. Data is read from one view or query stored procedure. Possible situations are shown in: Of course, there is a design question to be discussed here: Data Query and data operation are two concepts, the case discussed here is actually to confuse data query with data modification. But what I want to ask is how many data operations simply write data, rather than executing the query results? From a query, it is usually obtained from multiple tables through views or stored procedures. Operations on data usually involve updating multiple tables. In this case, how can sqlcommandbuilder generate corresponding Transact-SQL statements for multiple tables? For example, create a stored procedure:
Create procedure pro_data
As
Select top 10 a. *, C. Province name as sname, D. City name as cityname, E. District name as xname
From t_jgq A with (nolock)
Inner join province C with (nolock) on a. Sid = C. ID
Inner join City D with (nolock) on a. cityid = D. id
Inner join district and county e with (nolock) on a. Xid = E. ID
This stored procedure is designed to read data from the t_jgq table. However, t_jgq references "province name", "city name", and "district/county name" by ID, therefore, when obtaining the complete information of t_jgq, you must also read the data referenced by the foreign key from the table "Province", "city", and "district/county. This design also complies with the database entity design rules (see the third paradigm ). To operate data in t_jgq, we must also understand its "Province", "city", and "district/county" information. When they are oriented to business logic (or end users, is a complete entity. Modify the previous Code:
...........
Cmd. commandtext = "pro_data ";
Cmd. commandtype = commandtype. storedprocedure;
..............
SDA. Update (DST, "t_jgq"); // write the data changes in the "table" to the database. The following error occurs: dynamic SQL generation is not supported against multiple base tables.
}
In this case, sqlcommandbuilder is powerless. However, there is no path to perfection. We may also use sqlcommandbuilder to solve the problem above. When operating data in the t_jgq table, why must we use the pro_data stored procedure. We can declare a dataadapter and sqlcommandbuilder for each table. Based on this idea, let's change the code above. That error will not happen. Con. open (); // open the database connection
// Obtain the data in t_jgq
System. Data. sqlclient. sqlcommand cmd_jgq = con. createcommand (); // create a sqlcommand object on the current connection
Performance_jgq.commandtext = "select top 10 * From t_jgq ";
Cmd_jgq.commandtype = commandtype. text;
System. Data. sqlclient. sqldataadapter sda_jgq = new system. Data. sqlclient. sqldataadapter (cmd_jgq );
// Create a sqldataadapter object and set its selectcommand attribute
System. Data. sqlclient. sqlcommandbuilder sb_jgq = new system. Data. sqlclient. sqlcommandbuilder (sda_jgq );
// Establish Association
// Obtain provincial data
System. Data. sqlclient. sqlcommand cmd_sn = con. createcommand ();
Performance_sn.commandtext = "select * From Province ";
Performance_sn.commandtype = commandtype. text;
System. Data. sqlclient. sqldataadapter sda_sn = new system. Data. sqlclient. sqldataadapter (cmd_sn );
System. Data. sqlclient. sqlcommandbuilder sb_sn = new system. Data. sqlclient. sqlcommandbuilder (sda_sn );
// Obtain City Data
System. Data. sqlclient. sqlcommand cmd_s = con. createcommand ();
Pai_s.commandtext = "select * from city ";
Performance_s.commandtype = commandtype. text;
System. Data. sqlclient. sqldataadapter sda_s = new system. Data. sqlclient. sqldataadapter (cmd_s );
System. Data. sqlclient. sqlcommandbuilder sb_s = new system. Data. sqlclient. sqlcommandbuilder (sda_s );
// Obtain county data
System. Data. sqlclient. sqlcommand syntax _x = con. createcommand ();
Pai_x.commandtext = "select * from district/county ";
Performance_x.commandtype = commandtype. text;
System. Data. sqlclient. sqldataadapter sda_x = new system. Data. sqlclient. sqldataadapter (cmd_x );
System. Data. sqlclient. sqlcommandbuilder Sb_x = new system. Data. sqlclient. sqlcommandbuilder (sda_x );
Dataset DST = new dataset ();
Sda_jgq.fill (DST, "t_jgq ");
Sda_sn.fill (DST, "Province ");
Sda_s.fill (DST, "city ");
Sda_x.fill (DST, "district/county ");
// Modify the data of the "table" in DST
Datatable TB = DST. Tables [0];
TB. Rows [0] ["datalevel"] = 50;
Sda_jgq.update (DST, "t_jgq"); // write the data changes in the table to the database.
However, please note that the above Code is just a functional test code. In practice, this situation is almost impossible. How can we synchronize the "Province", "city", and "district/county" data referenced in t_jgq? Some people will say that using the datarelation object and using the getparentrow of the current row can solve this problem. But it is not that simple. Suppose we want to edit a piece of data in t_jgq on the form (of course, the related "Province", "city", and "district/county" data will also be displayed ), you can use the currencymanager object to traverse the data in t_jgq. But how can you synchronize the data of "Province", "city", and "district/county? Some people must say that the positionchanged event of currencymanager can solve this problem. Of course, additional code should be written (this is beyond the boundary of this article and can be discussed separately) and in the above Code, another fatal problem is that when we read data from "Province", "city", and "district/county", we did not set the conditions (nor can we set the conditions ), instead, all data is read. Even if we can tolerate all this, we will return to the line of code: sda_jgq.update (DST, "t_jgq "); // submit the modification data in a table. If we need to submit data in multiple tables, the code should be written as follows: sda_jgq.update (DST, "t_jgq "); // write the modified data in the t_jgq table
Sda_jgq1.update (DST, "t_jgq1"); // write data modified in t_jgq1
Sda_jgq2.update (DST, "t_jgq2"); // write the data modified in the t_jgq1 table. Assume that the data in t_jgq and t_jgq1 are successfully written to the database, but what should I do if the data in t_jgq2 fails to be written due to system exceptions? Another problem occurs here. How can we handle the transaction in this solution?
Summary of sqlcommandbuilderSqlcommandbuilder must be used with sqldataadapter. They can only respond to data processing of a single table. If you want to use sqlcommandbuilder for Composite data (data comes from multiple tables), its solution is to first read the data of each table locally and then combine it through datarelation locally. The amount of code and complexity of this solution are unimaginable. But sqlcommandbuilder automatically generates Transact-SQL for us. This function is too tempting. We need such capabilities, but we also need to face more complex environments. Does this solution exist? Yes, that is sqlbuilder. (To be continued: hope that interested friends can give comments or exchange QQ messages)
