Data Access Layer using SqlDataReader and C#

LINQ is a great new platform but there are two places where it is really weak currently.

1. Its performance is not great. Especially on sites / applications with high performance requirements. You can read more about that here: Performance benchmarks for LINQ vs. SqlDataReader, DataSet - Selects: Part 1.

2. For simple queries, it works great and I love the easy intellisense and integration with code. But for more advanced queries, it relaly starts to break down. You have to start writing straight SQL which makes you use 2 different models. Also, I really dislike the basic assumption that LINQ forces you to put all your Data Access code inline with your business layer and even presentation code.

For relatively simple data-driven projects or where the queries are light and the performance needs are not huge, LINQ is definitely what I use. But for high performance applications where speed is absolutely critical, there is just nothing better than using SqlDataReader. Its performance is better than LINQ or even DataSets. Here is a list of some of the benefits of this data access layer:

- Fastest way to access data in your database (faster than LINQ or Strongly Typed DataSets).

- Allows you to dynamically specify which columns to load in your table on the fly instead of having the performance hit of a "select *" from the table.

- Data binding is built-in to this DAL. Since it uses the generic List in System.Collections.Generic, it works with controls like GridView and Repeater.

The following is a model for a data access layer (DAL) using SqlDataReader and C#. All code in the project can be found here:

SqlDataReader_DAL_Code.zip

The examples here use a VERY simple single table in a database. Basically, we are going to "wrap" this Category table so that the data in this table is mapped to a strongly typed object and all data access is abstracted to a single DLL project that can be shared across other projects. The table schema looks like this in Microsoft SQL Server Management Studio:

You can create the Category table using this script:

-- create the Category table

create table Category 

(

    PrimaryKey int identity(1000,1) not null,

    UserAccountFk int null,

    CategoryName nvarchar(50) not null,

    DateCreated datetime not null,

    constraint PK_Category primary key (PrimaryKey),

)

In Visual Studio, I created a new Class Library project called MyDal.Data where I will put all of the data access layer code. Here is a screen of the Solution Explorer with all the files and directories you need to create to set up the base wrapper for this table.

Base: This namespace contains the base class for each table. There is a file here for each table in the database with the base methods for Insert, Update, Delete, and Selects by Primary Key. These files are not be called from outside this project. Each class is named "[table name]DataAccessBase".

DataObject: This namespace contains an object for each table. Each object contains a direct representation of a row in the table with strongly typed properties. The files are named exactly the same as the table name.

QueryBuilder: This namespace contains a builder object for each table. These objects assist building the list of columns to be loaded in select and update queries dynamically. The files are named "[table name]Builder".

Schema: This namespace contains an object for each table that defines its database schema including fields, data types, names, and sizes. The files are named "[table name]Schema".

Utility: Contains classes to assist with DAL tasks and utility methods.

At the root of the project are the classes that are to be used to interact with the database tables and data. Each table has a class here with the naming "[table name]DataAccess".

To use this DAL Layer, here are some examples to show how easy it is once the tables are wrapped. It completely abstracts the database access from the caller. Here are several examples of how to interact with the Category table:

Insert example

// Use the CategoryDataAccess class to insert/update/delete/select on the Category table.

MyDal.Data.CategoryDataAccess dalCategory = new MyDal.Data.CategoryDataAccess();

// Insert a new record in the Category table.

// Create new object to store the table row to insert.

MyDal.Data.DataObject.Category categoryToInsert = new MyDal.Data.DataObject.Category();

categoryToInsert.UserAccountFk = 1234;

categoryToInsert.CategoryName = "My Category Name 1";

// NOTE: No need to set the DateCreated timestamp because that is auto-assigned by the Insert() method.

// insert record and get it's newly assigned primary key.

categoryToInsert = dalCategory.Insert(categoryToInsert);

// Get the primary key of the newly inserted record.

int iPrimaryKey = categoryToInsert.PrimaryKey;

Select by primary key example

// Get the primary key of the newly inserted record.

int iPrimaryKey = 1000;

// Use the CategoryDataAccess class to insert/update/delete/select on the Category table.

MyDal.Data.CategoryDataAccess dalCategory = new MyDal.Data.CategoryDataAccess();

// Find this record by it's PrimaryKey.

List<MyDal.Data.DataObject.Category> categoryList = dalCategory.SelectBy_PrimaryKey(iPrimaryKey);

// Check if a record was found for this primary key.

if (categoryList.Count > 0)

{

    // Do something with this record like get its CategoryName

    string sCategoryName = categoryList[0].CategoryName;

}

Update example

// Get the primary key of the newly inserted record.

int iPrimaryKey = 1000;

// Use the CategoryDataAccess class to insert/update/delete/select on the Category table.

MyDal.Data.CategoryDataAccess dalCategory = new MyDal.Data.CategoryDataAccess();

// Find this record by it's PrimaryKey so we can update it.

List<MyDal.Data.DataObject.Category> categoryList = dalCategory.SelectBy_PrimaryKey(iPrimaryKey);

// Check if a record was found for this primary key.

if (categoryList.Count > 0)

{

    // Update this record's Category Name.

    categoryList[0].CategoryName = "My Category Name 2";

    // Call the Update() method and specify that we want to Update the CategoryName field only.

    dalCategory.Update(categoryList[0], MyDal.Data.Schema.CategorySchema.ColumnList.CategoryName);

}

Select example with only specific columns loaded

// Use the CategoryDataAccess class to insert/update/delete/select on the Category table.

MyDal.Data.CategoryDataAccess dalCategory = new MyDal.Data.CategoryDataAccess();

// Find a record by its CategoryName and only load the PrimaryKey field.

List<MyDal.Data.DataObject.Category> categoryNameList = 

    dalCategory.SelectBy_CategoryName(

    "My Category Name 1", 

    MyDal.Data.Schema.CategorySchema.ColumnList.PrimaryKey);

// Loop through all records with this CategoryName.

for (int i = 0; i < categoryNameList.Count; i++)

{

    // Do something with each record

}

Delete example

// Use the CategoryDataAccess class to insert/update/delete/select on the Category table.

MyDal.Data.CategoryDataAccess dalCategory = new MyDal.Data.CategoryDataAccess();

// Delete the record by PrimaryKey.

int iPrimaryKey = 1000;

dalCategory.Delete(iPrimaryKey);

A detailed step by step guide to wrapping the Category table can be found here:

Data Access Layer using SqlDataReader and C# - Code Explanation

This article explains the code in each file that is required to wrap a table in your database.