.NET

Start With Microsoft Enterprise Library 6.0

Let's create one sample example of Catalog Management.

Using Database

Step 1

Create two Table Categories and a Product as in the following:

table design

Please find the following SQL script for the two tables Category and Product.

  1. CREATE TABLE [dbo].[Category](  
  2.     [CategoryID] [bigint] IDENTITY(1,1) NOT NULL,  
  3.     [CategoryName] [varchar](250) NULL,  
  4.  CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED   
  5. (   [CategoryID] ASC  
  6. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  7. ) ON [PRIMARY]  
  1. CREATE TABLE [dbo].[Product](  
  2.     [ProductID] [bigint] IDENTITY(1,1) NOT NULL,  
  3.     [CategoryID] [bigint] NULL,  
  4.     [ProductName] [varchar](250) NULL,  
  5.  CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED   
  6. (  
  7.     [ProductID] ASC  
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  9. ) ON [PRIMARY]  
  10. GO  
  11. SET ANSI_PADDING OFF  
  12. GO  
  13. ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [fk_CategoryID] FOREIGN KEY([CategoryID])  
  14. REFERENCES [dbo].[Category] ([CategoryID])  
  15. GO  
  16. ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [fk_CategoryID]  
Step 2

Let's create all the user-defined Stored Procedures for inserting and displaying all the products.

Insert a Product using the Stored Procedure “UspInsertProduct”.
  1. CREATE PROCEDURE [dbo].[UspInsertProduct]  
  2. (  
  3. @ProductName varchar(250)=null  
  4. ,@CategoryID bigint null  
  5. )  
  6. AS  
  7. BEGIN  
  8. INSERT INTO [dbo].[Product]  
  9.            ([ProductName],  
  10.            [CategoryID])  
  11.      VALUES  
  12.            (@ProductName  
  13.            ,@CategoryID)  
  14. END  
Select all the Products using the Stored Procedure “UspSelectProduct”.
  1. CREATE PROCEDURE [dbo].[UspSelectProduct]  
  2. AS  
  3. BEGIN  
  4. SELECT     
  5. Product.ProductID AS ProductID,   
  6. Product.CategoryID AS CategoryID,   
  7. Product.ProductName AS ProductName,  
  8. Category.CategoryName AS CategoryName  
  9. FROM  Category INNER JOIN  
  10. Product   
  11. ON Category.CategoryID = Product.CategoryID   
  12. END  
Create another Stored Procedure for all the categories.
  1. CREATE PROCEDURE [dbo].[uspSelectCategory]  
  2. AS  
  3. BEGIN  
  4. SELECT [CategoryID]  
  5.       ,[CategoryName]  
  6.   FROM [Category]  
  7.   END  
We have a Product table with the three fields ProductID, CategoryID and ProductName. And a Category table with the two fields CategoryID and CategoryName. Also we have created three Stored Procedures for selecting a Category, inserting a Product and selecting a Product. Now we will build a data access layer to retrieve all the records from the Product and Category table and return the result as a collection of Product and category objects. We will implement it using the Microsoft Enterprise Library.

Using Code

Step 1

Let's create one application with the entire layer as we are using daily in 3-tier architecture such as DAL, BAL, Entity, Utility, UI.

DAL

In the preceding image you found there are various class libraries: 
  • BALClassLibary: This class library is for the Business Layer.

  • DALClassLibary: This class library is for the database operations like CRUD logic (data access layer).

  • EntityClassLibary: This class library is for maintainiing all the properties over the database.

  • ProductCatalog: for the UI layer.

  • UtilityClassLibary: for all the database constants, all the utilities like sent mail and so on.

Step 2

Install the Microsoft Enterprise Library using the Package Manager Console.

PM- Install EnterpriseLibrary.Data

Install

EnterpriseLibrary

Step 3

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

  1. <configSections>  
  2.    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data"/>  
  3. </configSections>  
  4. <dataConfiguration defaultDatabase="CommunityConnectionString" />  
Here's the connection string.
  1. <add name="CommunityConnectionString" connectionString="Data Source=SQLEXPRESS;Initial Catalog=Community;Integrated Security=True"  
  2. providerName="System.Data.SqlClient" />  
Note: defaultdatabasename equal connection string name.

Step 4

At the EntityClassLibrary, create two entity classes, Product and Category.
  1. //Category Entity  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Text;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace EntityClassLibrary  
  9. {  
  10.   public  class Category  
  11.     {  
  12.       public Int32? CategoryID { get; set; }  
  13.       public string CategoryName { get; set; }  
  14.     }  
  15. }  
  16.   
  17. using System;  
  18. using System.Collections.Generic;  
  19. using System.Linq;  
  20. using System.Text;  
  21. using System.Threading.Tasks;  
  22.   
  23.   
  24. namespace EntityClassLibrary  
  25. {  
  26.     public class Product  
  27.     {  
  28.         public Int32? ProductID { get; set; }  
  29.         public string ProductName { get; set; }  
  30.         public Category ProductCategory { get; set; }  
  31.     }  
  32. }  
Step 5

At the UtilityClassLibrary, let's create one class DataBaseConstat that has all the constant values.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace UtilityClassLibrary  
  8. {  
  9.     public static class DataBaseConstat  
  10.     {  
  11.         public static string ConnectionString = "CommunityConnectionString";  
  12.         public static string ProductID = "ProductID";  
  13.         public static string ProductName = "ProductName";  
  14.         public static string CategoryID = "CategoryID";  
  15.         public static string CategoryName = "CategoryName";  
  16.         public static string UspInsertProduct = "UspInsertProduct";  
  17.         public static string UspSelectProduct = "UspSelectProduct";  
  18.         public static string uspSelectProductDetail = "uspSelectProductDetail";  
  19.         public static string uspSelectCategory = "uspSelectCategory";  
  20.   
  21.     }  
  22. }  
Step 6

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

add references

Data Access Layer

Create List method for getting Category records

Here we have added two class library references, UtilityClassLibary and EntityClassLibary.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using Microsoft.Practices.EnterpriseLibrary.Data;  
  7. using Microsoft.Practices.EnterpriseLibrary.Common;  
  8. using EntityClassLibrary;  
  9. using UtilityClassLibrary;  
  10. using System.Data.Common;  
  11. using System.Data;  
Then we will go to create a List method for getting the Category records here.
  1. public static List<Category> GetAllCategoryList()  
  2. {  
  3.     Database database;  
  4.     DbCommand dbCommand;  
  5.     List<Category> CategoryList = null;  
  6.   
  7.     CategoryList = new List<Category>();  
  8.     DatabaseProviderFactory factory = new DatabaseProviderFactory();  
  9.     database = factory.Create(DataBaseConstat.ConnectionString);  
  10.     dbCommand = database.GetStoredProcCommand(DataBaseConstat.uspSelectCategory);  
  11.     using (IDataReader objReader = database.ExecuteReader(dbCommand))  
  12.     {  
  13.         CategoryList = CreateCategory(objReader);  
  14.     }  
  15.     if (CategoryList == null)  
  16.         return null;  
  17.     else  
  18.         return CategoryList;  
  19. }  
Then we will create CreateCategory data for readers:
  1. private static List<Category> CreateCategory(IDataReader objReader)  
  2. {  
  3.     List<Category> categoryList = new List<Category>();  
  4.     Category category;  
  5.     bool isnull = true;  
  6.     while (objReader.Read())  
  7.     {  
  8.         isnull = false;  
  9.         category = new Category();  
  10.         category.CategoryID = objReader[DataBaseConstat.CategoryID] != DBNull.Value ?  
  11.         Convert.ToInt32(objReader[DataBaseConstat.CategoryID]) : category.CategoryID = null;  
  12.         category.CategoryName = objReader[DataBaseConstat.CategoryName] != DBNull.Value ?  
  13.         Convert.ToString(objReader[DataBaseConstat.CategoryName]) : category.CategoryName = null;  
  14.   
  15.         categoryList.Add(category);  
  16.   
  17.     }  
  18.     if (isnull) return null;  
  19.     else return categoryList;  
  20. }  
Create insert methods for the products.
  1. public static void InsertProduct(Product productObj)  
  2. {  
  3.     Database database = null;  
  4.     DbCommand dbCommand = null;  
  5.     try  
  6.     {  
  7.         DatabaseProviderFactory factory = new DatabaseProviderFactory();  
  8.         database = factory.Create(DataBaseConstat.ConnectionString);  
  9.         dbCommand = database.GetStoredProcCommand(DataBaseConstat.UspInsertProduct);  
  10.         database.AddInParameter(dbCommand,DataBaseConstat.ProductName,DbType.String, productObj.ProductName);  
  11.         database.AddInParameter(dbCommand, DataBaseConstat.CategoryID, DbType.Int32, productObj.ProductCategory.CategoryID);  
  12.         database.ExecuteNonQuery(dbCommand);  
  13.     }catch(Exception ex)  
  14.     {  
  15.         throw ex;  
  16.     }finally  
  17.     {  
  18.         database = null;  
  19.   
  20.     }  
  21. }  
Create list methods for the product.
  1. public static List<Product> GetAllList()  
  2. {  
  3.     Database database;  
  4.     DbCommand dbCommand;  
  5.     List<Product> ProductList = null;  
  6.     ProductList = new List<Product>();  
  7.     DatabaseProviderFactory factory = new DatabaseProviderFactory();  
  8.     database = factory.Create(DataBaseConstat.ConnectionString);  
  9.     dbCommand = database.GetStoredProcCommand(DataBaseConstat.UspSelectProduct);  
  10.     using (IDataReader objReader = database.ExecuteReader(dbCommand))  
  11.     {  
  12.         ProductList = CreateProduct(objReader);  
  13.     }  
  14.     if (ProductList == null)  
  15.         return null;  
  16.     else  
  17.         return ProductList;  
  18. }  
Then we go to create CreateProduct data for the readers:
  1. private static List<Product> CreateProduct(IDataReader objReader)  
  2. {  
  3.     List<Product> productList = new List<Product>();  
  4.     Product product;  
  5.     bool isnull = true;  
  6.     while (objReader.Read())  
  7.     {  
  8.         isnull = false;  
  9.         product = new Product();  
  10.         product.ProductID = objReader[DataBaseConstat.ProductID] != DBNull.Value ?  
  11.         Convert.ToInt32(objReader[DataBaseConstat.ProductID]) : product.ProductID = null;  
  12.         product.ProductName = objReader[DataBaseConstat.ProductName] != DBNull.Value ?  
  13.         Convert.ToString(objReader[DataBaseConstat.ProductName]) : product.ProductName = null;  
  14.         product.ProductCategory = new Category();  
  15.         product.ProductCategory.CategoryID = objReader[DataBaseConstat.CategoryID] != DBNull.Value ?  
  16.         Convert.ToInt32(objReader[DataBaseConstat.CategoryID]) : product.ProductCategory.CategoryID = null;  
  17.         product.ProductCategory.CategoryName = objReader[DataBaseConstat.CategoryName] != DBNull.Value ?  
  18.         Convert.ToString(objReader[DataBaseConstat.CategoryName]) : product.ProductCategory.CategoryName = null;  
  19.         productList.Add(product);  
  20.   
  21.     }  
  22.     if (isnull) return null;  
  23.     else return productList;  
  24. }  
Then we create the BAL layer.

BAL layer
Here we have added all the libraries, DALClassLibrary and EntityClassLibary.

added all the Library

Code for BAL Layer
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using DALClassLibrary;  
  7. using EntityClassLibrary;  
  8.   
  9. namespace BALClassLibrary  
  10. {  
  11.     public static class ProductBAL  
  12.     {  
  13.         public static void InsertProduct(Product productObj)  
  14.         {  
  15.             ProductDAL.InsertProduct(productObj);  
  16.         }  
  17.         public static List<Product> GetAllProducts()  
  18.         {  
  19.             return ProductDAL.GetAllList();  
  20.         }  
  21.         public static List<Category> GetAllCategory()  
  22.         {  
  23.             return ProductDAL.GetAllCategoryList();  
  24.         }  
  25.     }  
  26. }  
Now I will use the BAL layer in the UI section to Insert a Product and Display a Product in the Grid.

Here's the Insert Product page.

Insert Product page

Here's the Grid record.

product id

Please find the UI aspx code:
  1. <table>  
  2.    <tr>  
  3.        <td colspan="2">  
  4.            <asp:Label ID="lblMesg" runat="server"></asp:Label>  
  5.   
  6.        </td>  
  7.    </tr>  
  8.    <tr>  
  9.        <td>Product Name</td>  
  10.        <td><asp:TextBox ID="txtProductName" runat="server"></asp:TextBox></td>  
  11.    </tr>  
  12.     <tr>  
  13.         <td>  
  14.             Category  
  15.         </td>  
  16.         <td>  
  17.             <asp:DropDownList ID="ddlCategory" runat="server"></asp:DropDownList>  
  18.         </td>  
  19.     </tr>  
  20.    <tr>  
  21.        <td colspan="2">  
  22.           <asp:Button ID="btnSubmit" runat="server" Text="Save" OnClick="btnSubmit_Click" />  
  23.        </td>  
  24.    </tr>  
  25. lt;/table>  
Here's the UI GridView.
  1. <asp:GridView ID="grdData" runat="server" AutoGenerateColumns="false" >  
  2.         <Columns>  
  3.            <asp:BoundField DataField="ProductID" HeaderText="ProductID"/>  
  4.               <asp:BoundField DataField="ProductName" HeaderText="ProductName"/>  
  5.                 
  6.               
  7.         </Columns>  
  8. </asp:GridView>  
Let's bind the category dropdownlist and GridView in the Page load.

Here's the method for binding the dropdownlist category.
  1. public void BindCategory()  
  2. {  
  3.   
  4.     ddlCategory.DataSource = ProductBAL.GetAllCategory();  
  5.     ddlCategory.DataValueField = "CategoryID";  
  6.     ddlCategory.DataTextField = "CategoryName";  
  7.     ddlCategory.DataBind();  
  8. }  
Here's the method to bind the GridView.
  1. public void BindGrid()  
  2. {  
  3.    grdData.DataSource = ProductBAL.GetAllProducts();  
  4.    grdData.DataBind();  
  5. }  
On the page load we have passed just the following two methods.
  1. protected void Page_Load(object sender, EventArgs e)  
  2. {  
  3.     if (!Page.IsPostBack)  
  4.     {  
  5.         BindGrid();  
  6.         BindCategory();  
  7.     }  
  8. }  
Then we will create on button click to insert product data. Here's the code.
  1. protected void btnSubmit_Click(object sender, EventArgs e)  
  2. {  
  3.     if(!string.IsNullOrEmpty(txtProductName.Text.ToString()))  
  4.     {  
  5.         Product productObj = new Product();  
  6.         productObj.ProductName = txtProductName.Text.ToString();  
  7.         productObj.ProductCategory = new Category();  
  8.         productObj.ProductCategory.CategoryID = Convert.ToInt32(ddlCategory.SelectedValue.ToString());  
  9.         try  
  10.         {  
  11.             ProductBAL.InsertProduct(productObj);  
  12.             lblMesg.Text = "Insert SucessFully";  
  13.             BindGrid();  
  14.         }catch(Exception ex)  
  15.         {  
  16.             lblMesg.Text = ex.Message.ToString();  
  17.         }  
  18.          
  19.     }  
  20. }  
Also please find the attached source code and database script. We need to change the connection string over the webconfig file depending on your server.

Thanks, happy coding. if any issue occurs, then please specify it in the comments section.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs