Writing persistent layers using DLinq and Hibernate

By: John Charles (Juan Carlos) Olamendy Turruellas

Introduction

In this article, I will illustrate the principles, techniques and tools to develop applications using Linq to SQL and Hibernate technologies. As we know, ADO.NET is the standard data access library in Microsoft.NET and Linq to SQL is an extension to .NET languages to add native support for accessing and updating data by using traditional object operations following the object-oriented paradigm. Hibernate is a technology that enables the developer to manage (access and update) relational data in Java platform by defining persistent entities and mapping them to relational database tables by executing object operations.

ORM State of the art

Developing software is a complicated task and most significant development projects involve a relational database where CRUD operations are the corner stone of the solution. There are a lot of monolithic applications where the data access code and the data presentation are in one place. This is very difficult to develop and maintain, because the developer must understand how to write SQL code.

Then a new approach and several technologies supporting it come up. Object-relational mapping (ORM) is an approach to automatically connect the domain model of your application to a relational database system by using metadata as the descriptor of the object and data.

ORM solves the object-relational impendence mismatch problem (tabular data versus domain model) by replacing direct persistence related to the database access with high level object operations.

It’s very important to remark that there are many benefits to using ORM other than data access techniques, as shown:

  • ORM automatically mapping the object model to the back-end relational data using metadata. This simplifies the development and maintenance effort.
  • It requires a few lines of code to implement the data management logic by creating dynamic queries.
  • ORM technologies provide transparent caching objects, thus improving the overall performance of the system.

Getting started with Hibernate the examples

In order to illustrate the ORM concepts using Hibernate technology, we’re going to connect to a PostgreSQL database and create a table to persist the state of the Department business entity (see Listing 1).

CREATE TABLE department(

deptno integer NOT NULL,

deptname character varying(255),

“location” character varying(255),

CONSTRAINT department_pkey PRIMARY KEY (deptno)

)

WITH (OIDS=FALSE);

ALTER TABLE department OWNER TO john;

Listing 1

For the Hibernate code examples, we’re going to create a standalone console application in Java using NetBeans 6.5.

First step is to add a project using NetBeans 6.5 (see Figure 1).

Figure 1

Then set a name and the location for the project (see Figure 2).

Figure 2

In order to support Hibernate in the project, we need to add the Hibernate library to the project. Open the Projects windows and right-click on the Libraries node of the project and select “Add Library” option from the context menu. Then browse to the Hibernate library in the “Add Library” dialog box (see Figure 3).

Figure 3

The implementation strategy for this example is by following the top-down approach. That is, we’re going to configure the underlying Hibernate files for the connection to the back-end database system in this case the PostgreSQL database where the department table resides, then we’re going to generate the business entities’ classes and their underlying mapping using the NetBeans tools, as well as an utility class for doing common operations in Hibernate such as configuration loading and session creation.

Now let’s create the Hibernate configuration file using the wizards in NetBeans. The Hibernate configuration file (hibernate.cfg.xml) contains information about the database connection, resource mappings and other connections properties. This file is used by the Configuration class’ instances when to invoke the configure method to load this information for Hibernate to use.

Select the File | New File menu option. In the “New File” wizard, go to the Hibernate categories and select the “Hibernate Configuration File” in the “File Types” pane, and click on Next button (see Figure 4).

Figure 4

In the next page, leave it as shown in Figure 5 and click on the Next button.

Figure 5

In the “Select a Data Source” page, select a register data source to the department table in the PostgreSQL database and click on the Finish button.

The IDE creates the configuration file at the root of the context classpath of the application (in the Files window, WEB-INF/classes). In the Projects window the file is located in the <default package> source package. The configuration file contains information about a single database. If you plan to connect to multiple databases, you can create multiple configuration files in the project, one for each database servers, but by default the helper utility class will use the hibernate.cfg.xml file located in the root location.

If you expand the Libraries node in the Projects window you can see that the IDE added the required Hibernate JAR files and the PostgreSQL connector JAR (see Figure 6).

Figure 6

To use this Hibernate configuration we can create a helper class that handles startup and that accesses Hibernate’s SessionFactory class to obtain a Session object. The instances of this class call configure method, which loads the hibernate.cfg.xml configuration file from the classpath and then builds the SessionFactory to obtain the Session object.

