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.

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

CREATE TABLE [dbo].[Category](
    [CategoryID] [bigint] IDENTITY(1,1) NOT NULL,
      NULL,
    CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
    (
        [CategoryID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Product](
    [ProductID] [bigint] IDENTITY(1,1) NOT NULL,
    [CategoryID] [bigint] NULL,
      NULL,
    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
    (
        [ProductID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [fk_CategoryID] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO

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”.

CREATE PROCEDURE [dbo].[UspInsertProduct]
(
    @ProductName varchar(250) = null
    , @CategoryID bigint null
)
AS
BEGIN
    INSERT INTO [dbo].[Product]
               ([ProductName],
                [CategoryID])
        VALUES
               (@ProductName
               , @CategoryID)
END

Select all the Products using the Stored Procedure “UspSelectProduct”.

CREATE PROCEDURE [dbo].[UspSelectProduct]
AS
BEGIN
    SELECT 
        Product.ProductID AS ProductID,
        Product.CategoryID AS CategoryID,
        Product.ProductName AS ProductName,
        Category.CategoryName AS CategoryName
    FROM Category
    INNER JOIN Product ON Category.CategoryID = Product.CategoryID
END

Create another Stored Procedure for all the categories.

CREATE PROCEDURE [dbo].[uspSelectCategory]
AS
BEGIN
    SELECT [CategoryID]
         ,[CategoryName]
    FROM [Category]
END

We have a Product table with 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, and UI.

In the preceding image, you find there are various class libraries.

• BALClassLibary: This class library is for the Business Layer.
• DALClassLibary: This class library is for database operations like CRUD logic (data access layer).
• EntityClassLibary: This class library is for maintaining all the properties of 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

Step 3. 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>

<dataConfiguration defaultDatabase="CommunityConnectionString"/>

Here's the connection string.

<add name="CommunityConnectionString" 
     connectionString="Data Source=SQLEXPRESS;Initial Catalog=Community;Integrated Security=True" 
     providerName="System.Data.SqlClient" />

Note. default database name equal connection string name.

Step 4. At the EntityClassLibrary, create two entity classes, Product and Category.

// Category Entity
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EntityClassLibrary
{
    public class Category
    {
        public Int32? CategoryID { get; set; }
        public string CategoryName { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EntityClassLibrary
{
    public class Product
    {
        public Int32? ProductID { get; set; }
        public string ProductName { get; set; }
        public Category ProductCategory { get; set; }
    }
}

Step 5. At the UtilityClassLibrary, let's create one class DataBaseConstat that has all the constant values.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UtilityClassLibrary
{
    public static class DataBaseConstat
    {
        public static string ConnectionString = "CommunityConnectionString";
        public static string ProductID = "ProductID";
        public static string ProductName = "ProductName";
        public static string CategoryID = "CategoryID";
        public static string CategoryName = "CategoryName";
        public static string UspInsertProduct = "UspInsertProduct";
        public static string UspSelectProduct = "UspSelectProduct";
        public static string uspSelectProductDetail = "uspSelectProductDetail";
        public static string uspSelectCategory = "uspSelectCategory";

    }
}

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

Create a List method for getting Category records

Here we have added two class library references, UtilityClassLibary and EntityClassLibary.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using EntityClassLibrary;
using UtilityClassLibrary;
using System.Data.Common;
using System.Data;

Then we will go to create a List method for getting the Category records here.

public static List<Category> GetAllCategoryList()
{
    Database database;
    DbCommand dbCommand;
    List<Category> CategoryList = null;

    CategoryList = new List<Category>();
    DatabaseProviderFactory factory = new DatabaseProviderFactory();
    database = factory.Create(DataBaseConstat.ConnectionString);
    dbCommand = database.GetStoredProcCommand(DataBaseConstat.uspSelectCategory);

    using (IDataReader objReader = database.ExecuteReader(dbCommand))
    {
        CategoryList = CreateCategory(objReader);
    }

    if (CategoryList == null)
        return null;
    else
        return CategoryList;
}

Then we will create CreateCategory data for readers.

private static List<Category> CreateCategory(IDataReader objReader)
{
    List<Category> categoryList = new List<Category>();
    Category category;
    bool isnull = true;
    while (objReader.Read())
    {
        isnull = false;
        category = new Category();
        category.CategoryID = objReader[DataBaseConstat.CategoryID] != DBNull.Value ?
            Convert.ToInt32(objReader[DataBaseConstat.CategoryID]) : (int?)null;
        category.CategoryName = objReader[DataBaseConstat.CategoryName] != DBNull.Value ?
            Convert.ToString(objReader[DataBaseConstat.CategoryName]) : null;

        categoryList.Add(category);

    }
    if (isnull) return null;
    else return categoryList;
}

Create insert methods for the products.

public static void InsertProduct(Product productObj)
{
    Database database = null;
    DbCommand dbCommand = null;
    try
    {
        DatabaseProviderFactory factory = new DatabaseProviderFactory();
        database = factory.Create(DataBaseConstat.ConnectionString);
        dbCommand = database.GetStoredProcCommand(DataBaseConstat.UspInsertProduct);
        database.AddInParameter(dbCommand, DataBaseConstat.ProductName, DbType.String, productObj.ProductName);
        database.AddInParameter(dbCommand, DataBaseConstat.CategoryID, DbType.Int32, productObj.ProductCategory.CategoryID);
        database.ExecuteNonQuery(dbCommand);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        database = null;

    }
}

Create list methods for the product.

public static List<Product> GetAllList()
{
    Database database;
    DbCommand dbCommand;
    List<Product> ProductList = null;
    ProductList = new List<Product>();
    DatabaseProviderFactory factory = new DatabaseProviderFactory();
    database = factory.Create(DataBaseConstat.ConnectionString);
    dbCommand = database.GetStoredProcCommand(DataBaseConstat.UspSelectProduct);
    using (IDataReader objReader = database.ExecuteReader(dbCommand))
    {
        ProductList = CreateProduct(objReader);
    }
    if (ProductList == null)
        return null;
    else
        return ProductList;
}

Then we go to create CreateProduct data for the readers.

private static List<Product> CreateProduct(IDataReader objReader)
{
    List<Product> productList = new List<Product>();
    Product product;
    bool isnull = true;
    while (objReader.Read())
    {
        isnull = false;
        product = new Product();
        product.ProductID = objReader[DataBaseConstat.ProductID] != DBNull.Value ?
            Convert.ToInt32(objReader[DataBaseConstat.ProductID]) : (int?)null;
        product.ProductName = objReader[DataBaseConstat.ProductName] != DBNull.Value ?
            Convert.ToString(objReader[DataBaseConstat.ProductName]) : null;
        product.ProductCategory = new Category();
        product.ProductCategory.CategoryID = objReader[DataBaseConstat.CategoryID] != DBNull.Value ?
            Convert.ToInt32(objReader[DataBaseConstat.CategoryID]) : (int?)null;
        product.ProductCategory.CategoryName = objReader[DataBaseConstat.CategoryName] != DBNull.Value ?
            Convert.ToString(objReader[DataBaseConstat.CategoryName]) : null;
        productList.Add(product);
    }
    if (isnull) return null;
    else return productList;
}

Then we create the BAL layer.

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

Code for BAL Layer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DALClassLibrary;
using EntityClassLibrary;

namespace BALClassLibrary
{
    public static class ProductBAL
    {
        public static void InsertProduct(Product productObj)
        {
            ProductDAL.InsertProduct(productObj);
        }

        public static List<Product> GetAllProducts()
        {
            return ProductDAL.GetAllList();
        }

        public static List<Category> GetAllCategory()
        {
            return ProductDAL.GetAllCategoryList();
        }
    }
}

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.

Here's the Grid record.

Please find the UI aspx code.

<table>
    <tr>
        <td colspan="2">
            <asp:Label ID="lblMesg" runat="server"></asp:Label>
        </td>
    </tr>
    <tr>
        <td>Product Name</td>
        <td><asp:TextBox ID="txtProductName" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td>
            Category
        </td>
        <td>
            <asp:DropDownList ID="ddlCategory" runat="server"></asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Button ID="btnSubmit" runat="server" Text="Save" OnClick="btnSubmit_Click" />
        </td>
    </tr>
</table>

Here's the UI GridView.

<asp:GridView ID="grdData" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" />
    </Columns>
</asp:GridView>

Let's bind the category dropdown list and GridView in the Page load.

Here's the method for binding the dropdown list category.

public void BindCategory()
{
    ddlCategory.DataSource = ProductBAL.GetAllCategory();
    ddlCategory.DataValueField = "CategoryID";
    ddlCategory.DataTextField = "CategoryName";
    ddlCategory.DataBind();
}

Here's the method to bind the GridView.

public void BindGrid()
{
    grdData.DataSource = ProductBAL.GetAllProducts();
    grdData.DataBind();
}

On the page load, we have passed just the following two methods.

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        BindGrid();
        BindCategory();
    }
}

Then we will create on button click to insert product data. Here's the code.

protected void btnSubmit_Click(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(txtProductName.Text))
    {
        Product productObj = new Product();
        productObj.ProductName = txtProductName.Text;
        productObj.ProductCategory = new Category();
        productObj.ProductCategory.CategoryID = Convert.ToInt32(ddlCategory.SelectedValue);

        try
        {
            ProductBAL.InsertProduct(productObj);
            lblMesg.Text = "Insert Successfully";
            BindGrid();
        }
        catch (Exception ex)
        {
            lblMesg.Text = ex.Message;
        }
    }
}

Also please find the attached source code and database script. We need to change the connection string over the web config file depending on your server.

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