Manipulating data in asp.net 2.0 47: Inserting, updating, and deleting data with SqlDataSource Controls _ self-study process

Introduction:

As discussed in the tutorial overview inserts, updates, and deletes data, the GridView control has built-in update and deletion capabilities, while the DetailsView and FormView controls have not only edit and delete functions, but also insertion capabilities. We don't have to write a line of code to apply these features directly to a data source control. In this tutorial, we point out that the ObjectDataSource control is best used with the GridView, DetailsView, and FormView controls to better implement the INSERT, update, and delete features. The same is true for SqlDataSource Controls!

For ObjectDataSource controls, to implement inserts, updates, and deletes, we need to specify the method that is invoked to implement the INSERT, update, and delete features. For SqlDataSource, we need to specify INSERT, update, and delete three kinds of SQL statements (or stored procedures). As you'll find out in this chapter, we can automatically build the data Source wizard by writing code or SqlDataSource.
Note: Since we have explored the insertion, editing, and deletion capabilities of the GridView, DetailsView and FormView controls, we are focused in this chapter on how to set up SqlDataSource to support these features. If you want to revisit how to implement these features of the GridView, DetailsView and FormView, refer to the tutorial overview inserting, updating, and deleting data

First step: Specify INSERT, UPDATE, and DELETE commands

As discussed in the previous 2 chapter tutorials, you need to set 2 properties to retrieve data from the SqlDataSource Control:

1. ConnectionString, specify the database to query.
2. SelectCommand specifies the name of the AD-HOC SQL statement or stored procedure used to return the result.

For parameters in SelectCommand, the value is specified in the SelectParameters section of the SqlDataSource control, which can be "hard-coded" values, ordinary parameter source values (such as from query strings, session variables, Web controls, and so on), or through programmatic settings. Occurs when a data Web control invokes the SqlDataSource Select () method-either automatically or programmatically-by connecting to the specified database, assigning values to the parameters and executing the query, retrieving and returning the results. Whether the returned result is a dataset or a DataReader, depending on whether the DataSourceMode property of the SqlDataSource is set to a dataset or a DataReader.

As with selecting data, to implement inserting, updating, and deleting data, we simply provide the appropriate insert, update, and delete SQL statements. The method is to assign the corresponding INSERT, update, and delete SQL statements to the InsertCommand, UpdateCommand, and DeleteCommand properties. If these SQL statements contain parameters (which are often encountered in reality), place these parameters in the corresponding insertparameters, updateparameters, and DeleteParameters sections.

Once you have specified the SqlDataSource control's InsertCommand, UpdateCommand, and DeleteCommand three commands, you can enable the Insert, edit, and delete features in the appropriate data Web control. For the sake of verification, we extend the deletion function of querying.aspx page.

Open the Insertupdatedelete.aspx and querying.aspx pages of the SqlDataSource folder, first select the Querying.aspx page, enter design mode, select the SqlDataSource Control and the GridView control (ID is P Roductsdatasource and GridView1), click the Edit menu, select Copy (or press CTRL + C directly), and then paste the 2 controls on the Insertupdatedelete.aspx page. Test the page in the browser and you will see the ProductID, ProductName and UnitPrice of all the products in the table.

Figure 1: All products sorted by ProductID and displayed

Add the Eletecommand command and DeleteParameters property of the SqlDataSource control

So far, our SqlDataSource only returns records from the table products and then displays them in the GridView control. Our goal is to extend its functionality to allow users to delete data in the GridView control.
To do this, we need to assign values to the DeleteCommand and DeleteParameters properties of the SqlDataSource control, and then enable the Delete feature of the GridView.

You can assign values to the DeleteCommand and DeleteParameters properties of the SqlDataSource control in the following ways:

1. Declaration Code
2. Specify property values in the Properties window
3. Specify in the Specify custom SQL statement or stored procedures interface in the Set Data Source Wizard
4. Click the Advanced button in the Specify columns from table or view interface in the Set Data Source Wizard, which automatically generates the delete SQL statements and parameters to use in the DeleteCommand and DeleteParameters properties.

We'll explore how to automatically generate a DELETE statement in the next 2nd step. Now we try to set DeleteCommand and DeleteParameters in the Properties window. Of course, direct declaration of code and use of the Settings Data Source Wizard can also be done.

Open the Insertupdatedelete.aspx page, enter design mode, select the SqlDataSource control with ID productsdatasource, open its Properties window (select the Properties window from the View menu, or press the F4 button directly) to choose the DeleteQuery property, An oval area appears to the right of the box.

Figure 2: Select the DeleteQuery property in the Properties window.

　　Note: The SqlDataSource control actually does not have a DeleteQuery property. Or, more precisely, it is made up of two attributes of DeleteCommand and DeleteParameters. Although we can see in design mode that the Properties window lists the DeleteQuery properties, when switching to the source mode, we see that the code has only the DeleteCommand property and no DeleteQuery attribute.

Click on the Oval area to convert to the Edit commands and Properties dialog box (see Figure 3). In the dialog box, you can specify the delete SQL statement and the parameters you want to use. Type the following query in the Delete:command text box: (Manually enter or use the Query Builder, whichever you like):

DELETE from the products
WHERE ProductID = @ProductID

Then, click the Refresh Parameters button to add the parameter @productid to the following argument list.

Figure 3: Select the DeleteQuery attribute in the Properties window (picture description is incorrect)

Do not assign a value for this parameter (select None in the following list of argument sources). When we enable the Delete feature for the GridView control, the GridView automatically passes the value for the parameter, passing the value of the DataKeys value of the row that was selected for deletion.

