Asp.Net之MVC(一)

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;


        }
    }
}