Inserting, Updating, and Deleting Data with the SqlDataSource (VB)
In previous tutorials we learned how the ObjectDataSource control allowed for inserting, updating, and deleting of data. The SqlDataSource control supports the same operations, but the approach is different, and this tutorial shows how to configure the SqlDataSource to insert, update, and delete data.
As discussed in An Overview of Inserting, Updating, and Deleting, the GridView control provides built-in updating and deleting capabilities, while the DetailsView and FormView controls include inserting support along with editing and deleting functionality. These data modification capabilities can be plugged directly into a data source control without a line of code needing to be written. An Overview of Inserting, Updating, and Deleting examined using the ObjectDataSource to facilitate inserting, updating, and deleting with the GridView, DetailsView, and FormView controls. Alternatively, the SqlDataSource can be used in place of the ObjectDataSource.
Recall that to support inserting, updating, and deleting, with the ObjectDataSource we needed to specify the object layer methods to invoke to perform the insert, update, or delete action. With the SqlDataSource, we need to provide
DELETE SQL statements (or stored procedures) to execute. As we'll see in this tutorial, these statements can be created manually or can be automatically generated by the SqlDataSource s Configure Data Source wizard.
Since we ve already discussed the inserting, editing, and deleting capabilities of the GridView, DetailsView, and FormView controls, this tutorial will focus on configuring the SqlDataSource control to support these operations. If you need to brush up on implementing these features within the GridView, DetailsView, and FormView, return to the Editing, Inserting, and Deleting Data tutorials, starting with An Overview of Inserting, Updating, and Deleting.
Step 1: Specifying
As we ve seen in the past two tutorials, to retrieve data from a SqlDataSource control we need to set two properties:
ConnectionString, which specifies what database to send the query to, and
SelectCommand, which specifies the ad-hoc SQL statement or stored procedure name to execute to return the results.
SelectCommand values with parameters, the parameter values are specified via the SqlDataSource s
SelectParameters collection and can include hard-coded values, common parameter source values (querystring fields, session variables, Web control values, and so on), or can be programmatically assigned. When the SqlDataSource control s
Select() method is invoked either programmatically or automatically from a data Web control a connection to the database is established, the parameter values are assigned to the query, and the command is shuttled off to the database. The results are then returned as either a DataSet or DataReader, depending on the value of the control s
Along with selecting data, the SqlDataSource control can be used to insert, update, and delete data by supplying
DELETE SQL statements in much the same way. Simply assign the
DeleteCommand properties the
DELETE SQL statements to execute. If the statements have parameters (as they most always will), include them in the
DeleteCommand value has been specified, the Enable Inserting, Enable Editing, or Enable Deleting option in the corresponding data Web control s smart tag will become available. To illustrate this, let s take an example from the
Querying.aspx page we created in the Querying Data with the SqlDataSource Control tutorial and augment it to include delete capabilities.
Start by opening the
Querying.aspx pages from the
SqlDataSource folder. From the Designer on the
Querying.aspx page, select the SqlDataSource and GridView from the first example (the
GridView1 controls). After selecting the two controls, go to the Edit menu and choose Copy (or just hit Ctrl+C). Next, go to the Designer of
InsertUpdateDelete.aspx and paste in the controls. After you have moved the two controls over to
InsertUpdateDelete.aspx, test out the page in a browser. You should see the values of the
UnitPrice columns for all of the records in the
Products database table.
Figure 1: All of the Products are Listed, Ordered by
ProductID (Click to view full-size image)
Adding the SqlDataSource s
At this point we have a SqlDataSource that simply returns all of the records from the
Products table and a GridView that renders this data. Our goal is to extend this example to allow for the user to delete products via the GridView. To accomplish this we need to specify values for the SqlDataSource control s
DeleteParameters properties and then configure the GridView to support deleting.
DeleteParameters properties can be specified in a number of ways:
- Through the declarative syntax
- From the Properties window in the Designer
- From the Specify a custom SQL statement or stored procedure screen in the Configure Data Source wizard
- Via the Advanced button in the Specify columns from a table of view screen in the Configure Data Source wizard, which will actually automatically generate the
DELETESQL statement and parameter collection used in the
We'll examine how to automatically have the
DELETE statement created in Step 2. For now, let s use the Properties window in the Designer, although the Configure Data Source wizard or declarative syntax option would work just as well.
From the Designer in
InsertUpdateDelete.aspx, click on the
ProductsDataSource SqlDataSource and then bring up the Properties window (from the View menu, choose Properties window, or simply hit F4). Select the DeleteQuery property, which will bring up a set of ellipses.
Figure 2: Select the DeleteQuery Property from the Properties Window
The SqlDataSource doesn t have a DeleteQuery property. Rather, DeleteQuery is a combination of the
DeleteParameters properties and is only listed in the Properties window when viewing the window through the Designer. If you are looking at the Properties window in the Source view, you'll find the
DeleteCommand property instead.
Click the ellipses in the DeleteQuery property to bring up the Command and Parameter Editor dialog box (see Figure 3). From this dialog box you can specify the
DELETE SQL statement and specify the parameters. Enter the following query into the
DELETE command textbox (either manually or using the Query Builder, if you prefer):
DELETE FROM Products WHERE ProductID = @ProductID
Next, click the Refresh Parameters button to add the
@ProductID parameter to the list of parameters below.
Figure 3: Select the DeleteQuery Property from the Properties Window (Click to view full-size image)
Do not provide a value for this parameter (leave its Parameter source at None ). Once we add deleting support to the GridView, the GridView will automatically supply this parameter value, using the value of its
DataKeys collection for the row whose Delete button was clicked.
The parameter name used in the
DELETE query must be the same as the name of the
DataKeyNames value in the GridView, DetailsView, or FormView. That is, the parameter in the
DELETE statement is purposefully named
@ProductID (instead of, say,
@ID), because the primary key column name in the Products table (and therefore the DataKeyNames value in the GridView) is
If the parameter name and
DataKeyNames value doesn t match, the GridView cannot automatically assign the parameter the value from the
After entering the delete-related information into the Command and Parameter Editor dialog box click OK and go to the Source view to examine the resulting declarative markup:
<asp:SqlDataSource ID="ProductsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" SelectCommand= "SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products]" DeleteCommand="DELETE FROM Products WHERE ProductID = @ProductID"> <DeleteParameters> <asp:Parameter Name="ProductID" /> </DeleteParameters> </asp:SqlDataSource>
Note the addition of the
DeleteCommand property as well as the
<DeleteParameters> section and the Parameter object named
Configuring the GridView for Deleting
DeleteCommand property added, the GridView s smart tag now contains the Enable Deleting option. Go ahead and check this checkbox. As discussed in An Overview of Inserting, Updating, and Deleting, this causes the GridView to add a CommandField with its
ShowDeleteButton property set to
True. As Figure 4 shows, when the page is visited through a browser a Delete button is included. Test this page out by deleting some products.
Figure 4: Each GridView Row Now Includes a Delete Button (Click to view full-size image)
Upon clicking a Delete button, a postback occurs, the GridView assigns the
ProductID parameter the value of the
DataKeys collection value for the row whose Delete button was clicked, and invokes the SqlDataSource s
Delete() method. The SqlDataSource control then connects to the database and executes the
DELETE statement. The GridView then rebinds to the SqlDataSource, getting back and displaying the current set of products (which no longer includes the just-deleted record).
Since the GridView uses its
DataKeys collection to populate the SqlDataSource parameters, it s vital that the GridView s
DataKeyNames property be set to the column(s) that constitute the primary key and that the SqlDataSource s
SelectCommand returns these columns. Moreover, it s important that the parameter name in the SqlDataSource s
DeleteCommand is set to
@ProductID. If the
DataKeyNames property is not set or the parameter is not named
@ProductsID, clicking the Delete button will cause a postback, but won't actually delete any record.
Figure 5 depicts this interaction graphically. Refer back to the Examining the Events Associated with Inserting, Updating, and Deleting tutorial for a more detailed discussion on the chain of events associated with inserting, updating, and deleting from a data Web control.
Figure 5: Clicking the Delete Button in the GridView Invokes the SqlDataSource s
Step 2: Automatically Generating the
As Step 1 examined,
DELETE SQL statements can be specified through the Properties window or the control s declarative syntax. However, this approach requires that we manually write out the SQL statements by hand, which can be monotonous and error-prone. Fortunately, the Configure Data Source wizard provides an option to have the
DELETE statements automatically generated when using the Specify columns from a table of view screen.
Let s explore this automatic generation option. Add a DetailsView to the Designer in
InsertUpdateDelete.aspx and set its
ID property to
ManageProducts. Next, from the DetailsView s smart tag, choose to create a new data source and create a SqlDataSource named
Figure 6: Create a New SqlDataSource Named
ManageProductsDataSource (Click to view full-size image)
From the Configure Data Source wizard, opt to use the
NORTHWINDConnectionString connection string and click Next. From the Configure the Select Statement screen, leave the Specify columns from a table or view radio button selected and pick the
Products table from the drop-down list. Select the
Discontinued columns from the checkbox list.
Figure 7: Using the
Products Table, Return the
Discontinued Columns (Click to view full-size image)
To automatically generate
DELETE statements based on the selected table and columns, click the Advanced button and check the Generate
DELETE statements checkbox.
Figure 8: Check the Generate
DELETE statements Checkbox
DELETE statements checkbox will only be checkable if the table selected has a primary key and the primary key column (or columns) are included in the list of returned columns. The Use optimistic concurrency checkbox, which becomes selectable once the Generate
DELETE statements checkbox has been checked, will augment the
WHERE clauses in the resulting
DELETE statements to provide optimistic concurrency control. For now, leave this checkbox unchecked; we'll examine optimistic concurrency with the SqlDataSource control in the next tutorial.
After checking the Generate
DELETE statements checkbox, click OK to return to the Configure Select Statement screen, then click Next, and then Finish, to complete the Configure Data Source wizard. Upon completing the wizard, Visual Studio will add BoundFields to the DetailsView for the
UnitPrice columns and a CheckBoxField for the
Discontinued column. From the DetailsView s smart tag, check the Enable Paging option so that the user visiting this page can step through the products. Also clear out the DetailsView s
Notice that the smart tag has the Enable Inserting, Enable Editing, and Enable Deleting options available. This is because the SqlDataSource contains values for its
DeleteCommand, as the following declarative syntax shows:
<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:DetailsView> <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, [Discontinued] = @Discontinued WHERE [ProductID] = @ProductID"> <DeleteParameters> <asp:Parameter Name="ProductID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ProductName" Type="String" /> <asp:Parameter Name="UnitPrice" Type="Decimal" /> <asp:Parameter Name="Discontinued" Type="Boolean" /> <asp:Parameter Name="ProductID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ProductName" Type="String" /> <asp:Parameter Name="UnitPrice" Type="Decimal" /> <asp:Parameter Name="Discontinued" Type="Boolean" /> </InsertParameters> </asp:SqlDataSource>
Note how the SqlDataSource control has had values automatically set for its
DeleteCommand properties. The set of columns referenced in the
UpdateCommand properties are based on those in the
SELECT statement. That is, rather than having every Products column in the
UpdateCommand, there are only those columns specified in the
ProductID, which is omitted because it s an
IDENTITY column, whose value cannot be changed when edited and which is automatically assigned when inserting). Moreover, for each parameter in the
DeleteCommand properties there are corresponding parameters in the
To turn on the DetailsView s data modification features, check the Enable Inserting, Enable Editing, and Enable Deleting options in its smart tag. This adds a CommandField with its
ShowDeleteButton properties set to
Visit the page in a browser and note the Edit, Delete, and New buttons included in the DetailsView. Clicking the Edit button turns the DetailsView into edit mode, which displays each BoundField whose
ReadOnly property is set to
False (the default) as a TextBox, and the CheckBoxField as a checkbox.
Figure 9: The DetailsView s Default Editing Interface (Click to view full-size image)
Similarly, you can delete the currently selected product or add a new product to the system. Since the
InsertCommand statement only works with the
Discontinued columns, the other columns have either
NULL or their default value assigned by the database upon insert. Just like with the ObjectDataSource, if the
InsertCommand is missing any database table columns that don t allow
NULL s and don t have a default value, a SQL error will occur when attempting to execute the
The DetailsView s inserting and editing interfaces lack any sort of customization or validation. To add validation controls or to customize the interfaces, you need to convert the BoundFields to TemplateFields. Refer to the Adding Validation Controls to the Editing and Inserting Interfaces and Customizing the Data Modification Interface tutorials for more information.
Also, keep in mind that for updating and deleting, the DetailsView uses the current product s
DataKey value, which is only present if the
DataKeyNames property is configured. If editing or deleting appears to have no effect, ensure that the
DataKeyNames property is set.
Limitations of Automatically Generating SQL Statements
Since the Generate
DELETE statements option is only available when picking columns from a table, for more complex queries you will have to write your own
DELETE statements like we did in Step 1. Commonly, SQL
SELECT statements use
JOIN s to bring back data from one or more lookup tables for display purposes (such as bringing back the
Categories table s
CategoryName field when displaying product information). At the same time, we might want to allow the user to edit, update, or insert data into the core table (
Products, in this case).
DELETE statements can be entered manually, consider the following time-saving tip. Initially setup the SqlDataSource so that it pulls back data just from the
Products table. Use the Configure Data Source wizard s Specify columns from a table or view screen so that you can automatically generate the
DELETE statements. Then, after completing the wizard, choose to configure the SelectQuery from the Properties window (or, alternatively, go back to the Configure Data Source wizard, but use the Specify a custom SQL statement or stored procedure option). Then update the
SELECT statement to include the
JOIN syntax. This technique offers the time-saving benefits of the automatically generated SQL statements and allows for a more customized
Another limitation of automatically generating the
DELETE statements is that the columns in the
UPDATE statements are based on the columns returned by the
SELECT statement. We may need to update or insert more or fewer fields, however. For example, in the example from Step 2, maybe we want to have the
UnitPrice BoundField be read-only. In that case, it shouldn't appear in the
UpdateCommand. Or we may want to set the value of a table field that does not appear in the GridView. For example, when adding a new record we may want the
QuantityPerUnit value set to TODO .
If such customizations are required, you need to make them manually, either through the Properties window, the Specify a custom SQL statement or stored procedure option in the wizard, or via the declarative syntax.
When adding parameters that do not have corresponding fields in the data Web control, keep in mind that these parameters values will need to be assigned values in some manner. These values can be: hard-coded directly in the
UpdateCommand; can come from some pre-defined source (the querystring, session state, Web controls on the page, and so on); or can be assigned programmatically, as we saw in the preceding tutorial.
In order for the data Web controls to utilize their built-in inserting, editing, and deleting capabilities, the data source control they are bound to must offer such functionality. For the SqlDataSource, this means that
DELETE SQL statements must be assigned to the
DeleteCommand properties. These properties, and the corresponding parameters collections, can be added manually or generated automatically through the Configure Data Source wizard. In this tutorial we examined both techniques.
We examined using optimistic concurrency with the ObjectDataSource in the Implementing Optimistic Concurrency tutorial. The SqlDataSource control also provides optimistic concurrency support. As noted in Step 2, when automatically generating the
DELETE statements, the wizard offers a Use optimistic concurrency option. As we'll see in the next tutorial, using optimistic concurrency with the SqlDataSource modifies the
WHERE clauses in the
DELETE statements to ensure that the values for the other columns haven't changed since the data was last displayed on the page.
About the Author
Scott Mitchell, author of seven ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer. His latest book is Sams Teach Yourself ASP.NET 2.0 in 24 Hours. He can be reached at mitchell@4GuysFromRolla.com. or via his blog, which can be found at http://ScottOnWriting.NET.