One thing missing from ASP.NET 1.x was a good way to use Rapid Application Development (RAD) tools to develop database-oriented Web Forms. I personally found myself using code whenever I needed to access data, either in a bound control or for other sorts of manipulations. Microsoft Visual Studio .NET (the previous version of Visual Studio, used to support 1.x applications) allowed you to bind data in code, but some of the implementation details were not great, and you generally needed to use code to initiate loading the data and the data binding.
ASP.NET 2.0 offers a major improvement, the various DataSource controls. A DataSource control is a non-visual control that encapsulates all the information needed to retrieve (and optionally insert, update, and delete) data without using code. Using a DataSource control relieves the developer from having to create a great deal of boilerplate code to bind the results of a query to a data control, allowing virtually all work to be done declaratively. Rather than a single DataSource control, there are several. The standard DataSource controls are:
SqlDataSource For SQL Server databases
AccessDataSource For Microsoft Office Access databases
ObjectDataSource For business objects
XmlDataSource For XML data
SiteMapDataSource For connecting to a valid site map
All DataSource controls inherit from the DataSourceControl class, which implements the IDataSource and IListSource interfaces. The examples in this book will use the SqlDataSource control.
Using SqlDataSource allows me to connect to SQL Server databases. SQL Server 2005 Express Edition offers a no-cost way to develop applications targeted to SQL Server 2005; for performance reasons, using a server-based database, as opposed to a file-based database (such as Access), makes a great deal of sense for a Web application.
The SqlDataSource Control
All the examples in this chapter are placed in a Web site named DataBinding. I created the Web site by using the File/New/Web Site menu option. In the newly created DataBinding Web site, open the Web Form generated by Visual Studio (named Default.aspx). Open the Toolbox, and drag an SqlDataSource control from the Data section onto the Web Form. It doesn't matter exactly where you place the control, because SqlDataSource is a non-visual control. After you place the control on the form, the SqlDataSource Tasks menu appears, as shown in Figure 5-1.
Figure 5-1: The SqlDataSource control, with the SqlDataSource Tasks menu displayed
Click Configure Date Source, the only option on the SqlDataSource Tasks menu, and the Configure Data Source wizard appears, as shown in Figure 5-2.
Figure 5-2: The first page of the Configure Data Source wizard
Because this is a new Web site, no database connections are configured. To generate connection configurations, click New Connection. The Add Connection dialog box appears, as shown in Figure 5-3.
Figure 5-3: The Add Connection dialog box
In the Server Name box, you can enter the name of the server that you want to use. If you are unsure of the name of the server (or if you don't want to type), you can click the down arrow to see a list of available servers, like the list shown in Figure 5-4.
Figure 5-4: The Add Connection dialog box showing a list of available servers
For this example, I selected the machine I was working on, named DELL670. In the Log On To The Server section, click Use Windows Authentication, rather than Use SQL Server Authentication.
Note
Using Windows Authentication for the connection will work properly as long as you are testing the Web site using the Web server built into Visual Studio 2005. Recall from Chapter 1, "The Web Forms Environment," that the built-in Web server runs as a normal application, whereas a "real" Web server runs as a service. When I move the application into production, presumably using Internet Information Services (IIS), I must do one of two things. I could change the connection to use SQL Server authentication, and then supply an explicit user name and password. Or I could add the user whose security context an ASP.NET application will run under in IIS (in this case, the user is <machinename>\ASPNET) as a user in SQL Server and continue using Windows Authentication.
In the Connect To A Database section, select the BikeBlog database from the list of databases, and then click OK. In the Configure Data Source wizard, you can see the resulting connection string by clicking the plus sign (+). The result should be something like Figure 5-5.
Figure 5-5: The Configure Data Source wizard with the connection string displayed
Click Next, and the second page of the wizard appears, as shown in Figure 5-6.
Figure 5-6: The second page of the Configure Data Source wizard
Saving the connection string is generally useful, and the default name is usually reasonable. On the third page of the Configure Data Source wizard, you can determine exactly how data should be obtained for the data source you are creating. You can specify a custom SQL statement or a stored procedure (which we will do later in this chapter), or you can specify the columns to include, and in other ways customize the SQL SELECT statement by using a convenient user interface.
Note
Whether you should use dynamically generated SQL statements or stored procedures is an issue that can cause heated debates in the developer community. For a discussion of the issues involved, with pointers to opinions that differ from mine, see my article "To SP or not to SP in SQL Server" at http://www.simpletalk.com/2005/04/11/to-sp-or-not-to-sp-in-sql-server/.
In this example, the BlogEntry table should be the default table. Figure 5-7 shows the columns that should be selected. Note that as you select columns, the SELECT statement appears at the bottom of the page. Selecting the columns and specifying the WHERE clause and the ORDER BY clause, and then looking at the resulting SELECT statement, can serve as a way to learn SQL syntax.
Figure 5-7: The Configure Data Source wizard with several columns selected
In SQL, a WHERE clause is used to restrict the number of rows returned by the query. Click WHERE, and the Add WHERE Clause dialog box shown in Figure 5-8 appears. In Figure 5-8, I added some data for an example WHERE clause.
Figure 5-8: The Add WHERE Clause dialog box with a WHERE clause entered
The Column list shows the columns in the table involved in the SELECT statement. I selected DateEntered for illustrative purposes. The Operator list allows you to select an operator to use in the WHERE clause. Several operators are available only for columns that are string-type columns. These string-only operators include LIKE, NOT LIKE, and CONTAINS. Finally, you can select the source of the right side of the SQL expression in the Source list. This is where some of the magic happens that allows for substantial reduction in the number of lines of code. In this example, I selected None as the source, which allowed me to enter a literal value in the Value box (in this example, 1/1/2005). The other options for source are:
Control The value to compare comes from a control on the current form.
Cookie The value to compare comes from a cookie.
Form The value to compare comes from a value on the form. Generally, Control is a better choice, unless you are trying to access a form element that is not in an ASP.NET runat=server form. Using Control allows you to use the server-side ID of the control. If you use Form, you must ensure that the ID you are using is the final ID that ASP.NET renders, which might or might not be the same as the server-side ID.
Profile The value to compare comes from a Profile value, part of the personalization and membership system that will be covered in Chapter 7, "Security and Administration."
QueryString The value to compare comes from a value passed in on the query string of the URL. If you select this option, you are prompted to specify the session variable key, and you can offer a default value. A URL can be in the following format: MyPage.aspx?var1=val1&var2=val2. Given this string, using QueryString as the source, if we specify var1 as the QueryStringField, val1 will be used as the value.
Session The value to compare comes from session state. If you select this option, you are prompted to specify the session variable key, and you can offer a default value.
When you click Add in the dialog box shown in Figure 5-8, the dialog box shown in Figure 5-9 appears.
Figure 5-9: The Add WHERE Clause dialog box with a WHERE clause added
When you click OK, you return to the Configure Data Source wizard, where you can see that the WHERE clause has been added to the SELECT statement at the bottom of the page, as shown in Figure 5-10.
Figure 5-10: The Configure Data Source wizard with a WHERE clause added to the SELECT statement
Next, click ORDER BY to open the Add ORDER BY Clause dialog box. ORDER BY is the part of the SQL SELECT statement that determines the order in which rows are returned. In an SQL SELECT statement, without an ORDER BY clause, the order of rows returned is undefined (even if it seems as if the rows returned are in some specific order). You can select three columns, choosing whether each is ascending or descending. In this example, it is reasonable to sort entries by DateEntered in descending order. This results in the dialog box shown in Figure 5-11. Note the SELECT statement at the bottom of the dialog box.
Figure 5-11: The Add ORDER BY Clause dialog box with the DateEntered column selected
After you add the ORDER BY clause, click Advanced in the Configure Data Source wizard to open the dialog box shown in Figure 5-12.
Figure 5-12: The Advanced SQL Generation Options dialog box
In this dialog box, you can allow data to be added or deleted from the configured data source and enable optimistic concurrency. One of the problems with any multi-user database system is what happens when two users simultaneously modify the same data. In many applications, the user who saves last saves best. For example, imagine that user Doug reads a row, and then user Jean also reads that row, and then user Jean saves changes to the row. When user Doug tries to save the data, his changes will overwrite Jean's changes. Often this is completely acceptable. If it is not, after selecting the first check box shown in Figure 5-12 to create INSERT, UPDATE, and DELETE statements, select the second check box. Selecting the Use Optimistic Concurrency check box modifies the UPDATE and DELETE statements to determine whether another user modified the data since it was loaded. For our example, select the first check box only.
Note
In this particular example, enabling optimistic concurrency will result in invalid SQL, because one column in the table is a text column. I could have avoided this by using a new feature of SQL Server 2005 to create a very long nvarchar column (up to 4000 Unicode characters in length) instead of the text type column, but doing so would mean that the database would load only in SQL Server 2005. Using a new feature in a new version of a database server is sometimes worth the cost. This time, on balance, I didn't feel that using the new feature of SQL Server 2005 was worth it for the minor possible gain.
After clicking OK in the Advanced SQL Generation Options dialog box and clicking Next in the wizard, the final page of the Configure Data Source wizard appears, which allows you to test the query. If you click Test Query, the Parameter Values Editor dialog box allows you to modify the value used in the WHERE clause. Click OK, and the dialog box that appears should be a similar to Figure 5-13.
Figure 5-13: The final screen of the Configure Data Source wizard, showing test data returned
Click Finish to return to the Design view of Default.aspx. The SqlDataSource Tasks menu now has an additional option, Refresh Schema. This option is useful if you change the name or type of any of the columns returned in the query.
