Retrieving Data with AJAX and the GridView Control - Adding Insert, Update, and Delete Statements
(Page 3 of 4 )
At this point, the SqlDataSource you’ve created has only a SELECT statement to extract data from the database:
SelectCommand="SELECT ProductID, Name, ProductNumber,
MakeFlag, SafetyStockLevel, ReorderPoint
FROM Production.Product" >
That’s fine, if all you want to do is display the data in the database. For a functional site, though, you probably want to be able to add new data, edit existing data, and even delete data. You can do all that just as easily as you did the SELECT statement, by asking your data source control to generate the remaining Create, Retrieve, Update, and Delete statements (fondly known as CRUD statements), using a wizard to make your work easier. To see this in action, switch to Design view, click on the SqlDataSource’s Smart Tag, and choose Configure Data Source. The Configure Data Source Wizard opens, displaying your current connection string. Click Next and the Configure Select Statement dialog box is displayed, as shown earlier in Figure 4-8.
Figure 4-15. The AutoFormat option in the GridView’s Smart Tag lets you choose the formatting option that best fits your site, and applies it automatically.
Recall the previous the Configure Data Source Wizard—it did not correctly identify the table in the autogenerated SELECT statement, omitting the schema name. You worked around that by specifying your own SQL statement. Since the SELECT statement you needed was relatively simple to type in, that was not a problem.
However, there is a lot of typing involved for all the CRUD statements. So for the rest of these statements, you will use the Wizard to generate the SQL code, and then just fix the table names.
Make sure the “Specify columns from a table or view” radio button is selected, and the Product table is selected. Check the columns you want returned by the SELECT statement (ProductID, Name, ProductNumber, MakeFlag, SafetyStockLevel, ReorderPoint). This will create a new SELECT statement.
Click the Advanced button to open the Advanced SQL Generation Options dialog box. Select the “Generate INSERT, UPDATE, and DELETE statements” checkbox, as shown in Figure 4-16.
Figure 4-16. You’ll use the Advanced SQL Options dialog box to automatically create the SQL statements to add, edit, and delete data from your data source.
Clicking this checkbox instructs the Wizard to create the remaining CRUD statements, and also enables the second checkbox, Use optimistic concurrency. This is a feature that safeguards your data in case another user makes a change to the database at the same time you do. Select this option as well, and Click OK. When you return to the Wizard, click Next then Finish. You may be asked to update your grid, which unfortunately will wipe out all your customization, but the good news is that you are now bound to a data source control that provides all four CRUD methods.
This Wizard breaks down if any of the fields in the grid can have null values. When a database table is created, you must specify if a column must have data or if null values (no data) are allowed. If you include fields in the GridView which are allowed to be null, then you must handcode the SqlDataSource declaration in Source view.
Open the Smart Tag on the GridView control again, and reapply the look and feel you want. Also—and this is important—select the checkboxes “Enable Editing” and “Enable Deleting.”
Switch to Source view. The SqlDataSource markup will appear similar to Example 4-2, except the new SQL commands have been added. You still need to modify the table names, or else you’ll get the error you saw earlier (see Figure 4-9). Add the schema name [Production] to each of the four statements highlighted in Example 4-2.
The following code does not include concurrency detection.
Example 4-2. SqlDataSource with CRUD statements
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString
%>"
SelectCommand=" SELECT [ProductID], [Name], [ProductNumber],
[MakeFlag], [SafetyStockLevel], [ReorderPoint]
FROM [Production].[Product]"
DeleteCommand="DELETE FROM [Production].[Product]
WHERE [ProductID] = @ProductID"
InsertCommand="INSERT INTO [Production].[Product] ([Name],
[ProductNumber],
[MakeFlag], [SafetyStockLevel], [ReorderPoint])
VALUES (@Name, @ProductNumber, @MakeFlag,
@SafetyStockLevel,
@ReorderPoint)"
UpdateCommand="UPDATE [Production].[Product] SET [Name] = @Name,
[ProductNumber] = @ProductNumber,
[MakeFlag] = @MakeFlag,
[SafetyStockLevel] = @SafetyStockLevel,
[ReorderPoint] = @ReorderPoint
WHERE [ProductID] = @ProductID" >
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="ProductNumber" Type="String" />
<asp:Parameter Name="MakeFlag" Type="Boolean" />
<asp:Parameter Name="SafetyStockLevel" Type="Int16" />
<asp:Parameter Name="ReorderPoint" Type="Int16" />
<asp:Parameter Name="ProductID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="ProductNumber" Type="String" />
<asp:Parameter Name="MakeFlag" Type="Boolean" />
<asp:Parameter Name="SafetyStockLevel" Type="Int16" />
<asp:Parameter Name="ReorderPoint" Type="Int16" />
</InsertParameters>
</asp:SqlDataSource>
Switch back to Design view and notice the Edit and Delete buttons on each row. They are the result of checking the Enable Editing and Enable Deleting checkboxes.
Taking apart the code in Example 4-2, on the first line is the declaration for the SqlDataSource (and its corresponding closing tag at the bottom). After the ID, the obligatory runat="server", and the ConnectionString attribute, you see four attributes: the SelectCommand (which was there previously) and the new DeleteCommand, InsertCommand, and UpdateCommand.
The DeleteCommand takes a single parameter (@ProductID), which is specified in the DeleteParameters element:
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32" />
</DeleteParameters>
—SQL CHEAT SHEET—
Parameters
A parameter to a SQL statement allows for parts of the statement to be replaced when it is actually run. SQL parameters are always preceded with the @ symbol. So, in the following SQL statement:
delete from Products where ReorderPoint > @ReorderPoint
all the records with a value of ReorderPoint greater than some specified value will be deleted from the Products table. One time the statement is run, that value may be 100, the next time it may be 5.
The UpdateCommand control requires more parameters, one for each column you’ll be updating, as well as a parameter for ProductID (to make sure the correct record is updated). Similarly, the InsertCommand takes parameters for each column for the new record. All of these parameters are within the definition of the SqlDataSource.
