Let DataAdapter implement Keyandmodifiedfield update

This article in the Experimental code please download from here:Keyandmodifiedfieldindataadapter.rar.

Create a Dbapp database in SQL Server 2000, and then use Query Analyzer to execute the. sql file in the SQL-GENDB directory to establish the student table.

Let DataAdapter implement Keyandmodifiedfield update

Using DataAdapter (where I use SqlDataAdapter, where all the DataAdapter are SqlDataAdapter) for database updates, it's easy to implement "include only primary keys", "Include all columns in the where phrase" and primary key and timestamp columns, but DataAdapter does not provide us with "primary key and Modified columns" concurrency mode. Because the concurrency pattern can produce a streamlined update command, the design costs are high. David Sceppa in its "Ado.net Core Reference," a book that can be dealt with in DataAdapter Rowupdateing event, but there is no detailed discussion. For this reason, I tried to implement keyandmodifiedfiled concurrency mode with this idea.

During the code implementation process, the main problems encountered include:

1, lack of effective schema information. Because the set phrase contains fields from the Update command and the fields that the where phrase contains are dynamically created, you create them based on the columns that are modified in DataRow, so you need to know the type of the field and the length and other relevant information. This information should have been included in the schema information for the table, but the information was generated at design time (I decompile the DataAdapter Designer designer code, It is found that Microsoft has passed the low-level implementation of COM and invoked with. NET, and that it cannot be acquired in the RowUpdating event, so how to save enough schema information is a difficult task to implement.

2, dynamically generate the update command to dynamically DataRow the data in different dataparameter, how to dynamically get different versions of the field value and fill in the Dataparameter also have a certain degree of difficulty.

3. It is difficult to obtain primary key information because of the lack of necessary schema information.

4. The field names in the dataset may be different from the field names of the actual table, which are mapped through tablemapping. When you dynamically generate SQL commands, you need to process them according to the information in the TableMapping, and you cannot have errors that do not match the field names.

For the above problem, the following strategies are mainly adopted in the program code implementation:

1, when the wizard generates DataAdapter with optimistic concurrency, so that DataAdapter Designer will generate all the fields of the current and original types of parameters, and is saved in the parameters attribute of UpdateCommand. My program first backs up this information to a custom paramcollection, and in the future retrieves the parameter name (DataParameters supports string indexer), which eliminates the hassle of understanding schema information. However, the primary key information is still not well resolved and can only be specified manually.

When the program is initialized, there are several commands similar to the following, which are used to hold enough parameter information and primary key information.

Private SqlParameterCollection paramcollection;private String keyfieldname = "id";p aramcollection = Sqlupdatecommand1.parameters;

In the later Addparametertocommand method, we just need to retrieve the paramcollection according to the parameter name, we can get the sqltype of corresponding parameters, and no longer need schema information.

private void Addparametertocommand (IDbCommand cmd, string paraname) {   SqlParameter tmpsqlparameter;   Tmpsqlparameter = new SqlParameter ();   Tmpsqlparameter.parametername = Paraname;   Tmpsqlparameter.sqldbtype = This.paramcollection[paraname]. SqlDbType;   Tmpsqlparameter.sourceversion = This.paramcollection[paraname]. SourceVersion;   Tmpsqlparameter.sourcecolumn = This.paramcollection[paraname]. SourceColumn;   Tmpsqlparameter.size = This.paramcollection[paraname]. Size;   Tmpsqlparameter.direction = This.paramcollection[paraname]. Direction;   Tmpsqlparameter.precision = This.paramcollection[paraname]. Precision;   Cmd. Parameters.Add (Tmpsqlparameter);}

2, through the use of Reflactor decompile DataAdapter class, you can see that there has been a method named Parameterinput is based on the data in the DataRow to the SqlCommand to fill in the parameters used, just for the internal type. I copied it and put it into my program code, but I needed to make some minor changes.

3, the primary key information can only be manually specified, because in the program does not get the database schema information, so you can only manually specify. If you need to understand schema information, you can also design your own program implementation. David Sceppa gives a DataAdapter Builder tool in the Ado.net Core Reference book, which is written in vb.net, which reads the schema information function of the database table for reference. The code can be downloaded from the Book Companion CD CD (HTTP://WWW.WENYUAN.COM.CN/SOFT_SHOW.ASP?SOFTID=34).

4, in the RowUpdating event, we can get the required DataRow and TableMapping and StatementType information by Sqlrowupdatingeventargs. The information is then used to dynamically generate the update command, and the required parameters are finally filled in and executed. We then implemented a keyandmodified way to update the data.

private void Dastudent_rowupdating (object sender, System.Data.SqlClient.SqlRowUpdatingEventArgs args) {//-- In this program we only intercept the update command if (args).   StatementType!= statementtype.update) return;   String strMsg;   STRMSG = "Beginning update...\r\n";            STRMSG = "\ r \ n----------------------------\ r \ n"; SqlCommand cmd = Generateupdatecommand (args. Row, args.   TableMapping, True); Cmd.   Connection = args.Command.Connection; Cmd.   Transaction = args.Command.Transaction;   Args.command = cmd; String p = parameterinput (args.Command.Parameters, args. StatementType, args. Row, args.   TableMapping);   STRMSG + = "Command text:\r\n\r\n";   STRMSG + + Args.Command.CommandText + "\r\n\r\n----------------------------\r\n\r\n";   STRMSG + = p; This.txtMessages.Text = STRMSG;}   Private SqlCommand Generateupdatecommand (DataRow row, datatablemapping mappings, bool refreshrowafterupdate) {   SqlCommand cmd = new SqlCommand ();   String Paraname= ""; String tablename = mappings.   datasettable; StringbuildeR Commandtextbuilder = new StringBuilder ();   StringBuilder Modifiedfieldsbuilder = new StringBuilder ();   StringBuilder Whereclausebuilder = new StringBuilder ();   StringBuilder Tablefieldsbuilder = new StringBuilder ();   Commandtextbuilder.append ("UPDATE" + Delimit (tablename)); foreach (DataColumnMapping map in mappings). ColumnMappings) {//Determine if the column has been modified if (!ROW[MAP). DataSetColumn, DataRowVersion.Current]. Equals (Row[map.            DataSetColumn, DataRowVersion.Original]) {if (modifiedfieldsbuilder.tostring ()!= "") {         Modifiedfieldsbuilder.append (","); } paraname = "@" + map.         SourceColumn + "current"; Modifiedfieldsbuilder.append (delimit) (map.         SourceColumn) + "=" + Paraname);      Addparametertocommand (cmd, paraname);   } commandtextbuilder.append ("SET" + modifiedfieldsbuilder.tostring ());   Add PRIMARY KEY constraint Paraname = "@" + this.keyfieldname + "Original"; Whereclausebuilder.append (Delimit (This.keyfielDname) + "=" + Paraname);   Addparametertocommand (cmd, paraname); foreach (DataColumnMapping map in mappings). ColumnMappings) {//Determine if the column has been modified if (!ROW[MAP). DataSetColumn, DataRowVersion.Current]. Equals (Row[map. DataSetColumn, DataRowVersion.Original]) {if (whereclausebuilder.tostring ()!= "") {WH         Ereclausebuilder.append ("and"); } paraname = "@" + map.         SourceColumn + "Original"; Whereclausebuilder.append (delimit) (map.         SourceColumn) + "=" + Paraname);      Addparametertocommand (cmd, paraname);            } commandtextbuilder.append ("WHERE" + whereclausebuilder.tostring ()); if (refreshrowafterupdate) {foreach (DataColumnMapping map in mappings).  ColumnMappings) {if (tablefieldsbuilder.tostring ()!= "") {Tablefieldsbuilder.append (),         "); } tablefieldsbuilder.append (Delimit (map).      SourceColumn)); } tablefieldsbuilder.append ("from" + TablenamE + "WHERE" + Delimit (this.keyfieldname) + "= @" + this.keyfieldname + "Original"); Commandtextbuilder.append (";   \r\n\r\nselect "+ tablefieldsbuilder.tostring ());   } Cmd.commandtext = Commandtextbuilder.tostring (); return cmd;}

Updating the data in this way can reduce the complexity of the update command, especially when network bandwidth is limited, to reduce command length and improve communication efficiency. But program writing is more cumbersome. In the above program only implementation of the UPDATE command Keyandmodifiedfield update, more complete code can be left to the reader to design. Put a picture up: