Ado. NET Getting Started Tutorial (vii) About Command object Advanced application

Summary

In the previous article, "What you have to know about ADO (vi) about command objects and data retrieval", I explained in detail the basics of command objects and basic usage. As the most executing object in ADO, the command object is unpredictable. In this article, I'll talk to you about the advanced applications and techniques of command objects.

Directory

• Execute commands asynchronously
• Please use parameterized query
• Gets the ID of the inserted row
• Summarize

1. Execute Commands asynchronously

Before the ADO 2.0 release, Command object commands need to wait for the command to complete before other operations can be performed. For example, if the Excutenonquery () method is executed, the application will remain blocked until the data operation completes successfully or terminates abnormally and the connection times out. The asynchronous execution feature introduced in the ADO 2.0 release, obviously, ADO. NET is more robust and more perfect.

The fundamental idea of asynchronous execution is that when you perform a command operation, you do not have to wait for the command to complete, and you can handle other operations concurrently. ADO. NET provides a rich way to handle asynchronous operations, Beginexecutenonquery and Endexcutenonquery are a couple of typical ways to service asynchronous operations. The Beginexecutenonquery method returns the System.IAsyncResult interface object. We can poll (detect) the command for completion based on the IsCompleted property of IAsyncResult. Let's look at a simple example! This example uses what you must know about the database and data tables in Comand object and data retrieval. In this example, we will insert 500 rows of data into the tb_selcustomer and calculate the execution time. The code is as follows:

View Code

The processing results are as follows:

2. Please use parameterized query

In ADO, the query statement is passed to the external database server as a string . These strings contain not only the basic command keywords, the operators, but also the values that limit the query. Unlike other programming languages,. NET is based on strong typing to manage query string data. By providing type checking and validation, a command object can use parameters to pass values to an SQL statement or stored procedure. Unlike command text, parameter input is treated as a literal value, not an executable code. This helps protect against "SQL injection" attacks, where an attacker inserts a command into an SQL statement that compromises the security of the server. Parameterized commands can also improve query execution performance because they help the database server accurately match incoming commands to the appropriate cache query plan.

For different data sources, the parameter object is different, but it is derived from the DbParameter object. The following table lists the parameter objects that correspond to different data sources.

Data Provider	Corresponding Paramter object	Name space
SQL Server Data source	Using the Sqlparamter object	System.Data.SqlClient.SqlParameter
Ole DB Data source	Using the OleDbParameter Object	System.Data.OleDb.OleDbParameter
ODBC Data source	Using the Odbcparamter object	System.Data.Odbc.OdbcParameter
Oracle Data source	Using the OracleParameter Object	System.Data.OracleClient.OracleParameter

The properties of the Paramter object are many, common and very important mainly in the following:

• DbType: Gets or sets the data type of the parameter.
• Direction: Gets or sets a value that indicates whether the parameter can only be entered, output only, bidirectional, or a stored procedure return value parameter.
• isnullable: Gets or sets a value that indicates whether the parameter can be empty.
• paramtetername: Gets or sets the name of the Dbparamter.
• Size : Gets or sets the maximum size of the data in the column.
• Value : Gets or sets the value of the parameter.

In SQL Server, for example, the SqlCommand object contains a Paramters collection that contains all the required Sqlparamter objects in the Paramters collection. When the command is executed, ADO. NET also passes SQL text, placeholders, and parameter collections to the database.

tip: placeholders are different for different data sources. SQL Server data sources use @parametername format to name parameters, and OLE DB and ODBC data sources use question marks (? ) to identify the location of the parameter, while Oracle uses the named parameter in the :p armname format.

Let's look at an example and modify the basic information of the test customer 1 . The basic information for the modification is as follows:

1. Phone number (phone): 18665691100
2. e-mail (email):[email protected]
3. Address (contactaddress): Nanshan District, Shenzhen, China

The code is as follows:

View Code

See the above code do not know how they feel, do you think too much cumbersome? Indeed, we can do it in a more concise way. Specifically, we can construct an array of parameter objects first, and then iterate through the Paramters collection that is added to the Command object. The code is as follows:

View Code

The above two types of writing, the result is exactly the same. Querying the database, we can get the following results:

3. How do I get the ID of the inserted row?

Many times, we need to know what the ID of the insert row is, so that we can do other things with the ID of the inserted row, such as the display on the page and so on. Of course there are many ways to implement, such as using the C # out modifier to decorate the parameters, I prefer to use the SQL Server database native OUTPUT keyword. The OUTPUT keyword returns a field of the insert operation (typically the primary key ID). So we can easily get the primary key of the inserted row by combining the OUTPUT keyword with the ExecuteScalar method. Or look at a simple example to put! We insert a new customer into the Tb_selcustomer and return the customer's ID. The code is as follows:

View Code

The results of the operation are as follows:

4. Summary

In short, thecentral role of the Command object is to execute commands. in the execution of the command process, the situation is very complex. Nonetheless, the command object has superior human resources (attributes and methods) to deal with everything that can happen. It can be said that thestable play of command objects, for the ADO laid a solid foundation . So far, we have basically understood all the content of the ADO Dataprovider component. So I'll focus on the heart of ADO----dataset and how to localize the data source later. In addition, I am very looking forward to receiving your recommendation and attention.

Article posted: http://liuhaorain.cnblogs.com

Ado. NET Getting Started Tutorial (vii) About Command object Advanced application