ASP. NET-use parameters for the sqldatasource Control

Generally, SQL statements and stored procedures include parameters calculated during runtime. SQL statements written using parameters are called parameterized SQL statements.

When using the sqldatasource control, you can specify SQL queries and statements that use parameters. The database information is read and written based on the value calculated during the runtime, which helps improve the flexibility of the Data Binding environment. You can obtain parameter values from various sources. These sources include ASP. NET applications.ProgramVariables, user IDs, and user-selected values. You can use parameters to perform the following operations: Provide search conditions for data retrieval, provide values to be inserted, updated, or deleted in the data storage area, and provide values for sorting, paging, and filtering.

 

1. Use Parameters

Like all data source controls, the sqldatasource control accepts input parameters at runtime and manages parameters in the parameter set. Each data operation has a set of related parameters. For selection operations, you can use the selectparameters set. For update operations, you can use the updateparameters set, and so on.

You can specify the name, type, direction, and default value for each parameter. For parameters that obtain values from a specific object (such as a control, session variable, or user configuration file), you need to set other attributes. For example, controlparameter requires that the controlid be set to identify the control from which the parameter value is to be obtained, and the propertyname attribute be set to specify the attribute containing the parameter value.

In addition, the sqldatasource control automatically creates parameters based on the values passed by data binding controls that support automatic update, insertion, and deletion (such as the gridview or formview control.

 

2. Specify parameters in the command

When using the sqldatasource control, you can set the command attribute of the control to the name of a parameterized SQL statement or stored procedure. If the stored procedure of a command is specified, the command type of the command must be specified as storedprocedure.

2.1 parameter name

The sqldatasource control can add the value of the parameterprefix attribute to the beginning of all parameter names. (The default prefix is "@".)

If a data binding control such as the gridview control is bound to the sqldatasource control, during the update or delete operation, the data binding control simultaneously transmits the current record value and the original record value to the sqldatasource control. The current value is passed to the values dictionary. The original value is passed to the keys or oldvalues dictionary. For a given data operation, the contents of these dictionaries will be appended to the parameters set of the basic dbcommand object.

In the SQL command of the sqldatasource control, use the naming convention to match the parameter placeholder with the original value passed to the command. You can set the format of the placeholder name by setting the oldvaluesparameterformatstring attribute of the sqldatasource control. Set the oldvaluesparameterformatstring attribute to a string. The string "{0}" is a placeholder for the field name. For example, if you set the oldvaluesparameterformatstring attribute to "Old _ {0}", the name of the original value parameter will be resolved to the field name prefixed with "@ old. To update a field named lastmodifieddate. The current value of this field is passed to the values dictionary, and the original value of this field is passed to the oldvalues dictionary. In this case, the @ lastmodifieddate parameter is created to pass the current value, and the @ old_lastmodifieddate parameter is created to pass the original value. These two parameters can be included in the SQL statement to distinguish the current value and the original value of the field, as shown in the following example:

Update Table1 set lastmodifieddate = @ lastmodifieddate

Where key = @ key and lastmodifieddate = @ old_lastmodifieddate

When performing an open concurrent check or using a data source that can modify the primary key, you must be able to separate the current and original values in the command.

 

2.2 Use parameters for sqlclient providers

By default, the sqldatasource control uses SQL Server as the data source through the system. Data. sqlclient data provider. The system. Data. sqlclient Provider supports naming parameters as placeholders, as shown in the following example:

Select * from employees where lastname = @ lastname

And firstname = @ firstname

Using named parameters, it is not important to specify the order of parameters in the command parameter set. However, make sure that the parameter names used in SQL commands correspond to the parameter names in the relevant set.

The following example shows how to use the named parameters in the SQL command to use the sqldatasource control of the system. Data. sqlclient program.

<Asp: sqldatasource id = "employeedetailssqldatasource"

Selectcommand = "select employeeid, lastname, firstname from employees where employeeid = @ empid"

 

Insertcommand = "insert into employees (lastname, firstname) values (@ lastname, @ firstname );

Select @ empid = scope_identity ()"

Updatecommand = "Update employees set lastname = @ lastname, firstname = @ firstname

Where employeeid = @ employeeid"

Deletecommand = "delete employees where employeeid = @ employeeid"

 

Connectionstring = "<% $ connectionstrings: northwindconnection %>"

Oninserted = "employeedetailssqldatasource_oninserted"

Runat = "server">

 

<Selectparameters>

<Asp: parameter name = "empid" type = "int32" defaultvalue = "0"/>

</Selectparameters>

 

<Insertparameters>

<Asp: parameter name = "empid" direction = "output" type = "int32" defaultvalue = "0"/>

</Insertparameters>

 

</ASP: sqldatasource>

 

2.3. Use parameters for oledb and ODBC providers

To connect to the OLE database or ODBC data source, you can configure the sqldatasource control so that it uses the data source through the system. Data. oledb or system. Data. ODBC provider. The system. Data. oledb and system. Data. ODBC providers only support The positioning parameters of the character identity are shown in the following example:

Select * from employees where lastname =? And firstname =?

When system. Data. oledb and system. Data. ODBC are used together with parameterized SQL statements, the specified order of parameter placeholders must match the Parameter order in the relevant parameter set. You can control the order of parameters by explicitly specifying these parameters in the collection of related data operations (such as the updateparameters set of related updatecommand. When you create a parameter set by explicitly creating parameters that are automatically created by using the values passed by the data binding control, the explicitly created parameters will override all automatically generated parameters. In this way, you can ensure that the parameters are transmitted in the required order. If a stored procedure that can return values is called, you must specify the returnvalue parameter as the first parameter in the command parameter set.

Note: by default, the parameters based on the bound fields in the data binding control are added from the parameter dictionary to the command in the following order: Values, keys, and oldvalues. For delete operations, only the keys dictionary is used. For insert operations, only the values dictionary is used.

The following example shows how to specify parameters for the sqldatasource control that uses the system. Data. oledb provider. To ensure that the order of parameters in the set matches the order of placeholders in SQL statements, you can explicitly specify these parameters.

<Fields>

<Asp: boundfield datafield = "employeeid" headertext = "employee ID" insertvisible = "false" readonly = "true"/>

<Asp: boundfield datafield = "firstname" headertext = "first name"/>

<Asp: boundfield datafield = "lastname" headertext = "last name"/>

<Asp: templatefield headertext = "Birth Date">

<Itemtemplate>

<Asp: Label id = "birthdatelabel" runat = "server"

TEXT = '<% # eval ("birthdate", "{0: d}") %>'/>

</Itemtemplate>

<Insertitemtemplate>

<Asp: Calendar id = "insertbirthdatecalendar" runat = "server"

Selecteddate = '<% # BIND ("birthdate") %>'/>

</Insertitemtemplate>

<Edititemtemplate>

<Asp: Calendar id = "editbirthdatecalendar" runat = "server"

Visibledate = '<% # eval ("birthdate") %>'

Selecteddate = '<% # BIND ("birthdate") %>'/>

</Edititemtemplate>

</ASP: templatefield>

</Fields>