Object Relational Mapping

With .NET 3.0/3.5, MS has finally released not one but two ORM toolkits. I say finally because MS previously promised an ORM toolkit to developers in the form of a project called ObjectSpaces. ObjectSpaces was never released and its demise is clouded in a series of potentially bad integrations with WinFS, Project Green, or the MS Business Framework, all of which were eventually cancelled.
The spark that reignited MS ambitions to produce an ORM was the Language  Integrated Query (LINQ) project. LINQ’s goal as part of C# 3.0 and Visual Basic  9.0 was to provide a set based metaphor for dealing with heterogeneous data types including object collections, XML, and last but not least, relational data.
For the relational component of LINQ, the language team came up with a project called LINQ to SQL and shipped it as part of .NET Framework 3.5.
During the same time period, the Data Programmability Group in the SQL Server team had been working on a new layer to sit over top of SQL Server’s logical data model. This new “Entity Data Model” (EDM) would allow developers to interact with their relational database using a more abstract conceptual model based on the ideas of Dr. Peter Chen. For this team, the promise of Language Integrated
Query was what really brought the notion of the EDM to life, and so began the
“Entity Framework” project. The Entity Framework did not ship until several months after LINQ to SQL as part of the .NET 3.5 Service Pack 1 release.
On the outside, LINQ to SQL and the Entity Framework ORMs appear very similar. Both tools use a graphical design tool and wizard to map a relational database into an object model. They can both use LINQ queries to project relational data into objects using composable queries. Both provide change tracking so that changes made to the objects can be persisted back to the database with a single method call.
Given these similarities, Microsoft has spent some considerable energy in explaining when to use each of these technologies. While these two ORMs have strong similarities in programming models, their internal architectures are quite different. These key differences are:
LINQ to SQL has been locked directly to SQL Server, whereas the Entity
Framework includes a provider model that allows it to work with any relational engine. As mid 2009 the list of available providers in the ecosystem included SQL Server, Oracle, Sybase, DB2, Informix, MySQL, PostgresSQL,
SQLite, OpenLink Virtuoso, and Firebird.
LINQ to SQL does not provide a mechanism for streaming records one at a time. Using Entity SQL, the Entity Framework can provide Data Reader style access to results using the same mapping layer used for object queries.
LINQ to SQL provides an out of the box experience for lazy loading. Lazy loading allows the engine to automatically (by default) go out and retrieve new data as it is needed based on code that access different elements of the object graph. In the Entity Framework, you have to be explicit when you make trips to the database. Both ORMs allow you to load deep object graphs eagerly as well.
Perhaps the largest difference between Entity Framework and LINQ to
SQL is the conceptual modeling layer that EF provides. EF provides a much richer mapping layer than LINQ to SQL, allowing you to join tables together, provide elaborate class hierarchies with abstract and concrete types, and retain many-to-many relationships in your model. Future plans will see the conceptual model being used in other MS products such as SQL Server
Reporting Services and SQL Server Analysis Services.
Finally, expect to see significant investment in the Entity Framework ORM from Microsoft. Both ORMs are now managed by the same product team at MS and they have stated that while LINQ to SQL will continue to be supported and maintained, future innovation will be focused on the Entity
Framework.

DataSets, DataTables, Data & Table Adapters

With DataReaders you need to be careful about how long you keep your connection open while iterating. Sometimes it is better to pull back your entire result set into memory, release your connection to the database, and then process the result set afterwards. This is where DataSets can be helpful. If you have a batch processing scenario that requires the entire result set in memory or you need to provide end user editing in a grid control, then DataSets are a good place to hold that data.
One of the primary benefits of a DataSet is that it not only caches the data in an efficient memory structure, but it can provide change tracking (what records were added? Deleted? Changed? What were the original values of each column, etc). DataSets work collaboratively with a SqlDataAdapter object, which is simply a composite of 4 SqlCommands (InsertCommand, SelectCommand,
UpdateCommand, and DeleteCommand) mapping to the appropriate CRUD behavior (create, read, update, and delete).
DataSets can contain multiple result sets, each mapped into its own DataTable.
Tables can be related to each other with Relations which mimic foreign keys.
DataSets take care of master-detail linkages and keep everything “hooked up” when inserting records into parent and child tables with identity columns. Each table will require its own SqlDataAdapter. If you are doing a mix of insert,updates, and deletes and your database has referential constraints, you’ll need to orchestrate the changes, making sure parent records are inserted before children and that deletions happen in the reverse order.
DataSets/DataTables come in two flavors: typed and untyped. Untyped DataSets simply infer the table and column definitions after executing the SqlDataAdapter.
Fill method. Accessing the columns must be done similarly to DataReaders using column names in quotes or by positional reference. Typed Datasets on the other hand use wizards and a design surface to generate a strongly typed object. The typed dataset is defined by an XSD document. The XSD is then turned into a strongly typed/named class that essentially sits on top of an untyped dataset. A table mappings collection on the SqlDataAdapter class allows you to use different names for your strongly typed object than that of your database table and column names.
Many people in the .NET 1.0-2.0 era used typed datasets as business entities and wrapped some additional business logic around them. Although you could map column names, you were typically tied tightly to your table design in the database. DataSets can also be converted to and from XML, including a diffgram format that retains pending change information. In many circles, DataSets have and continue to be serialized between distributed layers of an application.
This makes for a pretty productive development environment, but has drawn architectural criticisms compared to properly modeled services with messages and contracts.
In .NET 1.0, DataAdapters and DataSets were different objects that really didn’t know about each other until you executed the DataAdapter Fill or Update method and passed the DataSet/DataTable to the respective method. It was possible for the table mappings in the SqlDataAdapter to get out of sync with the strongly typed dataset. To improve this situation, Microsoft introduced
TableAdapters in .NET 2.0, which are really just DataAdapters that are strongly typed and embedded into the definition of the typed dataset.

Running Queries in Visual Studio

If you’ve never used SQL before, you may want to play around with it and create some sample queries before you start using it in an ASP.NET site. Most database products provide some sort of tool for testing queries. If you’re using a full version of SQL Server, you can try SQL Server Management Studio or SQL Query Analyzer. If you don’t want to use an extra tool, you can run your queries using the Server Explorer window described earlier. Just follow these steps in  Visual Studio:
1. Right-click your connection, and choose New Query.
2. Choose the table (or tables) you want to use in your query from the Add Table dialog box (as shown in Figure 15-5), click Add, and then click Close.
3. You’ll now see a handy query-building window. You can create your query by adding check marks next to the fields you want, or you can edit the SQL by hand in the lower portion of the window. Best of all, if you edit the SQL directly, you can type in anything—you don’t need to stick to the tables you selected in step 2, and you don’t need to restrict yourself to Select statements.
4. When you’re ready to run the query, select Query Designer ä Execute SQL from the menu. Assuming your query doesn’t have any errors, you’ll get one of two results. If you’re selecting records, the results will appear at the bottom of the window . If you’re deleting or updating records, a message box will appear informing you how many records were affected.

SQL Basics in ADO.NET

When you interact with a data source through ADO.NET, you use SQL to retrieve, modify, and update information. In some cases, ADO.NET will hide some of the details for you or even generate required SQL statements automatically. However, to design an efficient database application with a minimal amount of frustration, you need to understand the basic concepts of SQL.
SQL (Structured Query Language) is a standard data access language used to interact with relational databases. Different databases differ in their support of SQL or add other features, but the core commands used to select, add, and modify data are common. In a database product such as SQL Server, it’s possible to use SQL to create fairly sophisticated SQL scripts for stored procedures and triggers (although they have little of the power of a full object-oriented programming language). When working with ADO.NET, however, you’ll probably use only the following standard types of SQL statements:
• A Select statement retrieves records.
• An Update statement modifies existing records.
• An Insert statement adds a new record.
• A Delete statement deletes existing records.
If you already have a good understanding of SQL, you can skip the next few sections.
Otherwise, read on for a quick tour of SQL fundamentals. nTip To learn more about SQL, use one of the SQL tutorials available on the Internet, such as the one at http://www.w3schools.com/sql. If you’re working with SQL Server, you can use its thorough Books
Online help to become a database guru.