Executing stored procedures

Stored procedures sometimes get a bad rap from developers, given their long history. But we need to remember that, often, they are the most effective way to coalesce complex queries. Just don't let them store business logic.

Getting ready

We will be using the NuGet Package Manager to install the Entity Framework 4.1 assemblies.

The package installer can be found at http://nuget.org.

We will also be using a database for connecting the data and updating it.

Open the Improving Stored Procedures solution in the included source code examples.

How to do it...

  1. We start by adding a new unit test named TransactionTests to the test project. We make a test that connects to the database, adds an object, and then retrieves a count with a stored procedure, by using the following code:
    using DataAccess;
using DataAccess.Database;
using DataAccess.Queries;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Test.Properties;

namespace Test
{
  [TestClass]
  public class StoredProcedureTests
  {

    [TestMethod]
    public void ShouldAllowCallingStoredProcedureAndGettingResult()
    {
      //Arrange
      var init = new Initializer();
      var context = new BlogContext(Settings.Default.BlogConnection);
      init.InitializeDatabase(context);
      var repo = new BlogRepository(context);

      //Act
      var count = repo.GetBlogCount();

      //Assert
      Assert.AreEqual(1, count);
    }
  }

}
  2. Add an initializer to the DataAccess project in the Database folder with the following code to set up the data:
    using System;
using System.Data.Entity;
using BusinessLogic;

namespace DataAccess.Database
{

  public class Initializer : DropCreateDatabaseAlways<BlogContext>
  {
    public Initializer()
    {

    }
    protected override void Seed(BlogContext context)
    {
      context.Database.ExecuteSqlCommand(StoredProcedureDefinitions.GetBlogCountDefinition);
      context.Set<Blog>().Add(new Blog()
      {
        CreationDate = DateTime.Now,
        ShortDescription = "Testing",
        Title = "Test Blog"
      });
      context.SaveChanges();
    }
  }
}
  3. In the DataAccess project, we add a new C# class named StoredProcedureDefinitions with the following code, so we recreate our stored procedures with each database creation:
    namespace DataAccess.Database
{
  public static class StoredProcedureDefinitions
  {
    public static string GetBlogCountDefinition = @"CREATE PROCEDURE [dbo].[GetBlogCount]ASBEGINSET NOCOUNT ON;SELECT Count(*) FROM dbo.Blogs
      END
      ";
  }
}
  4. In the BusinessLogic project, add a new C# class named Blog with the following code:
    using System;

namespace BusinessLogic
{
  public class Blog
  {
    public int Id { get; set; }
    public DateTime Creationdate { get; set; }
    public string ShortDescription { get; set; }
    public string Title { get; set; }
    public double Rating { get; set; }
  }
}
  5. Add a Mapping folder to the DataAccess project, and add a BlogMapping class to the folder with the following code: 
    using System.ComponentModel.DataAnnotations;
using System.Data.Entity.ModelConfiguration;
using BusinessLogic;

namespace DataAccess.Mappings
{
  public class BlogMapping : EntityTypeConfiguration<Blog>
  {
    public BlogMapping()
    {
      this.ToTable("Blogs");
      this.HasKey(x => x.Id);
      this.Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
        .HasColumnName("BlogId");

      this.Property(x => x.Title).IsRequired().HasMaxLength(250);
      this.Property(x => x.Creationdate).HasColumnName("CreationDate").IsRequired();
      this.Property(x => x.ShortDescription).HasColumnType("Text").IsMaxLength().IsOptional().HasColumnName("Description");
     }

  }
}
  6. Modify the BlogContext class to contain the new mappings and a DbSet property for Blog with the following code:
    using System;
using System.Data.Entity;
using System.Linq;
using BusinessLogic;
using DataAccess.Mappings;

namespace DataAccess
{
  public class BlogContext : DbContext, IUnitOfWork
  {
    public BlogContext(string connectionString) : base(connectionString)
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Configurations.Add(new BlogMapping());
      base.OnModelCreating(modelBuilder);
    }

    public IQueryable<T> Find<T>() where T : class
    {
      return this.Set<T>();
    }

    public void Refresh()
    {
      this.ChangeTracker.Entries().ToList().ForEach(x=>x.Reload());
    }

    public void Commit()
    {
      this.SaveChanges();
    }
  }
}
  7. In the DataAccess project, we add a new folder named Queries with a new C# class named StoredProcedures, with the following code:
    using System.Linq;

namespace DataAccess.Queries
{
  public static class StoredProcedures
  {
    public static int GetBlogCount(this IBlogRepository repository)
    {
      var items = repository.UnitOfWork.Context.Database.SqlQuery<int>(@"GetBlogCount");
      var count = items.FirstOrDefault();
      return count;
    }
  }
}
  8. Run our test, and see how it works.

How it works...

We start-off our solution, as always, with a test that ensures that our features are properly implemented, and that we have demonstrated the functionality required.

In this example, for clarity alone, we have put the definition of the stored procedure into our code. This is not a requirement, and we could have easily let the stored procedure be defined in an embedded SQL file, or have it already exist in the database.

Once the stored procedure is created, we put an extension method on the repository that allows us to invoke the SQL statement from the Database object on the DbContext. We have to drill through a couple of layers, but this is preferable to surfacing a raw DbContext. We want that layered abstraction in this case.

Entity Framework takes the SQL query, executes it, and then tries to map the return operation into the type that we have given it. In this case, we told it that the return parameter is of the integer type.

There's more...

When dealing with stored procedures in the Entity Framework code first model, we have to be aware of several things that could hamstring us, and cause runtime errors throughout our application.

Handling return type mapping

If the type that is returned cannot be parsed into the type that it is expecting, we will get a runtime error. This error will bubble from the framework and will need to be guarded against at the point that we call the stored procedure.

Remembering access rules

If we are calling a stored procedure, we need to make sure that we schema qualify any stored procedure name that is outside of the dbo schema. We will also need to make sure that we have execute permissions to that stored procedure, just as we would if we were calling it directly from ADO.NET.

See also

In this chapter:

  • Retrieving entities with stored procedures
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset