Choosing Data Access Options for ASP.NET Web Applications

ASP.NET provides many options for storing, retrieving, and displaying data. For developers who are new to ASP.NET, the appropriate options are not always obvious. This topic provides recommendations and guidelines for choosing the options that best fit your scenario. The topic contains the following sections:

• Database Management Systems (DBMS)

• SQL Server Editions

• Object-Relational Mappers (ORM)

• Entity Framework Development Workflows

• LINQ versus SQL

• Working with Data in ASP.NET Web Forms Applications

  • Data-Bound Controls

    • Controls for Displaying Lists

    • Controls for Displaying a Single Record

    • TemplateField, BoundField, and DynamicField Controls

    • DynamicControl Control

  • Data Source Controls

  • Model Binding

  • Manual Data Binding

  • Data-Binding Expressions

  • Dynamic Data Project Templates

• Working with Data in ASP.NET Web Pages Applications

• Accessing Data Through a Web Service

• Summary of Recommended Choices

Database Management Systems (DBMS)

The technology you use to connect to a database in ASP.NET code that runs on a server is ADO.NET. ADO.NET communicates with a database management system (DBMS) such as SQL Server or Oracle by using data provider software. Microsoft data providers enable you to connect to the following databases:

• SQL Server, including SQL Server Express and LocalDB

• SQL Server Compact

• Any database that supports ODBC or OLEDB and is suitable for use in a web application

You can also get data providers from third-party software vendors. Some popular databases that you can get data providers for include MySQL, SQLite, Oracle, and DB2. For information about data providers that are available, see .NET Framework Data Providers and ADO.NET Data Providers.

If you're choosing a database for an ASP.NET web application and you don't have special needs that dictate a different choice, choose SQL Server. Some reasons for choosing SQL Server include the following:

• SQL Server is supported by Microsoft.

• SQL Server is integrated with other Microsoft data access technologies such as the Entity Framework. See Object-Relational Mappers later in this topic.

• Visual Studio provides built-in tools for working with SQL Server. SQL Server Data Tools (SSDT), enables you to create databases, manipulate schema and data, generate and run scripts, debug, and deploy databases and database updates.

• Visual Studio includes web deployment features that are designed to facilitate deploying SQL Server databases along with web projects. For more information, see Configuring Database Deployment in Visual Studio in ASP.NET Web Site Project Deployment Overview.

• The Visual Studio web project templates use SQL Server for the ASP.NET membership database by default.

• Nearly all ASP.NET documentation on the MSDN web site, ASP.NET web sites, and in blog posts uses SQL Server. Relatively little documentation that would help you get started and resolve difficulties is available for other databases.

SQL Server Editions

There are several SQL Server editions to choose from:

• LocalDB. A special execution mode of SQL Server express that enables you to work with databases as .mdf files that you can locate wherever you want in the file system.

  LocalDB was introduced with SQL Server 2012. Typically, LocalDB database files are kept in the App_Data folder of a web project. LocalDB was introduced with SQL Server 2012 and is the default edition for web application development in Visual Studio 2012. It is not suitable for production web applications.

• SQL Server Express. The entry-level, free edition of SQL Server.

  SQL Server Express includes an option called user instances that enables you to work with database files like LocalDB, but the user instance feature is deprecated.

• SQL Server Developer Edition. Has all the features of Enterprise edition but is licensed only for development and testing, not for production.

• SQL Server (full editions)

  Several full editions are available: Enterprise, Business Intelligence, Standard, and Web.

• Windows Azure SQL Database. The cloud edition of SQL Server, formerly known as SQL Azure.

• SQL Server Compact 4.0. A free, lightweight database engine that you can use in ASP.NET web applications for both development and production.

  SQL Server Compact uses a different database engine and .NET data provider than the other editions. For more information, see Differences Between SQL Server Compact and SQL Server and Using SQL Server Compact for ASP.NET Web Applications.

Guidance for choosing an edition for your production database is outside the scope of this topic. That choice is generally determined by your production environment, or you're working on an existing application that already has a database. If you need to choose a SQL Server edition for production, see the following resources:

• ASP.NET Data Access Content Map (See Choosing between SQL Server and Windows Azure SQL Database.)

• Features Supported by the Editions of SQL Server 2012

You don't have to use the same edition of SQL Server in development that you use in production. The choice of SQL Server edition for development depends on your development environment and the edition you choose for your production database:

• If you're using Visual Studio 2012, and your production database is SQL Database or a full edition of SQL Server, the default choice for development is LocalDB. LocalDB is installed with Visual Studio 2012 by default.

• If you're using Visual Studio 2010 or WebMatrix, and your production database is SQL Database or a full edition of SQL Server, the default choice for development is SQL Server Express. SQL Server Express 2008 is installed with Visual Studio 2010 by default. LocalDB doesn't work with WebMatrix.

• If your production database is SQL Server Compact, use SQL Server Compact for development.

Exceptions that might dictate a different choice for development include the following:

• If you want to use IIS as your development web server in Visual Studio 2012, use SQL Server Express instead of LocalDB. For information about why you might want to use IIS in development, and how to configure Visual Studio to use it, see Test your ASP.NET MVC or WebForms Application on IIS 7 in 30 seconds on Rick Anderson's blog.

• If there are features of the edition you use in production that aren't available in LocalDB or SQL Server Express, use Developer Edition or the same edition that you use in production.

• If you want to enable multiple developers to work on the same Visual Studio web project, with some of them using Visual Studio 2010 and some of them using Visual Studio 2012, see How can Visual Studio 2010 and Visual Studio 2012 share a project that uses a database? in ASP.NET Data Access FAQ.

For help with configuring a connection string for the version of SQL Server that you choose, see SQL Server Connection Strings for ASP.NET Web Applications.

When you use a different database for development than the one you use for production, you have to deploy the database when you deploy (publish) the web application. Deploying a database includes creating the production database, creating tables and other objects in the production database, and copying initial data to the database. Visual Studio includes web deployment features that facilitate deploying databases along with web projects. For more information, see Configuring Database Deployment in Visual Studio in Web Application Project Deployment Overview for Visual Studio and ASP.NET, and the Deploying a database scenarios in Web Deployment Content Map for Visual Studio and ASP.NET.

Object-Relational Mappers (ORM)

To read or update data, you can use ADO.NET directly or you can let an object-relational mapper (ORM) framework handle the low-level code that interfaces with an ADO.NET data provider. If you use ADO.NET directly, you have to manually write and execute SQL queries. You also have to write code that converts data from the database's format into objects, properties, and collections that you can work with in code.

A quick way to see what an ORM does for you is to compare samples of code you would write for an ORM and code you would write to do the same task using ADO.NET directly. In the following example, a school database has an Instructor table, a Course table, and a CourseInstructor association table for the many-to-many relationship between them. To display a list of instructors and the courses they teach, you populate Instructor and Course objects similar to these:

public class Instructor
{
    public string LastName { get; set; }
    public string FirstMidName { get; set; }
    public List<Course> Courses { get; set; }
}

public class Course
{
    public string Title { get; set; }
    public int Credits { get; set; }
}

Here is what code to populate these classes looks like when you use the Entity Framework ORM:

public IEnumerable<Instructor> GetInstructors()
{
    List<Instructor> instructors = new List<Instructor>();
    using (var db = new SchoolContext())
    {
        instructors = db.Instructors.Include("Courses").ToList();
    }
    return instructors;
}

And here is code that accomplishes the same task by using ADO.NET directly:

public IEnumerable<Instructor> GetInstructorsADONET()
{
    var connString = ConfigurationManager.ConnectionStrings["SchoolContext"].ConnectionString;
    List<Instructor> instructors = new List<Instructor>();
    using (var conn = new SqlConnection(connString))
    {
        conn.Open();

        var cmd = new SqlCommand("SELECT Person.PersonID, Person.LastName, Person.FirstName, Course.Title, Course.Credits " +
            "FROM Course INNER JOIN " +
                  "CourseInstructor ON Course.CourseID = CourseInstructor.CourseID RIGHT OUTER JOIN " +
                  "Person ON CourseInstructor.PersonID = Person.PersonID " +
                  "WHERE (Person.Discriminator = 'Instructor') " +
                  "ORDER BY Person.PersonID", conn);
        var reader = cmd.ExecuteReader();

        int currentPersonID = 0;
        Instructor currentInstructor = null;
        while (reader.Read())
        {
            var personID = Convert.ToInt32(reader["PersonID"]);
            if (personID != currentPersonID)
            {
                currentPersonID = personID;
                if (currentInstructor != null)
                {
                    instructors.Add(currentInstructor);
                }
                currentInstructor = new Instructor();
                currentInstructor.LastName = reader["LastName"].ToString();
                currentInstructor.FirstMidName = reader["FirstName"].ToString();
            }
            if (reader["Title"] != DBNull.Value)
            {
                var course = new Course();
                course.Title = reader["Title"].ToString();
                course.Credits = Convert.ToInt32(reader["Credits"]);
                currentInstructor.Courses.Add(course);
            }
        }
        if (currentInstructor != null)
        {
            instructors.Add(currentInstructor);
        }

        reader.Close();
        cmd.Dispose();
    }
    return instructors;
}

Notice that not only does much more code have to be written and tested and debugged when you don't use an ORM, but also the code you write is database-specific. The Entity Framework code would not change if you decided to migrate the application from SQL Server to Windows Azure SQL Database.

This is a simplified example; in a real-world example with more tables, more columns, and more complex relationships, the differences would be much greater. As you can see, an ORM can make you more productive, and your application easier to maintain. For these reasons, in most scenarios you would want to use an ORM in a data-driven ASP.NET application.

The most commonly used ORMs that work with ASP.NET are the following:

• The ADO.NET Entity Framework is the main ORM that Microsoft provides for the .NET Framework.

