Professional VB 2005 with .NET 3.0 (Programmer to Programmer)
ASP.NET 2.0 provides some unique data-access server controls that make it easy for you to get at the data you need. As data for your applications finds itself in more and more types of datastores, it can sometimes be a nightmare to figure out how to get at and aggregate these information sets onto a Web page in a simple and logical manner. ASP.NET data source controls are meant to work with a specific type of datastore by connecting to the datastore and performing operations such as inserts, updates, and deletes - all on your behalf. The following table details the new data source controls at your disposal:
| Data Source Control | Description |
|---|---|
| SqlDataSource | Enables you to work with any SQL-based database, such as Microsoft SQL Server or even Oracle |
| AccessDataSource | Enables you to work with a Microsoft Access file (.mbd) |
| ObjectDataSource | Enables you to work with a business object or a Visual Studio 2005 data component |
| XmlDataSource | Enables you to work with the information from an XML file or even a dynamic XML source (for example, an RSS feed) |
| SiteMapDataSource | Enables you to work with the hierarchical data represented in the site map file (.sitemap) |
ASP.NET itself provides a number of server controls that you can use for data-binding purposes. That means you can use these data source controls as the underlying data systems for a series of controls with very little work on your part. These data-bound controls in ASP.NET include the following:
-
<asp:GridView>
-
<asp:DataGrid>
-
<asp:DetailsView>
-
<asp:TreeView>
-
<asp:Menu>
-
<asp:DataList>
-
<asp:Repeater>
-
<asp:DropDownList>
-
<asp:BulletedList>
-
<asp:CheckBoxList>
-
<asp:RadioButtonList>
-
<asp:ListBox>
-
<asp:AdRotator>
The newest and most sought-after control in this group is the GridView control. This control was introduced in ASP.NET 2.0 and makes the DataGrid control more or less obsolete. The GridView control enables paging, sorting, and editing with very little work on your part. The next section looks at using the GridView control with SQL Server and taking advantage of these advanced features.
Using the GridView and SqlDataSource Controls
For an example of using these two controls together to display some information, let’s turn to Visual Studio 2005. Start a new page and drag and drop a GridView control onto the design surface of the page. Pulling up the smart tag for the control on the design surface, you can click the Auto Format link to give your GridView control a better look and feel, rather than the default provided.
Next, drag and drop an SqlDataSource control onto the design surface. This control is a middle-tier component, so it appears as a gray box on the design surface. The first step is to configure the SqlDataSource control to work with the data you want from your Microsoft SQL Server instance (see Figure 20-11).
Working through the configuration process for the SqlDataSource control, you must choose your data connection and then indicate whether you want to store this connection in the web.config file (shown in Figure 20-12). This is highly advisable.
Within this configuration process you also choose the table you’re going to work with, and test out the queries that the wizard will generate. For this example, choose the Customers table and select every row by checking the * check box, as shown in Figure 20-13.
After working through the configuration process, you will notice that your web.config file has changed to include the connection string:
<configuration> <connectionStrings> <add name="NorthwindConnectionString" connectionString="Server=.;Integrated Security=True;Database=Northwind" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> ... </system.web> </configuration>
Once you have configured the SqlDataSource control, the next step is to tie the GridView control to this SqlDataSource control instance. This can be done through the GridView control’s smart tag, as shown in Figure 20-14. You can also enable paging and sorting for the control in the same form.
The code generated by the wizard (it is also how you would code it yourself) is shown here:
<%@ Page Language="VB" %> <script runat="server"> </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>GridView Example</title> </head> <body> <form runat="server"> <div> <asp:GridView runat="server" BorderWidth="1px" BackColor="White" GridLines="Vertical" CellPadding="3" Border BorderColor="#999999" ForeColor="Black" DataSource DataKeyNames="CustomerID" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True"> <FooterStyle BackColor="#CCCCCC"></FooterStyle> <PagerStyle ForeColor="Black" HorizontalAlign="Center" BackColor="#999999"></PagerStyle> <HeaderStyle ForeColor="White" Font-Bold="True" BackColor="Black"></HeaderStyle> <AlternatingRowStyle BackColor="#CCCCCC"></AlternatingRowStyle> <Columns> <asp:BoundField ReadOnly="True" HeaderText="CustomerID" DataField="CustomerID" SortExpression="CustomerID"></asp:BoundField> <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" SortExpression="CompanyName"></asp:BoundField> <asp:BoundField HeaderText="ContactName" DataField="ContactName" SortExpression="ContactName"></asp:BoundField> <asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle" SortExpression="ContactTitle"></asp:BoundField> <asp:BoundField HeaderText="Address" DataField="Address" SortExpression="Address"></asp:BoundField> <asp:BoundField HeaderText="City" DataField="City" SortExpression="City"></asp:BoundField> <asp:BoundField HeaderText="Region" DataField="Region" SortExpression="Region"></asp:BoundField> <asp:BoundField HeaderText="PostalCode" DataField="PostalCode" SortExpression="PostalCode"></asp:BoundField> <asp:BoundField HeaderText="Country" DataField="Country" SortExpression="Country"></asp:BoundField> <asp:BoundField HeaderText="Phone" DataField="Phone" SortExpression="Phone"></asp:BoundField> <asp:BoundField HeaderText="Fax" DataField="Fax" SortExpression="Fax"></asp:BoundField> </Columns> <SelectedRowStyle ForeColor="White" Font-Bold="True" BackColor="#000099"></SelectedRowStyle> </asp:GridView> <asp:SqlDataSource runat="server" SelectCommand="SELECT * FROM [Customers]" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"> </asp:SqlDataSource> </div> </form> </body> </html>
First consider the SqlDataSource control. This control has some important attributes to pay attention to. The first is the SelectCommand attribute. This is the SQL query that you will be using. In this case, it is a Select * From [Customers] query (meaning you are grabbing everything from the Customers table of the Northwind database). The second attribute to pay attention to is the ConnectionString attribute. The interesting aspect of this attribute is the use of <%$ConnectionStrings:NorthwindConnectionString %> to get at the connection string. This value points at the settings placed inside the web.config file for those who don’t want to hard-code their connection strings directly in the code of their pages. If you did want to do this, you would use something similar to the following construction:
ConnectionString="Server=(local);Trusted_Connection=True;Integrated Security=SSPI; Persist Security Info=True;Database=Northwind"
Looking now to the GridView control, you can see how easy it is to add the capability to perform paging and sorting capabilities to the control. It is simply a matter of adding the attributes AllowPaging and AllowSorting to the control and setting their values to True (they are set to False by default):
<asp:GridView runat="server" BorderWidth="1px" BackColor="White" GridLines="Vertical" CellPadding="3" Border BorderColor="#999999" ForeColor="Black" DataSource DataKeyNames="CustomerID" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True"> <!-- Inner content removed for clarity --> </asp:GridView>
Each of the columns from the Customers table of the Northwind database is defined in the control through the use of the <asp:BoundField> control, a subcontrol of the GridView control. The BoundField control enables you to specify the header text of the column through the use of the HeaderText attribute. The DataField attribute actually ties the values that are displayed in this column to a particular value coming from the Customers table, and the SortExpression attribute should use the same values for sorting - unless you are sorting on a different value than what is being displayed.
In the end, your page should look similar to what is shown in Figure 20-15.
Allowing Editing and Deleting of Records with GridView
Now let’s expand upon the previous example by allowing for the editing and deleting of records that are displayed in the GridView. If you are using the Visual Studio 2005 SqlDataSource Configuration Wizard to accomplish these tasks, then you need to take some extra steps beyond what was shown in the previous GridView example.
Go back to the SqlDataSource control on the design surface of your Web page and pull up the control’s smart tag. Select the Configure Data Source option to reconfigure the SqlDataSource control to enable the editing and deletion of data from the Customers table of the Northwind database.
When you come to the screen in the Configure Select Statement dialog (see Figure 20-16), click the Advanced button.
This will pull up the Advanced SQL Generation Options dialog, shown in Figure 20-17.
As shown in this dialog, select the Generate Insert, Update, and Delete statements check box. This will construct the SqlDataSource control to not only handle the simple Select query, but also the Update and Delete queries. Press OK and then work through the rest of the wizard.
Return to the GridView control’s smart tag and select the Refresh Schema. You will also find check boxes in the smart tag now for editing and deleting rows of data. Make sure both of these check boxes are checked, as shown in Figure 20-18.
Now look at what changed in the code. First, the SqlDataSource control has changed to allow for the updating and deletion of data:
<asp:SqlDataSource runat="server" SelectCommand="SELECT * FROM [Customers]" ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>" DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = @original_CustomerID" InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)" UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID"> <DeleteParameters> <asp:Parameter Type="String" Name="CustomerID"></asp:Parameter> </DeleteParameters> <UpdateParameters> <asp:Parameter Type="String" Name="CompanyName"></asp:Parameter> <asp:Parameter Type="String" Name="ContactName"></asp:Parameter> <asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter> <asp:Parameter Type="String" Name="Address"></asp:Parameter> <asp:Parameter Type="String" Name="City"></asp:Parameter> <asp:Parameter Type="String" Name="Region"></asp:Parameter> <asp:Parameter Type="String" Name="PostalCode"></asp:Parameter> <asp:Parameter Type="String" Name="Country"></asp:Parameter> <asp:Parameter Type="String" Name="Phone"></asp:Parameter> <asp:Parameter Type="String" Name="Fax"></asp:Parameter> <asp:Parameter Type="String" Name="CustomerID"></asp:Parameter> </UpdateParameters> <InsertParameters> <asp:Parameter Type="String" Name="CustomerID"></asp:Parameter> <asp:Parameter Type="String" Name="CompanyName"></asp:Parameter> <asp:Parameter Type="String" Name="ContactName"></asp:Parameter> <asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter> <asp:Parameter Type="String" Name="Address"></asp:Parameter> <asp:Parameter Type="String" Name="City"></asp:Parameter> <asp:Parameter Type="String" Name="Region"></asp:Parameter> <asp:Parameter Type="String" Name="PostalCode"></asp:Parameter> <asp:Parameter Type="String" Name="Country"></asp:Parameter> <asp:Parameter Type="String" Name="Phone"></asp:Parameter> <asp:Parameter Type="String" Name="Fax"></asp:Parameter> </InsertParameters> </asp:SqlDataSource>
Second, other queries have been added to the control. Using the DeleteCommand, InsertCommand, and UpdateCommand attributes of the SqlDataSource control, these functions can now be performed just as Select queries were enabled through the use of the SelectCommand attribute. As you can see in the queries, a lot of parameters are defined within them. These parameters are then assigned through the <DeleteParameters>, <UpdateParameters>, and <InsertParameters> elements. Within each of these subsections, the actual parameters are defined through the use of the <asp:Parameter> control, where you also assign the datatype of the parameter (through the use of the Type attribute) and the name of the parameter.
Besides these changes to the SqlDataSource control, only one small change has been made to the GridView control:
<Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True"></asp:CommandField> <asp:BoundField ReadOnly="True" HeaderText="CustomerID" DataField="CustomerID" SortExpression="CustomerID"></asp:BoundField> <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" SortExpression="CompanyName"></asp:BoundField> <asp:BoundField HeaderText="ContactName" DataField="ContactName" SortExpression="ContactName"></asp:BoundField> <asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle" SortExpression="ContactTitle"></asp:BoundField> <asp:BoundField HeaderText="Address" DataField="Address" SortExpression="Address"></asp:BoundField> <asp:BoundField HeaderText="City" DataField="City" SortExpression="City"></asp:BoundField> <asp:BoundField HeaderText="Region" DataField="Region" SortExpression="Region"></asp:BoundField> <asp:BoundField HeaderText="PostalCode" DataField="PostalCode" SortExpression="PostalCode"></asp:BoundField> <asp:BoundField HeaderText="Country" DataField="Country" SortExpression="Country"></asp:BoundField> <asp:BoundField HeaderText="Phone" DataField="Phone" SortExpression="Phone"></asp:BoundField> <asp:BoundField HeaderText="Fax" DataField="Fax" SortExpression="Fax"></asp:BoundField> </Columns>
The only change needed for the GridView control is the addition of a new column from which editing and deleting commands can be initiated. This is done with the <asp:CommandField> control. From this control, you can see that we also enabled the Edit and Delete buttons through a Boolean value. Once built and run, your new page will look like the one shown in Figure 20-19.
Don’t Stop There!
Due to limited space, this chapter can only present one example, but it is important to realize that many other DataSource controls are at your disposal. The ObjectDataSource control is rather powerful for those who wish to enforce a strict n-tier model and separate the data retrieval logic into an object that the GridView and other data-bound controls can work with. The XmlDataSource control is one control that you will most likely find yourself using a lot, as more and more data is being stored as XML, including dynamic data (such as Web logs via RSS). These DataSource controls are fine-tuned for the type of data-stores for which they are targeted, so you will find a lot of benefit in exploring their capabilities in detail.
Категории