金沙官网线上entityframework学习笔记--009-使用原生

 实例1-使用实例-单个语句:

1 使用原生SQL语句更新--Database.ExecuteSqlCommand

假设你有一张如图9-1所示的Payment数据库表。

金沙官网线上 1

图9-1

1.1 实体类型:

金沙官网线上 2金沙官网线上 3

1  public class Payment
2     {
3         public int PaymentId { get; set; }
4         public decimal Amount { get; set; }
5 
6         public string Vendor { get; set; }
7     }

View Code

1.2 数据访问类:

金沙官网线上 4金沙官网线上 5

 1 public class EF6RecipesContext: DbContext
 2     {
 3         public DbSet<Payment> Payments { get; set; }
 4         public EF6RecipesContext()
 5             : base("name=EF6CodeFirstRecipesContext")
 6         {
 7         }
 8         protected override void OnModelCreating(DbModelBuilder modelBuilder)
 9         {
10             base.OnModelCreating(modelBuilder);
11 
12             modelBuilder.Entity<Payment>().ToTable("Payments", "example9");
13         }
14     }

View Code

1.3 代码演示:

金沙官网线上 6金沙官网线上 7

 1 // 删除之前的测试数据
 2             using (var context = new EFRecipesEntities())
 3             {
 4                 context.Database.ExecuteSqlCommand("delete from chapter3.payment");
 5             }
 6             //插入两行数据
 7             using (var context = new EFRecipesEntities())
 8             {
 9                 var sql = @"insert into Chapter3.Payment(Amount, Vendor)
10                    values (@Amount, @Vendor)";    //这里可以使用@p0这样的参数占位符,ado.net为自动为我们创建参数对象
11                 var parameters = new DbParameter[]
12                     {
13                         new SqlParameter {ParameterName = "Amount", Value = 99.97M},
14                         new SqlParameter {ParameterName = "Vendor", Value = "Ace Plumbing"}
15                     };
16 
17                 var rowCount = context.Database.ExecuteSqlCommand(sql, parameters);
18 
19                 parameters = new DbParameter[]
20                     {
21                         new SqlParameter {ParameterName = "Amount", Value = 43.83M},
22                         new SqlParameter
23                             {
24                                 ParameterName = "Vendor",
25                                 Value = "Joe's Trash Service"
26                             }
27                     };
28 
29                 rowCount += context.Database.ExecuteSqlCommand(sql, parameters);
30                 Console.WriteLine("{0} rows inserted", rowCount.ToString());
31             }
32 
33             // 获取并显示数据
34             using (var context = new EFRecipesEntities())
35             {
36                 Console.WriteLine("Payments");
37                 Console.WriteLine("========");
38                 foreach (var payment in context.Payments)
39                 {
40                     Console.WriteLine("Paid {0} to {1}", payment.Amount.ToString(),
41                                       payment.Vendor);
42                 }
43             }
44 
45             Console.WriteLine("nPress <enter> to continue...");
46             Console.ReadLine();

View Code

输出:

1 2 rows inserted
2 Payments
3 ========
4 Paid $99.97 to Ace Plumbing
5 Paid $43.83 to Joe's Trash Service

金沙官网线上 8金沙官网线上 9

2 使用原生SQL语句获取对象--Database.SqlQuery()

假设你有如图所示的一个拥有Student实体类型的模型。

金沙官网线上 10

2.1 实体类型

金沙官网线上 11金沙官网线上 12

