Home › ADO.NET › Stored Procedures, Views and Transaction Error handling with Code First Entity Framework

Stored Procedures, Views and Transaction Error handling with Code First Entity Framework

By Christos S. on August 17, 2013 • ( 2 )

Code First doesn’t support Stored Procedure mapping by default, at least in the way Database First does (from the designer window). More over, it has an unusuall way to map a Domain Model class to a database view. This post will show you how to:

Map a domain model to a database View
Call a stored procedure via the DbContext
Handle database transactions errors

Before starting building the application, I strongly recommend those who are new to Code First read the first two parts of these series, Part 1 and Part 2. I always use Visual Studio 2012 for my applications but this time due to circumstances, I will build the project in VS 2010 SP1. That shouldn’t be a problem though, for those who want to use VS 2012. Let’s start.

Create a blank solution named Corporation and add a c# class library project to it, named Model. This will be our Domain Model project. Add three classes named Product, Order and AvailableProduct respectively. Make sure you paste the respective code in each of these classes, as follow.

namespace Model
{
    // Represents a Product object
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public string Description { get; set; }

        public List<Order> Orders { get; set; }
    }
}

namespace Model
{
    // Represents an Order for a product
    public class Order
    {
        public int OrderId { get; set; }
        public DateTime OrderDate { get; set; }
        public string CustomerName { get; set; }
        public int Quantity { get; set; }

        public int ProductId { get; set; }
        public Product Product { get; set; }
    }
}

namespace Model
{
    //This class will map a database View
    public class AvailableProduct
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public string Description { get; set; }
    }
}

Each product can have many Orders and each order is associated with only one Product. If you have read the second part of these series you will already have figured out that the ProductId property in an order cannot be null. The third class we added in the domain model AvailableProduct is going to map a database view later. Our domain model is ready for use, let’s create the DataAccess layer now. Add a new C# class library again in your solution, named DataAccess. Add a reference to the Model project and make sure you install Entity Framework through the Nuget Packages Manager... We are going to use the pattern, that each Domain class has it’s configuration class. Add a new folder named DbConfigurations into the DataAccess project and add a new class to that folder, with the same name DbConfigurations. Paste the following code.

using System.Data.Entity.ModelConfiguration;
using Model;

namespace DataAccess.DbConfigurations
{
    // Configuration for Product Domain class
    public class ProductConfiguration : EntityTypeConfiguration<Product>
    {
        public ProductConfiguration()
        {
            Property(p => p.ProductId).IsRequired();
            Property(p => p.Name).IsRequired();
            Property(p => p.Price).IsRequired();
        }
    }

    // Configuration for Order Domain class
    public class OrderConfiguration : EntityTypeConfiguration<Order>
    {
        public OrderConfiguration()
        {
            Property(o => o.OrderId).IsRequired();
            Property(o => o.Quantity).IsRequired();
            Property(o => o.OrderDate).HasColumnType("date");
        }
    }

    // Configuration for vWGetAvailableProducts
    public class AvailableProductConfiguration : EntityTypeConfiguration<AvailableProduct>
    {
        public AvailableProductConfiguration()
        {
            ToTable("vWGetAvailableProducts");
            HasKey(av => av.ProductId);
        }
    }
}

I won’t comment the first two configuration classes for the Product and Order domain classes, since we have seen them in previous posts. Though I would like to talk a litle about the third configuration class AvailableProductConfiguration. We build AvailableProduct domain class in order to map a database view, named vWGetAvailableProducts. This class can represent a Product without it’s orders. Have in mind though, that at this point we haven’t even build the database so we cannot talk about views or even stored procedures. So the idea is this: For the first time we start the application, Entity Framework will create three tables, one for each domain class. Then we are going to delete manually from the database the vWGetAvailableProducts table and create a database View with the same name. That way, the following property will refer to the View instead of a table. So simple.

public DbSet<AvailableProduct> vWGetAvailableProducts { get; set; }

In order to complete the DataAccess layer, add a new class named ProductsContext with the following code.

using System.Data.Entity;
using Model;
using DataAccess.DbConfigurations;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class ProductsContext : DbContext
    {
        public DbSet<Product> Products { get; set; }
        public DbSet<Order> Orders { get; set; }
        public DbSet<AvailableProduct> vWGetAvailableProducts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new ProductConfiguration());
            modelBuilder.Configurations.Add(new OrderConfiguration());
            modelBuilder.Configurations.Add(new AvailableProductConfiguration());
        }
    }
}

Time to create the client project. Add new ASP.NET Empty Application in your solution, named WebClient. Make sure you add references both for the DataAccess and the Model projects and also install Entity Framework through the NuGet Packages Manager. Add a new Application Configuration file named Global.asax and paste the following code.

using System.Data.Entity;
using DataAccess;
using Model;

namespace WebClient
{
    public class Global : System.Web.HttpApplication
    {

        protected void Application_Start(object sender, EventArgs e)
        {
            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<ProductsContext>());
            SeedDatabase();
        }

        private static void SeedDatabase()
        {
            Product p1 = new Product
            {
                ProductId = 1,
                Name = "Book",
                Description = "A book about life",
                Price = 35m
            };
            Product p2 = new Product
            {
                ProductId = 2,
                Name = "Pencil",
                Description = "A simple pencil",
                Price = 3m
            };
            Product p3 = new Product
            {
                ProductId = 3,
                Name = "Pen",
                Description = "A pen that can write in water",
                Price = 5m
            };
            Product p4 = new Product
            {
                ProductId = 4,
                Name = "Notebook",
                Description = "A notebook to keep your notes",
                Price = 15m
            };

            using (var context = new ProductsContext())
            {
                if (context.Products.Count() == 0)
                {
                    context.Products.Add(p1);
                    context.Products.Add(p2);
                    context.Products.Add(p3);
                    context.Products.Add(p4);

                    context.SaveChanges();
                }
            }
        }
// Rest of code ommitted...

That way we can initialize our database with some mock Products. Products will be added only if there are no products in the Products table.
Add a new Web Form page, named Default.aspx and paste the following code.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebClient.Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            color: #3366FF;
        }
        .style3
        {
            background-color: #FFFFFF;
        }
        .style4
        {
            color: #3366FF;
            background-color: #FFFFFF;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h1 class="style1"><em>Welcome to our Market!</em></h1>

        <asp:GridView ID="grvAvProducts" runat="server" BackColor="White" 
            BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <RowStyle ForeColor="#000066" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#007DBB" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#00547E" />
        </asp:GridView>
        <strong>
        <br class="style4" />
        </strong><span class="style4"><strong><span class="style3">Make an order:</span></strong></span>
        <br />Product ID:
        <asp:DropDownList ID="ddlProductId" runat="server" AppendDataBoundItems="True">
        </asp:DropDownList> <br /> Quantity:&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:DropDownList ID="ddlQuantity" runat="server">
            <asp:ListItem>1</asp:ListItem>
            <asp:ListItem>2</asp:ListItem>
            <asp:ListItem>5</asp:ListItem>
            <asp:ListItem>10</asp:ListItem>
        </asp:DropDownList><br /> Customer:&nbsp;&nbsp;
        <asp:TextBox ID="txtCustName" runat="server"></asp:TextBox>
        <br /> 
        <asp:Button ID="btnOrder" runat="server" Text="Order" BackColor="#3366FF" 
            Font-Bold="True" onclick="btnOrder_Click" /><br />
        <asp:Label ID="lblOrder" runat="server" Text=""></asp:Label><br />
        <span class="style1"><strong>Orders:</strong></span>
        <asp:GridView ID="gdvOrders" runat="server" BackColor="White" 
            BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <RowStyle ForeColor="#000066" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#007DBB" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#00547E" />
        </asp:GridView>
        <br />
    </div>
    </form>
</body>
</html>

In the code behind file add the following code.

using DataAccess;
using Model;

namespace WebClient
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                using (var context = new ProductsContext())
                {
                    List<AvailableProduct> availableroducts = context.vWGetAvailableProducts.ToList();
                    grvAvProducts.DataSource = availableroducts;
                    grvAvProducts.DataBind();
                    List<Order> availableOrders = context.Orders.ToList();
                    gdvOrders.DataSource = availableOrders;
                    gdvOrders.DataBind();
                    List<int> productIds = new List<int>();
                    ddlProductId.Items.Clear();
                    foreach (AvailableProduct avp in availableroducts)
                        productIds.Add(avp.ProductId);
                    ddlProductId.DataSource = productIds;
                    ddlProductId.DataBind();
                    lblOrder.Text = "";
                }
            }
        }

        protected void btnOrder_Click(object sender, EventArgs e)
        {
            using (var context = new ProductsContext())
            {
                try
                {
                    context.ProcessOrder(Int32.Parse(ddlProductId.SelectedItem.Text),
                        txtCustName.Text, Int32.Parse(ddlQuantity.SelectedValue));
                    lblOrder.Text = "Order has been approved..";
                }
                catch (Exception ex)
                {
                    lblOrder.Text = ex.Message;
                }
            }
            RefreshOrdersGrid();
        }

        private void RefreshProductsGrid()
        {
            using (var context = new ProductsContext())
            {
                List<AvailableProduct> availableProducts = context.vWGetAvailableProducts.ToList();
                grvAvProducts.DataSource = availableProducts;
                grvAvProducts.DataBind();
            }
        }

        private void RefreshOrdersGrid()
        {
            using (var context = new ProductsContext())
            {
                List<Order> availableOrders = context.Orders.ToList();
                gdvOrders.DataSource = availableOrders;
                gdvOrders.DataBind();
            }
        }
    }
}

