[Note: This post is a preliminary version of a document that has been published on MSDN. The published version with changes resulting from the comments received is now available here. Thanks to everyone who sent comments about this version.]
This is one of a series of posts that present preliminary versions of pages that will eventually be published on MSDN. These pages are a work in progress and suggestions for improvements or corrections are welcome. The intended audience is newcomers to ASP.NET and the purpose is to provide basic guidance for making data access choices for ASP.NET application development. Please forgive the less than ideal formatting -- the HTML for this post was generated by tools that weren't designed for blog publishing. I corrected some of the deficiencies but did not fix all of them.
The series includes:
-- Tom Dykstra ASP.NET User Education
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 first section provides a high-level summary of all of the recommendations, and following sections provide explanations and reasons for the recommendations.
(Microsoft Access is not recommended for web applications.)
SQL Server Editions
See Choosing a SQL Server Edition for ASP.NET Web Application Development.
Object-Relational Mappers (ORMs)
Working with Data in ASP.NET Web Pages Applications
Entity Framework development workflows
Entity Framework Development Workflows
LINQ versus SQL
Use LINQ whenever possible.
Web Forms list controls
Controls for Displaying Lists
Web Forms single-record controls
Controls for Displaying a Single Record
Web Forms field controls for GridView and DetailsView controls
Using Templated Data-Bound Controls
Web Forms data-bound controls for templates in ListView, Repeater, FormView, and TemplateField controls
Web Forms data binding methods
Data Source Controls
Manual Data Binding
Dynamic Data project templates (when to use)
Use in the following scenario:
Dynamic Data Project Templates
Accessing data through a web service
Accessing Data Through a Web Service
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 provides data providers that enable you to connect to the following databases:
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 (ADO.NET) 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:
For information about which edition and version of SQL Server to choose for your scenario, see Choosing a SQL Server Edition for ASP.NET Web Application Development.
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:
Here is what code to populate these classes looks like when you use the Entity Framework ORM:
And here is code that accomplishes the same task by using ADO.NET directly:
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 much more productive, and your application much 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:
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:
Developers who are considering whether to adopt the Entity Framework frequently ask about its 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 insignificant or is 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:
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.
As shown in the following diagram, there are three ways you can work with data models and databases in the Entity Framework: Database First, Model First, and Code First.
Choose Code First for new development unless one of the following conditions is true:
For more information, see ASP.NET Data Access Content Map.
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 sorts the resulting list by last name:
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:
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:
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.
LINQ is an alternative to SQL, and it is the recommended way to query data for the following reasons:
Exceptional scenarios in which you might want to use SQL instead of LINQ include the following:
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.
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:
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 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 other 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.
You have several choices if you want to create a list or table of data:
Two controls are intended for use when you want to display or update the field values of a single record:
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.
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:
When you use TemplateField controls in the GridView and DetailsView controls, and when you use the FormView and ListView controls, 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:
Without the DynamicControl control, 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:
Data source control 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 them 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'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:
For more information, see Data Source Web Server Controls.
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 create, read, update, and delete (CRUD) 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 delete an instructor when the user clicks a Delete link:
The following example shows the methods identified by the SelectMethod and DeleteMethod attributes in the preceding example:
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. Notice that the DeleteInstructor method receives an Instructor object. The model binding framework automatically creates an instructor object based on data that the GridView control provides in view state, and it passes this object to the method as a parameter. When 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, the choice of data binding method is simple: use model binding instead of data source controls unless you want to avoid writing code to handle data access and your data access requirements are simple enough to do that by using data source controls.
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.
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.
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. In ASP.NET 4.0 and earlier versions, your only options are 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 strongly typed expressions instead: the Item (display only) and BindItem (display and update) expressions. 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:
For more information about data-binding expressions, see ASP.NET Data Access Content Map.
Visual Studio includes project templates for creating dynamic data web applications. You must use either the Entity Framework or LINQ to SQL to access your database. 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:
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 Content Map.
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 the Object-Relational Mappers section 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:
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.
The following technologies can be used to make data available to clients over a web service:
Choose WCF Data Services if you're developing a service that will be used 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 internet. Conversely, choose WebAPI for internet-based services because the whitelist approach is more secure than the blacklist approach.