public class Student
    {
        public int StudentId { get; set; }
        public string Degree { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

View Code

2.2 上下文对象DbContext

金沙官网线上 13金沙官网线上 14

 1 public class EFRecipesEntities : DbContext
 2     {
 3         public EFRecipesEntities()
 4             : base("ConnectionString")
 5         {
 6         }
 7 
 8         public DbSet<Student> Students { get; set; }
 9 
10         protected override void OnModelCreating(DbModelBuilder modelBuilder)
11         {
12             modelBuilder.Entity<Student>().ToTable("example9.Students");
13             base.OnModelCreating(modelBuilder);
14         }
15     }

View Code

2.3 代码演示:

金沙官网线上 15金沙官网线上 16

 1 using (var context = new EFRecipesEntities())
 2             {
 3                 // 删除出测试数据
 4                 context.Database.ExecuteSqlCommand("delete from example9.students");
 5 
 6                 // 插入数据
 7                 context.Students.Add(new Student
 8                     {
 9                         FirstName = "Robert",
10                         LastName = "Smith",
11                         Degree = "Masters"
12                     });
13                 context.Students.Add(new Student
14                     {
15                         FirstName = "Julia",
16                         LastName = "Kerns",
17                         Degree = "Masters"
18                     });
19                 context.Students.Add(new Student
20                     {
21                         FirstName = "Nancy",
22                         LastName = "Stiles",
23                         Degree = "Doctorate"
24                     });
25                 context.SaveChanges();
26             }
27 
28             using (var context = new EFRecipesEntities())
29             {
30                 var sql = "select * from example9.Students where Degree = @Major";
31                 var parameters = new DbParameter[]
32                     {
33                         new SqlParameter {ParameterName = "Major", Value = "Masters"}
34                     };
35                 var students = context.Database.SqlQuery<Student>(sql, parameters);
36                 Console.WriteLine("Students...");
37                 foreach (var student in students)
38                 {
39                     Console.WriteLine("{0} {1} is working on a {2} degree",
40                                       student.FirstName, student.LastName, student.Degree);
41                 }
42             }
43 
44             Console.WriteLine("nPress <enter> to continue...");
45             Console.ReadLine();
46         }

View Code

输出:

 Students...
 Robert Smith is working on a Masters degree
 Julia Kerns is working on a Masters degree


这里在查询语句中使用“*”表示所有的列名,实体框架会将返回的列匹配到合适的属性上。一般情况下,这会工作得很好。但是,查询中只有部分列返回时,实体框架会在实例化对象时抛出一个异常。一个更好的方法和最佳实践是,在你的查询语句中显式枚举所有列(也就是说,指定所有的列名)。

  如果你的SQL语句返回的列多于实例化实体所需数量(也就是说,列值数量多于实体对象属性数量),实体框架会忽略掉多于的列。如果你仔细想想,这不是一个令人满意的行为。再一次重申,在SQL语句中显式枚举你所期望返回的列名,确保它们与实体类型匹配

  SqlQuery()方法有很多限制,如果你在使用TPH继承映射,你的SQL语句返回的行要映射到不同的派生类型上,实体框架不能使用鉴别列来将行映射到正确的派生类型。你可能会得到一个运行时异常,因为行中可能不包含正在实例化类型所需的值。

  有趣的是,我们可以使用SqlQuery()方法实例化根本就不是实体的类型。例如,我们创建一个StudentName类,它只包含姓,和名两个属性民。如果我们的SQL语句也只返回这两个列,我们可以使用SqlQuery<StudentName>()方法和指定的SQL语句获取类型StudentName的实例集合。

  我们很小心地使用短语,SQL语句,而不是查询语句,是因为SqlQuery()方法可以接受任何返回行集合的SQL语句。这当然包含查询语句,但也包含执行存储过程的SQL语句。

 
int totalCount = 0;
            List<int> alist = new List<int> { 5001536, 2, 3 };
            List<User_info> userInfoList = UserCenterBus.Select_WebSiteBase<User_info>(1, 10, User_info._USER_INFO_, User_info._ID_ + " DESC", out totalCount, m => alist.Contains(m.ID));
            base.Response.Write(JsonHelper.ConvertJsonToStr(userInfoList));

View Code

 

金沙官网线上 17金沙官网线上 18

/// <summary>
        /// 自定义SQL分页查询_WebSite库_LINQ用于自定义分页SQL和非INT类型变量值传输(防止非INT类型值SQL注入)
        /// </summary>
        /// <typeparam name="T">返回类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="fromTableSql">select * from {0} where {1} order by {2}:填写{0}</param>
        /// <param name="orderByTableFieldSql">select * from {0} where {1} order by {2}:填写{2}</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="whereLinq">关于T的linq语句==>生成可DbParameter[]防SQL注入参数数组</param>
        /// <returns></returns>
        public static List<T> Select_WebSiteBase<T>(int pageIndex, int pageSize, string fromTableSql, string orderByTableFieldSql, out int totalCount, Expression<Func<T, bool>> whereLinq)
        {
            DB.MySql.WebSite.Entity.WherePart wherePart = DB.MySql.WebSite.Entity.WhereBuilder.Instance_MySql.ToSql<T>(whereLinq);
            List<DbParameter> dbParameterList = new List<DbParameter>(0);
            if (wherePart.Parameters != null && wherePart.Parameters.Count > 0)
            {
                foreach (var paramter in wherePart.Parameters)
                {
                    dbParameterList.Add(new MySqlParameter(paramter.Key, paramter.Value));
                }
            }
            string pageSql = string.Format(@"SELECT * FROM {0} WHERE {1} ORDER BY {2} LIMIT {3},{4};", fromTableSql, wherePart.Sql, orderByTableFieldSql, (pageIndex - 1) * pageSize, pageSize);
            string totalCountSql = string.Format(@"SELECT COUNT(*) FROM {0} WHERE {1};", fromTableSql, wherePart.Sql);
            List<T> tList = DB.MySql.WebSite.BLL.BLLGeneric.Select<T>(CommandType.Text, pageSql + totalCountSql, out totalCount, dbParameterList.ToArray());
            dbParameterList.Clear();
            dbParameterList = null;
            return tList;
        }

View Code

 实例2-使用实例-多个语句:

金沙官网线上 19金沙官网线上 20

本文由金沙官网线上发布于编程,转载请注明出处:金沙官网线上entityframework学习笔记--009-使用原生

您可能还会对下面的文章感兴趣: