Entity Framework应用：使用Code First模式管理存储过程

在EF中使用存储过程和使用视图是很相似的，一般会使用Database对象上的两个方法：SqlQuery和ExecuteSqlCommand。为了从存储过程中读取很多数据行，我们只需要定义一个类，我们会将检索到的所有数据行物质化到该类实例的集合中。比如，从下面的存储过程读取数据：

CREATE PROCEDURE [dbo].[SelectBooks]
@BookTypeName AS NVARCHAR(10)
AS
BEGIN
select B.Name,B.Author,B.PublicationDate,T.BookTypeName from Books as B
join BookTypes as T on B.BookTypeId=T.BookTypeId where T.BookTypeName=@BookTypeName
END

 1、定义实体类

Book实体类定义如下：

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

namespace CodeFirstProcedureApp.Model
{
    public class Book
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public string Author { get; set; }

        public DateTime PublicationDate { get; set; }

        public virtual BookType BookType { get; set; }
    }
}

 BookType实体类定义如下：

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

namespace CodeFirstProcedureApp.Model
{
    public class BookType
    {
        public BookType()
        {
            Books = new HashSet<Book>();
        }

        public int BookTypeId { get; set; }

        public string BookTypeName { get; set; }

        public virtual ICollection<Book> Books { get; set; }
    }
}

 2、定义与存储过程结果匹配的实体类

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

namespace CodeFirstProcedureApp.Model
{
    public class BookFromProcedure
    {
        public string Name { get; set; }

        public string Author { get; set; }

        public DateTime PublicationDate { get; set; }

        public string BookTypeName { get; set; }
    }
}

 注意：类的属性名必须和存储过程中定义的列名一致。

3、创建种子初始化器类

using CodeFirstProcedureApp.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstProcedureApp.EF
{
    public class Initializer : DropCreateDatabaseIfModelChanges<EFDbContext>
    {
        protected override void Seed(EFDbContext context)
        {
            // 创建初始化数据
            BookType bookType = new BookType()
            {
                BookTypeName = "文学小说",
                Books = new List<Book> 
                {
                  new Book(){Name="人间失格",Author="太宰治",PublicationDate=DateTime.Parse("2015-08-01")},
                  new Book(){Name="解忧杂货店",Author="东野圭吾",PublicationDate=DateTime.Parse("2014-05-01")},
                  new Book(){Name="追风筝的人",Author="卡勒德胡赛尼",PublicationDate=DateTime.Parse("2006-08-01")},
                  new Book(){Name="百年孤独",Author="加西亚马尔克斯",PublicationDate=DateTime.Parse("2011-06-01")},
                  new Book(){Name="霍乱时期的爱情",Author="加西亚马尔克斯",PublicationDate=DateTime.Parse("2015-06-01")}
                }
            };

            BookType bookType2 = new BookType()
            {
                BookTypeName = "科学",
                Books = new List<Book> 
                {
                   new Book(){Name="人类简史",Author="尤瓦尔赫拉利",PublicationDate=DateTime.Parse("2017-01-01")}
                }
            };

            context.BookTypes.Add(bookType);
            context.BookTypes.Add(bookType2);
            base.Seed(context);
        }
    }
}

 4、定义数据上下文类

using CodeFirstProcedureApp.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstProcedureApp.EF
{
    public class EFDbContext :DbContext
    {
        public EFDbContext()
            : base("name=AppConnection")
        {
            Database.SetInitializer(new Initializer());
        }

        // 添加到数据上下文中
        public DbSet<Book> Books { get; set; }

        public DbSet<BookType> BookTypes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // 配置表名和主键
            modelBuilder.Entity<Book>().ToTable("Books").HasKey(p => p.Id);
            modelBuilder.Entity<BookType>().ToTable("BookTypes").HasKey(p => p.BookTypeId);
            // 设置实体关系
            //  BookType和 Books 一对多关系 外键：BookTypeId
            modelBuilder.Entity<BookType>().HasMany(p => p.Books).WithRequired(t => t.BookType)
                .Map(m =>
                {
                    m.MapKey("BookTypeId");
                });
            base.OnModelCreating(modelBuilder);
        }
    }
}

 5、运行程序

