Click here to Skip to main content
Click here to Skip to main content

DLinq Quick Start

, 4 Oct 2007
Rate this:
Please Sign up or sign in to vote.
Start using DLinq in easy steps

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.

Screenshot - linq_prj.gif

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

Screenshot - add-designer.gif

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.

Screenshot - design-table.gif

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Maruf Maniruzzaman
Software Developer KAZ Software Ltd. Bangladesh.
Bangladesh Bangladesh
Have completed BSc in Computer Science & Engineering from Shah Jalal University of Science & Technology, Sylhet, Bangladesh (SUST).
 
Story books (specially Masud Rana series), tourism, songs and programming is most favorite.
 
Blog:
Maruf Notes
http://blog.kuashaonline.com
 
Working on small project for 2 factor authentication auth2.com

Comments and Discussions

 
Generaldlinq Pinmembernisha885-Sep-10 21:19 
GeneralLike duplicated work PinmemberMichael Sync23-Sep-07 18:14 
GeneralRe: Like duplicated work PinmemberMaruf Maniruzzaman23-Sep-07 19:28 
GeneralRe: Like duplicated work PinmemberMichael Sync23-Sep-07 20:37 
GeneralArgh! PinprotectorMarc Clifton22-Sep-07 13:41 
GeneralRe: Argh! [modified] PinmemberMaruf Maniruzzaman22-Sep-07 16:59 
GeneralRe: Argh! PinmemberMarc Greiner3-Oct-07 2:11 
GeneralRe: Argh! [modified] PinmemberMaruf Maniruzzaman3-Oct-07 19:45 
GeneralRe: Argh! PinmemberMaruf Maniruzzaman6-Oct-07 16:09 
GeneralRe: Argh! Pinmembernorm .net23-Apr-08 22:57 
GeneralRe: Argh! PinprotectorMarc Clifton24-Apr-08 2:23 
GeneralRe: Argh! Pinmembernorm .net24-Apr-08 2:55 
GeneralNot much flesh on these bones PinmemberRob Graham22-Sep-07 3:45 
GeneralRe: Not much flesh on these bones PinmemberMaruf Maniruzzaman22-Sep-07 6:00 
GeneralRe: Not much flesh on these bones Pinmembernorm .net22-Sep-07 8:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 4 Oct 2007
Article Copyright 2007 by Maruf Maniruzzaman
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid