http://www.developer.com/

Back to article

Create Quick Database Interfaces with ASP.NET Dynamic Data


June 8, 2009

<

Introduction

Have you ever had the need to add database editing support to your ASP.NET web application? Most likely, you have. As you know, this work is not particularly difficult, but it is tedious and often repetitive work. On the lowest level, you analyze your database tables, and then create suitable editing controls for each table field, write logic to update the database, and you are done. Next, repeat the same for every table you need to support.

If you feel you could spend your time better, then Microsoft has a possible time-saver made just for you. Called ASP.NET Dynamic Data and part of .NET 3.5 SP1 (Service Pack 1), this new technique lets you quickly create web-based interfaces for your database editing needs. ASP.NET Dynamic Data is written so that it supports all the basic database operations: reading, creating, updating and deleting. Shortly put, the web interfaces supports the basic CRUD operations for those tables you want to enable.

Figure 1 shows an example of an editing view created by ASP.NET Dynamic Data. The idea is that by default, the project template contains a page for each of the four basic database operations, and you will then specify which tables you wish to enable for viewing and editing.



Click here for larger image

Figure 1. An example editing page generated by Dynamic Data.

This article will walk you through creating and customizing an ASP.NET Dynamic Data web application, starting from the initial steps to a complete application. Code examples are written using C#, and the familiar SQL Server sample database Northwind is used as the data source.

Getting started with Dynamic Data

Developing your first ASP.NET Dynamic Data application is simple, but still the results are effective. To get started, you first need to install both Visual Studio 2008 Service Pack 1 and .NET 3.5 Service Pack 1. After installing both, your Visual Studio's New Project dialog box will have two ASP.NET Dynamic Data templates to choose from (Figure 2).



Click here for larger image

Figure 2. Visual Studio 2008 SP1 brings the new Dynamic Data project templates.

Since ASP.NET Dynamic Data is aimed at writing user interfaces for databases, it's only natural to have different options for database access. The two available options are using LINQ to SQL to access your databases, and the other is to access them using ADO.NET Entity Framework (ADO.NET Entities for short). Although it's technically possible to mix these two data access options in a single ASP.NET Dynamic Data project, the idea is that you select one, and then stick to that.

That said, you often have to make an early choice of your data access strategy when starting development of ASP.NET Dynamic Data applications. Given this, it is wise to give some thought as to how you would like to access your databases before starting your project.

For this article, the ADO.NET Entity Framework is chosen, as it provides an elegant solution to many database access needs. Thus, the correct template icon to select from the New Project dialog box would be named "ASP.NET Dynamic Data Entities Web Application". Once you name your project and click OK, Visual Studio will create a new solution for you with a skeleton application in place.

Although many other application types can be run successfully after creating the project, ASP.NET Dynamic Data applications make an exception. If you try to directly run the application without changing anything, you will run into a quite disappointing error saying that "there are no accessible tables". But be brave, this problem is easy to fix!

The fix you need to do is easy to understand, if you recall that you haven't yet specified any database for the application. Without a database connection, the application wouldn't be able to display any data. (Another thing is whether the application could give you a clearer error message.) To solve the problem is then, a matter of defining a database connection.

Since the project template you chose earlier was designed for ADO.NET Entities, you should next add such a connection to your project. To do so, right-click the main project node in Visual Studio's Solution Explorer, and then choose the Add/New Item command from the popup menu. This will bring the Add New Item dialog box onto the screen. From this dialog box, select the ADO.NET Entity Data Model object (this is easiest to find from the Data section on the left), and then enter a name for the object. It's a good idea to name the object after your database, for instance NorthwindModel for the SQL Server sample database.

Once you click OK, the Entity Data Model Wizard will open (Figure 3). You can either create a model from a database, or an empty model. Since you will need a SQL database for ASP.NET Dynamic Data applications in any case, it is usually the easiest to generate the model after an existing database. The next step in the wizard is to specify the connection string for your database. This of course depends on your database, but at minimum you will need the server's (host) name, your database name, and finally a user id and a password.



Click here for larger image

Figure 3. The Entity Data Model Wizard.

After you have specified all the necessary details, continue to the next step in the wizard. Here, you would specify all the tables, views and stored procedures you want to include in your model. The easiest, but not always the best option, is to work directly with the underlying SQL tables. For instance, using views would let you later change table structure without affecting the application. Once you have selected your database objects (from now on, this article will refer to these collectively as tables for simplicity), you can click the Finish button in the wizard, and then let Visual Studio create the entity model for you. The results should appear similar to those in Figure 4. In your project, you should have a new file: a .edmx file which describes your model in XML.



Click here for larger image

Figure 4. The Entity Data Model showing in Visual Studio./BODY>

Connecting to the model

With the entity data model created and added to your project, you are ready to connect the model to the rest of the application. In ASP.NET Dynamic Data applications, there is something called a metamodel, to which you can connect either a LINQ or an ADO.NET Entity Framework context.

The metamodel contains information about the database which you wish to edit using your ASP.NET Dynamic Data application. It also contains details about which tables should be visible, how columns should be formatted, and so on.

The metamodel is initialized in the Global.asax code behind file, which is a logical place to register application-wide settings. Code in the Global.asax file (and its code behind file Global.asax.cs) is executed when the ASP.NET application loads, i.e. just before the first page is served.

If you take a look at the default implementation, you will see a long comment about how the metamodel should be registered. The last line of the comment is an actual code line (around line number 29), and you should uncomment and then edit this line to connect or register your data source with the application. Assuming you have an entity model named NorthwindEntities, then the code line should look like this:

  model.RegisterContext(typeof(NorthwindEntities),
    new ContextConfiguration() {
      ScaffoldAllTables = true
    });

Note how you don't need to specify an instance of your data model; it is enough to specify the type of the class using the C# typeof operator. Internally, ASP.NET Dynamic Data uses reflection to gather the necessary data at run-time.

Next, you need to understand the concept of a scaffold. In ASP.NET Dynamic Data, a scaffold specifies which database tables and their fields belong to the editable objects in the resulting application. By default, no tables are visible in the application, but for testing purposes, you could enable every table and all their fields to be editable. This can be done by setting the ScaffoldAllTables property to true, as shown in the previous code listing.

With these registrations in place, you can now run the application. The default page of the application lists all the tables in your data model (Figure 5), and each table name is also a link. Clicking a link allows you to go to a page that displays the records on that particular table (Figure 6). If you take a look at the records, you can see that these listing pages also contain links to create new records as well as edit and delete them. By default, these edits are done using separate pages. However, ASP.NET Dynamic Data also supports in-place editing on the data grids.



Click here for larger image

Figure 5. The default view for a Dynamic Data application.



Click here for larger image

Figure 6. A default list page showing customer records.

ASP.NET Dynamic Data is also intelligent: if the model (and thus the underlying database) has referential integrity set between tables, then your web application will have links from one table to its related tables. In the sample database, a customer can have multiple orders. Thus, the listing for the Customers table contains an automatic link to the orders by this customer. Similarly, from an order page there is an automatic link to order detail rows.

Seeing how Dynamic Data applications are built

After taking a stroll through the generated web application and its many built-in features, it is a good time to close the browser and return to Visual Studio. Next, you are going to learn how ASP.NET Dynamic Data applications are constructed.

First, you can see that your project contains a special folder called DynamicData. This is the default name for this folder, but you can also customize its name by setting the DynamicDataFolderVirtualPath property of the metamodel class in the Global.asax.cs file.

The DynamicData folder contains several subfolders, of which you need to be aware. Four important folders are Content, CustomPages, FieldTemplates and PageTemplates. By design, the Content subfolder contains common resources such as logos and other images used by the application (the actual layout is done using a master template named Site.master). CustomPages folder is initially empty, but it is designed to be a placeholder for your pages that you might want to create for individual tables. That is, by default in ASP.NET Dynamic Data applications, all tables share the same look and feel for their listing and editing pages. Now, if you wanted to have a different page for inserting, say, customer and order records, then you could store your alternative layout pages under the CustomPages folder.

The next two subfolders are key to the operation of ASP.NET Dynamic Data applications. First, there is the folder called FieldTemplates. This folder contains around a dozen user controls, each of which is used to render certain types of database fields. For instance, for Boolean fields, the Boolean.ascx control is used, and for VARCHAR fields, the Text.ascx control is used, and so on. ASP.NET Dynamic Data determines the type of each column based on the model, and then decides which control to render.

The PageTemplates folder contains a set of regular .aspx files. There are three types of files for the basic database operations: creating (Insert.aspx), reading (List.aspx) and updating (Edit.aspx). The fourth basic operation, deletion, is handled by the List.aspx page.

Armed with this knowledge, you can open any of these files and see how they are built. Recall that the same .aspx pages (and their code behind files) are used for every table available in the model. Thus, the pages are not generated statically based on the model, but instead are formed at run-time based on the model. Because of this, you can see that the code in the .aspx files refers often to an object named table, which is a variable defined in the page class.

This object, actually of type MetaTable, contains meta- information about a selected table; the object itself is initialized in the Page_Load event handler. In the .aspx file, the code then refers to members like table.DisplayName and table.GetActionPath.

Customizing using attributes

So far, you have briefly seen how ASP.NET Dynamic Data applications work. After you have seen how the different features of the application, you should next learn how you can control how data is displayed in the application.

Earlier, when looking at the Global.asax.cs file, the ScaffoldAllTables property was set to true. This property value means that every table in the model became active in the application for editing and deleting. But maybe you would like to better control which tables are active. To do this, you need to use attributes. In the data model, the classes that represent the tables are defined as partial classes. This means that you can effectively continue adding features to these classes, including specifying attributes. To write the necessary code, you need to add a new file to your project.

The easiest way to add this file is to right-click your project in Visual Studio's Solution Explorer and then choose the Add/Class command. Visual Studio will then ask you for a class name; choose any name you want, for instance "MetadataControl". Once the file has been created, you can start editing it.

Assuming for example, that you want to hide the employees table, you would add a partial class definition for the Employee class, originally defined in the ADO.NET entity model file NorthwindModel.Designer.cs (if you followed the naming convention suggested earlier). Replacing the skeleton public class definition written by Visual Studio, add the following code:

  [ScaffoldTable(false)]
  public partial class Employees
  {
  }

With this definition, the Employees class defined in the original entity model gets extended, and the ScaffoldTable attribute is added. This attribute is one of the key ones in ASP.NET Dynamic Data. Many of the most important attributes are defined in the System.ComponentModel.DataAnnotations namespace. Other often-needed attributes include ScaffoldColumn (of which you will soon see an example), DisplayName, DisplayFormat, Range and UIHint.

With the above ScaffoldTable attribute (and the value of false) in place, you should now run the application to see how it behaves. In the initial screen showing the names of tables in the data model, you should see that the Employees table link is missing. And, if you take a look for example at the Orders table, you can see that the hyperlinks to the table are also gone.

Notice how there are two ways to show and hide tables. You can make all tables visible using the ScaffoldAllTables property in the Global.asax.cs file, and then hide them one by one using attributes. The other option is to go the other way around: leave ScaffoldAllTables to false, and then allow individual tables to become visible using the ScaffoldTable(true) attribute.

Next, let's see how you can control individual fields using attributes. Previously, you used partial classes to help in specifying the ScaffoldTable attribute, but if you wanted to have attributes associated with individual columns, you would need another kind of solution. Since you cannot have partial property definitions, you must use an additional class to define properties that are associated with database fields.

To do this, you start with a similar partial class like with the ScaffoldTable attribute, but you use the MetadataType attribute to associate another class with the original. Then, this additional class, called the metadata class, allows you to define public properties for each field in the data model which you want to control. The following example shows how this is done:

  [MetadataType(typeof(EmployeesMetadata))]
  public partial class Employees
  {
  }
  
  class EmployeesMetadata
  {
    [DisplayName("Person's title")]
    public object Title { get; set; }
  }

Here, the partial class Employees is defined as before, but also the MetadataType attribute is applied to the class. The constructor of this attribute accepts a type as a parameter, which in turn points to the metadata class called EmployeesMetadata. This class is then free to define new properties and associated field- level attributes to them. For instance, here the DisplayName attribute (in the System.ComponentModel namespace) is associated with the column Title to specify a new, more descriptive title for the field. Of course, the property names must match those in the database for this to work.

Modifying user interfaces

While understanding how attributes are applied in ASP.NET Dynamic Data applications is key to your success, you must also understand how you can customize the web page templates that together create the user interface for your application. As briefly mentioned before, ASP.NET Dynamic Data applications by default utilize an ASP.NET template file. This file is named Site.master, and can be found from the root folder of the project.

If you wanted to change the user interface, then editing the Site.master file would be a good place to start. It utilizes XHTML code and defines a single content placeholder into which the actual pages are embedded at runtime. Furthermore, the master file uses CSS styles, which are in turn defined in the file Site.css. To do application-wide font and color changes, editing this style sheet file would give you a head start.