Select the File | New File menu option. In the “New File” wizard, go to the Hibernate categories and select the “HibernateUtil.java” in the “File Types” pane, and click on Next button (see Figure 7).

Figure 7

In the “Name and Location” page, let’s enter the values as shown in the Figure 8.

Figure 8

For the examples, I’m going to use the POJO Department.java class definition. The “Hibernate Mapping Files and POJOs from a Database” wizard generates files based on the department table in the back-end database. When you use the wizard you select all the tables for which you want POJOs and mapping files and the IDE then generates the files for you based on the database tables and adds the mapping entries to hibernate.cfg.xml configuration file.

Select the File | New File menu option. In the “New File” wizard, go to the Hibernate categories and select the “Hibernate Mapping Files and POJOs from a Database” in the “File Types” pane, and click on Next button (see Figure 9).

Figure 9

In the “Name and Location” page, leave the data as shown in the Figure 10.

Figure 10

Add from the Available Table panel to Selected Table panel, the tables to be mapped to business entities  and click on the Next button(see Figure 11).

Figure 11

In the “Generation of Code” page, leave the data as shown in the Figure 12 and click on the Finish button.

Figure 12

Mapping classes to database tables can be accomplished through XML configuration file or using Java annotation. The most common used configuration file in Hibernate is the XML configuration file, and from this file Hibernate can generate skeletal source code for the persistence classes. The one-to-many and many-to-many relationships are also generated.

Let’s see the generated mapping (see Listing 2) and business entities (see Listing 3) for our example.

<?xml version=”1.0″?><!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”

http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd”&gt;

<!– Generated Jul 3, 2009 2:49:24 PM by Hibernate Tools 3.2.1.GA –>

<hibernate-mapping>

<class name=”hibernatetestjavaapp.Department” table=”department” schema=”human_resources”>

<id name=”deptno” type=”int”>

<column name=”deptno” />

<generator />

</id>

<property name=”deptname” type=”string”>

<column name=”deptname” length=”50″ not-null=”true” />

</property>

<property name=”location” type=”string”>

<column name=”location” length=”50″ />

</property>

</class>

</hibernate-mapping>

Listing 2

package hibernatetestjavaapp;// Generated Jul 3, 2009 2:49:18 PM by Hibernate Tools 3.2.1.GA

/**

* Department generated by hbm2java

*/

public class Department  implements java.io.Serializable {

private int deptno;

private String deptname;

private String location;

public Department() {

}

public Department(int deptno, String deptname) {

this.deptno = deptno;

this.deptname = deptname;

}

public Department(int deptno, String deptname, String location) {

this.deptno = deptno;

this.deptname = deptname;

this.location = location;

}

public int getDeptno() {

return this.deptno;

}

public void setDeptno(int deptno) {

this.deptno = deptno;

}

public String getDeptname() {

return this.deptname;

}

public void setDeptname(String deptname) {

this.deptname = deptname;

}

public String getLocation() {

return this.location;

}

public void setLocation(String location) {

this.location = location;

}

}

Listing 3

Now we’re ready to manage the department data using Hibernate from the main application. We’re going to follow a template for the creation of the queries (see Listing 4).

/** To change this template, choose Tools | Templates

* and open the template in the editor.

*/

package hibernatetestjavaapp;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

/**

*

* @author root

*/

/*

* To change this template, choose Tools | Templates

* and open the template in the editor.

*/

package hibernatetestjavaapp;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

/**

*

* @author root

*/

public class Main {

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

SessionFactory factory = HibernateUtil.getSessionFactory();

Session session = factory.openSession();

try

{

session.getTransaction().begin();

//Here comes the Hibernate code to manage the data

session.getTransaction().commit();

}

catch (Throwable e)

{

if (session.getTransaction().isActive())

{

session.getTransaction().rollback();

}

System.out.println(e.getMessage());

}

session.close();

}

}

Listing 4

As you can see the HibernateUtil class invokes the getSessionFactory to create a new session factory and from this point, a new session is created. The SessionFactory is a heavyweight object, and there would be normally a single instance in the application. The Session has amount in common with JDBC Connection object, because in order to manipulate an object in a database system, we need to open a session first and then execute the underlying operations. The Session instances also manage caching functionality, lazy loading of objects, and watching for changes of the associated objects. The Transaction instances in Hibernate have the same responsibilities than their counterpart JDBC transactions. It’s required Hibernate operations to be enclosed in transactions.

Now let’s start to manipulate the department data using Session object in order to illustrate how to create, update, read and delete (CRUD) operations.

The create operation is implemented very simple. Just create an instance of the Department class and invoke the save method of the Session instance (see Listing 5). Creating an instance of a class does not automatically persist the object in the database. You need to explicitly save the object with a valid Hibernate session by using the active session’s save method.

If you are not able to determine the state of the object, then the active session’s saveOrUpdate method is used. In this case, Hibernate uses the identifier of the object to determine whether insert a new row or update an existing one.

/** To change this template, choose Tools | Templates

* and open the template in the editor.

*/

package hibernatetestjavaapp;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

/**

*

* @author root

*/

public class Main {

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

SessionFactory factory = HibernateUtil.getSessionFactory();

Session session = factory.openSession();

try

{

session.getTransaction().begin();

//Here comes the Hibernate code to manage the data

Department objDepartment = new Department();

objDepartment.setDeptno(120);

objDepartment.setDeptname(“Human Resources”);

objDepartment.setLocation(“Philadelphia”);

session.save(objDepartment);

session.getTransaction().commit();

}

catch (Throwable e)

{

if (session.getTransaction().isActive())

{

session.getTransaction().rollback();

}

System.out.println(e.getMessage());

}

session.close();

}

}

Listing 5

In order to retrieve a department object from the database, we need to use HQL. HQL is conceptually similar to SQL with the exception that the names in the query refers to the names used in the mapping files. Let’s supposed we want to get the last created entity, so we need to create a HQL query where the identifier of this entity (in this case, the department number) is equal to a value.

In our example, we need to create a Query object (similar in functionality to PreparedStatement in JDBC), populate the parameter of the query with the appropriate value and display the result. We use the uniqueResult method because it is guaranteed to throw an exception if somehow the query identifies more than one department (see Listing 6).

There is another method to get data from the underlying database system using the active session’s load and get methods. These methods are straightforward taking as arguments the class type of the object to load and its identifier. The load method also takes a lock mode as argument to specify whether Hibernate should look into the cache for the object, and which database lock model to follow.

/** To change this template, choose Tools | Templates

* and open the template in the editor.

*/

package hibernatetestjavaapp;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

/**

*

* @author root

*/

public class Main {

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

SessionFactory factory = HibernateUtil.getSessionFactory();

Session session = factory.openSession();

try

{

session.getTransaction().begin();

//Here comes the Hibernate code to manage the data

Query objQuery = session.createQuery(“from Department where deptno=:deptno”);

objQuery.setInteger(“deptno”,120);

Department objDepartment = (Department)objQuery.uniqueResult();

System.out.println(“Department data. Name=”+objDepartment.getDeptname()+”, Location=”+objDepartment.getLocation());

session.getTransaction().commit();

}

catch (Throwable e)

{

if (session.getTransaction().isActive())

{

session.getTransaction().rollback();

}

System.out.println(e.getMessage());

}

session.close();

}

}

Listing 6

In the same way, we can create a query to get a list of departments which are located in New York by using the follow query “Query objQuery = session.createQuery(“from Department where location=:location”)” and the complete code is shown in the Listing 7.

/** To change this template, choose Tools | Templates

* and open the template in the editor.

*/

package hibernatetestjavaapp;

import java.util.List;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

/**

*

* @author root

*/

public class Main {

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

SessionFactory factory = HibernateUtil.getSessionFactory();

Session session = factory.openSession();

try

{

session.getTransaction().begin();

//Here comes the Hibernate code to manage the data

Query objQuery = session.createQuery(“from Department where location=:location”);

objQuery.setString(“location”,”New York”);

List<Department> arrDepartment = (List<Department>)objQuery.list();

for(Department objDepartment:arrDepartment)

{

System.out.println(“Department data. Name=”+objDepartment.getDeptname()+”, Location=”+objDepartment.getLocation());

}

session.getTransaction().commit();

}

catch (Throwable e)

{

if (session.getTransaction().isActive())

{

session.getTransaction().rollback();

}

System.out.println(e.getMessage());

}

session.close();

}

}

