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

Getting Started





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

Getting Started


By John Paul Mueller


Databases are the center of the business universe. Companies use them for all kinds of tasks everything from storing inventories to tracking customers. It s no wonder, then, that Microsoft has added new database features to ASP.NET 2.0. Many developers complained that the database support in the previous version was a tad light; indeed, the many workaround articles made the deficiencies in the database support apparent. This series will help you understand how the new database features work.


Note that this series uses the Northwind database for several examples. If you re using SQL Server 2005, you know that it doesn t include this database anymore. I chose to use the Northwind database because it s easier to understand than the newer AdventureWorks database and I wanted to be sure everyone could work with the examples. You can download the Northwind database at


Creating a Basic Connection

You have access to many of the features that Visual Studio provided in the past. However, you won t find SQL Server as a default entry for the servers in Server Explorer anymore you must create database connections explicitly. This means right-clicking Data Connections in Server Explorer and choosing Add Connection from the context menu. You ll see the Add Connection dialog box. The appearance of this dialog box varies by the Data Source you select.


If you want to change a data source, click Change. Microsoft has changed the data source selection process, as shown in Figure 1. This new dialog box makes it easier to choose a data source because you don t have to go through an entire list of odd-sounding names. After you select one of the sources, make sure you check the Data provider dropdown listbox (some data sources support more than one provider). Microsoft usually supports the newest option, so you won t need to change this selection very often. If you plan to use the same data source for most or all of your work, you ll want to check Always use this selection or Visual Studio won t remember your choice.


Figure 1: Change the data source and data provider as needed to create a database connection.


After you choose a data source, follow the instructions provided by the wizard. Generally, you ll find that Microsoft hasn t changed much in this area from previous versions of Visual Studio. For example, the SQL Server connection requires that you provide a SQL Server instance name, authentication choice, and database information.


One new feature that the SQL Server version of the Add Connection dialog box does support is the option to connect directly to a file. For example, if you want to attach to the Northwind database used by previous versions of SQL Server, you don t have to use any of the SQL Server utilities to do it. Simply locate the MDF file by browsing for it. The Add Connection dialog box will add full path information to the database, as shown in Figure 2. You must also provide a logical name for the database. Interestingly enough, this approach automatically adds the database to SQL Server for you.


Figure 2: Visual Studio 2005 lets you create directly a connection to a SQL Server file, so you don t need to fiddle with the SQL Server utilities.


Using Basic Drag and Drop

The easiest way to create a data display once you have a connection in place is to use drag and drop. Simply drag a database object from Server Explorer onto the Web form. The process for creating the required objects and code is almost automatic. You ll see results immediately, but you need to configure the display to suit your needs. In many cases, you ll also need to add code-behind to support the data connection. For example, you might need to perform pre-processing before you perform an update. Even so, you can perform an amazing amount of work using drag and drop to let the IDE do the work for you.


Visual Studio 2005 developers will notice almost immediately that the Web projects you create using Visual Web Developer don t work the same as the desktop projects. A Windows form won t accept a database object you drag and drop from Server Explorer. When you create a Windows form application, you must first define a data source by creating an entry in the Data Sources window, and then drag and drop objects from the Data Sources window to the form. Visual Web Developer works much the same as Visual Studio did in the past. You can drag and drop a database object, such as a table, from Server Explorer to the Web form and see the resulting GridView and data source on screen. Although the Visual Web Developer approach is simpler and faster, you only get one view of the data (a grid).


You ll experience other differences between Windows forms and Web forms. When using the Data Sources window, you can choose between any of the displays that Visual Studio provides using a dropdown listbox entry before you drag and drop the object to the Windows form. In addition, you can configure the individual fields as needed for your application. For example, a text field can appear as a TextBox, ComboBox, Label, LinkLabel, or ListBox. A Web form requires configuration after the fact. If you want to create a checkbox on screen to hold the content of a Boolean data field, you must add it manually.


