DLINQ Integration

text zoom

Previous: ASP.NET And LINQ Contd.. Writing Queries In DLINQ :Next

DLINQ Integration

DLINQ is a smart data access layer framework (component of the ADO.NET family), with an advanced runtime infrastructure and design time tools for managing data (queries and data manipulations) from a relational db as an object.

DLinq defines two core attributes, [Table] and [Column], which indicate which CLR types and properties correspond to external SQL data. The [Table] attribute can be applied to a class and associates the CLR type with a named SQL table or view. The [Column] attribute can be applied to any field or property and associates the member with a named SQL column. Both attributes are parameterized to allow SQL-specific metadata to be retained.

The usage of this framework is very easy: The basic class in DLINQ is call "Entity class", each class represent a single database table. The properties in this class will represent the table columns.

The API is provided via System.Linq and System.Data.Linq namespaces.

o System.Linq.Queryable defines Enumerable like methods that build Expressions that here will be executed by SQL specific engines

o System.Data.Linq defines memory objects needed to map database objects such as tables

We will create new classes which are called "Employee" and "Department". We have to declare the classes as a DLINQ table by adding the "Table" attribute (This attribute found in the System.Data.Linq namespace), and declare the class properties as table columns by adding the Column attribute.

Our classes should look like this:

[Table(Name="Employees")]

class Employee

{

[Column(IsPrimaryKey = true)]

public int Id;

[Column]

public string Name;

}

[Table(Name="Employees")]

class Department

{

[Column(IsPrimaryKey = true)]

public int Id;

[Column]

public string Name;

}

And now let add the relationship between those tables

[Table(Name="Employees")]

class Employee

{

… private EntityRef<Department> _Department;

[Association(ThisKey = "DepartmentCode", OtherKey="Id")]

public Department Department

{

get

{

return this._Department.Entity;

}

set

{

return this._Department.Entity = value;

}

.... [Table(Name="Employees")]

class Department

{

… private EntitySet<Employee> _Employees;

[Association(ThisKey="Id", OtherKey="DepartmentCode")]

public EntitySet<Employee> Employees

{

get

{

return this._Employees;

}

set

{

this._Employees.Assign(value);

}

Note:

In the father's table of the relationship (In department table) we used the EntitySet class to hold a list of "sons". In the son's table we used the EntityRef struct in order to give us the ability to see the father object when we holding the son (if we want to get the department name for example when we holding an employee object).

Before we are going to use our new class lets talk about the DataContext engine.

The DataContext is the smart brain which works behind the scene. You just need to initialize it with the connection and use his sophisticated functionality. This engine is responsible to translate the queries and manipulation that you made on the object to a SQL statements (And in a case there is a return value it will create an appropriate object with this data). In addition, it implements the SQO (Standard Query Operators) and gives us the ability to use the standard familiar SQL syntax.

Now after we know what DataContxt is good for, let's use it. In the following example I'll create an instance of the DataContext class (Initialized by the connection string). I'll request from it to retrieve the object that manage my employees table. After I'll get that object I'll use some SQL to retrieve all employees that their names start with the string "Y". Then I'll print the results to console.

string connectioString = "...";

DataContext myDb = new DataContext(connectioString);

Table<Employee> employees = myDb.GetTable<Employee>();

var query = from c in employees where c.Name.StartsWith("Y") select c;

foreach (var var in query)

{

Console.Write(var.Name);

}