LTS Data Access Layer

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:

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:

<Type Name="Address">  

and change it to:

<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).

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:

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:

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:

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

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

And the same for other DataAccess classes.
CodeProject

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.