Click here to Skip to main content
14,423,257 members

Building Data Access Layer Using Microsoft Enterprise Library 5.0

Rate this:
3.00 (11 votes)
Please Sign up or sign in to vote.
3.00 (11 votes)
20 Aug 2013CPOL
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

  1. 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;
  2. 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>   
  3. 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
  4. 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);
    } 
  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:
    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.
    #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:
    [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:
    [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;
    }

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jitendra Ballia
Software Developer
India India
MCA

Comments and Discussions

 
GeneralMy vote of 1 Pin
GiddyUpHorsey11-Feb-14 15:23
MemberGiddyUpHorsey11-Feb-14 15:23 
GeneralMy vote of 1 Pin
Roger Landolt23-Aug-13 12:06
MemberRoger Landolt23-Aug-13 12:06 
GeneralMy vote of 2 Pin
Guillaume Leparmentier21-Aug-13 0:27
MemberGuillaume Leparmentier21-Aug-13 0:27 
GeneralMy vote of 2 Pin
cjb11020-Aug-13 23:19
Membercjb11020-Aug-13 23:19 
GeneralNot an article. Pin
PIEBALDconsult20-Aug-13 7:02
professionalPIEBALDconsult20-Aug-13 7:02 
GeneralRe: Not an article. Pin
Jitendra Ballia20-Aug-13 7:07
MemberJitendra Ballia20-Aug-13 7:07 
GeneralRe: Not an article. Pin
PIEBALDconsult20-Aug-13 7:13
professionalPIEBALDconsult20-Aug-13 7:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 20 Aug 2013

Stats

42.2K views
15 bookmarked