So far, you’ve seen how to make good-looking web pages with clever and useful controls. You know how to change the appearance of the page in response to user selections, and how to use AJAX to enhance the performance of your application. But the applications you’ve made so far have been limited in what they can actually do. In this chapter we add the most frequently sought after functionality: the ability to retrieve, change and store data.
Think about the web sites you visit most often, and you’ll find that almost all of them have one thing in common—they interact with persistent data. Persistent data is data that survives a single session; data that you expect will be there the next time you visit. In fact, it may even be data that can have significant financial consequences.
Shopping sites have databases to track their inventories and customer transactions. News sites keep databases with articles and photos in them, perhaps referenced by topic and date. Search engines use unimaginably large (and wicked-fast) databases.
Nearly every real-world commercial web application must be able to perform the four essential “CRUD” interactions with a database: Create, Read, Update, and Delete.
Fortunately, ASP.NET provides controls that make it easy and fast to perform these essential activities. We will demonstrate these interactions with SQL Server Express (or its big brother, SQL Server) but they work equally well—or nearly so—with Microsoft Access and most commercial databases. In theory, you can interact with virtually any collection of data or with XML files, but that is an advanced topic we won’t go into here.
Along the way we’ll show you enough about database interactions that even if you’ve never used a relational database, such as SQL Express, you’ll have little trouble working with one through your web application.
To see how to interact with a database, you’ll begin by creating a web application that can be used to display information about the AdventureWorks database. You’ll start out by simply retrieving and displaying a subset of data. These exercises will teach you how to connect your controls to a database to retrieve, filter, and sort the data and then use the myriad options for presenting it attractively.
As you may remember, AdventureWorks is a free database from Microsoft that represents a fictional company that sells outdoor and extreme sports gear. The database tracks products, inventory, customers, transactions, and suppliers.
See Chapter 1 for instructions on installing this sample database if you have not already done so.
ASP.NET includes a number of controls specifically designed for displaying data. We’ll focus on the GridView control, but other data controls include the DataList, Repeater, DetailsView, and FormView.
The GridView control displays columns and rows of data and allows sorting and paging. It is by far the most popular data display control and is ideal for understanding how data display controls interact with data-retrieval controls and code. The GridView control allows the user to click on a column header to sort the data. GridViews also let you present just a small subset of the data at one time, called a page, with links for easy access to other pages—this process is called “paging” through data. You can do these, and for numerous other data manipulations, with very little programming. A GridView with data from the AdventureWorks database is shown in Figure 4-1.
Binding Data Controls
Database information is stored in memory as tables (just as it is retrieved from a relational database). Tables consist of rows and columns that match nicely to the GridView control.
You could write code to pick out each piece of data you want and write it into the appropriate row or column of the data control, but that’s time-consuming and error-prone. It is more efficient and safer to bind the control directly to the underlying data.
In the early days of Graphical User Interface (GUI) programming, binding was a bit of a “trick”—great for simple programs, but useless for commercial applications because the minute you wanted to do anything out of the ordinary, the binding would become a straitjacket. Microsoft has solved that with ASP.NET by exposing events on the Data Control that allow you to insert custom code at every stage of the retrieval and binding of the data to the control.
Figure 4-1. This GridView control displays data from the AdventureWorks database in a table format that makes it easier to read, and allows users to click the column headings to sort the data.
Binding is most often used with the larger data controls such as GridView, but you can also bind many other controls, such as DropDownList, ListBox, CheckBoxList, and RadioButtonList. All of these controls have a DataSource property that identifies the source to which the control is bound. For example, you might keep a list of all your customers’ names in a database. Binding that data to a ListBox can be a convenient way to allow a customer service representative to quickly pick a customer rather than typing in a name that might otherwise be difficult to spell.
To see how all this works, you’ll build the GridView from Figure 4-1. Once you have it up and running, you’ll add some features to it, including the ability to use the grid to update the database with new data!
To begin, create a new ASP.NET AJAX-enabled web site named AWProductData.
The IDE automatically places the all-important ScriptManager control onto your page. Open your toolbox and click the Data tab. You’ll find two types of objects: display controls, which are designed to present data, and DataSource controls, which are designed to help you manage interacting with data sources, as shown in Figure 4-2.
Figure 4-2. The Data tab in the Toolbox contains the controls that you’ll need to display data, and to interact with data sources.
Using a DataSource Control
By default, the Data controls are arranged so the display controls are on top, and the DataSource controls are below (You can drag them into any order you like or arrange them alphabetically by right-clicking on any control and selecting Sort Items Alphabetically.) There is a DataSource control for use with Microsoft SQL Server or SQL Server Express, one for Microsoft Access, one for any type of Object, one for use with SiteMaps (for binding to menu controls—more on this in Chapter 6), and one for XML documents as a data source.
Since the AdventureWorks database is a SQL Server database, you’ll use the SqlDataSource control whether you are using SQL Server or SQL Server Express. This control will allow you to access the AdventureWorks database, but first you need to direct the control where to find it.
Switch to Design view and drag the SqlDataSource control from the Toolbox directly onto the design surface. A Smart Tag will open, as seen in Figure 4-3.
Figure 4-3.A Smart Tag opens when you drag the SqlDataSource control onto your page allowing you to configure the data source.
When you click on Configure Data Source, you invoke a wizard that will walk you through the steps of configuring your data source—hooking up the control to the underlying data table(s).
The first step is to create (or choose) a data connection as seen in Figure 4-4.
Figure 4-4.To configure your DataSource control, you need to provide it with a data connection. You can choose a preexisting connection from the list (if you have previously created any for this web site), or create a new data connection by clicking the New Connection button.
Previous data connections in this web site will be listed in the drop-down menu. To make a new connection, click the New Connection… button to get the Add Connection dialog shown in Figure 4-5.
Figure 4-5.The Add Connection dialog is where you specify a new connection for your data source. Select the server, the logon credentials, and finally the database you want to use.
Following the steps in Figure 4-5, prepare your connection to the database:
Select your server from the Server Name drop-down menu. If it is not there, type the name of the server. Typically, if you are using SQLExpress, the name will be “.\SqlExpress” (dot-slash then SqlExpress) and if you are using SQL Server it will be the name of your computer, or it will be (local)—including the parentheses.
Leave the radio button set to “Use Windows Authentication.”
If Windows Authentication does not work, you may need to use SQL Server authentication. If so, your database administrator will tell you what credentials to enter. They may or may not be the same as your Windows login credentials.
Select the option, “Select or enter a database name:”.
Choose the AdventureWorks database in the database name drop-down.
Click the Test Connection button to verify that it all works.
This dialog box constructs a connection string, which provides the information necessary to connect to a database on a server.
Click OK to complete the string and return to the Configure Data Source Wizard. Click the plus mark next to “Connection string” to see the connection string you’ve just created, as shown in Figure 4-6. The segment Integrated Security=True was created when you chose Windows Authentication rather than SQL Server Authentication.
In Figure 4-6, the Wizard displays an expanded data connection in the drop-down menu, consisting of the name of the server (in this case the local machine, virtdell380, concatenated with sqlexpress, followed by the name of the database and database owner). You don’t need to enter this information yourself.
Figure 4-6. Click the plus sign to view the connection string you just created. This is what gives your control access to the database.
When you click Next, the Wizard will ask if you’d like to save this Connection string in the “application configuration file.” In an ASP.NET program, the application configuration file is web.config, and saving the connection string there is an excellent idea, so be sure to check the checkbox and give the string a name you can easily remember. The Wizard will make a suggestion for the name of the connection string, as shown in Figure 4-7.
Figure 4-7.It’s a good idea to save the connection string in the application’s web.config file, so you can use it again with other controls.
This will cause the following lines to be written to web.config:
The Wizard next prompts you to configure the SELECT statement. The SELECT statement is the SQL code the control uses to retrieve the exact subset of data you are looking for from the database. Fortunately, if you are not fluent in SQL (most often pronounced “see-quill”), the Wizard will help you build the statement.
Starting with the radio buttons at the top of the dialog box, select “Specify columns from a table or view.” (You would select the other button if you had a custom SQL statement prepared, as you’ll see shortly.)
Selecting the button, displays the table drop-down menu. Here, you are presented with the various table options that represent the different sets of data in the database. For this exercise, choose the Product table. The various columns from the Product table will be displayed, as shown in Figure 4-8. Simply check the columns you want retrieved, and they’ll be added to the SELECT statement. The choices you make will be displayed in the text box at the bottom of the dialog. For this exercise, select the ProductID, Name, ProductNumber, MakeFlag, SafetyStockLevel, and ReorderPoint columns. You could narrow the set of data with the WHERE button, or specify the order in which to retrieve the data with the ORDER BY button. For the moment, you can ignore them both.
Figure 4-8. To configure the SELECT statement, specify the table and columns within it you want to retrieve, and the Wizard builds the proper SQL statement for you…more or less.
When you’ve completed the table setup, click Next, to move to the last page of the Wizard, and then click the Test Query button. The test fails, as shown in Figure 4-9.
Figure 4-9. The Wizard let you down; the Query Test failed and you’re looking at this error message because this database requires a schema name in front of the table names.
In this instance, the Wizard falls on its face. It turns out that the AdventureWorks database prefixes a schema name in front of each table name and the Wizard is unprepared for that. It generates a SELECT statement without schema names, as you saw back in Figure 4-8.
Schema in this context refers to an optional name used for organizing the tables in a large database. For example, in the AdventureWorks database, all the tables relating to the HR department have the schema name HumanResources prefixed to every table name, separated by a period, such as HumanResources.EmployeeAddress. Other schemas in the AdventureWorks database include Person, Production, Purchasing, and Sales.
As mentioned, a schema name is optional in SQL. In fact, in our experience, they are rarely used, and the Wizard is unaware of them. However, since the AdventureWorks database (which ships as part of Microsoft SQL Server) does use them, the Wizard becomes confused and flies off to Kansas leaving you on your own.
The square brackets surrounding each field and table name in the generated SELECT statement are not required, but are used to guarantee that there will be no problems if the name includes any space characters (usually a very bad idea in any case). We often remove them from the finished statement to enhance readability.
Think of this as proof that people are not yet entirely replaceable by automation. Hit the Previous button to go back one step and fix the SELECT statement manually. Click the radio button captioned “Specify a custom SQL statement or stored procedure,” and then click Next. In the SQL Statement box, shown in Figure 4-10, type in:
SELECT ProductID, Name, ProductNumber, MakeFlag, SafetyStockLevel, ReorderPoint FROM Production.Product
Figure 4-10. The SQL statement editing dialog, after adding the schema name to the table name, and removing all the extraneous square brackets.
As you can see, this is nearly the same SELECT statement that you built with the Wizard in Figure 4-8, except the Product table now has the required schema (Production) in front of it. We’ve also left out the square brackets on the columns, as mentioned in the note above.
Click Next to proceed to the next page of the Wizard, and then click Test Query. This time, you should get the results shown in Figure 4-11.
Figure 4-11. When you test the SELECT statement this time, you’ll see the results you were looking for.
Behold—the triumph of 3 billion years of random mutation and natural selection over 50 years of automation!
Click Finish to save your work. It may not look like much, but you’ve just enabled your application to access the AdventureWorks database, meaning all that data is now at your control.
Please check back next week for the continuation of this article.
/ 7 
ASP.NET Export to Excel and Word using VB.NET and C#
