CodeNode.Datalayer – A Complete ADO.NET Wrapper

Rahul Garg | May 23, 2015 | .NET | No Comments

The data layer is one of the most important component of the application and we have pretty good options to take care this requirement like ADO.NET, LinqToSql , Entity Framework. Entity Framework is one of the most used ORM currently but ADO.NET still maintained its charm and still a valid choice for accessing a database from .NET applications.

As I believe that some dev folks out there still need a complete, fast and easy to use ADO.NET wrapper , I decided to create the one. Although I don’t claim that it is a replacement for any ORM.

I developed a small module named CodeNode.Datalayer targated to provide access to the most often used features of ADO.NET while boosting developer productivity. I will not post complete code of classes here as you can visit them at github repository.

You can find Nuget and code details at below links:

Nuget Packages at: Packages Link

Source Code at: CodeNode Repository

This module can be divided in followings categories:

Request
Provider
NullSafeDataReader
Extensions
Attribute

Request : This part contains three classes.
1. DataRequest : This is responsible to have command name, command type and list of parameters.
```
var request = new DataRequest("getUserById", CommandType.StoredProcedure)
```
2. DataParameter : This class defines the attributes of SQL parameter, such as name, type , direction size etc. We can define a parameter and attach it to request object as :
```
request.Params.Add(new DataParameter ("userId", 123));
```
  As we can see that Params is a LIst<DataParameter > in DataRequest, so we can attach as many parameter as required for stored procedure. We can also provide parameter direction in case it required
```
request.Params.Add(new DataParameter ("Id", 11,ParameterDirection.InputOutput));
```
3. DataBulkRequest : This class will be used in case bulk insert will be required. It support attributes like destination table, time out, batch size etc.
```
public DataBulkRequest(DataTable source, string destinationTable, int timeOut, int batchSize) : this(source, destinationTable, imeOut)
        {
            BatchSize = batchSize;
    }

var bulkRequest   = new DataBulkRequest(sourceDatatable, "Users",500, 1000);
```

Provider : This part contains two classes.
1. BaseDataProvider :This is base class for our data provider and mainly responsible for setting connection string. If your connection-string name is “DefaultConnection”, you don’t need to provide it otherwise you have to pass its name in constructor.
2. DataProvider :This is one of the important parts of module, which provide all code functions for ADO.NET. As described if connection-string name is “DefaultConnection” , we can directly use , Otherwise, we need to provide connection-string name.
```
var provider  = new DataProvider();

var provider  = new DataProvider("MyAppConnectionStringName");
```
  It contains following methods :
  - INullSafeDataReader ExecuteDataReader(DataRequest request) : This internally calls INullSafeDataReader ExecuteDataReader(DataRequest request, CommandBehavior behavior) with CommandBehavior.CloseConnection and returns instance of NullSafeDataReader.
  - INullSafeDataReader ExecuteDataReader(DataRequest request, CommandBehavior behavior) : Returns instance of NullSafeDataReader.
  - object ExecuteScalar(DataRequest request) : Return object returned from SQL after successful execution .
  - int ExecuteNonQuery(DataRequest request) : Returns result of ExecuteNonQuery.
  - T ExecuteNonQueryForOutParameter<T>(DataRequest request, string parametName) : We can use this method when we expects a output parameter from ExecuteNonQuery. Suppose we are expecting output value of type string and parameter name is “EmailId“. Please make sure that “EmailId” exist as a parameter in request.Params and its direction marked as ParameterDirection.InputOutput or ParameterDirection.Output as required.
```
var email  = provider. ExecuteNonQueryForOutParameter<string>(request," EmailId ");
```
  - DataSet ExecuteDataSet(DataRequest request) : It returns object of dataset and can be used when more than 1 result set are expected.
  - DataTable ExecuteDataTable(DataRequest request) : It returns object of data table.
  - void ExecuteBulkCopy(DataBulkRequest request) : This method is useful when bulk insert is required.

NullSafeDataReader : This wraps SqlDataReader to provide null safe methods over it. It means if you got an integer as DbNull form database it will convert it into its default value which is 0 in case of integer. This is for all value types. It just remove the necessity to define Nullable<T> in model because we can directly map NullSafeDataReader to Model.

Extensions: Currently this folder contains only one class as rest of all extension classes moved in CodeNode.Extension project.

NullSafeDataReaderExtention : This really ease our life and map our models to result provided from ExecuteDataReader method.
It contains two public methods :

public static T MapToObject<T>(this INullSafeDataReader dataReader, bool useMapColumnAttribute = false);
public static List<T> MapToList<T>(this INullSafeDataReader dataReader, bool useMapColumnAttribute = false);

I think a little explanation is required here. Suppose we have a SQL table as below. We need to retrieve its data and fill into our c# models.
We need to create a c# POCO for it.

public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string EmailId { get; set; }
        public string Address { get; set; }
        public decimal Income { get; set; }
    }

We created a procedure to find the user.

CREATE PROCEDURE [dbo].[getUserById] @userId int
AS
BEGIN
 SELECT
 Id,
 Name,
 EmailId,
 Address,
 Income
 FROM Users
 WHERE Id = @userId
END

Now our complete call would be:


Public User GetUser(int id) {

 var dataProvider = new DataProvider();

 var request = new DataRequest("getUserById", CommandType.StoredProcedure)

 request.Params.Add(new DataParameter("userId", id));

 using(var dataReader = dataProvider.ExecuteDataReader(request))
 {
    return dataReader.MapToObject<User> ();
 }
}

var myUser = GetUser(11);

Suppose stored procedure returns list of users, we just need to change dataReader. MapToObject <User>() to dataReader. MapToList<User>() and it will provide List<User>.
One thing is to be noted that our model properties name and type is exact same what we returned from stored procedure so that we can find properties through reflection. We can use “MapColumnAttribute” to map column of different name to data reader.

Attribute : This part contains a class MapColumnAttribute and can be use to map c# model property to different name column with data reader.Now suppose our model has property SpecialName in place of Name but stored procedure returns column as Name.

public class User
   {    public int Id{get;set;}
        public string SpecialName{get;set;}
        public string EmailId{get;set;}
        public string Address{get;set;}
        public decimal Income{get;set;}
   }

We can decorate our property with MapColumnAttribute and need to pass useMapColumnAttribute to true in NullSafeDataReaderExtention methods.

public class User
   {    public int Id{get;set;}
        [MapColumn("Name")]
        public string SpecialName{get;set;}
        public string EmailId{get;set;}
        public string Address{get;set;}
        public decimal Income{get;set;}
   }

    dataReader.MapToObject<User>(true);

Feel free to provide your feedback and to post any query related to module.

Do share the wisdom and motivate us to keep writing such online tutorials for free and do comment if anything is missing or wrong or you need any kind of help.

Keep Learning.. Happy Coding..

Recommended -

Tags:.NET, ADO.NET, C#, Datalayer

0 Comments

Inline Feedbacks

View all comments