In addition to the template and style sheet files, you do of course have the actual .aspx pages that implement the four basic operations. This can be found inside the DynamicData\PageTemplates folder in your solution. For instance, you might wish to change how the listing pages operate. For this need, you would edit List.aspx.

Another option for customizations is the DynamicData\FieldTemplates folder. This folder contains the user controls that ASP.NET Dynamic Data uses to render different database field types. For instance, a Boolean field is shown as a checkbox to the user. If you wanted to change this, you could simply go and edit the files Boolean.ascx and Boolean_Edit.ascx. There usually are two controls defined for each data type: one for viewing and another for editing. Often, the viewer control is simply a read-only version of the edit control.

It is also possible to define your own user controls to work with your data. For instance, you might wish to change the control that is used to display dates. By default, the DateTime.ascx control uses a textbox control for this, but you could easily change this to, say, a calendar control. If you wanted to make this an application-wide change, then you could simply edit DateTime.ascx, and all date fields would change. You can also change the control on a field by field basis. This is done using attributes, just like with the previously shown ScaffoldTable and ScaffoldColumn attributes. Assume that you had implemented a calendar user control DataTimeCalendar.ascx like this:

  <%@ Control Language="C#" AutoEventWireup="true"
  CodeBehind="DateTimeCalendar.ascx.cs"
  Inherits="DynamicDataTest.DynamicData.
  FieldTemplates.DateTimeCalendar" %>
  
  <asp:Calendar ID="Calendar1" runat="server">
  </asp:Calendar>

This declaration mirrors any other user control definition. However, for a user control to be suitable for ASP.NET Dynamic Data use, you need to tweak the code-behind file a bit. First, you need to change the inheritance chain from the regular System.Web.UI.UserControl to System.Web.DynamicData.FieldTemplateUserControl.

Of course, you are not limited to controls that are available in ASP.NET. For instance if you have purchased a nice, feature-rich third-party control or created one on your own, you are free to use them in your ASP.NET Dynamic Data applications. Specifying which user control should be used when ASP.NET Dynamic Data renders the database views can be controlled using the UIHint attribute. This attribute is applied to metadata classes just like the ScaffoldColumn attribute. With the previous DateTimeCalendar user control declaration in place, you could use the UIHint attribute as follows:

  [MetadataType(typeof(EmployeesMetadata))]
  public partial class Employees
  {
  }
  
  class EmployeesMetadata
  {
    ...
    [UIHint("DateTimeCalendar")]
    public object HireDate { get; set; }
  }

Here, the HireDate field of the Employees table is assigned the UIHint attribute with the value of "DateTimeCalendar". When ASP.NET Dynamic Data shows the table, the regular text label is replaced with the calendar control (Figure 7). Note that you also need to write a couple of lines of code to the user control's code behind file so that you can set the calendar to show the correct hire date:

  protected override void OnDataBinding(EventArgs e)
  {
    base.OnDataBinding(e);
    object val = FieldValue;
    if (val != null)
    {
      Calendar1.SelectedDate = (DateTime)val;
      Calendar1.VisibleDate = Calendar1.SelectedDate;
    }
  }



Click here for larger image

Figure 7. A custom calendar showing employee hire dates.

Other than this kind of code, it is quite easy to customize your pages with custom controls. As you can see, the row listings and editing pages need not solely rely on labels and text boxes.

Validating database values

While creating simple database editing user interfaces with ASP.NET Dynamic Data is a snap, creating business rules to regulate data entry is nonetheless part of almost any application. If possible, the best practice is to start this process from the database level: you should enforce required fields in SQL Server, for instance. Similarly, if you know that a field can only have values from 0 to 100, you might add a constraint directly to the field using the CHECK keyword or the Check Constraints dialog box in SQL Server Management Studio.

In ASP.NET Dynamic Data applications, certain database level constraints are automatically enforced. For example, required fields (NOT NULL) automatically generate a validation error at runtime if left blank. However, more complex constraints not reflected by the model (such as CHECK constraints) will generate a generic error saying that the update wasn't successful. To give the user of the application a clearer indication of what's missing, you can again use attributes.

For instance, the Range attribute (defined in the System.ComponentModel.DataAnnotations namespace) can be used to specify minimum and maximum values for either an integer or floating point field. This attribute is applied similarly to the ScaffoldColumn and UIHint attributes.

