.NET Data Access strategies overview

Due to the increasing number of data access strategies being introduced to .NET with little guidance on which to use I decided to create an overview of the different methodologies available. This is really just a cheatsheet for myself but maybe it’s useful to others as well…

The basics

DATASET: sql_data_adapter.Fill(data_set);

DATAREADER: SqlDataReader sql_data_reader = sql_command.ExecuteReader();

DataReaders are preferred for most web application tasks as they are light weight and fast.

Untyped DataSets

Dataset.Tables(0).Rows(0)(”Name”) style access. No type safety. Not very nice.

Strongly-typed DataSets

Add a “DataSet” item to your project and use the inbuilt DataSet Designer to configure your schema. Creates an .xsd file in your App_Code folder. Large memory footprint. Not efficient for remoting. Data model does not always match the problem domain well. Good for small, quick and dirty solutions but may not scale well.

Inherit from the strongly typed dataset object or use partial classes to avoid losing any custom code you write if you have to regenerate your .xsd file.

TableAdapters are like DataAdapters but geared towards strongly-typed DataSets.

N-Tier Data Application

N-Tier support for typed datasets provides enhancements to the Dataset Designer that assist in separating TableAdapter code and typed dataset code into discrete projects.

To separate TableAdapter code and typed dataset code into discrete projects, set the DataSet Project property in the Dataset Designer.

Create class library, select Show Data Sources from the Data menu, add a new data source…

More detail

Hierarchical Updates

Hierarchical update capabilities have been incorporated into the Dataset Designer to enable saving data in multiple related tables. A new TableAdapterManager object providing generated code that includes the save logic necessary for maintaining referential integrity has been added to typed datasets. Instead of calling the Update method for each TableAdapter, call the TableAdapterManager.UpdateAll method. For more information, see Hierarchical Update.

Custom entity classes

Write your own entity classes, entity collection classes, object population code, sql parameter population code, etc. Use this in conjunction with stored procedures. Introduction of Generics removes the need for writing custom collection classes - can simply use List<T> to hold a collection of your custom entity objects. Requires writing a lot of code already inbuilt into Strongly-Typed DataSets.

LINQ to SQL (Language Integrated Query)

Add a LINQ to SQL Classes item to your project.  Creates a .dbml file and associated files in your App_Code folder. Use the inbuilt Object Relational Designer to create your model.

The Object Relational Designer (O/R Designer) assists developers in creating and editing the LINQ to SQL objects that map between an application and a database. The O/R Designer creates the DataContext, entity classes, and DataContext methods that are used by LINQ to SQL to communicate with the remote database and handle data that is used in your application.

ADO.NET Entity Framework and the Entity Data Model (EDM)

Idea is to work with data from a conceptual point of view instead of a storage point of view. Create a design schema and a storage schema and map the two together.

http://msdn.microsoft.com/en-us/library/bb387122.aspx

ASP.NET Dynamic Data (Note: web only)

Used for scaffolding on top of LINQ to SQL or EDM.

ASP.NET Data Access overview (web only!)

ASP.NET also enables you to perform data binding declaratively. This requires no code at all for the most common data scenarios.

ASP.NET includes two types of server controls that participate in the declarative data binding model: data source controls and data-bound controls.

Data Source controls

SqlDataSource

Represents an SQL database to data-bound controls. Data source controls enable rich capabilities for retrieving and modifying data, including querying, sorting, paging, filtering, updating, deleting, and inserting.

SqlDataSource is intended to replace the ADO.NET code you would normally write in a page to create a connection and command to query a database. Because the data queries are specified directly as properties of the data source control, this is sometimes called a two-tier model as the data access code is in the presentation layer. For this reason, the SqlDataSource control is usually aimed at small hobbyist or personal sites that do not require a fully encapsulated data middle-tier object.

ObjectDataSource

ObjectDataSource control is targeted at larger enterprises with a need for middle-tier encapsulation of database queries.

Data Bound controls

These data source controls are used in conjunction with data bound controls (GridView, Repeater, etc). Two-way binding functionality is provided.

You can also bind directly to any collection that implements IEnumerable however you will lose a lot of the automatic functionality.

Other useful links

Data Access recommendations