• LINQ to SQL is a legacy ORM that Microsoft provides. (Don't confuse LINQ to SQL with LINQ. For information about LINQ, see LINQ versus SQL later in this topic.)

• NHibernate is an open source ORM for the .NET Framework.

Other data access frameworks that provide some of the benefits of an ORM but with less abstraction and potentially better performance include micro ORMs such as Dapper and Massive, and the Enterprise Library Data Access Application Block.

For new development where you're not constrained by a legacy data access approach, Microsoft recommends the Entity Framework. (Except for ASP.NET Web Pages applications that you develop by using WebMatrix. For more information, see Working with Data in ASP.NET Web Pages Applications later in this topic.) Some factors that make the Entity Framework the best choice include the following:

• Microsoft supports and continues to enhance the Entity Framework. Starting with version 6.0, the Entity Framework is open source and is also being enhanced by an open source community. LINQ to SQL is supported but Microsoft is investing minimal efforts to enhance it. (See "Where does Microsoft stand on LINQ to SQL?" in Data Developer Center - Top Questions and Answers on Data.) LINQ to SQL has a much more limited feature set to begin with, and choosing LINQ to SQL would limit your ability to take advantage of future advances in data access technology. NHibernate is not supported by Microsoft.

• The Entity Framework is the easiest ORM framework to learn for an ASP.NET developer. Because Microsoft continues to focus ORM development efforts on the Entity Framework, Microsoft and third parties continue to produce new tutorials, videos, and books. Most new tutorials use the Entity Framework even if they are focused on some other technology, such as the latest release of MVC or new model binding features for Web Forms in ASP.NET 4.5. LINQ to SQL is sometimes said to be easier to learn because it has a simpler feature set, but you can get started with the Entity Framework by using only the features you need. You can take advantage of its more advanced features later when you are ready for them. The learning curve for nHibernate is relatively steep.

• The community of Entity Framework users is larger than the LINQ to SQL and nHibernate communities. When you run into a problem that you need help with, you're more likely to get the answer you need when you post it to sites like stackoverflow.com or the ASP.NET data access forum if you're using Entity Framework than if you're using one of the other ORMs.

• The Entity Framework offers the most productivity gains because it is integrated with the .NET Framework and Visual Studio web development tools. Although this is also true to some degree of LINQ to SQL, some features have been developed exclusively for the Entity Framework. For example, Visual Studio automates deployment for Entity Framework Code First databases; see Web Application Project Deployment Overview for Visual Studio and ASP.NET. Integrated development tooling for NHibernate is relatively limited.

• The Entity Framework gives you a choice of databases or data services that you can use. Microsoft provides support for using SQL Server, SQL Server Compact, and WCF Data Services with the Entity Framework. Third-party vendors provide support for other databases, such as Oracle, MySQL, and SQLite. LINQ to SQL is limited to SQL Server. NHibernate can also be used with multiple database engines, but support is potentially more problematic.

Developers who are considering whether to adopt the Entity Framework frequently express concern about its impact on application performance. Any ORM, including the Entity Framework, will sometimes perform inefficiently compared to what you could accomplish by writing SQL and code manually. In most scenarios, the loss in performance is not likely to be noticeable to end users. In other scenarios a loss in performance may be an acceptable trade-off for the improvements in application maintainability and reliability that you get from using the Entity Framework. For scenarios where Entity Framework performance is not acceptable, you can take one of these approaches to resolve the issue:

• Write and execute your own SQL statements or stored procedures for scenarios that are not handled efficiently by the Entity Framework.

  For example, suppose you want to change a date field by setting it to the current date in a table that has millions of rows. Using the Entity Framework to do that one row at a time would be very inefficient, but you could do it quickly by running a SQL update query.

• Change the way you configure or use the Entity Framework in problematic scenarios.

  For example, the Entity Framework determines how an entity has changed (and therefore which updates need to be sent to the database) by comparing the current values of an entity with the original values. If you're tracking a large number of entities and you trigger automatic change tracking many times in a loop, the performance cost might be significant. In these scenarios you can improve performance by disabling change tracking until the loop is completed.

For more information about executing custom SQL statements while using the Entity Framework or configuring the Entity Framework to handle special situations, see Advanced Entity Framework Scenarios for an MVC Web Application on the ASP.NET site.

For more information about the Entity Framework, see ASP.NET Data Access Content Map.

Entity Framework Development Approaches

As shown in the following diagram and in this video clip, there are three ways you can work with data models and databases in the Entity Framework: Database First, Model First, and Code First.

• Database First

  If you already have a database, the Entity Framework designer built into Visual Studio can automatically generate a data model that consists of classes and properties that correspond to existing database objects such as tables and columns. The information about your database structure (store schema), your data model (conceptual model), and the mapping between them is stored in XML in an .edmx file. The Entity Framework designer provides a graphical UI that you can use to display and edit the .edmx file.

• Model First

  If you don't have a database yet, you can begin by creating a model in an .edmx file by using the Entity Framework graphical designer in Visual Studio. When the model is finished, the Entity Framework designer can generate DDL (data definition language) statements to create the database. As in Database First, the .edmx file stores model and mapping information.

• Code First

  Whether you have an existing database or not, you can use the Entity Framework without using the designer or an .edmx file. If you don't have a database, you can code your own classes and properties that correspond to tables and columns. If you do have a database, Entity Framework tools can generate the classes and properties that correspond to existing tables and columns. The mapping between the store schema and the conceptual model represented by your code is handled by convention and by a special mapping API. If you let Code First create the database, you can use Code First Migrations to automate the process of deploying the database to production. Migrations can also automate the deployment of database schema changes to production when your data model changes.

Choose Code First for new development unless one of the following conditions is true:

• You want to use a graphical designer to model database objects and relationships.

  The Entity Framework designer only works with Database First and Model First. Before you choose Model First, however, consider how you want to handle updates to the data model after you create the database, and how you want to deploy the database and deploy updates to it. Code First Migrations automates the process of implementing and deploying database schema changes that result from data model changes. The advantages of Code First Migrations might outweigh the advantages of the Entity Framework designer. For help making this choice, see the links to Model First and Code First resources in ASP.NET Data Access Content Map.

• You want the Entity Framework to use stored procedures automatically for create, update, and delete operations.

  In the current release of Entity Framework (5.0), you can't configure Code First so that the Entity Framework automatically calls a stored procedure whenever you create, update, or delete an entity.

For more information, see ASP.NET Data Access Content Map.

LINQ versus SQL

LINQ is a feature of the C# and Visual Basic languages that lets you query data by writing code. You can write LINQ expressions in two ways: as queries and as fluent API.

LINQ query syntax is similar to SQL except that the FROM clause comes first in LINQ so that Visual Studio can provide IntelliSense for the remainder of the statement. The following example of a LINQ query retrieves Department entities for departments that have at least one course assigned to them, and it sorts the resulting list by last name:

var departments = from i in db.Departments
                          where i.Courses.Count > 0
                          orderby i.Name
                          select i;

Fluent API refers to the practice of chaining method calls in a single statement, as shown in the following example which does exactly the same thing as the LINQ query shown previously:

var departments = db.Departments.Where(i => i.Courses.Count > 0).OrderBy(i => i.Name);

The choice between between queries and fluent API depends on which syntax you're more comfortable with or want to learn, and you can mix and match based on the needs of each scenario. If you decide to use fluent API, you must learn or be familiar with lambda expressions.

LINQ uses the .NET provider model to access data, which means that the same code can access different data stores depending on which provider is used. The .NET Framework includes the following LINQ providers:

• LINQ to Objects

  Query any in-memory collection that implements the IEnumerable interface.

• LINQ to DataSet

  Query DataTable and DataRow objects in a DataSet object.

• LINQ to XML

  Query in-memory XML data.

• LINQ to SQL

  Query a LINQ to SQL data model. For information about LINQ to SQL, see Object-Relational Mappers (ORM) earlier in this topic.

• LINQ to Entities

  Query an Entity Framework data model.

For an introduction to LINQ, see LINQ (Language-Integrated Query).

There is also a version of LINQ that is designed to facilitate constructing queries at run time out of information that is not known until run time. For example, you might want to specify a different OrderBy clause depending on which column a user clicks in a grid. In these scenarios you can use dynamic LINQ. For more information, see Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library) on ScottGu's blog.