Other attributes that you can use to check values include the Required and RegularExpression attributes. It is also possible to limit string lengths using the StringLength attribute. If you instead prefer to write custom validation code yourself, then you can write a method to extend the data model you have. For instance, if you are using ADO.NET Entities, then the NorthwindModel.Designer.cs file will have partial method declarations for validating fields.

For example, if you had a business rule saying that employee hire date must be a Monday, then you could write code to implement the OnHireDateChanging method of the Employees class. You could do this with code similar to the following:

  public partial class Employees
  {
    partial void OnHireDateChanging(System.DateTime? value)
    {
      if (value.HasValue)
      {
        if (value.Value.DayOfWeek != DayOfWeek.Monday)
        {
          throw new Exception(
            "The hire date must be a Monday");
        }
      }
    }
  }

However, the problem with this approach is that the OnChanging event is executed many times: when viewing records, when editing them, and finally when saving them. This means that the actual database values must match the rules - if they don't, then the data cannot be shown.

If you instead would like to validate values only when changes are saved back to the database, then you would need to write a bit more code. The entity data model class (NorthwindEntities in the example application) contains an event called SavingChanges which you can handle. You should write your validation rule(s) into this event handler.

  using System.Data;
  using System.Data.Objects;
  ...
  public partial class NorthwindEntities
  {
    partial void OnContextCreated()
    {
      this.SavingChanges
        += new EventHandler(MyHandlerForSavingChanges);
    }
  
    internal void MyHandlerForSavingChanges(
    object sender, EventArgs e)
    {
      IEnumerable<ObjectStateEntry> changedEntities =
        this.ObjectStateManager.
        GetObjectStateEntries(EntityState.Added |
        EntityState.Modified);
      foreach (ObjectStateEntry entry in changedEntities)
      {
        if (entry.Entity is Employees)
        {
          Employees emp = (Employees)entry.Entity;
          if (emp.HireDate.HasValue)
          {
            if (!(emp.HireDate.Value.DayOfWeek ==
              DayOfWeek.Monday))
            {
              throw new Exception(
                "The hire date must be a Monday");
            }
          }
        }
      }
    }
  }

Here, the code first implements the OnContextCreated partial method and then adds an event handler for the SavingChanges event. The event handler itself (in MyHandlerForSavingChanges) collects all the added or modified records from the data model, and then loops thru them to see which are related to the Employees table. Once a match is found, the value of the HireDate field is checked, and if not a Monday, an exception is raised.

Writing custom validation code is not difficult, but often you can avoid this as the attributes provided by ASP.NET Dynamic Data already are quite versatile. If they don't meet your needs however, you can write custom rules using the two techniques shown previously.

Conclusion

In this article, you have seen how ASP.NET Dynamic Data can be used to write functional web front-ends to databases in no time. Without doing almost any customizations, ASP.NET Dynamic Data allows you to create web interfaces that are suitable at least for administrative purposes. With some additional development, you can create effective applications that appeal to a wide range of users. However, ASP.NET Dynamic Data applications might not directly suit all your needs. In these cases, it is helpful to know that you can easily integrate an ASP.NET Dynamic Data application into (an existing) regular ASP.NET WebForms application. You can even enable in-place editing of data using the grids if you comment and uncomment about ten lines of code in Global.asax.cs file. Follow the instructions inside the file itself.

Because ASP.NET Dynamic Data applications are user- interface-wise based on a master page and CSS styles, the applications you write can also be quickly modified to have the necessary look and feel. In the future, these customizations will be even easier. CodePlex already contains a public preview of a forthcoming version, and ASP.NET Dynamic Data will also be a native part of .NET Framework 4.0. Given this, ASP.NET Dynamic Data has a bright future. The technology is already useful, and especially so if you need to write quick interfaces for your databases. And many of us do.

Links

Official ASP.NET Dynamic Data page
Microsoft Visual Studio 2008 Service Pack 1 download page
Using ASP.NET Dynamic Data on MSDN
ASP.NET Dynamic Data 4.0 Preview 4

About the Author

Jani Järvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP and a frequent author and has published three books about software development. He is the group leader of a Finnish software development expert group at ITpro.fi and a board member of the Finnish Visual Studio Team System User Group. His blog can be found at http://www.saunalahti.fi/janij/. You can send him mail by clicking on his name at the top of the article.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date