asp:Feature
LANGUAGES: VB.NET
ASP.NET VERSIONS: 2.0
Create New Views of Data with ASP.NET 2.0: Part II
Perspective, Protection, and Procedures
By John Paul Mueller
In Part I of this series, you discovered some new ways to create connections in your data in ASP.NET. These new techniques almost seem like cheating, but you can use them to gain valuable time when creating a project. Because time is becoming ever more critical to developers today, anything that saves time should be welcome.
To wrap up this series, we ll consider what you can do with the data now that you have a firm basis for accessing it. ASP.NET 2.0 provides an interesting array of new controls you can use to display your data. Of course, Microsoft adds new controls every time it introduces a new platform. The news in this case is that the controls are actually easy to configure and use. I think you ll agree that, even though there are things you might like to add or do differently, these new controls are a vast improvement over what Microsoft provided in the past.
Even though users are going to be impressed with the new views of data you can provide, the big issue for many developers today is keeping the data safe. Visual Studio makes this process considerably easier, as well. In the past, the IDE often caused more problems than it solved in using all the functionality that SQL Server provides. Today you ll find that it s extremely easy to use views and stored procedures in your applications. Relying on these two SQL Server features makes it a lot harder for miscreants to do things that you hadn t anticipated, and keeps your data safer even when a hacker does manage to break into your application. The technology may seem simple, but it can significantly improve security without a lot of extra effort on your part. In fact, if anything, the new techniques actually make your job easier.
Selecting a Different Data Perspective with Controls
The way you see your data on screen is important. In the past, ASP.NET developers had to choose between the limited views that Microsoft provided or a lot of custom programming. The latest version of ASP.NET remedies the situation somewhat by providing a number of alternative controls for changing the data view. You saw the GridView control in Part I, so I won t discuss it again here.
The DataList control provides a list of information, as shown in Figure 1. The default settings use the field name followed by its value, as shown in the figure. You ll find that this control works well for reports or mailing labels. Use the DataList control when you need a list of items.
Figure 1: Use the DataList control
for reports or mailing labels.
The power of the DataList control is in the templates it provides. Select Edit Templates from the DataList Tasks smart tag to see the templates shown in Figure 2. As you can see, you can individually change many of the items that you had to program in the past. For example, you can easily add an alternating item template for even-numbered entries to differentiate them from the odd-numbered items in the list.
Figure 2: Templates provide a
powerful means of changing the control presentation without programming.
Notice the Item Template shown in Figure 2. You can change each of the controls within the template to provide a custom look. If you want to use a dropdown listbox for a particular data field, you can easily add it. The point is, you can use any of the Web form controls to display information as part of a template. ASP.NET selects the correct template for the occasion, so you don t have to worry about the user seeing inappropriate information or data in the wrong format for a particular need. When you finish changing the template, simply choose End Template Editing from the DataList Tasks smart tag.
It s easy to confuse the DetailsView control with the DataList control at first because they look essentially the same. However, the DetailsView control differs from the DataList control in a number of ways. First, the DetailsView control works with individual records. The presentation shows a two-column table that contains the field names first and the values second. Second, because of the presentation method, the DetailsView control offers fewer templates, but it also lets you manage the fields it displays directly. The DetailsView control works well for master/detail displays. You can also use it where you want a form-like view in tabular format. The control displays only one record at a time, rather than the list of records shown in Figure 2.
The FormView control is a cross between the DetailsView and DataList controls. You use it when you want to present one record at a time in a form view. The presentation features aren t quite as nice as the DataList control; for example, you only have one Item Template entry to use. However, you have the full flexibility that the DataList control provides. Generally, you ll use this control for data entry purposes. The most important consideration with the FormView control is that it provides functionality for viewing, editing, deleting, and inserting records without the coding requirements you might have faced in the past.
The Repeater control is completely freeform. Unlike all the other display controls presented so far, the Repeater control doesn t do anything automatically when you assign it a data source. In fact, you can t even use this control in Design view except to assign the data source. In this case, you must work with the control in Source view by adding any of the templates it supports, including ItemTemplate, AlternatingItemTemplate, SeparatorTemplate, HeaderTemplate, and FooterTemplate. Because the IDE doesn t do anything for you, this is the best option when you need a completely custom look for the display.
Protecting Data with Views
Over the years I ve seen a lot of very convoluted code to perform database processing in ASP.NET applications. In many cases, you don t need to use code to obtain a particular view of data. All you really need is a view that you create in SQL Server. Using a view lets someone see the data without editing it. Views present a particular dataset and don t require any programming on your part. The most important reason to use a view is to protect your data. Because SQL Server controls the view and is read-only, a novice user can t accidentally change the data.
You might think that you must exit Visual Studio to create a new view. However, it s very easy to create a view without leaving the IDE. All you need to do is create the database connection in Server Explorer and locate the Views folder for that connection. The following steps help you create a new view for the Northwind database that lists products by supplier:
1) Right-click the Views folder and choose Add New View from the context menu. You ll see an Add Table dialog box that contains a list of the tables for the Northwind database.
2) Highlight the Categories, Products, and Suppliers tables by Shift-Clicking each table entry. Click Add. The IDE automatically adds the tables to the view.
3) Click Close to close the Add Table dialog box. At this point you must select the fields you want to appear in the view. You don t have to select all the fields, only those that the viewer should see. In fact, you can use a field in the query without actually displaying it on screen.
4) Select, in order, the Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.ContactTitle, Products.ProductID, Products.ProductName, Categories.CategoryName, and Products.Discontinued fields in the top window of the view. This action creates a list of field entries you can configure in the second window.
5) Choose Ascending in the Sort Type field for the SupplierID field in the second window. This action sorts the data.
6) Clear the checkbox for the Discontinued field. This keeps the field from appearing in the output, even though you can use it for other purposes.
7) Type <>1 in the Filter field for the Discontinued field. The Discontinued field is a Boolean value, so setting this filter rejects any items that the supplier has discontinued from the output.
8) Test the query by clicking the Execute SQL button on the toolbar (the button with the exclamation mark icon). Figure 3 shows typical output from this query.
Figure 3: Create a view to display
data without allowing any edits.
At this point you can save the view. Simply click Save as normal and the IDE will create the new view in SQL Server for you. I used a name of Products by Supplier for this view. Once you create the view, you can access it as you would any other data source. Unfortunately, you can t use the drag and drop technique as you can with a table (you must configure the SqlDataSource manually). When you get to the Configure the Select Statement page of the Configure Data Source wizard, choose Products by Supplier from the Name dropdown listbox and check * in the Columns list. After you finish the connection, you can use any of the data view controls to display the information on screen. Notice that you ve created a custom view of the information without writing a single line of code yourself.
Controlling Access with Stored Procedures
Creating a stored procedure is similar to creating a view. However, stored procedures are far more flexible. You can use stored procedures to select, insert, update, and delete data. As previously noted, you can select a different stored procedure for each of these actions when you create a database connection. Consequently, you can create stored procedures that let the user select and update records, but not delete them, to protect the data. As with views, you can create stored procedures in most cases without writing a single line of code. More importantly, the stored procedure also makes it possible to create unique database views in your ASP.NET application without writing a single line of code.
You ll notice two significant differences between creating views and stored procedures. First, the IDE doesn t assume anything about creating a stored procedure. You start with what amounts to a blank editing area where you can type the SQL query manually if you want. Begin by removing the template code from the stored procedure (leave the comments for variables because you might need to add variables to the stored procedure later). Right-click the editing area and choose Insert SQL from the context menu. You ll see the same Add Table dialog box that you see when you use a view. At this point you perform the same actions as you would with a view to create a select stored procedure.
Second, you can create stored procedures to perform actions other than selection. After you choose the tables you want to use, right-click the Query Builder dialog box and choose one of the items from the Change Type menu on the context menu. Changing the query type automatically changes the Query Builder presentation to help you build that query type. Figure 4 shows how an update query for the Categories table might appear.
Figure 4: Query Builder changes its
appearance to match the query type.
Notice that an update requires that you provide variable names for the query. These variables contain the update values for the record. For example, you would need to pass a value to the variable named CatName to update the CategoryName field. You must define these variables separately from the query. Consequently, the full SQL statement for this example would look like the one in Figure 5.
CREATE PROCEDURE dbo.UpdateCategory
(
@CategoryID int,
@CatName nvarchar,
@Desc ntext,
@Pict image
)
AS
UPDATE Categories
SET CategoryName = @CatName,
Description = @Desc,
Picture = @Pict
WHERE CategoryID = @CategoryID
Figure 5: Creating an update query.
Even though Query Builder created the actual query, you need to add the variable declarations for the stored procedure. Variables appear with an @ sign in front of the variable name. The variable type must match the type of the data field you want to update. You can obtain this information from Server Explorer by selecting the table column and viewing its properties in the Properties Window. Note that you should also create a variable for the column that uniquely identifies the record you want to update (CategoryID in this case).
New Data Management Methods
This overview of ASP.NET data viewing techniques should provide you with a new outlook on database management. It really is easier to create database applications using the new functionality that ASP.NET 2.0 provides. In addition, you can do a lot more without writing any code at all. The biggest issue is figuring out the sometimes convoluted steps to accomplishing a particular task. Even though Microsoft has made database development tasks easier, they need to advertise some of these features in a way that developers can find them more easily.
The source code accompanying this series is available for download.
John Paul Mueller is a freelance author and technical editor. He has writing in his blood, having produced 69 books and more than 300 articles to date. The topics range from networking to artificial intelligence, and from database management to heads-down programming. His most recent book is Web Development with Microsoft Visual Studio 2005 (Sybex, ISBN: 078214439X). His technical editing skills have helped more than 48 authors refine the content of their manuscripts. You can reach John at mailto:[email protected] or via his Web site at http://www.mwt.net/~jmueller/.