Listing 7

Now let’s suppose we want to update the data of a department entity (see Listing 8).

/** To change this template, choose Tools | Templates

* and open the template in the editor.

*/

package hibernatetestjavaapp;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

/**

*

* @author root

*/

public class Main {

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

SessionFactory factory = HibernateUtil.getSessionFactory();

Session session = factory.openSession();

try

{

session.getTransaction().begin();

//Here comes the Hibernate code to manage the data

Query objQuery = session.createQuery(“from Department where deptno=:deptno”);

objQuery.setInteger(“deptno”, 120);

Department objDepartment = (Department)objQuery.uniqueResult();

objDepartment.setLocation(“Detroit”);

session.getTransaction().commit();

}

catch (Throwable e)

{

if (session.getTransaction().isActive())

{

session.getTransaction().rollback();

}

System.out.println(e.getMessage());

}

session.close();

}

}

Listing 8

And finally to complete the CRUD operations, let’s explain how to delete this department entity (see Listing 9).

/** To change this template, choose Tools | Templates

* and open the template in the editor.

*/

package hibernatetestjavaapp;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

/**

*

* @author root

*/

public class Main {

/**

* @param args the command line arguments

*/

public static void main(String[] args)

{

SessionFactory factory = HibernateUtil.getSessionFactory();

Session session = factory.openSession();

try

{

session.getTransaction().begin();

//Here comes the Hibernate code to manage the data

Query objQuery = session.createQuery(“from Department where deptno=:deptno”);

objQuery.setInteger(“deptno”, 120);

Department objDepartment = (Department)objQuery.uniqueResult();

session.delete(objDepartment);

session.getTransaction().commit();

}

catch (Throwable e)

{

if (session.getTransaction().isActive())

{

session.getTransaction().rollback();

}

System.out.println(e.getMessage());

}

session.close();

}

}

Listing 9

It’s remarkable to say that the examples in the previous listings are based on persistent objects associated to an active connection. But in Hibernate, there the concept Detach objects which are objects with a representation (using mapping) in the databases, but changes to their states are not reflected in the database because they are not connected to the database. A detached object can be created by closing a session that it was associated with, or by evicting it from the session with a call to the active session’s evict method. In order to persist the changes of their states, the application must re-attach it to a valid Hibernate session by calling the load, refresh, merge, update and save methods on the new session with a reference to the detached object.

Paging in Hibernate is also straightforward. There are two methods on the Query interface for implementing paging, setFirstResult and setMaxResult methods. The setFirstResult method takes an integer that represents the first row in the result set starting with 0. The setMaxResults method specifies how many rows must be retrieved by the operation (see Listing 10).

Query qryDepartments = session.createQuery(“from Department”);qryDepartments.setFirstResult(1);

qryDepartments.setMaxResults(10);

Listing 10

In order to sort your query results, you will need to use the sort by clause. You can order the result by either ascending (asc) or descending (desc) (see Listing 11).

Query objQuery = session.createQuery(“from Department d where d.location=:location order by d.deptno desc”);

Listing 11

Hibernate also supports join operations. The operations supported by Hibernate are inner join, cross join, left outer join, right outer join, and full outer join.

Batch updates on Hibernate are very straightforward by using the executeUpdate method for executing SQL statements. This method returns an integer value that contains the number of rows affected by the operation. Let’s supposed that we want to raise 10% the salary to employees using a batch update, then we execute the following code (see Listing 12).

Query qryRaiseSalary = session.createQuery(“update Employee set salary=salary*1.1”);int nRowCount = qryRaiseSalary.executeUpdate();

Listing 12

Getting started with Linq to SQL the examples

In order to illustrate the ORM concepts using Linq to SQL technology, we’re going to create a console project in Microsoft Visual Studio.NET 2008. As the back-end database, we’re going to use the AdventureWorks database shipped with Microsoft SQL Server 2005/2008 as well as the Production.Product. We’re going to explain the operations on the data source using Linq to SQL similar as we do in the Hibernate cases.

Next step is to add a “Linq to SQL” artifact to the project by selecting the Project | Add New Item menu option (see Figure 13).

Figure 13

Modeling the business entities

After that, the Object Relational (O/R) designer is launched which enables to model the classes that represent the underlying data model and the methods for each stored procedure in the back-end database system. It will also create a strongly typed DataContext class (the ProductionDataContext class).

Unlike the DataSet/TableAdapter approach, when we’re defining the entity classes in the designer, we don’t have to specify the SQL queries; instead we have to focus on defining the entity classes and the mapping of these classes to the underlying data model. SQL to Linq framework will generate the appropriate SQL statements at runtime when you interact and use the business entities.

In order to create the business entities, the easy way is to open a database connection in the Server Explorer windows, select the tables and views you want to model and map from it, and drag and drop them onto the designer surface.

In this case, drag and drop the Production.Product table on the designer surface (see Figure 14). After that you can see the Product business entity.

Figure 14

Let’s see a part of the generated Product class mapping back to the Production.Product table on the data source by exposing properties that represent the fields and the relationships (see Listing 13).

      [Table(Name=”Production.Product”)]public partial class Product : INotifyPropertyChanging, INotifyPropertyChanged

{

private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

private int _ProductID;

private string _Name;

private string _ProductNumber;

private bool _MakeFlag;

private bool _FinishedGoodsFlag;

private string _Color;

private short _SafetyStockLevel;

private short _ReorderPoint;

private decimal _StandardCost;

private decimal _ListPrice;

private string _Size;

private string _SizeUnitMeasureCode;

private string _WeightUnitMeasureCode;

private System.Nullable<decimal> _Weight;

private int _DaysToManufacture;

private string _ProductLine;

private string _Class;

private string _Style;

private System.Nullable<int> _ProductSubcategoryID;

private System.Nullable<int> _ProductModelID;

private System.DateTime _SellStartDate;

private System.Nullable<System.DateTime> _SellEndDate;

private System.Nullable<System.DateTime> _DiscontinuedDate;

private System.Guid _rowguid;

private System.DateTime _ModifiedDate;

#region Extensibility Method Definitions

partial void OnLoaded();

partial void OnValidate(System.Data.Linq.ChangeAction action);

partial void OnCreated();

partial void OnProductIDChanging(int value);

partial void OnProductIDChanged();

partial void OnNameChanging(string value);

partial void OnNameChanged();

partial void OnProductNumberChanging(string value);

partial void OnProductNumberChanged();

partial void OnMakeFlagChanging(bool value);

partial void OnMakeFlagChanged();

partial void OnFinishedGoodsFlagChanging(bool value);

partial void OnFinishedGoodsFlagChanged();

partial void OnColorChanging(string value);

partial void OnColorChanged();

partial void OnSafetyStockLevelChanging(short value);

partial void OnSafetyStockLevelChanged();

partial void OnReorderPointChanging(short value);

partial void OnReorderPointChanged();

partial void OnStandardCostChanging(decimal value);

partial void OnStandardCostChanged();

partial void OnListPriceChanging(decimal value);

partial void OnListPriceChanged();

partial void OnSizeChanging(string value);

partial void OnSizeChanged();

partial void OnSizeUnitMeasureCodeChanging(string value);

partial void OnSizeUnitMeasureCodeChanged();

partial void OnWeightUnitMeasureCodeChanging(string value);

partial void OnWeightUnitMeasureCodeChanged();

partial void OnWeightChanging(System.Nullable<decimal> value);

partial void OnWeightChanged();

partial void OnDaysToManufactureChanging(int value);

partial void OnDaysToManufactureChanged();

partial void OnProductLineChanging(string value);

partial void OnProductLineChanged();

partial void OnClassChanging(string value);

partial void OnClassChanged();

partial void OnStyleChanging(string value);

partial void OnStyleChanged();

partial void OnProductSubcategoryIDChanging(System.Nullable<int> value);

partial void OnProductSubcategoryIDChanged();

partial void OnProductModelIDChanging(System.Nullable<int> value);

partial void OnProductModelIDChanged();

partial void OnSellStartDateChanging(System.DateTime value);

partial void OnSellStartDateChanged();

partial void OnSellEndDateChanging(System.Nullable<System.DateTime> value);

partial void OnSellEndDateChanged();

partial void OnDiscontinuedDateChanging(System.Nullable<System.DateTime> value);

partial void OnDiscontinuedDateChanged();

partial void OnrowguidChanging(System.Guid value);

partial void OnrowguidChanged();

partial void OnModifiedDateChanging(System.DateTime value);

partial void OnModifiedDateChanged();

#endregion

public Product()

{

OnCreated();

}

[Column(Storage=”_ProductID”, AutoSync=AutoSync.OnInsert, DbType=”Int NOT NULL IDENTITY”, IsPrimaryKey=true, IsDbGenerated=true)]

public int ProductID

{

get

{

return this._ProductID;

}

set

{

if ((this._ProductID != value))

{

this.OnProductIDChanging(value);

this.SendPropertyChanging();

this._ProductID = value;

this.SendPropertyChanged(“ProductID”);

this.OnProductIDChanged();

}

}

}

[Column(Storage=”_Name”, DbType=”NVarChar(50) NOT NULL”, CanBeNull=false)]

public string Name

{

get

{

return this._Name;

}

set

{

if ((this._Name != value))

{

this.OnNameChanging(value);

this.SendPropertyChanging();

this._Name = value;

this.SendPropertyChanged(“Name”);

this.OnNameChanged();

}

}

}

……

Listing 13

You see that the Product class is decorated with the Table attribute pointing to the underlying database table. As well as this class inherits from INotifyPropertyChanging and INotifyPropertyChanged interfaces which are used by the DataContext based class to track when data is dirty or need to be refreshed. Then every attribute of the Product class is decorated by the Column attribute. The Column attribute establishes the primary key, if it’s a surrogate key (auto-generated), as well as the data type and underlying information such as nullability and other constraint checks.

The other important class is the DataContext based class which is the core channel used to communicate with the data source. It contains the properties and methods for interacting with the database tables added on the designer (see Listing 14). This class is decorated with the System.Data.Linq.DatabaseAttributte attribute to establish which database to connect on the SQL server.

      [System.Data.Linq.Mapping.DatabaseAttribute(Name=”AdventureWorks”)]public partial class ProductionDataContext : System.Data.Linq.DataContext

{

private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();

#region Extensibility Method Definitions

partial void OnCreated();

partial void InsertProduct(Product instance);

partial void UpdateProduct(Product instance);

partial void DeleteProduct(Product instance);

#endregion

public ProductionDataContext() :

base(global::LinqToSqlTestConsApp.Properties.Settings.Default.AdventureWorksConnectionString, mappingSource)

{

OnCreated();

}

public ProductionDataContext(string connection) :

base(connection, mappingSource)

{

OnCreated();

}

public ProductionDataContext(System.Data.IDbConnection connection) :

base(connection, mappingSource)

{

OnCreated();

}

public ProductionDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) :

base(connection, mappingSource)

{

OnCreated();

}

public ProductionDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :

base(connection, mappingSource)

{

OnCreated();

}

public System.Data.Linq.Table<Product> Products

{

get

{

return this.GetTable<Product>();

}

}

}

Listing 14

The final aspect is the DBML file which contains the connection string, the provider information and the metadata mappings to the Production.Product table (see Listing 15).

<?xml version=”1.0″ encoding=”utf-8″?><Database Name=”AdventureWorks” xmlns=”http://schemas.microsoft.com/linqtosql/dbml/2007″&gt;

<Connection Mode=”AppSettings” ConnectionString=”Data Source=remote_server;Initial Catalog=AdventureWorks;Integrated Security=True” SettingsObjectName=”LinqToSqlTestConsApp.Properties.Settings” SettingsPropertyName=”AdventureWorksConnectionString” Provider=”System.Data.SqlClient” />

<Table Name=”Production.Product” Member=”Products”>

<Type Name=”Product”>

<Column Name=”ProductID” Type=”System.Int32″ DbType=”Int NOT NULL IDENTITY” IsPrimaryKey=”true” IsDbGenerated=”true” CanBeNull=”false” />

<Column Name=”Name” Type=”System.String” DbType=”NVarChar(50) NOT NULL” CanBeNull=”false” />

<Column Name=”ProductNumber” Type=”System.String” DbType=”NVarChar(25) NOT NULL” CanBeNull=”false” />

<Column Name=”MakeFlag” Type=”System.Boolean” DbType=”Bit NOT NULL” CanBeNull=”false” />

<Column Name=”FinishedGoodsFlag” Type=”System.Boolean” DbType=”Bit NOT NULL” CanBeNull=”false” />

<Column Name=”Color” Type=”System.String” DbType=”NVarChar(15)” CanBeNull=”true” />

<Column Name=”SafetyStockLevel” Type=”System.Int16″ DbType=”SmallInt NOT NULL” CanBeNull=”false” />

<Column Name=”ReorderPoint” Type=”System.Int16″ DbType=”SmallInt NOT NULL” CanBeNull=”false” />

<Column Name=”StandardCost” Type=”System.Decimal” DbType=”Money NOT NULL” CanBeNull=”false” />

<Column Name=”ListPrice” Type=”System.Decimal” DbType=”Money NOT NULL” CanBeNull=”false” />

<Column Name=”Size” Type=”System.String” DbType=”NVarChar(5)” CanBeNull=”true” />

<Column Name=”SizeUnitMeasureCode” Type=”System.String” DbType=”NChar(3)” CanBeNull=”true” />

<Column Name=”WeightUnitMeasureCode” Type=”System.String” DbType=”NChar(3)” CanBeNull=”true” />

<Column Name=”Weight” Type=”System.Decimal” DbType=”Decimal(8,2)” CanBeNull=”true” />

<Column Name=”DaysToManufacture” Type=”System.Int32″ DbType=”Int NOT NULL” CanBeNull=”false” />

<Column Name=”ProductLine” Type=”System.String” DbType=”NChar(2)” CanBeNull=”true” />

<Column Name=”Class” Type=”System.String” DbType=”NChar(2)” CanBeNull=”true” />

<Column Name=”Style” Type=”System.String” DbType=”NChar(2)” CanBeNull=”true” />

<Column Name=”ProductSubcategoryID” Type=”System.Int32″ DbType=”Int” CanBeNull=”true” />

<Column Name=”ProductModelID” Type=”System.Int32″ DbType=”Int” CanBeNull=”true” />

<Column Name=”SellStartDate” Type=”System.DateTime” DbType=”DateTime NOT NULL” CanBeNull=”false” />

<Column Name=”SellEndDate” Type=”System.DateTime” DbType=”DateTime” CanBeNull=”true” />

<Column Name=”DiscontinuedDate” Type=”System.DateTime” DbType=”DateTime” CanBeNull=”true” />

<Column Name=”rowguid” Type=”System.Guid” DbType=”UniqueIdentifier NOT NULL” CanBeNull=”false” />

<Column Name=”ModifiedDate” Type=”System.DateTime” DbType=”DateTime NOT NULL” CanBeNull=”false” />

</Type>

</Table>

</Database>

Listing 15

Linq to SQL enables modeling stored procedures as methods of the DataContext class. Let’s suppose we need to return a list of a product based on a given product subcategory id (see Listing 15).

create procedure spSelectProductBySubcategory@pSubcategoryId int

as

begin

select *

from Production.Product

where ProductSubcategoryID=@pSubcategoryId;

end;

go

Listing 16

Now we can use the Server Explorer windows to drag and drop this stored procedure on the in the right pane of the designer surface (see Figure 3).

Figure 15

Executing CRUD operations using Linq to SQL

Now that we have defined our object model representing the data model, we are ready to execute CRUD (create, read, update, delete) operations over the relational data schema through the object model in C#.

The first example is to display a list of the products in the database (see Listing 17). The first step is to create a ProductionDataContext instance and then develop a Linq query similar to SQL to return the list of products based on the product makeflag equals to true. In this case the where clause enables filtering features on the query. Notice that the filter expression follows the C# syntax rather the SQL syntax, in this case, the comparison uses the “==” logical operator rather than the “=” logical operator. The result is stored in the objResultset object which is defined by using the var keyword to define a strongly type compile time variable reference by inferring the type at compile time. You can also see that a relational projection operation is achieved by using the new features of C# language such as anonymous type and object initialization in order to return a subset of the fields. Anonymous types enable defining an inline class without having to explicitly declare it.

using System;using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace LinqToSqlTestConsApp

{

public class Program

{

static void Main(string[] args)

{

ProductionDataContext objDataContext = new ProductionDataContext();

var objResultset = from objProduct in objDataContext.Products

where objProduct.MakeFlag == true

select new { ProductId= objProduct.ProductID, Number = objProduct.ProductNumber, Name= objProduct.Name, Color = objProduct.Color };

foreach (var objProduct in objResultset)

{

System.Console.WriteLine(“ProductId={0}, Product Number={1}, Name={2}, Color={3}”, objProduct.ProductId, objProduct.Number, objProduct.Name, objProduct.Color);

}

System.Console.WriteLine(“Press any key to finish …”);

System.Console.ReadKey();

}

}

}

Listing 17

Now let’s create a new product item (see Listing 19).

using System;using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace LinqToSqlTestConsApp

{

public class Program

{

static void Main(string[] args)

{

ProductionDataContext objDataContext = new ProductionDataContext();

Product objProduct = new Product();

objProduct.Name = “Ad Race 11”;

objProduct.ProductNumber = “AR-53323”;

objProduct.MakeFlag = true;

objProduct.FinishedGoodsFlag = true;

objProduct.SafetyStockLevel = 100;

objProduct.ReorderPoint = 750;

objProduct.StandardCost = 0;

objProduct.DaysToManufacture = 0;

objProduct.ProductSubcategoryID = 22;

objProduct.ProductModelID = 31;

objProduct.SellStartDate = DateTime.Now;

objProduct.ModifiedDate = DateTime.Now;

objDataContext.Products.InsertOnSubmit(objProduct);

objDataContext.SubmitChanges();

System.Console.WriteLine(“Press any key to finish …”);

System.Console.ReadKey();

}

}

}

Listing 19

When you execute the SubmitChanges method of the DataContext, then all the updates to the data are wrapped in a transaction, so the underlying relational database will never be in an inconsistent state after updating the changes.

If no transaction is already in the scope, then the SubmitChanges method will automatically start a database transaction. You can also define your transaction scope using the TransactionScope object. This makes very easy to integrate your transactional code with existing one.

In the following step, we’re going to retrieve the newly created product item and update its Name field value (see Listing 20).

using System;using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace LinqToSqlTestConsApp

{

public class Program

{

static void Main(string[] args)

{

ProductionDataContext objDataContext = new ProductionDataContext();

var objProduct = (from objProd in objDataContext.Products

where objProd.Name == “Ad Race 11”

select objProd).First();

objProduct.Name = “Ad Race 111”;

objDataContext.SubmitChanges();

System.Console.WriteLine(“Press any key to finish …”);

System.Console.ReadKey();

}

}

}

Listing 20

The last CRUD operation to test is the delete operation. Now we’re going to delete the created product item (see Listing 21).

using System;using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace LinqToSqlTestConsApp

{

public class Program

{

static void Main(string[] args)

{

ProductionDataContext objDataContext = new ProductionDataContext();

var objProduct = (from objProd in objDataContext.Products

where objProd.Name == “Ad Race 111”

select objProd).First();

objDataContext.Products.DeleteOnSubmit(objProduct);

objDataContext.SubmitChanges();

System.Console.WriteLine(“Press any key to finish …”);

System.Console.ReadKey();

}

}

}

Listing 21

Now let’s call for the stored procedure defined in the Listing 1 (see Listing 6).

ProductionDataContext objDataContext = new ProductionDataContext();var arrProduct = objDataContext.spSelectProductBySubcategory(1);

Listing 22

In order to implement database paging in Linq to SQL, we need to use the Skip and Take methods (see Listing 7).

ProductionDataContext objDataContext = new ProductionDataContext();var objProductSubcategory = (from objProdSubc in objDataContext.ProductSubcategories

where objProdSubc.ProductSubcategoryID == 2

select objProdSubc).Skip(200).Take(10);

Listing 23

Conclusion

In this article, I’ve covered the principles, techniques and tools to write persistent layers using DLinq and Hibernate technologies.

Advertisements

2 thoughts on “

  1. Pingback: MVP Factor

  2. Pingback: Writing persistent layers using DLink and Hibernate « MSExpertos

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s