Click here to Skip to main content
15,567,585 members
Articles / Programming Languages / C#
Technical Blog
Posted 5 Dec 2009

Tagged as

Stats

18K views
16 bookmarked

LTS Data Access Layer

Rate me:
Please Sign up or sign in to vote.
4.64/5 (6 votes)
6 Dec 2009CPOL2 min read
With LINQ-to-SQL, how many methods do we need for CRUD operations in ALL of the tables in a database? The answer is 4 - no kidding.

In my previous article about LINQ to SQL classes file, we generated a code file and a map file from our dbml file. Now we're gonna customize them to simplify our object model and data access layer. So with LTS, how many methods do we need to do GetAll, Insert, Update, and Delete operations in ALL of the tables in a database? The answer is 4 - no kidding.

First, let's create a new namespace for our data access layer. Assuming our project (namespace) is AdventureWorksLT, we can create AdventureWorksLT.DataAccess namespace by adding a folder named DataAccess to AdventureWorksLT project, or adding a ClassLibrary project named AdventureWorksLT.DataAccess to the solution containing AdventureWorksLT project.

Note: If you choose ClassLibrary, you must Add Reference to the ClassLibrary in order to use its classes in your project.

Next, we're putting the DataContext class in the new namespace. Explore the generated code file, you can realize that DataContext class needs a connectionString and a mappingSource to be working. So our new DataContext class looks like this:

C#
using System.Data.Linq.Mapping;
using System.Configuration;
using System.Data.Linq;

namespace AdventureWorksLT.DataAccess
{
    public sealed class AdventureWorksLTDataContext : DataContext
    {
        static XmlMappingSource map = XmlMappingSource.FromXml(
            System.IO.File.ReadAllText("AdventureWorksLT.map"));
        static string connectionString = ConfigurationManager.ConnectionStrings[
            "AdventureWorksLTConnectionString"].ConnectionString;
        
        public AdventureWorksLTDataContext() : base(connectionString, map) { }
        public AdventureWorksLTDataContext(string connection) : base(connection, map) { }
    }        
}

The mappingSource is created from the generated map file so the map file must exist in your Debug folder, and the connectionString is stored in app.config (app.config should be in your main project). Or we can instantiate DataContext object with our own connection string with the second overload. You can see that our DataContext class is much more simplified now.

Now create AdventureWorksLT.Models namespace and put your entity classes in it (you can use these classes as they are in the generated code file, or customize them to your needs, but remember to put each class in its own file). After this, you should have a Models folder (or an AdventureWorksLT.Models ClassLibrary project) containing Address.cs, SalesOrderHeader.cs, Customer.cs, ...

Now that entity classes are in AdventureWorksLT.Models, we need to change something in the map file. Seek for:

XML
<Type Name="Address">  

and change it to:

XML
<Type Name="AdventureWorksLT.Models.Address"> 

Do that for all the other tables.

Go back to our AdventureWorksLT.DataAccess namespace to create DataAccess classes. With the old way, each of our tables needs 4 stored procedures and 4 methods for GetAll, Insert, Update, and Delete operations, but here, we just need an abstract class and some inheritances (the use of stored procedures was mentioned in my ORM article).

C#
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;

namespace AdventureWorksLT.DataAccess
{
    public abstract class AbstractDao<T, IdT> where T : class
    {
        public virtual T GetById(IdT id)
        {
            return default(T);
        }
        public virtual List<T> GetAll()
        {
            AdventureWorksLTDataContext db = new AdventureWorksLTDataContext();
            Table<T> someTable = db.GetTable(typeof(T)) as Table<T>;
            return someTable.ToList<T>();
        }
        public virtual T Save(T entity)
        {
            AdventureWorksLTDataContext db = new AdventureWorksLTDataContext();        
            ITable tab = db.GetTable(entity.GetType());
            tab.InsertOnSubmit(entity);
            db.SubmitChanges();
            return entity;
        }
        public virtual T Update(T newEntity, T originalEntity)
        {
            AdventureWorksLTDataContext db = new AdventureWorksLTDataContext();
            ITable tab = db.GetTable(newEntity.GetType());
            if (originalEntity == null)
            {
                tab.Attach(newEntity, true);
            }
            else
            {
                tab.Attach(newEntity, originalEntity);
            }
            db.SubmitChanges();
            return newEntity;
        }
        public virtual void Delete(T entity)
        {
            AdventureWorksLTDataContext db = new AdventureWorksLTDataContext();
            ITable tab = db.GetTable(entity.GetType());
            tab.Attach(entity);
            tab.DeleteOnSubmit(entity);
            db.SubmitChanges();
        }                
    }
}

And all we need for a class to have basic CRUD operations is this:

C#
public class CustomerDao : AbstractDao<Customer, int> { }

(Customer is our entity class, and int is the type of CustomerID (primary key)). We can also expand the class easily to give it more functionalities:

C#
using System.Collections.Generic;
using System.Linq;
using AdventureWorksLT.Models;

namespace AdventureWorksLT.DataAccess
{
    public class CustomerDao : AbstractDao<Customer, int>
    {
        public override Customer GetById(int id)
        {
            return GetAll().Single(c => c.CustomerId == id);
        }
        public List<Customer> GetByName(string customerName)
        {
            return GetAll().Where(c => c.Name.Contains(customerName)).ToList();
        }
    }
}

Now CustomerDao has GetAll, Save, Update, Delete from AbstractDao and 2 extended methods: GetById, GetByName:

Image 1

Likewise, our DataAccess class for Address should be like this:

C#
public class AddressDao : AbstractDao<Address, int> { } 

And the same for other DataAccess classes.

With a little extra work, our LTS code is much more simplified, flexible, and easier to upgrade.
Please leave me a comment should any problem arise on your coding way.

Image 2

Image 3

This article was originally posted at http://x189.blogspot.com/feeds/posts/default

License

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


Written By
Software Developer
Vietnam Vietnam
ASP.NET MVC enthusiast.
Developer @ X189 blog.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Alexandre N.30-Oct-12 11:57
professionalAlexandre N.30-Oct-12 11:57 
QuestionRepeated creation of AdventureWorksLTDataContext Pin
Darchangel22-Dec-09 8:16
Darchangel22-Dec-09 8:16 
AnswerRe: Repeated creation of AdventureWorksLTDataContext Pin
Duy H. Thai22-Dec-09 18:01
Duy H. Thai22-Dec-09 18:01 
I found the Exception "Cannot add an entity with a key that is already in use" using one global instance of AdventureWorksLTDataContext. It happened when I used DataBinding in my app.
GeneralRe: Repeated creation of AdventureWorksLTDataContext Pin
Darchangel23-Dec-09 7:48
Darchangel23-Dec-09 7:48 
QuestionBetter than GUI? Pin
Darchangel22-Dec-09 7:19
Darchangel22-Dec-09 7:19 
AnswerRe: Better than GUI? Pin
Duy H. Thai22-Dec-09 18:28
Duy H. Thai22-Dec-09 18:28 
GeneralRe: Better than GUI? Pin
Darchangel23-Dec-09 9:21
Darchangel23-Dec-09 9:21 
GeneralLooks nice. You should also compare to... Pin
Matt Slay7-Dec-09 17:12
Matt Slay7-Dec-09 17:12 
GeneralRe: Looks nice. You should also compare to... Pin
Duy H. Thai7-Dec-09 18:38
Duy H. Thai7-Dec-09 18:38 
GeneralRe: Looks nice. You should also compare to... Pin
Matt Slay14-Dec-09 4:35
Matt Slay14-Dec-09 4:35 
GeneralRe: Tool from the West Wind developer Pin
Duy H. Thai22-Dec-09 18:43
Duy H. Thai22-Dec-09 18:43 

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

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