ASP.NET Jumpstart: Building the Data Tier of the Media Share Library Starter Kit

 

Bill Evjen
Reuters

October 2005

Applies to:
   Microsoft ASP.NET 2.0
   Microsoft Visual Web Developer 2005 Express Edition

Summary: Learn how to build your own starter kit using Microsoft ASP.NET and Microsoft Visual Studio 2005 or Microsoft Visual Web Developer 2005 Express Edition. This is the second article in the ASP.NET Jumpstart series. (16 printed pages)

Click here to download the code sample for this article.

Contents

Introduction
Working with SQL Server Express
Pulling and Pushing Data Using the New Data Source Server Controls
Conclusion

Introduction

Welcome to second ASP.NET Jumpstart article, a four-article series that will run you through the creation of a complete application—from the beginning to the end. The first article in the ASP.NET Jumpstart series was an introduction to the completed starter kit showing you how to install and run the application. This second article is going to start you on the path of building the starter kit yourself—starting with the data tier.

The starter kit being built is called the Media Share Library Starter Kit. The Media Share Library Starter Kit enables you to easily create an application that allows registered users to present a collection of media items (such as movie DVDs, music CDs, books, and more) for other registered users to borrow. Users will be able to look through a library of items collectively held by the group using the application and request to borrow specific items from the registered owners of the items. The idea of the Media Share Library Starter Kit is to provide you with a framework that you can use to quickly organize a library collection that can be shared with a larger group of people.

The nice thing about the starter kit, whether you use it or not, is that it illustrates some of the more exciting additions to Microsoft ASP.NET in this latest release—ASP.NET 2.0. The starter kit makes heavy use of master pages, the new code-behind model, new server controls, and more. The focus of this article is on the starter kit's use of some new and exciting data features you will find in ASP.NET 2.0, such as using the new Microsoft SQL Server Express Edition files, as well as data source controls to do the work of inserting, updating, and deleting data.

Let's start on our path of building up the starter kit's data tier ourselves by first looking at building our own data store using the new SQL Server Express Edition.

Working with SQL Server Express

Microsoft's SQL Server 2005 Express Edition is a new database product that is based upon the same technologies used by the full-blown version of Microsoft's SQL Server 2005. SQL Server Express Edition is a trimmed down version of SQL Server 2005 and is meant to replace the need to use other database files (such as a Microsoft Access .mdb file) to constitute the data tier of your ASP.NET applications.

In the past, many hobbyists and Web developers found that it was quite easy to use Access files as the data store of their applications because these files were easy to install and implement, and because it was easy to copy from one location to another with the rest of the application. For this reason, Microsoft has provided SQL Server Express Edition files (using the new .mdf file extension). SQL Server Express Edition files can be treated in the same manner as the aforementioned Access files. These .mdf files can be easily created through an outstanding integration capability with Visual Studio, easily put to use inside your ASP.NET applications, and easily x-copied from one location to another as if it was just another file in your ASP.NET application, thereby making deployment a breeze.

With this ease of use comes so much more, however. The new SQL Server Express product supports new data types such as XML, VARCHAR(MAX), and even user-defined data types.

Let's start by first looking at the database file that will be used to store user and role information.

Building the ASPNETDB.MDF File

Since this starter kit is dealing with registered users, the first thing you might be wondering is where the user and role information used by the starter kit is stored. When you first build and run your application, you will notice that Visual Studio 2005 creates this database file for you dynamically.

After pressing F5 to launch and build the application, ASP.NET will dynamically create the required ASPNETDB.MDF database file that is used to store user's login and role information for the application.

You will find the ASPNETDB.MDF file along with the Library.mdf (which we will look at shortly) in the App_Data folder. If you don't see it there, you will need to press the Refresh button in the toolbar of the Visual Studio Solution Explorer. This is illustrated in Figure 1.

Figure 1. You will find the ASPNETDB.MDF in the App_Data folder

The nice thing about Visual Studio is that you can do everything you need to do with this and other SQL Server Express database files directly from Visual Studio. Double-clicking on the ASPNETDB.MDF file in the Solution Explorer will open up the database file in the Visual Studio Server Explorer, as shown in Figure 2.

Figure 2. Examining the ASPNETDB.MDF in Visual Studio

As you can see from Figure 2, not only can you work with the tables that are contained in the database, you can also work with database diagrams, views, stored procedures, functions, synonyms, types, and assemblies. This image of the ASPNETDB.MDF file shows all the tables you will need for working with a user's login information and roles, as well as the profile information associated with a user.

We are not going to want to make any changes or modifications to the ASPNETDB.MDF file, as this is a file needed by the ASP.NET engine to operate. It would be possible to just add the new tables we need to this database file and run the entire application from this one database file alone, but it is best practice to separate out your own custom database work from that of the default ASPNETDB.MDF file. For this reason, we will next look at the creation of the Library.mdf database file. This file will be used by the Media Share Library to store all the information concerning the items stored by the application, including whether any of the stored items are on loan or not.

Building the Library.mdf File

Let's take the construction of the Media Share Library data tier to the next level by building a simple database file that will be used for all the underlying data the application will need to store. What is actually being stored in the database? Well, if you have worked through the first ASP.NET Jumpstart article, which gave an introduction to the starter kit, you will see that users will need to store their own library of media (DVDs, books, CDs, and more) as well as information on the loan status of the items.

In order to create the database file you'll need, from the Visual Studio Solution Explorer right-click on the App_Data folder and select Add New Item from the provided menu. This will launch the Add New Item dialog box. From this dialog, select SQL database as the file type you are interested in creating in your application. Be sure to name the file Library.mdf. This is illustrated in Figure 3.

Figure 3. Creating the Library.mdf file for the Media Share Library application

Once created, you will find the new Library.mdf file in the App_Data folder and, as was the case with the ASPNETDB.MDF file, double-clicking on the Library.mdf file will open the file in the Visual Studio Server Explorer, as shown in Figure 4.

Figure 4. The newly created Library.mdf file

From the Server Explorer, you will notice that if you try to expand the Tables folder nothing will happen except that the plus icon next to the folder will disappear. This is due to the fact that there are no tables contained in the Library.mdf file at this moment.

To create our first table, right-click on the Tables folder within the Library.mdf file in the Server Explorer and select Add New Table from the provided menu. For this application, you will need to create two tables. The first table, named MediaTypes, will deal with the custom categories that are used by the Media Share Library application. Remember that each item stored in the library needs to be categorized in some fashion (for example, Books, DVDs, CDs, and son on) and these categories are to be dynamically generated from the contents of the MediaTypes tables. The MediaTypes table is a simple table to create.

When creating a table, you will notice that you are offered the ability to create database columns by providing some simple information about the column. This is shown in Figure 5.

Figure 5. Creating columns in three simple steps

When creating tables of information within a database, it is usually a good idea to have a unique key to identify each row contained in the table. For this reason, you will find that the MediaTypes table contains a column titled MediaTypeId that is of type int and doesn't allow for Null values (because you want each row to absolutely have an ID value). But that isn't it for this column definition. The other step to take is to right-click on the gray box at the start of the row and select Set Primary Key from the provided menu. After this, you will see a yellow key appear at the beginning of the row. Beyond that, make sure that each ID established is used as an identifier and is unique. Highlighting the column, you will be able to see the column's properties defined in the document window. From this properties pane, set the Identity specification so that the ID is used for identity and is unique. These settings are illustrated in Figure 6.

Figure 6. Making sure the column is used for unique identification

After the MediaTypeId column is ready, the next step is to create the other two columns used in this table—MediaTypeName (nvarchar(50)) and MediaComments (nvarchar(MAX)). When done, your MediaTypes table definition should appear, as shown in Figure 7.

Figure 7. The completed MediaTypes table

Looking over this table, you can pretty much tell what each column will be used for in the application. As stated earlier, MediaTypeId is used as an identifier for each row of the table. MediaType is the name of the category that will actually be presented in the application to the end user and MediaComments isn't actually used by the application at all; this allows developers or database administrators to provide comments for each category if required.

Once the table is in place, press CTRL+S to save the table definition. You will then be provided with a dialog box that allows you to give a name to your new table. Be sure to name the table MediaTypes. This is illustrated in Figure 8.

Figure 8. Saving the created table to the Library.mdf file

After the MediaTypes table has been created, the next table to create is a table to store all the library items stored in the application. This table will also specify whether or not these items are on loan to someone, who this person is, and when the item is due back to the owner.

The completed table, named Media, is shown in Figure 9.

Figure 9. The completed Media table in the Library.mdf file

From this table definition, you can see that there are some data items that are coming from other tables or databases used by the application. For instance, the UserName column stores the username of the registered user. This value is originally retrieved from the ASPNETDB.MDF file through the ASP.NET 2.0 membership system. Also, the MediaType column simply stores a number and this number is associated to one of the unique IDs used in the MediaTypes table that you just built.

Now that these two tables are in place and ready to go, let's next look at how the application pulls and stores data from these tables.

Pulling and Pushing Data Using the New Data Source Server Controls

There are a number of ways that one is able to pull data from a database (or push data back into the database) using .NET. With that said, you have to find a data retrieval scenario that works best for your application and situation. ASP.NET 2.0 makes this process rather simple with the introduction of a whole new series of data source server controls.

The new ASP.NET 2.0 data source controls provide a declarative way to connect to data stores and retrieve the data you need for your applications. Working with the new data source controls is considerably easier than the steps you used to have to take when using ASP.NET 1.x.

There are five new data source server controls at your disposal. The new data source controls include some that are specifically designed to work with Microsoft's SQL Server, the new Microsoft SQL Server Express Edition database files, XML files, and more. These data source controls connect to the assigned data store, retrieve the data, and perform any manipulations on the data that you specify using various server control attributes.

Selecting Data Using the SqlDataSource Control

If you look through the code of the Media Share Library Starter Kit, you will notice that there is considerable use of data source controls to select, insert, update, or delete information stored in the Library.mdf database file. Let's first start by looking at some simple Select examples.

A good example of a simple retrieval of data is on the MainLibrary.aspx page found in the Library folder of the Media Share Library application. This page contains a GridView server control that displays all the items entered into the library for a particular category. The category selection by the end user is done through a dropdown list control, directly above the GridView control, that displays the results. How is the category data populated into the dropdown list control? Well, this is done though the use of a SqlDataSource control that pulls the list of categories from the MediaTypes table created earlier in this article. Once retrieved, it is then passed to the dropdown list control. Let's first look at the SqlDataSource control that pulls the list of categories from the Library.mdf database file. This is presented in Listing 1.

Listing 1. Reviewing a simple SqlDataSource control for data selection

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:LibraryConnectionString %>"
 SelectCommand="SELECT [MediaTypeName], [MediaTypeId] FROM [MediaTypes] 
    ORDER BY [MediaTypeName]">
</asp:SqlDataSource>

This is a rather simple example of the SqlDataSource control. First off, like other server controls in ASP.NET, you are required to have an ID value and a runat="server" within the control. The next important item to notice is the ConnectionString attribute. This points to a location in the web.config file through the use of the <%$ ConnectionStrings:LibraryConnectionString %> value. What this cryptic value is really saying is that the value that is used needs to be retrieved from the <ConnectionStrings> section of the web.config file using what is found within the particular <add> node called LibraryConnectionString. Looking in the web.config file of the Media Share Library application, you will find the following, as shown in Listing 2.

Listing 2. The <ConnectionStrings> section of the web.config

<configuration 
 xmlns="https://schemas.microsoft.com/.NetConfiguration/v2.0">

   <connectionStrings>
      <add name="LibraryConnectionString" 
       connectionString="Data Source=.\SQLEXPRESS;
          AttachDbFilename=|DataDirectory|\Library.mdf;
          Integrated Security=True;User Instance=True"   
       providerName="System.Data.SqlClient"/>
   </connectionStrings>

</configuration>

As you can see from Listing 2, the LibraryConnectionString value points to the Library.mdf database file that was created earlier in this article.

Once the ConnectionString attribute in the SqlDataSource control has been established, the next attribute to pay attention to is the SelectCommand attribute. This attribute accepts the Select TSQL command that will be used to pull data from the specified table in the Library.mdf database file. In this instance, the columns MediaTypeName and MediaTypeId are pulled from the MediaTypes table and ordered alphabetically before being passed to the dropdown list control on the same page.

Binding the Selected Data to a Server Control

With the ConnectionString and SelectCommand attributes in place, the SqlDataSource control is ready to be bound to one or more controls that are contained on the page. In this case though, this SqlDataSource control is simply bound to a single control, DropDownList1. The code for this control is shown in Listing 3.

Listing 3. The DropDownList server control binding to a SqlDataSource control

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
 DataSourceID="SqlDataSource1" DataTextField="MediaTypeName" 
 DataValueField="MediaTypeId">
</asp:DropDownList>

From this example, you can see that the dropdown list binds itself to the output from the SqlDataSource1 control through the use of the DataSourceID attribute (which all data-bound server controls can use). The value provided to the DataSourceID attribute is the ID value used in the SqlDataSource control (in this case, SqlDataSource1). From there, the dropdown list has a particular usability requirement to place a value in the text field and an optional value field. Here is an example of one such <option>:

<option value="1">DVD</option>

In this case, we are going to want to use the MediaTypeId value for the value of the <option> and the MediaTypeName for the value displayed to the end user. Using the DropDownList server control, this is done through the use of the DataTextField and DataValueField attributes.

With the DropDownList1 now bound to the results coming from the SqlDataSource1 control, the results will be dynamically drawn to the page, as illustrated in Figure 10.

Figure 10. Dynamically generating the contents of a dropdown list

Selecting Data Along with Parameters

Sometimes you might be required to select data based on a specific parameter—also known as a parameterized query. For instance, you might want a list of all the customers in your customer database who are located in Canada. This is known as a parameterized query. You don't want an extensive list of all the customers. Instead, you pass in a value along with the Select statement (in this case, Canada) and a filtered result set is returned.

In the Media Share Library Starter Kit on the same MainLibrary.aspx page, you will find a GridView control that is driven by another SqlDataSource control (SqlDataSource2) that requires a parameterized query to get back a result. In this case, the GridView is actually controlled by what is selected by the end user from the dropdown list control shown earlier in Figure 10. If the end user selects DVD from the dropdown list, then this value is used in the Select statement and only the library items that are categorized as DVD are returned to be displayed in the GridView control. Of course, the data displayed in the GridView is completely controlled by the SqlDataSource2 control. This control is shown in Listing 4.

Listing 4. The SqlDataSource control along with a parameterized query

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
 ConnectionString="<%$ ConnectionStrings:LibraryConnectionString %>"
 SelectCommand="SELECT [ItemId], [UserName], [MediaName], [MediaISBN], 
   [MediaInputDate], [MediaViewed], [LoanedAmount], [OnLoan], [MediaType] 
   FROM [Media] WHERE ([MediaType] = @MediaType) ORDER BY [MediaName]">
   <SelectParameters>
      <asp:ControlParameter ControlID="DropDownList1" Name="MediaType" 
       PropertyName="SelectedValue"
       Type="Int32" />
   </SelectParameters>
</asp:SqlDataSource>

Looking at this SqlDataSource control, you can see that there is a lot of similarity between this and the SqlDataSource1 control we looked at earlier. Though similar, there are some big differences. First off, the SelectCommand value is quite a bit longer. The reason for this is that there are a large number of column values being retrieved from the Media table. The other reason, and the more interesting bit, is starting with the WHERE statement in the command.

WHERE ([MediaType] = @MediaType) ORDER BY [MediaName]

So what is actually being said with this bit of code is that the SqlDataSource control needs to select all the defined columns from the Media table where the value that comes from the MediaType column is equal to some specified parameter we will later provide (defined using MediaType). Then, when you are all done getting those values, these values need to be ordered alphabetically by the value that comes from the MediaName column.

Defining Parameters Declaratively

With that said, it is important to then give a value to the MediaType variable. This can be done in a couple of ways—declaratively or programmatically. Let's first review how this is done declaratively.

Since we have a Select statement with at least one parameter that needs to be defined (looking back to the example in Listing 4), it is then possible to use the <SelectParameters> section of the SqlDataSource control to provide a value to use for the MediaType variable. In addition to the <SelectParameters> section in the SqlDataSource control, you will also find the <DeleteParameters>, <FilterParameters>, <InsertParameters>, and <UpdateParameters>. Nested within the <SelectParameters> section, you can use the following controls:

<asp:Parameter>	A basic parameter placeholder, which can be defined later.
<asp:ControlParameter>	Used when the value will be retrieved from another server control found on the page.
<asp:CookieParameter>	Used when the key value needs to be retrieved from an end user's cookie.
<asp:FormParameter>	Used when the value comes from the form collection.
<asp:ProfileParameter>	Used when the value comes from the Profile object associated with the end user making the request.
<asp:QueryStringParameter>	Used when the key value comes from the URL querystring of the request.
<asp:SessionParameter>	Used when the key value comes from the end user's session object.

In our example from Listing 4, you can see that we are associating the MediaType value to what comes from the DropDownList1 server control through the use of the <asp:ControlParameter> control. (Partially shown again in Listing 5).

Listing 5. Using the <asp:ControlParameter> to retrieve the required value

<SelectParameters>
   <asp:ControlParameter ControlID="DropDownList1" Name="MediaType" 
    PropertyName="SelectedValue"
    Type="Int32" />
</SelectParameters>

As we want to retrieve the value to use for MediaType from the DropDownList1 control on the page, you can see that the ControlParameter control uses the attribute ControlID to make that association. The value provided to the ControlID attribute needs to be the ID attribute value of the associated control. The next attribute, Name, points to the actual name of the variable from the parameterized query. This is important, as there may be more than one Select parameter needing definition. In this case, there is only one, but it is still required to make that association. Since we are working with a dropdown list, we are going to need to define which property of the dropdown list we want the SqlDataSource control to use. For instance, when making an association to a dropdown list control, you can get at the SelectedIndex, SelectedItem, or SelectedValue values. For this reason, you are going to need to state which property you are interested in retrieving. After that, the last attribute defined in the SqlDataSource control is the Type attribute. This is where we actually type cast the value coming from the control—in this case, Int32.

With all of that in place, you can then use the dropdown list control to drive the SqlDataSource control and the values it pulls from the Media table in the Library.mdf database file. This is illustrated in Figure 11.

Figure 11. Driving the GridView with a SqlDataSource control

Defining Parameters Programmatically

The other option is to define the parameters programmatically instead of declaratively. This is done in the code-behind page as opposed to directly in the SqlDataSource control as was shown earlier.

Looking at the Default.aspx page in the Library folder of the Media Share Library Starter Kit, you will notice that this page displays alerts on items you have borrowed or items that you own. These alerts are provided to the SqlDataSource control used through a parameterized select query, as well. This Select command is shown here:

SELECT [UserName], [MediaName], [MediaISBN], [DueDate] FROM [Media] WHERE (([DueDate] < @DueDate) AND ([Borrower] = 
@Borrower) AND ([OnLoan] = @OnLoan)) ORDER BY [DueDate]

From this Select command, you can see that there are three variables required to be populated with a value before the command is considered complete—DueDate, Borrower, and OnLoan. If you look at the SqlDataSource control declaration in the Default.aspx page, you will notice that only two of the three variables from the Select command are present (shown in Listing 6).

Listing 6. The SqlDataSource control requiring parameters for the query

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:LibraryConnectionString %>" 
 SelectCommand="SELECT [UserName], [MediaName], [MediaISBN], [DueDate] 
   FROM [Media] WHERE (([DueDate] < @DueDate) AND ([Borrower] = @Borrower)    
   AND ([OnLoan] = @OnLoan)) ORDER BY [DueDate]">
     <SelectParameters>
        <asp:ProfileParameter Name="Borrower" PropertyName="Email" 
         Type="String" />
        <asp:Parameter DefaultValue="True" Name="OnLoan" Type="Boolean" />
     </SelectParameters>
</asp:SqlDataSource>

From the list of declared parameters declared here, you can see that only the Borrower and OnLoan parameters are declared. The DueDate parameter is missing from this list because this value will dynamically change by being the current date/time on the server. For this reason, this parameter will need to be declaratively defined.

Listing 7 shows the code-behind page for the Default.aspx page from where the OnLoan parameter is dynamically declared.

Listing 7. Dynamically defining the @OnLoan parameter

SqlDataSource1.SelectParameters.Add("DueDate", 
   TypeCode.DateTime, DateTime.Now.ToString())

From this single line of code, a SelectParameter is added to the SqlDataSource control through the Add() method, which asks for the name of the variable (in this case, DueDate), the type, and the value. For the value, we are simply providing the current date/time on the server.

With this in place, the SqlDataSource control now has all three parameters required for the Select command.

It is important to remember that even though this article demonstrated the use of Select commands, you can also use the same capabilities for Delete, Insert, and Update commands.

Conclusion

This article gave you a look at building the data tier of the Media Share Library Starter Kit. First we looked at how to build a new database and tables directly in SQL Server Express Edition, and then we took a look at using ASP.NET 2.0 data source controls and how to use them to pull the data you need from the created data files.

The next article in this ASP.NET Jumpstart series will show you how to use some of the new and exciting controls found in ASP.NET 2.0.

Have fun and happy coding!

 

About the author

Bill Evjen is a technical director for Reuters (www.reuters.com), the global news and information company, and is an active proponent of the .NET technologies and community-based learning initiatives for .NET. He has been actively involved with .NET since the first bits were released in 2000 and has since become president of the St. Louis .NET User Group (www.stlnet.org). Bill is also the founder and executive director of the International .NET Association, known as INETA (www.ineta.org), which represents more than 100,000 members worldwide. Based in St. Louis, Missouri, USA, Bill is an acclaimed author and speaker on ASP.NET and XML Web services. He has written such books as the ASP.NET Professional Secrets, the Visual Basic .NET Bible, XML Web Services for ASP.NET, and Web Services Enhancements: Understanding the WSE for .NET Enterprise Applications (all published by Wiley). He is presently working on Professional VB.NET 3rd Edition and Professional C# 3rd Edition (both from Wrox). He can be reached at evjen@yahoo.com.

© Microsoft Corporation. All rights reserved.