For data access scenarios in which LINQ is an alternative to SQL, LINQ is the recommended way to query data for the following reasons:

• LINQ provides compile-time validation of query syntax. With SQL you don't find out about syntax errors until run time.

• LINQ provides IntelliSense, which makes the process of writing queries quicker, easier, and less error-prone.

• LINQ provides a level of abstraction between your queries and the data store that they run against. You can change the data store without changing the code. For example, you might have written the queries in the previous example to run against an IQueryable object that would result in a database query. If you later decide to change the data source to an IEnumerable<T> collection, you can do that without changing the LINQ code. LINQ will automatically use the correct provider. (However, there may be slight changes between provider implementations, so whenever you make changes of this nature, test to verify that the result is the same.)

• Using LINQ automatically protects you from SQL injection attacks. If you're using SQL statements, you have to make a conscious effort to protect them from SQL injection attacks.

Exceptional scenarios in which you might want to use SQL instead of LINQ include the following:

• When you want to perform an operation more efficiently than would be possible by using the Entity Framework. For example, a single SQL UPDATE statement that affects many rows is more efficient than retrieving the rows with a LINQ statement, updating the entities in code, and then saving the changes.

• When you want to use an advanced SQL command such as the MERGE command.

• When you want to construct queries at run time, and dynamic LINQ does not have the degree of flexibility that you need.

There are two ways to execute SQL statements in the Entity Framework: Entity SQL and SQL that is native to the underlying database. Entity SQL is an older feature that is no longer the recommended method of executing SQL in the Entity Framework except for a few scenarios that require it. (One example is the EntityDataSource control, which uses Entity SQL.) To execute SQL in the Entity Framework, use the DbSet.SqlQuery Method, the Database.SqlQuery Method, or the Database.ExecuteSqlCommand Method. For more information, see Advanced Entity Framework Scenarios for an MVC Web Application on the ASP.NET site.

Working with Data in ASP.NET Web Forms Applications

In an ASP.NET Web Forms application, you use data-bound controls to automate the presentation or input of data in web page UI elements such as tables and text boxes and drop-down lists. The process of automating data transfer between a database or other data store and a data-bound control is called data binding, and three data-binding methods are available: data source controls, model binding, and manual data binding. ASP.NET Web Forms also includes Dynamic Data Scaffolding, which is a framework that can automatically generate web pages with all of the UI needed for displaying and editing data in a database.

This section contains the following topics:

• Data-Bound Controls

  What data-bound controls are available, and how to choose which ones to use.

• Data Source Controls

  What data source controls are available, when to use them, and how to choose which ones to use.

• Model Binding

  A brief introduction to Web Forms model binding and when to use it.

• Manual Data Binding

  A brief introduction to manually binding data to data-bound controls.

• Data-Binding Expressions

  How to insert variables in the markup of data-bound controls that will be used at runtime to display or update data fields from the bound data source.

• Dynamic Data Project Templates

  When to use the Dynamic Data project templates.

Data-Bound Controls

ASP.NET provides an assortment of server controls that automate the process of rendering UI in markup sent to the browser. These controls provide a variety of properties that you can set to configure the appearance of the generated UI, and they raise events that you can handle in server code. For example, the following markup is all you need to write in order to generate a table like the one that is shown after the markup:

Data-bound controls provide many properties that you can set in order to configure their appearance or behavior. Some of the controls also let you specify how the control generates HTML by using templates. A template is a block of HTML markup that you write and in which you include special variables that specify where and how the bound data is to be displayed. When the control is rendered, the variables are replaced with actual data and the HTML is rendered to the browser. For example, the following markup for a templated control generates the table that is shown after the markup:

ASP.NET Web Forms provides many controls that you can bind to a data source, and in most cases the choice of which one to use is obvious. To create a text box you use a TextBox control, and to create a drop-down list you create a DropDownList control, and so forth. You can see what controls are available in the Standard and Data sections of the Visual Studio Toolbox window. For more information about data-bound controls, see Data-Bound Web Server Controls.

For displaying a list or table of data, or for displaying the details of an individual record, there are multiple options. The following sections explain how to choose the option that best fits your scenario.

Controls for Displaying Lists

You have several choices if you want to create a list or table of data:

• Use the ListView control when you want to customize the generated HTML, and you also want some advanced features such as paging, sorting, grouping, and updating. The disadvantage of this control compared to the GridView control is that you have to write the HTML yourself.

• Use the Repeater control when you want to customize the generated HTML, and efficient performance is more important than advanced features. The Repeater control is simpler and has less overhead than the ListView control. It does not support advanced features such as paging, sorting, grouping, and updating. It also does not support an empty data template. (An empty data template provides an easy way to write markup for scenarios where the data source might not return any data.) As is true of the ListView control, you have to write all of the HTML.

• Use the GridView control for all other scenarios. This control provides a wide range of properties to configure appearance and behavior, and it can automate sorting, paging, and updating. You can let it generate all of the HTML automatically, or you can use templates to specify parts of the generated HTML manually. For some of its functions the GridView control might use view state heavily enough to cause performance degradation. In those scenarios you may be able to get better performance from the ListView or the Repeater control, at the expense of having to write more markup manually.

• Don't use the DataList and DataGrid controls. These are legacy controls that are superseded by the GridView control.

Controls for Displaying a Single Record

Two controls are intended for use when you want to display or update the field values of a single record:

• Use the DetailsView control when it is more important to minimize the amount of markup you have to write than it is to be able to customize the generated HTML.

• Use the FormView control when you want to customize the generated HTML and you can afford the extra time it takes to write all of the markup manually.

You can use EntityTemplate and DynamicEntity controls in the FormView control templates to generate some of the markup automatically, thereby combining the markup generation benefits of the DetailsView control with the flexibility of the FormView control. However, there is a limited amount of documentation that shows how to do this. For more information, see Dynamic Data Templates For C# WAP or WebSite and Dynamic Data Templates For VB WAP or WebSite.

TemplateField, BoundField, and DynamicField Controls

When you use the GridView and DetailsView controls, you can let the control generate columns (GridView) or rows (DetailsView) automatically, or you can specify how they are generated yourself. If you specify them yourself, you can write your own markup in TemplateField controls or you can use BoundField or DynamicField controls. The following example shows a GridView control that displays three LastName columns, using a different method for each column:

When the user clicks the Edit link, text boxes are displayed.

Here are some guidelines for choosing the right control for your scenario:

• Use the TemplateField control when you need to customize the UI that is generated. For example, in display mode you might want to display the first name and last name in a single field separated by a comma, while in edit mode you might want to display two text box controls.

• Use the DynamicField control when you're binding to strongly typed data and the default UI element for the bound data type meets your needs. The DynamicField control automatically formats the output in display mode and dynamically adds the appropriate validation controls in edit mode, based on the data type and any DataAnnotations attributes you have in your data model. For information about binding to strongly typed data, see Data-Binding Expressions later in this topic.

• Use the BoundField control when you're binding to weakly typed data and the default UI meets your needs. For example, if the list control is bound to a SqlDataSource control, it is getting weakly typed data. The BoundField control is an earlier version of the DynamicField control that does not provide automatic formatting and validation. You get server validation with both controls, but you get automatic validation on the client only with the DynamicField control.

DynamicControl Control

When you use TemplateField controls in the GridView and DetailsView controls, and when you write markup for FormView and ListView templates, you can specify UI elements such as Label and TextBox controls as shown in the previous example, or you can use DynamicControl controls. Like DynamicField controls, DynamicControl controls automatically format and validate based on DataAnnotations attributes in your data model. The following example shows them used to display last name, first name, and hire date fields all in one table column:

If Label and TextBox controls had been used in this example instead of DynamicControl controls, the DateTime value of the hire date would have been displayed with the time, for example: 3/11/1995 12:00:00 AM. But the HireDate field includes a DataAnnotations attribute that specifies its format, and the DynamicControl uses this information:

As in the case of the DynamicField control, choose the DynamicControl control in data-bound control templates when you're binding to strongly typed data, unless the UI that it creates is not what you want.

For more information about how to use the DynamicField and DynamicControl controls, see the following resource:

• Using Dynamic Data Functionality to Format and Validate Data (ASP.NET site. Shows how to use DynamicField controls with data source controls. The code that this tutorial puts in the Page_Init handler in order to enable dynamic data functionality is not necessary when you use model binding as your data binding method. Model binding automatically enables dynamic data functionality. For more information about model binding, see Model Binding later in this topic.)

Data Source Controls

Data source controls are controls that you put on a web page in order to specify the link between a data source and UI server controls. You typically create data source controls by including markup in an .aspx page, but they do not render any UI; their only purpose is to link data-bound controls to a data source. In the following example, a SqlDataSource control specifies the connection string and SQL select command, and the GridView control specifies the data source control that it uses to get data:

Data source controls are a good choice of data binding method if you don't want to write code to handle data access, your data access needs are relatively simple, and you're using rapid application development (RAD) methodology in order to get an application up and running in a minimum amount of time. With data source controls you can create a web page that offers full insert, read, update, and delete functionality without writing any code at all.

The specific data source control you use depends on the technology you choose for database access:

• Use the SqlDataSource control when you want to use ADO.NET directly and write your own queries.

• Use the EntityDataSource control for data that you access by using the Entity Framework. This control requires special configuration workarounds to work with Code First. A link will be added here when documentation on those workarounds is published.

