Building Data Access Layer Using Microsoft Enterprise Library 5.0

Jitendra Ballia

3.00/5 (11 votes)

Aug 20, 2013

CPOL

49675

Building data access layer using enterprise library

Introduction

You have a Customer table with three fields: ID, First Name, and Last Name. You are building a data access layer to retrieve all the records from the Customer table and return the result as a collection of Customer objects. You want implement it using Microsoft Enterprise Library.

Background

This is useful in programming.

Using the Code

In your Data Access Layer project, add references to the following Microsoft Enterprise Library DLLs:

using System.Data.SqlClient;
using System.Data.Common;
using System.Runtime.Serialization;  
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

At App.config or Web.config, add the following data configuration section:

<configSections>
    <section name="dataConfiguration" 
      type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, 
            Microsoft.Practices.EnterpriseLibrary.Data"/>
 </configSections>

At the Data Entity Layer, create a entity class Customer:

[DataObject]
[Serializable]
[DataContract]
public partial class Customer 
{
#region Constants
    public static readonly string TABLE_NAME = "[dbo].[Customer]";
#endregion
#region Properties
    [DataMember]
    public Int32? UserId
    {
        get;
        set;
    }
    [DataMember]
    public System.String Firstname
    {
        get;
        set;
    }
    [DataMember]
    public System.String lastname
    {
        get;
        set;
    }
#endregion

Then you go to Create mapping path code.

#region mapping methods
protected void MapTo(DataSet ds)
{
    DataRow dr;
    if (ds == null)
        ds = new DataSet();
    if (ds.Tables["TABLE_NAME"] == null)
        ds.Tables.Add(TABLE_NAME);
    ds.Tables[TABLE_NAME].Columns.Add("UserId", typeof(System.Int32));
    ds.Tables[TABLE_NAME].Columns.Add("Firstname", typeof(System.String));
    ds.Tables[TABLE_NAME].Columns.Add("lastname", typeof(System.String));
           
    dr = ds.Tables[TABLE_NAME].NewRow();

    if (UserId == null)
        dr["UserId"] = DBNull.Value;
    else
        dr["UserID"] = UserId;
    if (Firstname == null)
        dr["Firstname"] = DBNull.Value;
    else
        dr["Firstname"] = Firstname;
    if (lastname == null)
        dr["lastname"] = DBNull.Value;
    else
        dr["lastname"] = lastname;
    
    ds.Tables[TABLE_NAME].Rows.Add(dr);
}

Then you go to create Readreader data for readers:

private static Customer ReadReader(IDataReader objReader)
{
    Customer instance = new Customer ();
    bool isnull = true;
    while (objReader.Read())
    {
        isnull = false;
        instance.UserId = objReader["UserId"] != DBNull.Value ? 
          Convert.ToInt32(objReader["UserId"]) : instance.UserId = null;
        instance.Firstname = objReader["Firstname"] != DBNull.Value ? 
          Convert.ToString(objReader["Firstname"]) : instance.Firstname = null;
        instance.lastname = objReader["lastname"] != DBNull.Value ? 
          Convert.ToString(objReader["lastname"]) : instance.lastname = null;
        
    }
    if (isnull) return null;
    else return instance;
}

Then you go to create List method for getting Customer records:

private static List<Customer> ReadReaders(IDataReader objReader)
{
    List<Customer> instances = new List<Customer>();
    Customer instance;
    bool isnull = true;
    while (objReader.Read())
    {
        isnull = false;
        instance = new Customer ();
        instance.UserId = objReader["UserId"] != DBNull.Value ? 
          Convert.ToInt32(objReader["UserId"]) : instance.UserId = null;
        instance.Firstname = objReader["Firstname"] != DBNull.Value ? 
          Convert.ToString(objReader["Firstname"]) : instance.Firstname = null;
        instance.lastname = objReader["lastname"] != DBNull.Value ? 
          Convert.ToString(objReader["lastname"]) : instance.lastname = null;
        
        instances.Add(instance);

    }
    if (isnull) return null;
    else return instances;
}
#endregion

Create insert, update, and delete methods.

#region CRUD Methods

[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
public static Customer Get(System.Int32 userID)
{
    Database db;
    string sqlCommand;
    DbCommand dbCommand;
    Customer instance = null;

    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].wo_UserData_SELECT";
    dbCommand = db.GetStoredProcCommand(sqlCommand, userID);

    // Get results.
    using (IDataReader objReader = db.ExecuteReader(dbCommand))
    {
        instance = ReadReader(objReader);
    }
    return instance;
}

#region INSERT
public void Insert(System.Int32? userID, System.String fname, 
       System.String lname, DbTransaction transaction)
{
    Database db;
    string sqlCommand;
    DbCommand dbCommand;

    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].wo_UserData_INSERT";
    dbCommand = db.GetStoredProcCommand(sqlCommand, userID, fname, lname);

    if (transaction == null)
        db.ExecuteScalar(dbCommand);
    else
        db.ExecuteScalar(dbCommand, transaction);
    return;
}

[DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
public void Insert(System.Int32? userID, System.String fname, System.String lname)
{
    Insert(userID, fname, lname, null);
}
/// <summary>
/// Insert current UserData to database.
/// </summary>
/// <param name="transaction">optional SQL Transaction</param>
public void Insert(DbTransaction transaction)
{
    Insert(UserID, Fname, Lname,transaction);
}

/// <summary>
/// Insert current Customer to database.
/// </summary>
public void Insert()
{
    this.Insert((DbTransaction)null);
}
#endregion

#region UPDATE
public static void Update(System.Int32? userID, 
  System.String fname, System.String lname, DbTransaction transaction)
{
    Database db;
    string sqlCommand;
    DbCommand dbCommand;

    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].wo_UserData_UPDATE";
    dbCommand = db.GetStoredProcCommand(sqlCommand);
    db.DiscoverParameters(dbCommand);
    dbCommand.Parameters["@userID"].Value = userID;
   
    dbCommand.Parameters["@fname"].Value = fname;
    dbCommand.Parameters["@lname"].Value = lname;

    if (transaction == null)
        db.ExecuteNonQuery(dbCommand);
    else
        db.ExecuteNonQuery(dbCommand, transaction);
    return;
}

[DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
public static void Update(System.Int32? userID, System.String fname, System.String lname)
{
    Update(userID, fname, lname, null);
}

public static void Update(Customer customer)
{
    customer.Update();
}

public static void Update(Customer customer, DbTransaction transaction)
{
    customer.Update(transaction);
}

/// <summary>
/// Updates changes to the database.
/// </summary>
/// <param name="transaction">optional SQL Transaction</param>
public void Update(DbTransaction transaction)
{
    DataSet ds;
    Database db;
    string sqlCommand;
    DbCommand dbCommand;

    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].wo_UserData_UPDATE";
    dbCommand = db.GetStoredProcCommand(sqlCommand);
    db.DiscoverParameters(dbCommand);
    dbCommand.Parameters["@userID"].SourceColumn = "UserID";
   
    dbCommand.Parameters["@fname"].SourceColumn = "Fname";
    dbCommand.Parameters["@lname"].SourceColumn = "Lname";

    ds = new DataSet();
    this.MapTo(ds);
    ds.AcceptChanges();
    ds.Tables[0].Rows[0].SetModified();
    if (transaction == null)
        db.UpdateDataSet(ds, TABLE_NAME, null, dbCommand, null, UpdateBehavior.Standard);
    else
        db.UpdateDataSet(ds, TABLE_NAME, null, dbCommand, null, transaction);
    return;
}

/// <summary>
/// Updates changes to the database.
/// </summary>
public void Update()
{
    this.Update((DbTransaction)null);
}
#endregion

#region DELETE
[DataObjectMethodAttribute(DataObjectMethodType.Delete, false)]
public static void Delete(System.Guid? userID, DbTransaction transaction)
{
    Database db;
    string sqlCommand;
    DbCommand dbCommand;

    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].wo_UserData_DELETE";
    dbCommand = db.GetStoredProcCommand(sqlCommand, userID);

    // Execute.
    if (transaction != null)
    {
        db.ExecuteNonQuery(dbCommand, transaction);
    }
    else
    {
        db.ExecuteNonQuery(dbCommand);
    }
}

[DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
public static void Delete(System.Guid? userID)
{
    Delete(
    userID, null);
}

/// <summary>
/// Delete current UserData from database.
/// </summary>
/// <param name="transaction">optional SQL Transaction</param>
public void Delete(DbTransaction transaction)
{
    Database db;
    string sqlCommand;
    DbCommand dbCommand;


    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].wo_UserData_DELETE";
    dbCommand = db.GetStoredProcCommand(sqlCommand, UserID);

    // Execute.
    if (transaction != null)
    {
        db.ExecuteNonQuery(dbCommand, transaction);
    }
    else
    {
        db.ExecuteNonQuery(dbCommand);
    }
}

/// <summary>
/// Delete current UserData from database.
/// </summary>
public void Delete()
{
    this.Delete((DbTransaction)null);
}

#endregion  

public enum UserRole
{
    User=0,
    Admin=1,
    SuperAdmin=2,
    SubAdmin=3,
}
#endregion

Create a method GetCustomers in the CustomerDAL class with the following implementation:

[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
public static Customer [] GetCustomerlist(System.Int32 ? userID)
{
    Database db;
    string sqlCommand;
    DbCommand dbCommand;

    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].cst_UserData_SELECTlist";
    dbCommand = db.GetStoredProcCommand(sqlCommand, userID);

    List<Customer> objPrdList = null;
    using (IDataReader objReader = db.ExecuteReader(dbCommand))
    {
        objPrdList = ReadReaders(objReader);
    }
    if (objPrdList == null)
        return null;
    else
        return objPrdList.ToArray();
}

Create a method GetcustomerbyID:

[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
public static Customer Get(System.int32 userID)
{
    Database db;
    string sqlCommand;
    DbCommand dbCommand;
    Customer instance = null;

    db = DatabaseFactory.CreateDatabase();
    sqlCommand = "[dbo].cst_UserData_SELECT_USERNAME";
    dbCommand = db.GetStoredProcCommand(sqlCommand, username);

    // Get results.
    using (IDataReader objReader = db.ExecuteReader(dbCommand))
    {
        instance = ReadReader(objReader);
    }
    return instance;
}