使用SQL语句创建存储过程：

var createSql = @"CREATE PROCEDURE [dbo].[SelectBooks]
                                    @BookTypeName AS NVARCHAR(10)
                                    AS
                                    BEGIN
                                    select B.Name,B.Author,B.PublicationDate,T.BookTypeName from Books as B
                                    join BookTypes as T on B.BookTypeId=T.BookTypeId where T.BookTypeName=@BookTypeName
                                    END";
context.Database.ExecuteSqlCommand(createSql);

 查看数据库：

调用存储过程查询数据

注意：在使用存储过程前，先要在存储过程中执行该存储过程或者使用上面的程序生成存储过程。

var sql = "SelectBooks {0}";
var books = context.Database.SqlQuery<BookFromProcedure>(sql, "文学小说");
books.ToList().ForEach(p =>
{
      Console.WriteLine("BookName:" + p.Name + "     Author:" + p.Author + "    BookTypeName:" + p.BookTypeName + "     PublicationDate:" + p.PublicationDate);
});

 

在上面的代码中，我们制定了使用哪个类读取查询的结果，创建SQL语句时，也为存储过程的参数提供了一个格式化占位符，调用SqlQuery时为那个参数提供了一个值。假如要提供多个参数的话，多个格式化占位符必须要用逗号分隔，还要给SqlQuery提供值的数组。我们也可以使用表值函数代替存储过程。存储过程执行结果如下：

6、执行无返回值的存储过程

另一个用例就是假如存储过程没有任何的返回值，只是对数据库中的一张表或者多张表执行了一条命令的情况。一个存储过程做了多少事情不重要，重要的是它不返回任何数据。例如：下面的存储过程只是更新了一些数据：

CREATE PROCEDURE UpdateBooks
@name AS NVARCHAR(60),
@id as int
AS 
BEGIN
UPDATE Books SET Name=@name
WHERE Id=@id
END

 先在数据库中执行该存储过程，然后要调用该存储过程，我们使用ExecuteSqlCommand()方法。该方法会返回存储过程或者其它任何SQL语句受影响的行数。如果对这个返回值不感兴趣，可以忽略返回值。

var sql = "UpdateBooks @name,@id";
SqlParameter[] para = new SqlParameter[]
{
       new SqlParameter("@id",1d),
       new SqlParameter("@name","人间失败"),
};
var book = context.Books.Where(p => p.Id == 1);
Console.WriteLine("执行存储过程前的数据为：");
foreach (var item in book)
{
       Console.WriteLine(item.Name + "\t" + item.Author + "\t" + item.PublicationDate);
}
var rowsAffected = context.Database.ExecuteSqlCommand(sql, para);
Console.WriteLine("影响的行数为{0}条", rowsAffected);
Console.WriteLine("执行存储过程之后的数据为：");
var books = context.Books.Where(p => p.Id == 1);
foreach (var item in books)
{
        Console.WriteLine(item.Name + "\t" + item.Author + "\t" + item.PublicationDate);
}

上面的代码中为存储过程提供了两个参数：一个是Name，一个是Id。这里需要注意的是：我们必须严格按照它们在存储过程中定义的顺序依次传入相应的值，它们会以参数数组传入ExecuteSqlCommand。执行结果如下：

很大程度上，EF降低了存储过程的需要。然而，仍旧有很多原因要使用它们。这些原因包括安全标准、遗留数据库或者效率问题。比如，如果需要在单个操作中更新几千条数据，然后在通过EF检索出来；如果每次都更新一行，然后在保存那些实例，效率是很低的。最后，即使使用了SqlQuery()方法调用了存储过程，也可以更新数据。

注意：开发者可以执行任意的SQL语句，只需要将上面SqlQuery或ExecuteSqlCommand方法中的存储过程名称改为要执行的SQL语句就可以了。 

示例代码下载地址：https://pan.baidu.com/s/1hsAKT9a