1.EF(Entity Framework)
EF是微软以ADO.NET为基础发展的一个ORM框架。
2.实现CRUD
1:添加
insert into dbo.RoomType(TypeName, Price, AddBed, BedPrice, Remark) output inserted.ID
values('kkk',321,1,34,'oooo')
exec sp_executesql N'insert [dbo].[UserInfo]([UserName], [UserPass], [RegTime], [Email])
values (@0, @1, @2, @3)
select [ID]
from [dbo].[UserInfo]
where @@ROWCOUNT > 0 and [ID] = scope_identity()',N'@0 nvarchar(32),@1 nvarchar(32),@2 datetime,@3 nvarchar(32)',@0=N'sss56',@1=N'123456',@2='2015-06-17 10:32:43:217',@3=N'sss@256.com'
@@identity
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是,SCOPE_IDENTITY 只返回插入到当前作用域中的值;@@IDENTITY 不受限于特定的作用域。
例如,有两个表 T1 和 T2,并且在 T1 上定义了 INSERT 触发器。当将某行插入 T1 时,触发器被激发,并在 T2 中插入一行。 该方案演示了两个作用域:在 T1 上的插入,以及在 T2 通过触发器的插入。
假设 T1 和 T2 都有标识列,@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。@@IDENTITY 将返回在当前会话中的任何作用域内插入的最后一个标识列的值。这是在 T2 中插入的值。SCOPE_IDENTITY() 将返回在 T1 中插入的 IDENTITY 值。这是在同一个作用域内发生的最后的插入。如果在任何 INSERT 语句作用于作用域中的标识列之前调用 SCOPE_IDENTITY() 函数,则该函数将返回空值。
2:实现查询
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[UserName] AS [UserName],
[Extent1].[UserPass] AS [UserPass],
[Extent1].[RegTime] AS [RegTime],
[Extent1].[Email] AS [Email]
FROM [dbo].[UserInfo] AS [Extent1]
WHERE 343 = [Extent1].[ID]
3.练习
(1):输出某个具体客户的订单号
(2):输出某个具体订单(100001)号对应的用户名
(3):删除某个用户(userId=1)下的所有订单
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebEf
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Student student = new Student();
student.UserName = "sss56";
student.UserPwd = "123456";
student.Email = "sss@256.com";
student.RegTime = DateTime.Now;
itcastdbEntities2 db = new itcastdbEntities2();
db.Student.Add(student);//将数据添加到EF并且添加了标记
db.SaveChanges();//数据才会保存到数据库,返回受影响的行数
Response.Write(student.UserId);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
itcastdbEntities2 db = new itcastdbEntities2();
var sturInfoList = from s in db.Student
where s.UserId == 40
select s;
int i = 0;
//EF 延迟加载机制,数据用到的时候才去数据库中查询。不能用的时候不查询。
foreach (Student student in sturInfoList)
{
Response.Write(student.UserName);
}
}
protected void Button3_Click(object sender, EventArgs e)
{
itcastdbEntities2 db = new itcastdbEntities2();
var stuInfoList = from s in db.Student
where s.UserId == 40
select s;
//返回第一个元素,如果没有的话,返回null
Student student = stuInfoList.FirstOrDefault();
if (stuInfoList!=null)
{
db.Student.Remove(student);
db.Entry<Student>(student).State = System.Data.EntityState.Deleted;
db.SaveChanges();
}
else
{
Response.Write("要删除的数据不存在!");
}
//Student student = new Student();
//db.Student.Remove(student);
//db.Entry<Student>(student).State = System.Data.EntityState.Deleted;
//db.SaveChanges();
}
protected void Button4_Click(object sender, EventArgs e)
{
itcastdbEntities2 db = new itcastdbEntities2();
var sturInfoList = from s in db.Student
where s.UserId == 41
select s;
var student = sturInfoList.FirstOrDefault();
student.UserPwd = "66666";
db.Entry<Student>(student).State = System.Data.EntityState.Modified;
db.SaveChanges();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebEf
{
public partial class WebForm2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
itcastdbEntities3 db = new itcastdbEntities3();
Customer customer = new Customer() { CustomerName = "zhangsan", CustomerPwd = "123", SubTime = DateTime.Now };
OrderInfo orderInfo1 = new OrderInfo() {Id = Guid.NewGuid(), OrderNum = "10001", CreateDateTime = DateTime.Now, Customer = customer };
OrderInfo orderInfo2 = new OrderInfo() { Id = Guid.NewGuid(), OrderNum = "10002", CreateDateTime = DateTime.Now, Customer = customer };
db.Customer.Add(customer);
db.OrderInfo.Add(orderInfo1);
db.OrderInfo.Add(orderInfo2);
db.SaveChanges();//默认的已经开启了事务。工作单元模式(UnitOfWork)
}
protected void Button2_Click(object sender, EventArgs e)
{
itcastdbEntities3 db = new itcastdbEntities3();
var customerList = from c in db.Customer
select c;
foreach (var customer in customerList)
{
Response.Write(customer.CustomerName+":");
foreach (var orderInfo in customer.OrderInfo)//延迟加载
{
Response.Write(orderInfo.OrderNum);
}
}
}
protected void Button3_Click(object sender, EventArgs e)
{
itcastdbEntities3 db = new itcastdbEntities3();
//var customerList = from c in db.Customer
// where c.Id == 1
// select c;
//var customerInfo = customerList.FirstOrDefault();
//foreach (var orderInfo in customerInfo.OrderInfo)
//{
// Response.Write(orderInfo.OrderNum);
//}
var orderInfoList = from o in db.OrderInfo
where o.CustomerId == 1
select o;
foreach (var orderInfo in orderInfoList)
{
Response.Write(orderInfo.OrderNum);
}
}
protected void Button4_Click(object sender, EventArgs e)
{
itcastdbEntities3 db = new itcastdbEntities3();
var orderInfoList = from o in db.OrderInfo
where o.OrderNum == "10001"
select o;
var orderInfo = orderInfoList.FirstOrDefault();
Customer customer = orderInfo.Customer;
Response.Write(customer.CustomerName);
}
protected void Button5_Click(object sender, EventArgs e)
{
itcastdbEntities3 db = new itcastdbEntities3();
//var customer = (from c in db.Customer
// where c.ID == 1
// select c).FirstOrDefault();
//var orderInfoList = customer.OrderInfo;
//while (orderInfoList.Count > 0)
//{
// var orderInfo = orderInfoList.FirstOrDefault();
// db.Entry<OrderInfo>(orderInfo).State = System.Data.EntityState.Deleted;
//}
//db.SaveChanges();
var orderList = from o in db.OrderInfo
where o.CustomerId == 2
select o;
}
}
}