I know we wrote a lot of code but let me explain. Default page has two gridviews. The first one will call the database view and show all available products. The second one will show all orders that have been committed to database. The interesting part is how orders are commited to database. This is going to work through a stored procedure that we are going to write after the database is created. For now go and add the ProcessOrder function inside the ProductsContext class, after theOnModelCreating function.

public bool ProcessOrder(int ProductId, string CustomerName,int Quantity)
        {
            bool OrderProcessed = false;
            var productId = new SqlParameter
            {
                DbType = DbType.Int32,
                ParameterName = "@ProductId",
                Value = ProductId
            };

            var customerName = new SqlParameter
            {
                DbType = DbType.String,
                ParameterName = "@CustomerName",
                Value = CustomerName
            };

            var quantity = new SqlParameter
            {
                DbType = DbType.Int32,
                ParameterName = "@Quantity",
                Value = Quantity
            };
            try
            {
                this.Database.ExecuteSqlCommand("EXEC spMakeOrder " + ProductId
                    + "," + CustomerName + "," + Quantity);
                OrderProcessed = true;
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Order cannot be processed..");
            }
            return OrderProcessed;
        }

We are ready to go now. Build your application, set as start up project the WebClient and run it.

You should see the above image, and you will have noticed that there are no orders at the moment and no available products either through the view or the table. The important part is the database has been created and we can work with it now.
Delete the vWGetAvailableProducts table and run the following SQL queries against your database.

/* Create the View to get Available products */
CREATE VIEW vWGetAvailableProducts
AS
	SELECT * FROM Products

/* Create the stored procedure to make an order */
CREATE proc [dbo].[spMakeOrder]  
@ProductId int,  
@CustomerName nvarchar(50),  
@Quantity int  
as  
begin  
    insert into Orders  
    values (GETDATE(),@CustomerName,@Quantity,@ProductId)  
 return (select MAX(OrderId) from Orders)  
end

Now simple run your application again.. Notice that you got the database View results with the first gridview displaying all available products added in the Seed method and the first combobox was filled with the Id values of the available products. At this time you don’t have any orders in your database, so go and create one from your user interface.

There is one last thing I wanna show you. We will change slightly our stored procedure so that it fails each time is called. This way we will see how we can catch this database exception.

alter proc [dbo].[spMakeOrder]  
@ProductId int,  
@CustomerName nvarchar(50),  
@Quantity int  
as  
begin
	begin try
	 begin transaction  
      insert into Orders  
      values (GETDATE(),@CustomerName,@Quantity,15)  
     commit transaction
    end try 
    begin catch
      rollback transaction
      Raiserror('There is no such product in Database',
      16,1)
    end catch 
end

Build and run your website again. Try to make an order again. This time will fail. Notice that in our code we have already added Try – Catch blocks both in the ProcessOrder function and the btnOrder_Click click event.

That’s it, we saw how to call a stored procedure, we explained how to map a domain class to a database view and how to catch database errors inside a transaction. Download the project we created from here. I hope you enjoyed the post.