• Use the XmlDataSource control for XML data that you have in an object in memory (that is, you're not retrieving it from a database).

• Use the LinqDataSource control for data that you access by using the LINQ to SQL ORM.

• In projects that target ASP.NET 4 or earlier versions, use the ObjectDataSource control when you want to write custom code for data access, such as for multi-tier application architecture. In projects that target ASP.NET 4.5, model binding is recommended instead for this scenario. For more information, see the following section.

• Do not use the AccessDataSource control. You will find this control in the Toolbox for projects that target ASP.NET 4 and earlier versions, but Microsoft recommends against using Access in web applications. For more information, see Can my ASP.NET web application use a Microsoft Access database? in ASP.NET Data Access FAQ.

For more information, see Data Source Web Server Controls.

Model Binding

ASP.NET data source controls facilitate rapid application development, but they are inflexible. They are easy to work with for simple tasks that they were explicitly designed for, but the learning curve is steep if you need to do something special. Also, since you configure data source controls in .aspx page markup, you can't keep data access code in a data access layer, and you can't implement automated unit testing for your data access code. The ObjectDataSource control facilitates multi-tier application architecture and unit testing, but handling many common scenarios like two-way data-binding and input validation can be cumbersome.

In ASP.NET 4.5 Web Forms model binding addresses these issues. The model binding pattern was first introduced with ASP.NET MVC and was subsequently adapted for ASP.NET Web Forms. You write code for insert, read, update, and delete methods, and you specify in the markup of the data-bound control which methods it should call for each operation. The framework minimizes the amount of code you have to write for common data access operations, and since you're writing your own code there is no steep learning curve when you need to do something special.

In the following example, the GridView control specifies the methods to call to read a list of instructors and to update an instructor when the user clicks an Edit link:

You can also specify Insert and Delete methods, and you can add validation controls to display error messages. The following example shows the methods identified by the SelectMethod and UpdateMethod attributes in the preceding example:

public IEnumerable<Instructor> GetInstructors()
{
    List<Instructor> instructors = new List<Instructor>();
    using (var db = new SchoolContext())
    {
        instructors = db.Instructors.Include("Courses").ToList();
    }
    return instructors;
}

public void UpdateInstructor(int personID)
{
    using (var db = new SchoolContext())
    {
        var instructor = db.Instructors.Find(personID);
        if (instructor == null)
        {
            ModelState.AddModelError(String.Empty, String.Format("An instructor with ID {0} could not be found.", personID));
            return;
        }
        TryUpdateModel(instructor);
        if (ModelState.IsValid)
        {
            db.SaveChanges();
        }
    }
}

When the data-bound control needs to retrieve or update data, ASP.NET calls the appropriate method that you have identified, and in the method parameters ASP.NET automatically provides data received from the client in form fields, cookies, or query strings. In this example, the UpdateInstructor method automatically receives the ID value of the record to be updated because the GridView control specifies PersonID in the DataKeyNames attribute. And the TryUpdateModel method automatically updates the properties of the instructor object based on input received from the web page.

If your Select method returns an IQueryable object, the GridView control can automatically provide advanced functions such as paging and sorting.

In new development that targets ASP.NET 4.5, use model binding instead of data source controls, unless your data access needs are simple and you want to avoid writing code to handle data access.

In applications that target earlier versions of ASP.NET earlier than 4.5, you can't use model binding. For n-tier architecture, unit testing, and customizing data access, the alternative is to use the ObjectDataSource control.

For more information about Web Forms model binding, see ASP.NET Data Access Content Map.

Manual Data Binding

Data source controls and model binding do much of the data binding work in order to make you more productive, but in some scenarios you might want to manually set a data-bound control's DataSource property and call its DataBind method.

In applications that target ASP.NET 4 or earlier versions, this data-binding method is typically used when you want to bind a control to an in-memory collection instead of a database or data service. For example, the following code constructs an in-memory collection and binds it to a DropDownList control. The markup for the drop-down list control is shown after the data-binding code.

protected void Page_Load(object sender, EventArgs e)
{
    var instructors = new List<Instructor>();
    instructors.Add( new Instructor() { PersonID = 1, LastName="Abercrombie" });
    instructors.Add( new Instructor() { PersonID = 2, LastName="Zheng" });
    InstructorsDropDownList.DataSource = instructors;
    InstructorsDropDownList.DataBind();
}

In ASP.NET 4.5, you can handle scenarios like this more easily by using model binding and constructing the collection in a Select method. There are few scenarios in ASP.NET 4.5 where you would want to do manual data binding instead of model binding. One exception is for asynchronous data access, when you need to call a DataBind method in a callback handler.

Data-Binding Expressions

For templated data-bound controls, you write markup that indicates where values from each data field should be displayed, or which UI elements such as text boxes update which data fields. To do that you use data-binding expressions. For weakly typed data, you can use the Eval (for display only) and Bind (for display and update) expressions. The following example shows an Eval expression in a template that is used for display only, and a Bind expression in a template that is used for updating:

Eval and Bind expressions are not strongly typed; that is, the type of the expression is not known at design time. This means you don't get IntelliSense at design time or validation at compile-time for them. In applications that target ASP.NET 4.5 you can use the Item (display only) and BindItem (display and update) expressions for strongly typed data. To use these expressions, declare the data type of the items you're binding to the control by using the ItemType attribute, as shown in the following example:

If you're developing an application that targets ASP.NET 4.5, use the Item and BindItem expressions instead of the Eval and Bind expressions, except in the following scenarios:

• You're providing weakly typed data to the data-bound control.

  Data source controls such as the SqlDataSource control provide weakly typed data. In some model binding scenarios, you might have to return weakly typed data from your Select method, such as a non-generic IEnumerable collection or a DataSet object.

• The data-bound control you're using does not support strongly typed data binding.

  A few older controls that are no longer commonly used have not been updated. If the control you want to use inherits from the DataBoundControl type, it has an ItemType property and supports strongly typed data binding.

• You're using automatic column generation in the GridView control, and you want the control to generate BoundField controls instead of DynamicField controls.

  For more information, see GridView.AutoGenerateColumns.

Data-binding expressions are contained within <%# and %> delimiters. In addition to calling Eval, Bind, Item, and BindItem methods, you can call any publicly scoped code within the <%# and %> delimiters to execute that code and return a value during page processing. In ASP.NET 4.5 you can add a colon to the opening delimiter to specify that the value of the expression should be HTML-encoded, as shown in the following example:

The additional colon was not used in the preceding examples because the Text property of a data-bound control is automatically HTML-encoded. Using the colon in scenarios like this example where the value of the expression would otherwise not be HTML-encoded is recommended to help prevent cross-site scripting attacks.

For more information about data-binding expressions, see ASP.NET Data Access Content Map.

Dynamic Data Project Templates

Visual Studio includes project templates for creating dynamic data web applications. In ASP.NET 4.5, you must use the Entity Framework to access your database. (For applications that target ASP.NET 4, you can also use LINQ to SQL.) At run time, the dynamic data run-time scaffolding feature uses information about the database contained in the data model to determine how to display each table and each column within a table. The scaffolding feature also determines how to validate input entered in a web page to be stored in each table column.

Dynamic data run-time scaffolding is a good alternative to writing markup and code for data access manually when the following conditions are true:

• You need to quickly create an application that gives its users the ability to view and update data in a database.

• The application needs minimal business logic beyond basic create, read, update, and delete functionality, and you don't expect that to change in the future.

• The application needs minimal customization of the default UI for each data type, or customization is required and you're prepared to invest time in learning how to customize dynamic data templates.

• The application will be hosted on an internal network, and anyone who has access to the application can be granted access to all of the data that it makes available. It is difficult to configure fine-grained security restrictions for a Dynamic Data application.

The development of dynamic data scaffolding led to many advances in data handling in ASP.NET Web Forms, MVC, and Web Pages. Features such as automated display formatting and input validation based on data types and DataAnnotations attributes are no longer limited to dynamic data run-time scaffolding projects. Microsoft continues to develop data handling features built on dynamic data technology for ASP.NET. However, like LINQ to SQL, dynamic data run-time scaffolding is now a low priority for future development. If dynamic data run-time scaffolding features don't meet your needs now, don't create a dynamic data scaffolding application in expectation that the features you need will be added in the future.

A disadvantage of dynamic data run-time scaffolding is the steep learning curve for customizing how data is displayed and validated. Future development efforts are focused on technologies that offer similar benefits while making it possible for you to customize web application behavior by using skills and knowledge that you already have from working with ASP.NET.

For more information about Dynamic Data Scaffolding, see ASP.NET Dynamic Data.

Working with Data in ASP.NET Web Pages Applications

In ASP.NET Web Pages applications, the Database helper provides a quick and easy interface with ADO.NET that enables you to connect to a database and execute SQL queries that you write yourself. For example, the following code uses the Database helper to open a SQL Server Compact database and run a query, and then it displays the results in a table by using the WebGrid helper:

When you use the Database helper you're using ADO.NET directly, without an Object-Relational Mapper. (For information about ORMs, see Object-Relational Mappers (ORM) earlier in this topic.) The ASP.NET Web Pages framework is designed to provide an easy way for people who are new to web programming to get started and build applications that have relatively simple data access requirements. In these scenarios, using ADO.NET directly is a good choice because your data handling needs are not complex enough for the ORM's advantages to outweigh its learning curve.

For more information about the Database helper, see the following resources:

• Introducing ASP.NET Web Pages 2 - Displaying Data (ASP.NET site)

• Database Class (API reference)

If you're a more experienced developer and are building a relatively complex application with complex data access requirements, consider using the Entity Framework. If you do that, use Visual Studio instead of WebMatrix as your development environment. WebMatrix does not include some of the tools that are essential for working with the Entity Framework, such as the Entity Framework designer and the NuGet Package Manager Console.

Accessing Data through a Web Service

The following technologies can be used to make data available to clients over a web service:

• WCF Data Services

  With WCF Data Services, you have to explicitly configure security restrictions: by default everything is made available and you write code to place restrictions on whatever you do not want to be available.

• Web API

  With Web API, by default nothing is made available, and you write code to explicitly make available only the data that you want to be available.

Choose WCF Data Services if you want to quickly expose a database over HTTP for use inside a firewall on an internal company network. In this scenario the consequences of accidentally making sensitive data available over the web service are less than if you were making the service available over the Iinternet. Conversely, choose Web API for Internet-based services because the whitelist approach is more secure than the blacklist approach.

Summary of Recommended Choices

Options	Recommended Choice(s)	More Information
Database Management Systems (DBMS) SQL Server Third-party databases such as Oracle, MySQL, SQLite (Microsoft Access is not recommended for web applications.)	SQL Server	Database Management Systems (DBMS)
SQL Server Editions LocalDB SQL Server Express SQL Server Developer Edition SQL Server (full editions) Windows Azure SQL Database SQL Server Compact	Default choices for development: For Visual Studio 2012, and SQL Server or SQL Database in production: use LocalDB in development. For Visual Studio 2010 or WebMatrix, and SQL Server or SQL Database in production: use SQL Server Express in development. For SQL Server Compact in production: use SQL Server Compact in development.	SQL Server Editions
Object-Relational Mappers (ORM) ADO.NET Entity Framework LINQ to SQL nHibernate	For ASP.NET Web Forms and MVC: Entity Framework For ASP.NET Web Pages with WebMatrix: ADO.NET directly (the Database helper)	Object-Relational Mappers (ORM) Working with Data in ASP.NET Web Pages Applications
Entity Framework development workflows	If you don't need a graphical designer, or the benefits of Code First Migrations outweigh the desire for a designer: Code First For an existing database when you want to use a graphical designer: Database First For a new database when you want to use a graphical designer: Model First	Entity Framework Development Workflows
LINQ versus SQL	Use LINQ whenever possible.	LINQ versus SQL
Web Forms list controls	To customize generated HTML, with advanced features: ListView control To customize generated HTML, when read-only access is sufficient and efficiency is more important than advanced features: Repeater control For maximum automation of HTML generation: GridView control	Controls for Displaying Lists
Web Forms single-record controls	To customize generated HTML: FormView control For maximum automation of HTML generation: DetailsView control	Controls for Displaying a Single Record
Web Forms field controls for GridView and DetailsView controls	For strongly typed data: DynamicField control For weakly typed data: BoundField control To customize column or row appearance: TemplateField control	TemplateField, BoundField, and DynamicField Controls
Web Forms data-bound controls for templates in ListView, Repeater, FormView, and TemplateField controls	For strongly typed data: DynamicControl control For weakly-typed data: specific UI controls	DynamicControl Control
Web Forms data binding methods	When you don't want to write any code, and data access requirements are simple: data source controls For all other scenarios when it is feasible to write code for data retrieval and update: model binding For controls that don't support model binding, and when you need control over the timing of data binding (for example, for asynchronous data retrieval): manual data binding	Data Source Controls Model Binding Manual Data Binding
Data-binding expressions	For weakly typed data: Eval and Bind For strongly typed data: Bind and BindItem Use the colon in the opening delimiter for data-binding expressions (<%#:) to enable automatic HTML-encoding.	Data-Binding Expressions
Dynamic Data project templates (when to use)	Use in the following scenario: You have to create a database application rapidly. The application requires minimal business logic. The application requires minimal UI customization, or you're prepared to invest time in learning how to customize dynamic data templates. The application will be deployed on an internal network and does not need fine-grained security restrictions.	Dynamic Data Project Templates
Accessing data through a web service	On an internal company network: WCF Data Services Over the Internet: Web API	Accessing Data Through a Web Service

See Also

Concepts

ASP.NET Data Access Content Map