Object/Relational Mapping - SQL Server 2008

A main goal for OO developers has been finding an easy solution to join the database world to the OO one. The former is composed by tables, columns, and a tabular way to store the data. The latter is formed by classes, relationships between them, and data stored within objects. The way to retrieve a record from the database is different from what OO developers are accustomed to with object manipulation. First of all, OO developers must know another language—SQL—to retrieve records from the database. Then they need to use the appropriate Application Programming Interface (API) to execute the queries from the code. Finally, developers must manipulate the retrieved data, looping through the collection of records.

Using the ADO.NET framework, one of the most powerful and complete APIs to manage the database from the code, these steps can be transformed into the following code snippet:

string connString = "Data Source=.;Initial Catalog=AdventureWorks;" +
"Integrated Security=SSPI";
string SQL = "SELECT ProductID, Name FROM Production.Product";
SqlConnection conn = new SqlConnection(connString);
SqlCommand comm = new SqlCommand(SQL, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetInt32(0).ToString() + " " + reader.GetString(1));

Note that for brevity, this sample code is not optimized, and it doesn’t contain exception handling. It’s intended to highlight some important aspects related to OO development:

  • OO developers are not accustomed to using indexes to retrieve values from an object. They use properties instead. Using both the GetInt32(0) and GetString(1) methods to obtain the values from the related columns is not an OO approach.
  • OO developers are accustomed to encapsulating recurrent actions into a unique class’s method. For example, the preceding code could be reduced to a unique method that accepts the connection string and the SQL command, and is responsible for managing the connection to the database, executing the query, and so on.
  • OO developers usually avoid the mix of different languages in the code. So mixing either C# or Visual Basic code with SQL commands is not so digestible to OO purists. Developers try to separate the code that accesses the database in classes belonging to the data layer and the code using these classes in a separate business layer.

One of the earlier OO approaches to the database world taken by Microsoft is the strongly typed dataset. With the use of the Visual Studio Dataset Designer, developers can drag tables from the Server Explorer window and drop them onto the designer surface. This operation generates a new class derived from the DataSet class, typed on source tables. Practically, this means that the class contains properties related to the table’s columns, with the same name and datatype. Moreover, the class contains a DataTable-derived class for each table dropped on the designer surface and the code to select and eventually update the database. In this way, calling just one method, Update from the DataAdapter class, the database is updated. The previous code snippet can be rewritten with the use of these classes, as follows:

Products p = new Products();
ProductsTableAdapters.ProductsTableAdapter da =
new ProductsTableAdapters.ProductsTableAdapter();
foreach(Products.ProductsRow r in p._Products.Rows)
Console.WriteLine(r.ProductID + ", " + r.Name);

Here, the Products class is inherited from a DataSet class and is typed upon the Products class from the AdventureWorks database. The ProductsTableAdapters contains the Fill method that accepts a ProductsDataTable parameter; it is useful to fill the Products dataset with records from the Products table. The Fill method runs the query specified during the class code generation with the Visual Studio Dataset Designer.

As you can see, the code is more readable and maintainable, providing properties that have the same name as related properties. It is an OO approach to the database records!

But this is just one little step toward what OO developers would have from their classes. The preceding example shows an automatic way to manage the database but contains a trick: the Visual Studio Dataset Designer generates SQL statements for you, but they are still in the class code! A purist doesn’t want to see mixed code in the class.

A pure O/RM tool allows developers to manage data without worrying about how to retrieve the data. Developers do not need to write any SQL code in their classes; they just manage classes and objects. The following pseudo-code snippet illustrates what a pure O/RM should provide:

Products p = new Products();
p.ProductID = 1;

Console.WriteLine(p.ProductID + ", " + p.Name);

The Retrieve imaginary method “knows” we are looking for the Product record that has the ProductID property equal to 1 and creates the SQL command for us, filling the Products class fields with the related values. It’s really gorgeous, isn’t it?

If you believe that the LINQ to SQL framework provides something similar to this pure O/RM code to manage a database, we have to inform you that this is only partially true. In fact, the LINQ to SQL framework provides basic O/RM functionality. As declared by Luca Bolognese (one of the creators of the LINQ to SQL framework) during a conference, LINQ to SQL has been provided with some basic O/RM features in the first release, and it will be expanded in future releases, thanks to feedback from the developers. In this chapter, you will see these features in action, and you will be able to use them in your projects. At the end of the chapter, you will have an idea of what the LINQ to SQL framework provides and what it doesn’t yet offer.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics