DLinq Quick Start

Introduction

I am going to show the steps to start using DLinq in a project. Visual Studio 2008 will have LINQ support built in. To use it with Visual Studio 2005, we need to get LINQ installer downloaded and installed.

Creating a New Project

After we install the Linq setup, a new project type is added to Visual Studio 2005 project types tree. We now add a LINQ Console Application from File > New Project menu.

Now we create a table in the database.

CREATE TABLE USERS
(
LogonID nVARCHAR(20) NOT NULL PRIMARY KEY,
Name NVARCHAR(50),
Password NVARCHAR(30),
EmailAddress NVARCHAR(50),
LastLogon DateTime
);

And now add our mapping object class User:

[Table(Name="users")]
public class User
{
    private string logonID;
    private string name;
    private string password;
    private string emailAddress;
    private DateTime lastLogon;
    public User()
    {
    }
    [Column (Id=true, Storage = "logonID")]
    public string LogonID
    {
        get { return logonID; }
        set { logonID = value; }
    }
    [Column(Storage = "name")]
    public string Name
    {
        get { return name; }
        set { name = value; }
    }
    [Column(Storage = "password")]
    public string Password
    {
        get { return password; }
        set { password = value; }
    }
    [Column(Storage = "emailAddress")]
    public string EmailAddress
    {
        get { return emailAddress; }
        set { emailAddress = value; }
    }
    [Column(Storage = "lastLogon")]
    public DateTime LastLogon
    {
        get { return lastLogon; }
        set { lastLogon = value; }
    }
}

We assign table name with each class with Table attribute. Here we use [Table(Name="users")] to assign the User class to "users" table. We then add Column attribute with parameters. We can assign Storage parameter to the actual class private field. We have boolean Id parameter to assign a property as ID field.

Loading Data

Now we need to create a DataContext object.

DataContext db = new DataContext(
    @"Data Source=localhost\sqlexpress;Initial Catalog=master;Integrated Security=True"
    ); 

We provide the database connection string as the constructor parameter. Now we create a table and load all users from database.

Table<User> userTable = db.GetTable<User>();
db.Log = Console.Out;
var users = from c in userTable 
    select c;
foreach (User u in users) { 
    Console.WriteLine(u.LogonID);
}

All right, we have made it work. Now let's add some functionalities.

Insert Operation

To insert, we create a new instance of our mapping object and add it to the table:

User u1= new User();
u1.LogonID="manir";
u1.Name="Maruf Maniruzzaman";
u1.Password="1234";
u1.LastLogon=DateTime.Now;
userTable.Add(u1);
db.SubmitChanges();

We call SubmitChanges to apply the changes.

Modify Operation

First, we load a single user.

User u2 = userTable.Single(u => u.LogonID == "manir"); 

This is equivalent to:

select * from users u where u.LogonID="manir" limit 1

It returns a single row. We now change some property of the object and update the row in database.

u2.Password="5678";
db.SubmitChanges();

Yes, u2 is a live object and if we change any property of it and submit, the database row is changed accordingly.

Delete Operation

Here we load the object from table and call Remove method of Table to delete.

User u3 = userTable.Single(u => u.LogonID == "maruf");
userTable.Remove(u3);
db.SubmitChanges();    

One thing should be noted that we can do multiple operations and call the SubmitChanges to DataContext object once to apply all changes. So, next comes transaction management.

Database Transaction

We want the previous operations in a single atomic step. We need to manage the transaction. DLINQ makes it very simple. Just:

db.Connection.Open();
db.LocalTransaction = db.Connection.BeginTransaction();
try
{
    User u1= new User();
    u1.LogonID="manir";
    u1.Name="Maruf Maniruzzaman";
    u1.Password="1234";
    u1.LastLogon=DateTime.Now;
    userTable.Add(u1);

    User u2 = userTable.Single(u => u.LogonID == "sumi"); 
    User u3 = userTable.Single(u => u.LogonID == "maruf");
    
    u2.Password="5678";
    userTable.Remove(u3);
    //We now submit all changes
    db.SubmitChanges();
    //Last statement is commit      
    db.LocalTransaction.Commit();
}catch(Exception ex)
{
    // On any error we rollback everything
    db.LocalTransaction.Rollback();
}
//We are done with the LocalTransaction at this point
db.LocalTransaction = null;

It is also possible (and a better way) to wrap the db.SubmitChanges() in a transaction scope this way:

using(TransactionScope ts = new TransactionScope()) {
        //Do some operations.....
         db.SubmitChanges();
        ts.Complete();
    }

Using the Designer

We have, until now, added the mapping objects manually. Now we use the DLinq designer to do that for us. We can drag tables from Server/Database Explorer to create mapping objects, create association (relation) between them using tools graphically.

First we add a new DLinqObjects file to the project.

Project -> Add New Item -> DLinqObjects

A drawing surface will be created. We do our drawing here.

Now we drag the "users" table from the Server Explorer's connection tree and drop it on the drawing surface. The table is shown in graphical format. Add all tables you may require for the project.

Now build the project and mapping classes for the corresponding tables are generated in backing C# file.

You use the generated classes as we used manually created classes. You can also create a Object Datasource from the generated classes. To do that from Data menu, select Add New Data Source and then select Object type and next select the class (for example, User class now) class from the classes tree. You can use it with data controls.

SQL Metal - The Entity Class Generator Tool

With the distribution of LINQ, we get an entity class generator. We can use it to generate entity classes directly or generate an XML file that describes the metadata and then from that XML file, we can generate the entity classes. In command line, we use the following syntax:

>sqlmetal [options] [<input file>]

For example:

>>sqlmetal /server:localhost\SQLEXPRESS /database:master 
    /namespace:MyPrj /code:users.cs

Use this tool to generate entity classes and save duplicate work.

Automatically Exporting Schema

We may want that DLINQ creates the database schema automatically for us. One way to do that is we need to subclass the DataContext class and define all tables in it as public members. So, we define OurDataContext class as following:

public class OurDataContext: DataContext 
{ 
    public Table<Users> users; 
    public OurDataContext(string connection) : base(connection) {} 
} 
//....... 
//....... 

//Now call following to export schema...
 
DataContext db = new OurDataContext(
   @"Data Source=localhost\sqlexpress;Initial Catalog=master;Integrated Security=True"
    );
  
db.CreateDatabase(); 

That's it. It will create the users table from the metadata.

OK, maybe, it can help just as a quick start. I'll provide more information as I continue. I will improve this article (along with other 3 articles) when I get time. I have made a lot of modification after I have posted the article. I'll show you one real life example - how to design Data Access Layer of a business application. But I cannot do that in a single step. I'll write as I learn to map the knowledge of same of J2EE with .NET. Thank you.

References

• LINQ Hands On Labs
  Also the LINQ installation folder has a lot of documents. This article is a summary of those.