　　Note: The parameter name used in the DELETE query statement must be the same as the DataKeyNames value of the GridView, DetailsView, or FormView control. For example: because the primary key of the table products is ProductID, naturally, the DataKeyNames value of the GridView control is also ProductID, and accordingly, the parameters in the DELETE statement should be set to @productid (of course, You can also arbitrarily set other names, such as @id. When the parameter name does not match the datakeynames (for example, you set the parameter to @id), the GridView control cannot pass the DataKeys value to the arguments in the SQL statement.

After you type the related deletion information in the Edit Commands and Parameters dialog box, click the OK button. To view code in code mode:

<asp:sqldatasource id= "Productsdatasource" runat= "connectionstring=" <%$ connectionstrings
: Northwndconnectionstring%> "
selectcommand=
 " SELECT [ProductID], [ProductName], [UnitPrice] from [ Products] "
deletecommand=" deletes from the products WHERE ProductID = @ProductID ">
<DeleteParameters>
 <asp:parameter name= "ProductID"/>
</DeleteParameters>
</asp:SqlDataSource>

Set the GridView control to support the deletion feature

After the DeleteCommand property is set, the control's smart tag can contain the delete option. As discussed in the tutorial overview inserts, updates, and deletes data, clicking this button will cause the GridView control to add a CommandField column and set the Showdeletebutton property to True. As shown in Figure 4, when the page is accessed through a browser, the GridView control will contain a delete button.

Figure 4: In the GridView control, each row of records contains a delete button

When you click the Delete button, a postback event occurs, and the GridView control assigns the DataKeys value of the row record to the parameter ProductID and calls the Delete () method of the SqlDataSource Control. The SqlDataSource control connects to the database and executes the delete command. Finally the GridView control binds the SqlDataSource control again, fetching and displaying the current product (because the delete command was executed, the product that was just deleted is not displayed).

　　Note: Because the GridView control is passing its DataKeys value to the parameters of the SqlDataSource control, it is particularly important to set the DataKeyNames property of the GridView as the primary key column. Also, the SelectCommand of the SqlDataSource Control will return these columns. in this case, it is best to set the parameters in the DeleteCommand of the SqlDataSource control to @productsid. If the DataKeyNames property is not set, or the parameter name is not @productsid, the postback event occurs when the Delete button is clicked, but the record is not successfully deleted.

Figure 5 illustrates this principle graphically.

Figure 5: Clicking the Delete button of the GridView control will call the SqlDataSource Delete () method

Step 2nd: Automatically create INSERT, UPDATE, and DELETE statements

As mentioned in step 1th, INSERT, update, and delete SQL statements can be set in the Properties window or through control declarations. However, this requires our handwriting code, monotonous and error-prone. Fortunately, we can derive the production insert, UPDATE, and DELETE statements from the dynamic through the Data Source Setup Wizard. The method is to use its "Specify columns from table or view" mode.

Open the Insertupdatedelete.aspx page, add a DetailsView control in design mode, set its ID to manageproducts, and then select "Create New Data source" in its smart tag. Create a SqlDataSource named Manageproductsdatasource, as shown in the following figure:

Figure 6: Creating a SqlDataSource control named Manageproductsdatasource

When you select a database, select the NorthwindConnectionString connection string in the Drop-down list, click Next, in the Set Selection command interface, select Specify columns from table or view, select Table Products in the Drop-down list, and select the table's ProductID , ProductName, UnitPrice and discontinued columns.

Figure 7: Returns the ProductID, ProductName, UnitPrice, and discontinued of the table products

To automatically create the INSERT, update, and delete commands based on the selected table and selected columns, click the Advanced button and select the Generate INSERT, UPDATE, and DELETE commands option.

Figure 8: Select the Generate INSERT, UPDATE, and DELETE commands option

The Generate INSERT, UPDATE, and DELETE commands option is enabled when the query returns columns that contain primary key columns, sometimes columns of primary key. You can select the Use optimistic concurrency option when the Generate INSERT, UPDATE, and DELETE commands option is selected. When this option is selected, the WHERE clause is added to the update and delete commands to provide optimistic concurrency control. Now we're not busy. Select the "Use optimistic concurrency" option, and we'll discuss how to make the SqlDataSource control optimistic concurrency in later tutorials.

When you select the Generate INSERT, UPDATE, and DELETE commands option, click OK to go back to the Set selection command interface, and then click Next to finish. When you complete the wizard, Visual Studio adds ProductID, ProductName, and UnitPrice three bound columns (BoundFields) for the DetailsView control. and a Discontinued single box column (CheckBoxField). Select the paging item in the Smart tab of the DetailsView control and empty the DetailsView wide and high attributes.

We note that the smart tag also includes insert, edit, and delete options available because the SqlDataSource control's InsertCommand, UpdateCommand, and DeleteCommand properties are also assigned. As shown in the following code:

<asp:detailsview id= "manageproducts" runat= "Server" allowpaging= "True" autogeneraterows= "False" datakeynames= " ProductID "datasourceid=" Manageproductsdatasource "enableviewstate=" False "> <Fields> <asp:boundfield
 Datafield= "ProductID" headertext= "ProductID" insertvisible= "False" readonly= "True" sortexpression= "ProductID"/> <asp:boundfield datafield= "ProductName" headertext= "ProductName" sortexpression= "ProductName"/> <asp: BoundField datafield= "UnitPrice" headertext= "UnitPrice" sortexpression= "UnitPrice"/> <asp:CheckBoxField Datafield= "Discontinued" headertext= "discontinued" sortexpression= "discontinued"/> </Fields> </asp:D etailsview> <asp:sqldatasource id= "Manageproductsdatasource" runat= "Server" connectionstring= "<%$ Connectionstrings:northwndconnectionstring%> "deletecommand=" DELETE from [Products] WHERE [ProductID] = @ProductID "Insertcommand=" INSERT into [Products] ([ProductName], [UnitPrice], [DiscontinuEd] VALUES (@ProductName, @UnitPrice, @Discontinued) "selectcommand=" SELECT [ProductID], [ProductName], [UnitPrice],
  [Discontinued] From [Products] "updatecommand=" UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [discont Inued] = @Discontinued WHERE [ProductID] = @ProductID "> <DeleteParameters> <asp:parameter name=" ProductID " Type= "Int32"/> </DeleteParameters> <UpdateParameters> <asp:parameter name= "ProductName" String "/> <asp:parameter name=" UnitPrice "type=" Decimal "/> <asp:parameter" name= "discontinued" Boolean "/> <asp:parameter name=" ProductID type= "Int32"/> </UpdateParameters> <insertparameters > <asp:parameter name= "ProductName" type= "String"/> <asp:parameter name= "UnitPrice" type= "Decimal"/> & Lt;asp:parameter name= "discontinued" type= "Boolean"/> </InsertParameters> </asp:SqlDataSource>

　　Note: How the SQLDATASOURC control automatically assigns values to the Insertcommand,updatecommand and DeleteCommand properties. the columns involved in the InsertCommand and UpdateCommand attributes depend on the columns in the Select command, in other words, not all the columns of the table products appear in the InsertCommand and UpdateCommand properties. Only those columns returned by SelectCommand in the InsertCommand and UpdateCommand properties (except column ProductID, because it is an identity column, the value of the identity column is not allowed to change while editing, And when a new record is inserted, the identity column is automatically assigned a value. In addition, each parameter that appears in the InsertCommand, UpdateCommand, and DeleteCommand properties is in the <DeleteParameters>, <UpdateParameters>, < There are corresponding parameters in the insertparameters> tag.

Then turn to DetailsView Data modification attribute, enable insert, edit, delete function in smart tag. This adds a CommandField and sets the Showinsertbutton, ShowEditButton, and Showdeletebutton properties to true.

When you access the page in the browser, notice that the edit, delete, New button appears in the DetailsView control, click the Edit button, the DetailsView control enters edit mode, and the bound column whose ReadOnly property is set to False (the default) becomes a text box. And CheckBoxField will become a radio box.

Figure 9:detailsview Control's default editing interface

Similarly, you can delete the currently selected row, or add new product records to the system. Because the InsertCommand statement contains only the ProductName, UnitPrice, and discontinued columns, when the new record is completed, the other columns are either NULL or use the database default values. As with the ObjectDataSource control, it is assumed that such a column exists in the database table, that its value is not allowed to be null, and that no default value is set, if the
If you do not include the column in the InsertCommand command, an error occurs when you attempt to execute the INSERT statement.

　　Note: The default insert and edit interface for the DetailsView control does not implement user customization and validation, and we need to convert bound columns (boundfields) to template columns (Templatefields) in order to achieve user customization and add validation controls. For more information on this, see the previous tutorial adding Validation Controls to the editing and customizing the data modification Interface. Also, keep in mind that the DetailsView control will use the DataKey value of the current product when updating and deleting operations are performed. If editing or deleting fails, check that the DataKeyNames property is set correctly.

Limitations of automatically generating SQL statements

The Automatically generate INSERT, update, and DELETE commands option is selected to automatically generate an SQL statement only if you choose to return columns from the table. However, for more complex queries, we will manually write insert, UPDATE, and DELETE statements as we did in the first step. In general, we use joins in the SQL SELECT command to connect data from different tables in different tables (for example, we want to display the supplier name CategoryName while displaying the product information, but there are no CategoryName columns in the Products table, Then only the CategoryName column of the table categories is mobilized. At the same time, we perform edit, update, and delete operations on the main table (in this case, the main table is the Products table)

For this more complex query that requires manual input, it can be time-saving to follow these steps. First, create a SqlDataSource control that returns data from the table products, and select the Specify columns from table or view mode in its Set Data Source Wizard to automatically generate the Insert, UPDATE, and DELETE statements. When you finish setting up, open the SelectQuery property in the Properties window (or select Custom SQL statement or stored procedure mode directly in the Set Data Source Wizard), and then add the join words to the Select command. This method has both the advantage of automatically generating SQL statements, and the option to customize the SELECT statement.

Another limitation is that the auto-generated insert and Updat commands contain the columns returned by the Select command, and the columns we actually need to insert or update may be more or less than those columns. For example, in step 2nd, if we want to set the UnitPrice to read-only, then it should not be updatecommand in the statement, or we want to assign the QuantityPerUnit to "TODO" when the record is added, However, the QuantityPerUnit column is not displayed in the GridView control.

In this case, we need to enter the code manually. Either declare the code directly, or select Specify SQL statement or stored procedure mode in the Set Data Source Wizard or through the Properties window.

　　Note: When a new parameter is added, if there is no column in the data Web control that corresponds to the parameter, we should assign the parameter by other means: in the InsertCommand and UpdateCommand statements, by hard coding; by predefined sources such as query strings, Session state, controls on the page, and so on) pass parameters, and programmatically assign values to the parameters.

Summarize:

To enable the data Web control to enable built-in insert, edit, and delete functionality, the data source control that it binds to first provides these functional functions. For SqlDataSource, the InsertCommand, UpdateCommand and DeleteCommand attributes must contain insert,update and DELETE statements. In this tutorial we explored the manual and automatic 2 ways to generate code.

I wish you a happy programming!

Author Introduction

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.