Microsoft could have reduced the differences between desktop and Web applications, but probably not made them precisely the same. In fact, Microsoft hasn t provided a documented reason for these differences. In my opinion, the difference comes down to one of presentation. Because of the way ASP.NET works, it s likely that Microsoft found it difficult or impossible to create the localized data store that the Data Sources window provides. Perhaps this feature will appear in the next version of Visual Studio.


Configuring the Drag and Drop Presentation

When you drag and drop a table, view, or stored procedure from Server Explorer to the Web form, you see two controls, a GridView that manages the data and a data source, such as a SqlDataSource control, that manages the connection to the database. In both cases, you can perform almost all configurations using smart tags. You access the smart tag by clicking the tiny right arrow that appears in the upper-right corner of the control. Figure 3 shows the smart tag for the GridView control; Figure 4 shows the smart tag for the SqlDataSource control used for this example.


Figure 3: Use the GridView smart tag to change the way the application displays data.


Figure 4: Use the SqlDataSource smart tag to change the database connection and data manipulation commands.


The GridView smart tag contains the options you would expect. You can add paging, sorting, editing, deleting, and selection with the click of a mouse. In general, unlike previous versions of ASP.NET, these features work as expected as long as you provide the correct SQL commands as part of the data source. When using multiple tables, it s normally best to create views or stored procedures as part of the database and rely on them to perform the work for you. Using code-behind also works, but usually requires more work on your part and can result in performance penalties in some cases. If you have specialized needs that a stored procedure can t handle (a rarity), you can always create an assembly for SQL Server with the SQL Server Project template found in the Database folder of the language of your choice in the New Project dialog box. Creating a SQL Server assembly provides performance benefits and keeps your code hidden from prying eyes.


Sometimes a configuration option isn t very apparent. For example, when you select the Configure Data Source task on the SqlDataSource control smart tag, you ll see a Configure Data Source wizard. The first few steps of this wizard do, indeed, change the connection, as you d expect. However, the third step contains two options that let you choose between a custom set of SQL statements or a stored procedure, or an existing view or table. When you select the first option, Specify a Custom SQL Statement or Stored Procedure, you see SELECT, UPDATE, INSERT, and DELETE commands for this SqlDataSource control, as shown in Figure 5.


Figure 5: Use the IDE to help you create the required data manipulation commands.


In short, you don t need to write the actual source code for data manipulation (in most cases). You can use an existing stored procedure or Query Builder to create the required SQL statement. Using this approach tends to reduce errors. You can use this wizard whenever and as often as you like to obtain the desired results. In addition, this technique helps you create complex queries that otherwise would prove difficult. For example, you can create multiple table queries using this technique. Always execute the Refresh Schema task (as shown in Figure 4) whenever you change the data source in any way to ensure that the IDE knows about any changes you made.


Choosing a Data Source

You can choose from a number of data source controls in Visual Studio. In all cases, the control helps you interact with a particular kind of data. Most developers are already used to working with a data source to access SQL Server, but ASP.NET 2.0 provides an increased level of connectivity through other control types.


The data source controls include the SqlDataSource that creates a connection to SQL Server. The AccessDataSource provides similar connectivity to Access databases. The new ObjectDataSource control lets you access data-aware middle-tier components in a multi-tier application. My personal favorite is the XmlDataSource that you can use to access XML data anywhere on the Web site. Finally, the SiteMapDataSource control provides special access to the site data. You ll normally couple this control with a SiteMapPath control.


To add a new data source to your application, simply drag and drop it from the Toolbox onto the form. After you drag and drop the control, the IDE normally opens the smart tag and highlights the Configure Data Source option for you. The steps you follow to configure the data source depend on the data source control you choose. You ve already seen the SqlDataSource control earlier in this article. An XmlDataSource requires that you provide the name of the XML file as a minimum. However, XML data sources normally work better when you can also supply an XSL file to transform the content, as shown in Figure 6. It s possible to further refine XML as a data source by adding an XPath expression to filter the data.


Figure 6: Each data source type requires a different configuration process; XML is the easiest by asking only the XML filename.


XML data sources require special handling or the controls used to display the data won t work properly. Figure 7 shows an example of a typical XML file containing address book listings. This format won t work. You ll see an error stating that the display control, such as a GridView, wasn t able to determine the fields to use for the columns from the data source.



     George Smith


        1234 West Street

        Suite 20


















Figure 7: A non-working XML view of an address book.


Unfortunately, Figure 7 shows the format that most developers use to store data. The listing shows how a developer might take full advantage of the hierarchical structure that XML provides to organize the data. You can use XSLT to transform the data into the form shown in Figure 8. Notice that all of the same data appears in this XML file, but the information appears as values, rather than child elements. This second form isn t nearly as flexible as using elements.




     Address1="1234 West Street"

     Address2="Suite 20"






     TimeToCall="8:30 PM"

     SpouseName="Nancy" />

Figure 8: A working view of the address book.


Many developers are accustomed to using XSLT to transform XML into Web pages, but you can use it for so much more. In this case, you receive another XML file as output. The XSLT to transform Figure 7 to Figure 8 appears in Listing One. You specify this file as the input to the Transform file field shown in Figure 6.


This XSLT file assumes that the XML input you provide is symmetrical that every field appears in the source file even when the field doesn t contain data. You d need to provide additional processing to handle files that aren t symmetrical (those that contain missing elements when the field has no value).


It s important to remember that XSLT automatically adds the XML processing instruction to the output unless you specifically tell it not to do so. The XSLT code begins by creating the AddressBook root node shown in Figure 8.


The next step is to create one element for every person in the database. You use the element to loop through each person record and create the required element for it. Every other entry appears as an attribute for the element. Consequently, you see the code to start the element, but don t see the closing tag until the end of the XSLT file.


Some of the elements contained in the original XML file are easy to process. You simply change them from elements to attributes, as shown for the element. The tag creates the name and the tag creates the value.


Notice in Figure 7 that some elements contain multiple values, such as the

element. In this case, the element contains two child elements. This is an area where you can run into problems that require additional processing to determine whether the XML file contains the correct number of elements. However, you also need to determine how to create the attributed form. Two attributes can t have the same name. The example solves this problem by renaming the elements and giving them a number for uniqueness. The name= {concat( Address , position)} attribute tells XSLT to name each element Address and add the value of its position within the hierarchy to the name. Consequently, the first element appears as the Address1 attribute in the output. Because you must process the
elements in a loop, you access the value of each child element using instead of a name.


What happens when you re looking for a specific kind of element that has a generic name, such as the element? Each child element has a Location attribute that determines the telephone number type. In this case, you must include logic to detect the telephone number location and provide the correct attribute as output. The element helps you make the decision and the test= string(@Location) = Office provides the testing criterion (matching the Location attribute to the desired string value). The output contains the specific location as an attribute name and follows it with the element s value. The same processing sequence occurs with the children of the element.


Getting Beyond the Setup

At this point, you have your project set up. The important issues are to ensure you have a good connection, create the objects you require to interact with the database (such as view and stored procedures when working with SQL Server), use drag and drop to create as many application features as possible, and consider the use of alternative data sources when necessary. The one issue that many developers can t seem to get around is using drag and drop, which is an extremely valuable feature that is underused in many cases because developers don t want to appear to be too productive or doing too little coding. These new features in Visual Studio can make you more productive than you ever thought possible.


The next part in this series concentrates on the visual aspects of your application. Now that you have a good base in place, it s time to do something interesting with it. Visual Studio provides more controls than ever before to make your applications look great, and these new controls don t require a lot of work on your part to use. In addition, by combining these new controls with simplified use of both stored procedures and view, you can make your application more secure. Because presentation is everything to the user, read Part II to see how you can become a hero to the users of your application, while keeping safe the data on which users rely.


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


Begin Listing One Transforming Standard XML to a Attributed Format

















           name="{concat('Address', position())}">














































End Listing One




Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.