Using
ADO.NET
The most troublesome is preparing for database access operations.
DBCommand
Must add it to it
DBParameter
, Especially when the parameters of the page are particularly more, many of the code of the data access layer are flown here.
Ibatis
of
Parametermap
The configuration is a solution proposed for this issue, based on
xml
Configuration, put the field name and object's properties, and automatically
DBCommand
Provides the set of parameters it needs. Thus, we write a lot of repetition code directly.
In the Employees_ParameterMap.xml configuration file:
Select Employeeid, Lastname, Firstname from Employees Where EmployeeiD = # Employeeid # or lastname = # lastname #
Using the inline parameter map, the statement is only required to provide an Employee type object when performing a query, which automatically goes to read the value of the EmployeeID, LastName property of the you need, and return to the query results. The statement it executed when executed is as follows:
[Select EmployeeiD, Lastname, Firstname from Employees Where EmployeId = @ param0 or lastname = @ param1]
The way the required parameters is as follows:
[@ param0 = [EmployeeID, 12], @ param1 = [lastname, 8bbb7bfb-c]]
Also, in iBATII, the type of each parameter is also pointed out:
[@ param0 = [int32, system.int32], @ param1 = [string, system.string]]
For the following configuration:
Select Employeid, Lastname, Firstname from Employees Where EmployeeiD = # Employeeid # or lastname = # lastname # or country = # Country #
The ParameterMap it uses is as follows:
In each parameter mapping element, you can specify the columns corresponding to each property, its type, and the type of corresponding database, you can specify the default value when it is null, specifically, you can see the official document. However, it can be seen that we look forward to the same way as the inline parameters, as described above. But can't you do it? From the execution result of the program, it is not possible. Due to the previous configuration of the SQL statement, it is basically noticed to use the inline parameters. When using the parameter map, you cannot display the property parameters you want to use in the form of # proty #, can only replace each parameter element in order, as follows:
SELECT Employeeid, Lastname, FirstName from Employees Where Employeid =? Or LastName =? Or country =?
It can be seen that since it is not obvious to which property corresponds to which property corresponds to, if the above parameter element is changed, the parameter value corresponding to the EMPLOYEEID changes. At the same time, this reason, through the parameter mapping of this approach, it is not possible to reuse each parameter. If the carefully observation will find that since all specified column properties, why not automatically configure it? In fact, it doesn't work, it will only work when the stored procedure is executed. That is to say, the column attribute in the above Parameter element is not required. Below is an example of a stored procedure:
InsertemPloyee
INSERTEMPLOYEE accepts two parameters @lastname and @firstname. The mapping parameters I provide to it are as follows:
The result of the above configuration is (where the comma is behind):
[@ Lastname = [Lastname,
9A
8bc059-3], @firstname = [firstname, 46887db0-2]]
But when I changed their position. Its results are as follows:
[@ Lastname = [firstname,
9A
8bc059-3], @firstname = [Lastname, 46887DB0-2]]]]]]]]
That is, the same is true for the storage process, and the parameter mapping relationship is still closely related to the order of parameter elements. The Column property still does not show its role. So there is a certain limitation using parametermap. But for stored procedures, this must be used because it only supports parametermap to provide parameters for it without supporting inline parameters.
Finally, a few details that lists several parametermaps require special attention:
1. When configuring parametermap, if the incoming parameter object is a metadata type (int, string etc), then when configuring parameter elements, the property name uses Value. In this case, it is mainly used in the case where the specified parameters are stored.
2. If ParameterMap configured in ParameterMap does not include in the incoming parameter object (a key, value item of the property, or the iDictionary object), it will produce an exception, regardless of whether it is used in Statement.
3. When using parametermap's Extends properties, it will inherit the ParameterMap configuration corresponding to the Extends value, and will inherit all of its parameter maps, and the order is calculated from the inheritance. This should pay special attention when you need to use the extends property.
4. Pay special attention to the memory process transmission process, the order between parameters mapping and parameters of the stored procedure should be correct. Moreover, it is necessary to provide a parallel parameter with the stored procedure (Prameter configuration is enough), even if the partial parameter of the stored procedure already has a default value. Otherwise the System.ArgumentOutofrangeException is abnormal.
5. Under normal circumstances, the inline parameters should be used.
Source code download
