Updating entities with stored procedures

Sometimes, the policies of a given enterprise require the use of stored procedures for handling insert and update opeartions to the database. While we don't necessarily recommend this practice, this recipe shows how to ensure that Entity Framework complies with such a policy.

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 to the data and updating it.

Open the Improving Updating Entities with 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 an entity with a stored procedure, by using the following code:
    using System;
using System.Linq;
using BusinessLogic;
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 ShouldAllowEditsThroughUpsert()
    {
      //Arrange
      var init = new Initializer();
      var context = new BlogContext(Settings.Default.BlogConnection);
      init.InitializeDatabase(context);
      context.Set<Blog>().Add(new Blog
      {
        CreationDate = DateTime.Today.AddDays(-1),
        Rating = 0,
        ShortDescription = "Dummy",
        Title = "Dummy"
      });
      context.SaveChanges();

      //Act
      var blog = context.Set<Blog>().FirstOrDefault();
      blog.Title = "TestingSP";
      context.SaveChanges();

      //Assert
      Assert.IsTrue(context.Set<Blog>().Any(x => x.Title == "TestingSP"));
    }

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

      //Act
      context.Set<Blog>().Add(new Blog()
      {
        CreationDate = DateTime.Now,
        Rating = 1.5,
        ShortDescription = "Testing",
        Title = "SPInsert"
      });
      context.SaveChanges();

      //Assert
      Assert.IsTrue(context.Set<Blog>().Any(x => x.Title == "SPInsert"));
    }
  }

}
  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.UpsertBlogDefinition);
      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 UpsertBlogDefinition = @"CREATE PROCEDURE UpsertBlog
      @Id int = 0,
      @CreationDate date,
      @Description text,
      @Title nvarchar(250),
      @Rating float
     AS
      BEGIN
        SET NOCOUNT ON;
        DECLARE @return_status int;
        IF EXISTS (SELECT * FROM BLOGS WHERE BlogId = @Id)
          BEGIN
            UPDATE [EFCookbook].[dbo].[Blogs]
            SET [Description] = @Description,
             [Title] = @Title,
             [Rating] = @Rating
            WHERE BlogId = @Id
            SET @return_status = @Id
          END
        Else
          BEGIN
            INSERT INTO [EFCookbook].[dbo].[Blogs]
              ([CreationDate]
              ,[Description]
              ,[Title]
              ,[Rating])
            VALUES
              (@CreationDate,
              @Description,
              @Title,
              @Rating)
            SET @return_status = SCOPE_IDENTITY()
              END
              Select 'Return Status' = @return_status;
      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. Notice the override on the SaveChanges utilizes the stored procedure upsert:
    using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using BusinessLogic;
using DataAccess.Mappings;
using DataAccess.Queries;

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();
    }

    public override int SaveChanges()
    {
      int storedProcChanges = 0;
      var changeSet = ChangeTracker.Entries<Blog>();

      if (changeSet != null)
      {
        foreach (DbEntityEntry<Blog> entry in changeSet)
        {
          switch (entry.State)
          {
            case EntityState.Added:
            case EntityState.Modified:
            {
              var id = this.UpsertBlog(entry.Entity);
              entry.State = EntityState.Detached;
              if(id != -1) entry.Entity.Id = id;
              this.Set<Blog>().Attach(entry.Entity);
              storedProcChanges++;
            }
            break;
          }
        }
      }

      return base.SaveChanges() + storedProcChanges;
    }
    public DbContext Context { get { return this; } }
    public void Add<T>(T item) where T : class
    {
      this.Set<T>().Add(item);
    }
  }
}
  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.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using BusinessLogic;
using DataAccess.Database;

namespace DataAccess.Queries
{
  public static class StoredProcedures
  {
    private static string _upsertQuery = @"
      DECLARE @return_value int

      EXEC @return_value = [dbo].[UpsertBlog]
        @Id,
        @CreationDate,
        @Description,
        @Title,
        @Rating

      SELECT 'Return Value' = @return_value
    ";
    public static Blog GetBlog(this IBlogRepository repository, int blogId)
    {
      var items = repository.UnitOfWork.Context.Database.SqlQuery<Blog>(@"GetBlog @BlogId", new SqlParameter("BlogId",blogId) );

      var blog = items.FirstOrDefault();
      if (blog != null && repository.UnitOfWork.Context.Entry(blog) == null) repository.UnitOfWork.Context.Set<Blog>().Attach(blog);
      return blog;
    }
    public static int UpsertBlog(this DbContext context, Blog blog)
    {
      return context.Database.SqlQuery<int>(_upsertQuery,new object[]
        {
          new SqlParameter("Id",blog.Id),
          new SqlParameter("CreationDate",blog.CreationDate),
          new SqlParameter("Description",blog.ShortDescription),
          new SqlParameter("Title",blog.Title),
          new SqlParameter("Rating",blog.Rating)
        }).First();
    }
  }
}
  8. Run our test, and see how it works.

How it works...

We start-off our solution with a couple of tests that ensure we have achieved the goal, which, in this case, is to insert or update an entity without the use of generated SQL statements, but instead by using an update and insert stored procedure.

We move to setting up an initializer that will create the stored procedure for us every time our test database is dropped and recreated. We also provide a class to centralize these definitions in case of changes. This can load from .txt or .sql files just as easily. This ensures that the stored procedure will be there when we call it.

We then move to defining the blog and the blog mapping for our context. These will not be used by our context for SQL generation on update and insert, but will be used for selections and deletes. These two pieces can, over course, be changed as well, but would be overkill for this recipe.

The BlogContext is where we are able to modify the save behavior, by overriding the SaveChanges method. We are able to pull all of the Blog object state entries from the change tracker. This give us the ability to check for added or modified blog objects, execute our upsert stored procedure, and then clear the object modified state, so that the generated SQL doesn't pick up those changes. Notice how we detach the objects before modifying the ID. The reason for that is, the context will return an error if you try to change the key of an attached object.

There's more...

When we are forced to deal with stored procedures, we want to make sure that we avoid some serious runtime errors with the following suggestions and support:

Manually changing states

We have to be very careful with this, as the runtime error chances increase drastically. The following are a few of the common error scenarios to avoid:

  • Adding an object with the same key as one already tracked
  • Trying to modify a tracked object's key
  • Trying to modify the state of an added object without first detaching and changing its ID

Abstract usage

If we are planning to use the stored procedure updates for more than one object type, then we should abstract the query string and the parameter collection behind a factory, so that the SqlQuery<> usage in the context can be as clean and generic as possible.

Extensions for stored procedure support

There are several open source libraries that make this easier to accomplish, here are a couple of them:

http://www.codeproject.com/KB/database/CodeFirstStoredProcedures.aspx

http://archive.msdn.microsoft.com/EFExtensions

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