Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

LTS Data Access Layer

, 6 Dec 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

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 your should have a Models folder (or a 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 store 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 store 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><t, /> where T : class
    {
        public virtual T GetById(IdT id)
        {
            return default(T);
        }
        public virtual List<T><t /> GetAll()
        {
            AdventureWorksLTDataContext db = new AdventureWorksLTDataContext();
            Table<T><t /> someTable = db.GetTable(typeof(T)) as Table<T><t />;
            return someTable.ToList<T><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();
        }                
    }
}</t /></t /> </t /> </t /> </t, /> 

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><customer, /><customer, /><customer, />
    {
        public override Customer GetById(int id)
        {
            return GetAll().Single(c => c.CustomerId == id);
        }
        public List<Customer><customer /> GetByName(string customerName)
        {
            return GetAll().Where(c => c.Name.Contains(customerName)).ToList();
        }
    }
}</customer /></customer, /> </customer, /></customer, />
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.

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 rise on your coding way.

License

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

Share

About the Author

Duy H. Thai
Software Developer
Vietnam Vietnam

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberalexandresn30-Oct-12 11:57 
QuestionRepeated creation of AdventureWorksLTDataContext PinmemberDarchangel22-Dec-09 8:16 
AnswerRe: Repeated creation of AdventureWorksLTDataContext PinmemberAssassinX18922-Dec-09 18:01 
GeneralRe: Repeated creation of AdventureWorksLTDataContext PinmemberDarchangel23-Dec-09 7:48 
QuestionBetter than GUI? PinmemberDarchangel22-Dec-09 7:19 
AnswerRe: Better than GUI? PinmemberAssassinX18922-Dec-09 18:28 
GeneralRe: Better than GUI? PinmemberDarchangel23-Dec-09 9:21 
GeneralLooks nice. You should also compare to... PinmemberMatt Slay7-Dec-09 17:12 
GeneralRe: Looks nice. You should also compare to... PinmemberAssassinX1897-Dec-09 18:38 
GeneralRe: Looks nice. You should also compare to... PinmemberMatt Slay14-Dec-09 4:35 
GeneralRe: Tool from the West Wind developer PinmemberAssassinX18922-Dec-09 18:43 

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 | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 6 Dec 2009
Article Copyright 2009 by Duy H. Thai
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid