Get rid of the developer's dependence on CommandBuilder
William Vaughnbeta V Corporation
March 2003
Correspondence: Microsoft® ADO.NET
Summary: Analyze the principle of ADO.NET CommandBuilder and demonstrate how to build query commands using the Visual Studio .NET DataAdapter Configuration Wizard (11 pages)
table of Contents
COMMAVAndBuilder is supported from COM-based ADO how to work s other way? Create a query action command The code adjustment UpdateCommand adjustment deleteCommand summary
I have participated in the Microsoft's hosted a technology exchange (http://communities2.Microsoft.com/home/chatsevents.aspx). I accidentally made people with Microsoft .NET Framework development team and other people who tried new technologies. Communication. This conversation contains other developers' discussion on CommandBuilder. You can also see many related topics every day, so I think it's time to answer the relevant topics.
COMMAVANDBUILDER support
We first start from the basics, namely Microsoft® ADO.NET. ADO.NET Update Rows. If you are familiar with Microsoft® Visual Basic 6.0 Data Object Wizard (VB6 Data Object Wizard), this is not a new concept. (Homer SIMPSON is expressed, "d'oh!") Is like Data Object Wizard, ADO.NET DataAdapter is used to manage four command objects. One of these commands are used to get the other three commands used to increase, update delete rows. .
· SELECTCOMMAND. Specify SQL code and parameters, use one or more SELECT queries to get rows
INSERTCOMMAND. Specify SQL code and parameters insert a new line in the database.
· DeleteCommand. Specify SQL code and parameters to remove the specified rowset in the database table
• UpdateCommand. Specify SQL code and parameters Update the specified rowset in the database table
As you can see, ADO.NET developers can use all SQL code to get or change data in the specified table in the database. Different from the classic ADO operation will create queries operations at runtime. For example, we can Use the Microsoft® Visual Studio® .NET DataAdapter Configuration Wizard (DACW) to create some or all of these commands. This is a big difference. When you compare .NET development and COM-based Visual Studio 6.0 development, client processing The "black box" concept is weakened because more content is included in the source file. This means if you don't like the standard code codes creative, you can change it (Risk).
CommandBuilder automatically generates SQL code to mitigate your work, give the ADO.NET Command object, and the Parameters (parameter collection) they associated with them. Very good. He can work, but just under very limited cases, He has a price, this article will discuss this problem. Unlike the DACW, the code and commands are created behind the CommandBuilder - in another black box.
Upgrade from COM-based ADO
In order to better compare ADO.NET and standard ADO, let's further explore standard ADO. COM-based standard ADO (adoc) does not exist CommandBuilder, or at least not existing on the surface. He seems to have a black box to process query action. AdoC pre-initializes the SELECT query to get all the source data described all described in the result set. He reduced the server-side query generated speed and caused the object of the update result set to be bloated but he had to do this. So Adoccian is an OSFA (One size Fits All) Universal size; a standard interface for various situations) OLE DB interface, other providers are unable to provide this capability to provide sufficient, compatible, or correct source data. This means he (adoC) is a Agricultural app and can serve more supporter. ADOC has an Update criteria property that Ado.Net CommandBuilder (see Microsoft Knowledge Base Article 190727). In my point of view, this is an important performance weakening of CommandBuilder. Update CRITERIA allows developers to adjust the ADOC construction SQL command action test Current conflict. In this case, the SQL query action is set, and the SQL query action determines whether this action is completed according to whether the server-side data line has changed due to update. For example, below Update CRITERIA settings determines how you choose the SQL command to run:
AppRiteriaKey. It is only used for primary key. He indicates if it exists, updates or deletes him.
AdcriteriaAllCols. Constructing the SQL code comparison RECORDSET (Result Set) and all columns of the server side. This is similar to the CommandBuilder.
· AdcriteriaUpdcols. (Default) Constructs the SQL code comparison, only the part of the corresponding result is changed.
· AdcriteriaTimeStamp. Construct SQL code comparison, applied to the TimeStamp column (if available).
As you can see, Adoc and Update criteria properties allow you to decide whether to import columns that have never touched (or unable to touch). This is very important, because there are many cases, you have taken only Read the column. For example, your user is allowed to read the Currentsalary column, but does not allow changing it. No similar way to prevent the set of Update declaration in ADO, your application will have to use other methods. Many powerful concurrency Verification techniques have almost all of the server-timing stamp columns to discover send - accept changes. Typical timestamp tests, the server can tell you immediately when you change from your last read, CommandBuilder cannot use the timestamp column to manage concurrency.
Ok, end lengthy discussion, ADO.NET CommandBuilder does not support Update Criteria properties. His simply uses rough ways (similar to adcriteriaAllCols) to test the change of server end lines. This problem is accustomed to using ADO and use clever solutions ADOC developers are disappointed.
Give AdoC more control and help, and he is better for some providers, Microsoft looks for the prototype of the ADO.NET skeleton. This can be understood why many developers don't expect to use ADO to only make them only In summary - they have prepared to transfer their query operations logic to new products. Use ado.net them must do a lot and absolutely need to convert their roles: doing lighter, faster, simpler. Construct .NET data The provider is also a key point, so he is tantamount to the OLE DB. This strategy will reduce the work of the SQL action query for the ADO.NET developer.
ADO.NET COMMANDBUILDER How to work CommandBuilder requires you to provide a correct, executable, and standard SelectCommand to associate with a DataAdapter. He also requires a viable connection. This is because CommandBuilder opens to and from the server together with DataAPter. End and construct of query statements. When it is complete, it will turn off the connection. Build a CommandBuilder with code in your application as follows:
Listing 1. Preparation of CommandBuilder
DIM CN As SqlConnection
DIM DA As SqldataAdapter
DIM CB As SqlcommandBuilder
CN = New SqlConnection ("Data Source = DEMOSERVER â ...")
Da = New SqldataAdapter ("SELECT AU_ID, AU_LNAME, CITY, CN)
CB = New SQLCOMMAndBuilder (DA) 'Create a new CommandBuilder
From this code, you don't know what is made behind. At this time, if you open the Microsoft® SQL ServerTM log (this is a good idea when using the new code), you can't know what to construct the CommandBuilder, From beginning to end. This is because ADO.NET does not construct any command actions until it is called before being called. For example, I add the following code in Listing 1 (it gets the SQL command for UpdateCommand):
Msgbox (cb.getupdateCommand.commandtext.toString)
ADO.NET Execution:
EXEC SP_EXECUTESQL N 'set fmtonly off; set no_browsetable on; _ _
Set
FMTONLY
On
; SELECT AU_ID, AU_LNAME, CITY, City from authors'
This operation returns the source data result set obtained by the SELECT statement. He cooperates with DataAdapter to construct another three query commands (INSERTCOMMAND, UpdateCommand, and DeleteCommand). Unless SELECTCOMMAN is complicated than the standard select command. Yes, if SELECTCOMMAN is too complex or included Join Operation CommandBuilder will not build other commands. Here is some other restrictions:
· Your selectcommand must return at least one PrimaryKey or Unique (unique marking) column in the query section. Especially point to use TimeStAMP column instead of PRIMARYKEY, you cannot work.
· SELECTCOMMAND cannot involve the SQL query, stored procedures, or view that exists in Join operation. This means that your SELECT statement must correspond to a single table.
· The column involved in SelectCommand must be read and written at the same time. You cannot import those columns or expressions that are not writable for some reason.
• CommandBuilder does not attempt to provide any mechanism to get the output prompt from the query operation (such as the Identity) variable).
· If selectcommand's CommandText, Connection, CommandTimeout, or Transaction property changes, you must notify CommandBuilder to execute the refreshschema method, this operation causes another round trip (with the server). · CommandBuilder generated UpdateCommand or deleteCommand without changing the final reading Any line. This means you will have to write your own code to force the Update or DELETE operation.
· CommandBuilder is designed for unstoppable tables. You don't expect to update (a set) table by primary key / foreign key association.
· If your SELECTCOMMAND SQL column contains special characters, such as spaces, numbers, quotes, or non-alphanukes, you cannot use CommandBuilder.
When .NET Framework 1.1 Introduced (carrying Visual Studio .NET 2003) I found that CommandBuilder adds the .NET frame data provider to Oracle client and ODBC. No, CommandBuilder cannot support all .NET framework data providers, so when you You don't feel surprised when there is a problem with the data provider.
Other methods?
Ok, do you have any other alternatives? Well, there are some other methods. We use the DACW around us to help solve this problem. Before starting, I usually use the stored procedure. Execute the SELECT and query action. Now create a Command object to perform a stored procedure, set the CommandText property for your stored procedure name, set the CommandType to .StoredProcedure Type - very similar adoc.
Now Parameters collection (he is used to describe the Input, Return Variable, and Output) is another topic. This DACW can also provide help. We completed the wizard from the head to generate all parameter collections. No, I will not like this. You Yes, but I usually make some must modifications to the query operation. Listing 2 shows how to initialize SELECTCOMMAND.
Listing 2. Set SELECTCOMMAND INPUT, OUTPUT, and RETURN parameter variables
CN = New SqlConnection ("Server = Betav10; Database = Biblio;")
& "Integrated Security = SSPI")
CMD = New SQLCOMMAND ("PriceBytitle", CN)
cmd.commandtype = commandtype.storedProcedure
With cmd.parameters
.Add ("@ titlewanted", sqldbtype.varchar, 20)
.Add ("@ titlesfound", sqldbtype.int) .direction = _
ParameterDirection.output
.Add ("@maxpricefound", sqldbtype.decimal) .direction = _
ParameterDirection.output
.Add ("@ minpricefound", sqldbtype.decimal) .direction = _
ParameterDirection.output
.Add ("@ avgpricefound", sqldbtype.decimal) .direction = _parameterDirection.output
.Add ("@ return", sqldbtype.int) .direction = parameterDirection.ReturnValue
End with
Create a query action command
Indeed, SelectCommand is quite simple. Query command movement makes you headache. We often encounter a complex connection inquiry in the program, but we will only show how you will show you how you are in DACW There will be a way you need SQL operations. We will also pay attention to how to solve typical problems - concurrency and identification management. That is, if ADO.NET knows how to change your app after you finally change how to inform you. And you How do I know how much the logo generated by the last server is?
The following example explains the timestamp and sent, suppose you just want to do this (you can). He assumes you run SQL Server (any version). For other DBMS (Database Management System) Oracle or DB2, you will have to do it Some fine code changes to accommodate your dbms. No, usually we use DACW to initialize build SQL and Command objects, we don't need any modifications to the code (manual).
First, you need to first create the DemotsConcurrency table and getDemotsRows stored procedures so that Visual Studio .NET and DACW can detect them. The sample program for compilation completion versions is included in the directory to complete these. Confirm on the Server Explorer pane right-click Tables And Stored Procedures icon, then click Refresh At this time, the IDE (integrated development environment) will be able to see the newly created stored procedures and tables. GetDemotsRows Stored Procedure as follows:
Listing 3. GetDemotsRows
Create Procedure DBo.getDemotsRows
(@StateWanted Char (2) = 'WA')
As SELECT PID, NAME, STATE, TIMESTAMP
From demotsconcurrency
Where stat = @StateWanted
Return
When the table and stored procedures are established, start DACW. Click Toolbox, click the DATA tab, and click SqlDataAdapter. This will start DACW and you specify a connection to use. Select or create a connection to your DBMS (connected to you) The sample table and stored procedure for the stored procedure.
Next, tell the DACW you need the code of the specified SQL statement. We will use this way we will use the stored procedure for SELECTCOMMAND. Yes, you can use the stored procedure for the command used, but this is another topic. We will spend a little time to edit SelectCommand to point him to our stored procedure.
Click Query Builder. Tool will help you create a SQL command with a trust-style operation. Select the demotsconcurrency table from the list and click Close after clicking Add. The selected performance is displayed in the top of the Query Builder.
Select all columns in the DemotsConcurrency table. No, don't simply click * - he will make you difficult to master the code. Now you click all columns, the SQL statement generates the display in the query pane. In the select command, the criteria column input mimic stored procedure The input parameters are filtered, the format is like "= @StateWanted", and the criteria is listed in the second panel of Query Builder. Verify that the generated SQL imports a parameter, you will pass him to the stored procedure. After completion, you should populate the query Builder as shown in Figure 1. Click Okay to return to DACW. Click Next, and Finish- you have finished! Figure 1. DACW query Builder populates the SELECTCOMMAND query
Check the generated code
At this point, DACW is like magic. Your code doesn't seem to change - until you expand the "Windows Form Designer Generated Code" area. Click on the flag next to the label and check the code of the DACW into your project. We will delete some of them Code, no need for any tense.
The first step we have to do this to build our SqldataAdapter from the generated code. Expand the "Windows Form Designer Generated Code" area knows that you find SQLSELECTCOMMAND1 as shown in Listing 4.
Listing 4. DACW- Generate SELECTCOMMAND
'
'SQLSELECTCOMMAND1
'
Me.sqlselectcommand1.commandtext = "SELECT PID, NAME, STATE, _
TimeStamp from demotsconcurrency where (state = "& _
@StateWanted) "
Me.sqlselectCommand1.connection = me.sqlconnection1
Me.sqlselectCommand1.Parameters.Add (New _
System.data.sqlclient.sqlparameter ("@ statewield", _
System.Data.sqldbtype.varchar, 2, "state"))
'
Some of these rows are somewhat strange, but we will intercept him. We are interested in DACW-generated parameters (parameters) collection. But first we want to use him, we need to create a subscriber to generate our DataAdapter (such as list 5 Indicated).
Listing 5. GeneratedTaAdapter subroutine
Private sub generatedataAdapter ()
End Sub
Cut SQLSELECTCOMMAND1 encoding from DACW-generated (inventory 4) to this GeneratedataAdapter subroutine. At this point, I will clean up the code and make some small changes. First, we have to instantiate SqldataAdapter. Example Use "Da" as Object variables. Listing 6 Instant DataAPter and use the New Configuration to create SelectCommand, set his CommandText (used to point to our stored procedure), and point to the job database connection. The rest of the part is used to construct Parameters (parameters ) Collection. These codes can be obtained by DACW.
In this section, we simplify the location of the Parameters collection and use DACW-generated code. It is not only imported into the parameter name (@StateWanted), more brought into the data type and length of the source. But wait, State Columns are defined in the database, not VARCHAR. Obviously, DACW does not do correctly, therefore we need to fix him. Listing 6 shows our final editor. List 6. Adjust code Mixing DACW-Standard code
Private sub generatedataAdapter ()
'
'SQLSELECTCOMMAND1
'
Da = New SqldataAdapter ("getDemotsRows", CN)
Da.selectCommand.commandtype = commandtype.storedProcedure
With da.selectcommand.parameters
.Add (new system.data.sqlclient.sqlparameter ("@ statewield", _
System.data.sqldbtype.char, 2, "state"))
End with
End Sub
There are three tasks to do after this step. Re-build InsertCommand, DeleteCommand, and UpdateCommand code blocks, we need to repeat this process - but the technology used is subtle. For this example, we will use DACW-generate SQL, But not all parameters, because we will use TimeStamp to detect concurrency. Get three pieces of code to create query operations, our final code is displayed in Listing 7. We will gradually demonstrate these operations separately.
First, INSERTCOMMAND does not need timestamp columns in the command. DACW knows when to do it is best because the server creates timestamp variables - Identity columns. This means that the DACW-generated code is not always executed correctly. We don't have worry INSERT's concurrent detection Because we use the identity column to confirm that the insert row is unique. At this time, we need to return a new identity variable after the row is inserted. DACW adds an additional select command for this purpose.
Listing 7. DataAdapter InsertCommand Adjustment
'Insertcommand
'Creating a DA INSERTCOMMAND instance
Da.insertCommand = New SQLCOMMAND (Nothing, CN)
With da.insertcommand
.Commandtext = "INSERT INTO DEMOTSCONCURRENCY (Name, State) _
& "Values (@name, @State)" _
& "SELECT PID, NAME, STATE, TIMESTAMP from Demotsconcurrency" _
& "Where (pid = scope_idententity ())"
'Set the parameters collection. Both of the input parameters
.Parameters.add (new system.data.sqlclient.sqlparameter ("@ name", _
System.Data.sqldbtype.varchar, 50, "name"))
.Parameters.add (new system.data.sqlclient.sqlparameter ("@ State", _
System.data.sqldbtype.char, 2, "state") "Change from VARCHAR to CHAR
End with Adjust UpdateCommand
UpdateCommand puts a little new challenge. In this example we test the new version of the server-side TimeStamp column through the original TimeStamp (managed by the ADO.NET DATAROWVERSION.ORIGINAL Name Space). If the test does not match, we can know the line. Already changed after you get it. Like INSERTCOMMAND, UpdateCommand imports the second select command to return the current line of data. Frank, this is very boring, he must match the results of the operation we have just made. In the case of (very special), you have not written all columns, which may be useful, but this is undoubtedly wasting time. I eliminate several additional parameters from the DACW generating code to get the result of the list 8 below.
Listing 8. DataAdapter UpdateCommand Adjustment
'
'SQLUPDATECOMMAND1
'
'Creating a DA UpdateCommand instance
Da.UpdateCommand = New SQLCOMMAND (Nothing, CN)
With da.updatecommand
.Commandtext = "Update demotsconcurrency set name =" _
& "@Name, State = @State" _
& "WHERE (PID = @PID) and (timestamp = @original_timestamp);" _
.Parameters.add (new system.data.sqlclient.sqlparameter ("@ PID", _
System.Data.sqldbtype.int, 4, "pid"))
.Parameters.add (new system.data.sqlclient.sqlparameter ("@ name", _
System.Data.sqldbtype.varchar, 50, "name"))
.Parameters.add (new system.data.sqlclient.sqlparameter ("@ State", _
System.data.sqldbtype.char, 2, "state"))
.Parameters.add (new system.data.sqlclient.sqlparameter)
"@ORIGINAL_TIMESTAMP", _
System.data.sqldbtype.varbinary, 8, _
System.Data.ParameterDirection.Input, _
False, ctype (0, byte), ctype (0, byte), "TimeStamp", _
System.Data.DataRowVersion.original, Nothing))
End with
Adjust deleteCommand
DeleteCommand is very beautiful and simple. In this example, we simplify an actual line (delete) demonstration through the original PID (prior key) and the original TimeStamp.
Listing 9. DataAdapter deleteCommand adjustment
'Sqldeletecommand1
'
Da.DeleteCommand = New Sqlcommand (Nothing, CN) 'Instantiate New Da DeleteCommand
With da.deleteCommand
.Commandtext = "delete from demotsconcurrency" _ & "= @original_pid) and (timestamp = @original_timestamp)"
.Parameters.add (new system.data.sqlclient.sqlparameter ("@ Original_PID", _
System.data.sqldbtype.int, 4, system.data.parameterDirection.Input, false, _
Ctype (0, byte), ctype (0, byte), "PID", _
System.Data.DataRowVersion.original, Nothing))
.Parameters.add (new system.data.sqlclient.sqlparameter)
"@ORIGINAL_TIMESTAMP", _
System.data.sqldbtype.varbinary, 8, _
System.Data.ParameterDirection.Input, False ,_
Ctype (0, byte), CType (0, Byte), "TimeStamp", _
System.Data.DataRowVersion.original, Nothing))
End with
to sum up
The example contains the DataAdapter command group. I hope this guide can get you to get rid of the dependence on DACW and CommandBuilder. Through this twelfth page, you can try some three-column query generators to help you and Your group enhances the learning curve.
About author
William (Bill) Vaughn is the president of Beta V Corporation Redmond, Washington. He provides training, consultants, and service consultations, specialists, and implementation. William's recent books have been "ADO.NET" And Ado Examples and Best Practices for Visual Basic Programmers-2nd Edition "and C # version" ADO.NET Examples and Best Practices for C # Programmers ". It is very worth learning in the future. William created many paper organization lectures in the world Computer Forum he is still an excellent speaker. You can contact him through Billva@nwlink.com.
