Data Access Layer in C# using Repository and Data Mapper – Part 1

Data access is a popular subject in most of the applications. There are plenty of options on various data access techniques and frameworks that often create confusion in the minds of developers choosing the right approach or strategy for creating an effective data access layer. Two main things that should be considered while creating a data access layer are simplicity and reliability.

When creating an application with a domain model, one should consider the usage of a layer that is used to map objects in the domain model to the relational table schema while keeping them independent of each other. The main objective of the data mapper is to strip the responsibility of persistence from the entity objects in favor of classes external to the entities. The repository uses this data mapper classes to access, modify and persist these entities back to the database. A repository is a layer that mediates between the business entities and data mappers in your data access code. The business layer uses this repository for all the data access functionality it requires.

In this article series we’ll see how to create a simple data access layer in C# that uses the data mapper and repository pattern implementation. The database used in the sample is SQL Server, which can be replaced by any other relational database. I have used the enterprise library data access application block for all ADO.NET queries.

Before writing the C# code, we’ll work on the database schema. We’ll use a customer table and the CRUD procs to demonstrate the repository and data mapper sample in this article series.

The scripts for creating the customer table is

CREATE TABLE [dbo].[Customers](

      [CustomerIntId] [bigint] IDENTITY(1,1) NOT NULL,

      [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [Address] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [CreatedDate] [datetime] NULL,

      [ChangedDate] [datetime] NULL,

      [Version] [timestamp] NULL,

 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

      [CustomerIntId] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Stored procedures used in the sample.

CREATE PROCEDURE [dbo].[usp_GetAllCustomers]   

AS

BEGIN

      SET NOCOUNT ON;

      SELECT [CustomerIntId] as 'CustomerIntId'

      ,[FirstName] as 'FirstName'

      ,[LastName] as 'LastName'

      ,[Address] as 'Address'

      ,[CreatedDate] as 'CreatedDate'

      ,[ChangedDate] as 'ChangedDate'

      ,[Version] as 'Version'

      FROM [dbo].[Customers]

END

CREATE PROCEDURE [dbo].[usp_GetCustomerById]

      @CustomerIntId bigint

AS

BEGIN

      SET NOCOUNT ON;

    SELECT [CustomerIntId] as 'CustomerIntId'

      ,[FirstName] as 'FirstName'

      ,[LastName] as 'LastName'

      ,[Address] as 'Address'

      ,[CreatedDate] as 'CreatedDate'

      ,[ChangedDate] as 'ChangedDate'

      ,[Version] as 'Version'

  FROM [dbo].[Customers]

  WHERE [CustomerIntId] = @CustomerIntId

END

CREATE PROCEDURE [dbo].[usp_InsertCustomer]

      @FirstName varchar(50),

      @LastName varchar(50),

      @Address varchar(500),

      @CustomerIntId bigint OUT

AS

BEGIN

      SET NOCOUNT ON;

      INSERT INTO [dbo].[Customers]

           ([FirstName]

           ,[LastName]

           ,[Address]

           ,[CreatedDate]

           ,[ChangedDate])

     VALUES

           (@FirstName

           ,@LastName

           ,@Address

           ,getdate()

           ,getdate())

      SET @CustomerIntId = SCOPE_IDENTITY()

END

CREATE PROCEDURE [dbo].[usp_UpdateCustomer]

      @CustomerIntId bigint,

      @FirstName varchar(50),

      @LastName varchar(50),

      @Address varchar(500),

      @Version timestamp,

      @VersionNew timestamp OUT

AS

BEGIN

      SET NOCOUNT ON;

    UPDATE [dbo].[Customers]

      SET [FirstName] = @FirstName

      ,[LastName] = @LastName

      ,[Address] = @Address

      ,[ChangedDate] = getdate()

      WHERE CustomerIntId = @CustomerIntId

      AND Version = @Version

      IF @@ROWCOUNT = 0

      BEGIN

            RAISERROR('Concurrent update error. Updated aborted.', 16, 2)

      END

      SELECT @VersionNew = [Version]      FROM [dbo].[Customers]

      WHERE [CustomerIntId] = @CustomerIntId   

END

CREATE PROCEDURE [dbo].[usp_DeleteCustomerById]

      @CustomerIntId bigint

AS

BEGIN

      SET NOCOUNT ON;

    DELETE FROM [dbo].[Customers]

    WHERE [CustomerIntId] = @CustomerIntId

END

Next we’ll see how to create a data access layer for accessing the Customer data that we created in the database.