Create New Views of Data with ASP.NET 2.0: Part II

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 PartI of this series, you discovered some new ways to create connections inyour data in ASP.NET. These new techniques almost seem like cheating, but youcan use them to gain valuable time when creating a project. Because time isbecoming ever more critical to developers today, anything that saves timeshould be welcome.

To wrap up this series, we ll consider what you can dowith the data now that you have a firm basis for accessing it. ASP.NET 2.0provides 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 anduse. I think you ll agree that, even though there are things you might like toadd or do differently, these new controls are a vast improvement over whatMicrosoft provided in the past.

Even though users are going to be impressed with the newviews of data you can provide, the big issue for many developers today iskeeping the data safe. Visual Studio makes this process considerably easier, aswell. In the past, the IDE often caused more problems than it solved in usingall the functionality that SQL Server provides. Today you ll find that it sextremely easy to use views and stored procedures in your applications. Relyingon these two SQL Server features makes it a lot harder for miscreants to dothings that you hadn t anticipated, and keeps your data safer even when a hackerdoes manage to break into your application. The technology may seem simple, butit can significantly improve security without a lot of extra effort on yourpart. 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 thepast, ASP.NET developers had to choose between the limited views that Microsoftprovided or a lot of custom programming. The latest version of ASP.NET remediesthe situation somewhat by providing a number of alternative controls forchanging the data view. You saw the GridView control in PartI, so I won t discuss it again here.

The DataList control provides a list of information, asshown in Figure 1. The default settings use the field name followed by itsvalue, as shown in the figure. You ll find that this control works well forreports or mailing labels. Use the DataList control when you need a list ofitems.

Figure 1: Use the DataList controlfor reports or mailing labels.

The power of the DataList control is in the templates itprovides. Select Edit Templates from the DataList Tasks smart tag to see thetemplates shown in Figure 2. As you can see, you can individually change manyof the items that you had to program in the past. For example, you can easilyadd an alternating item template for even-numbered entries to differentiatethem from the odd-numbered items in the list.

Figure 2: Templates provide apowerful means of changing the control presentation without programming.

Notice the Item Template shown in Figure 2. You can changeeach of the controls within the template to provide a custom look. If you wantto 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 informationas 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 ordata in the wrong format for a particular need. When you finish changing thetemplate, simply choose End Template Editing from the DataList Tasks smart tag.

It s easy to confuse the DetailsView control with theDataList control at first because they look essentially the same. However, theDetailsView control differs from the DataList control in a number of ways.First, the DetailsView control works with individual records. The presentationshows a two-column table that contains the field names first and the valuessecond. Second, because of the presentation method, the DetailsView controloffers fewer templates, but it also lets you manage the fields it displaysdirectly. The DetailsView control works well for master/detail displays. Youcan also use it where you want a form-like view in tabular format. The controldisplays only one record at a time, rather than the list of records shown inFigure 2.

The FormView control is a cross between the DetailsViewand DataList controls. You use it when you want to present one record at a timein a form view. The presentation features aren t quite as nice as the DataListcontrol; 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 importantconsideration with the FormView control is that it provides functionality forviewing, editing, deleting, and inserting records without the codingrequirements you might have faced in the past.

The Repeater control is completely freeform. Unlike allthe other display controls presented so far, the Repeater control doesn t doanything automatically when you assign it a data source. In fact, you can teven use this control in Design view except to assign the data source. In thiscase, you must work with the control in Source view by adding any of thetemplates it supports, including ItemTemplate, AlternatingItemTemplate,SeparatorTemplate, HeaderTemplate, and FooterTemplate. Because the IDE doesn tdo anything for you, this is the best option when you need a completely customlook for the display.

Protecting Data with Views

Over the years I ve seen a lot of very convoluted code toperform database processing in ASP.NET applications. In many cases, you don tneed to use code to obtain a particular view of data. All you really need is aview that you create in SQL Server. Using a view lets someone see the datawithout editing it. Views present a particular dataset and don t require anyprogramming on your part. The most important reason to use a view is to protectyour data. Because SQL Server controls the view and is read-only, a novice usercan t accidentally change the data.

You might think that you must exit Visual Studio to createa 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 andlocate the Views folder for that connection. The following steps help youcreate a new view for the Northwind database that lists products by supplier:

1)     Right-clickthe Views folder and choose Add New View from the context menu. You ll see anAdd Table dialog box that contains a list of the tables for the Northwinddatabase.

2)     Highlightthe Categories, Products, and Suppliers tables by Shift-Clicking each table entry.Click Add. The IDE automatically adds the tables to the view.

3)     ClickClose to close the Add Table dialog box. At this point you must select thefields 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 thequery 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 fieldsin the top window of the view. This action creates a list of field entries youcan configure in the second window.

5)     ChooseAscending in the Sort Type field for the SupplierID field in the second window.This action sorts the data.

6)     Clearthe checkbox for the Discontinued field. This keeps the field from appearing inthe output, even though you can use it for other purposes.

7)     Type<>1 in the Filter field for the Discontinued field. The Discontinuedfield is a Boolean value, so setting this filter rejects any items that thesupplier has discontinued from the output.

8)     Testthe query by clicking the Execute SQL button on the toolbar (the button withthe exclamation mark icon). Figure 3 shows typical output from this query.

Figure 3: Create a view to displaydata without allowing any edits.

At this point you can save the view. Simply click Save asnormal and the IDE will create the new view in SQL Server for you. I used aname of Products by Supplier for this view. Once you create the view, you canaccess it as you would any other data source. Unfortunately, you can t use thedrag and drop technique as you can with a table (you must configure theSqlDataSource manually). When you get to the Configure the Select Statementpage of the Configure Data Source wizard, choose Products by Supplier from theName dropdown listbox and check * in the Columns list. After you finish theconnection, you can use any of the data view controls to display theinformation on screen. Notice that you ve created a custom view of theinformation 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 proceduresto select, insert, update, and delete data. As previously noted, you can selecta different stored procedure for each of these actions when you create adatabase connection. Consequently, you can create stored procedures that letthe 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 asingle line of code. More importantly, the stored procedure also makes itpossible to create unique database views in your ASP.NET application withoutwriting a single line of code.

You ll notice two significant differences between creatingviews and stored procedures. First, the IDE doesn t assume anything aboutcreating a stored procedure. You start with what amounts to a blank editingarea where you can type the SQL query manually if you want. Begin by removingthe template code from the stored procedure (leave the comments for variables becauseyou might need to add variables to the stored procedure later). Right-click theediting area and choose Insert SQL from the context menu. You ll see the sameAdd Table dialog box that you see when you use a view. At this point youperform the same actions as you would with a view to create a select storedprocedure.

Second, you can create stored procedures to performactions 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 theChange Type menu on the context menu. Changing the query type automaticallychanges 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 itsappearance to match the query type.

Notice that an update requires that you provide variablenames 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 toupdate the CategoryName field. You must define these variables separately fromthe query. Consequently, the full SQL statement for this example would looklike 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: Creatingan update query.

Even though Query Builder created the actual query, youneed to add the variable declarations for the stored procedure. Variablesappear with an @ sign in front of the variable name. The variable type mustmatch the type of the data field you want to update. You can obtain thisinformation from Server Explorer by selecting the table column and viewing itsproperties in the Properties Window. Note that you should also create avariable for the column that uniquely identifies the record you want to update (CategoryIDin this case).

New Data Management Methods

This overview of ASP.NET data viewing techniques shouldprovide you with a new outlook on database management. It really is easier tocreate database applications using the new functionality that ASP.NET 2.0provides. In addition, you can do a lot more without writing any code at all.The biggest issue is figuring out the sometimes convoluted steps toaccomplishing a particular task. Even though Microsoft has made databasedevelopment tasks easier, they need to advertise some of these features in away that developers can find them more easily.

The source code accompanyingthis series is available for download.

John Paul Muelleris a freelance author and technical editor. He has writing in his blood, havingproduced 69 books and more than 300 articles to date. The topics range fromnetworking to artificial intelligence, and from database management to heads-downprogramming. His most recent book is WebDevelopment with Microsoft Visual Studio 2005 (Sybex, ISBN: 078214439X).His technical editing skills have helped more than 48 authors refine thecontent of their manuscripts. You can reach John at mailto:[email protected] or via his Web siteat http://www.mwt.net/~jmueller/.