Building Data Access Layer Using Microsoft Enterprise Library 5.0

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

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

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

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

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

3. At the Data Entity Layer, create a entity class Customer:
   C#

   [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

4. Then you go to Create mapping path code.
   C#

   #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);
   } 

5. 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;
   } 

6. Then you go to create List method for getting Customer records:
   C#

   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

7. Create insert, update, and delete methods.
   C#

   #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

8. Create a method GetCustomers in the CustomerDAL class with the following implementation:
   C#

   [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();
   }

9. Create a method GetcustomerbyID:
   C#

   [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;
   }