MVC 中使用EF

EF

1）简单查询

后台代码

 using MvcApplication18.Models;

 using System;

 using System.Collections.Generic;

 using System.Data.SqlClient;

 using System.Linq;

 using System.Web;

 using System.Web.Mvc;

 namespace MvcApplication18.Controllers

 {

     public class HomeController : Controller

     {

         //

         // GET: /Home/

         appDBEntities db = new appDBEntities();

         public ActionResult Index()

         {

             SqlParameter[] parameter = { };

             List<SecondHand_info> list = db.Database.SqlQuery<SecondHand_info>("select * from SecondHand_info where SecondHand_keyID >88", parameter).ToList();

           ViewData["list"] = list;

             return View();

         }

     }

 }

前台代码

 @model MvcApplication18.Models.SecondHand_info

 @using MvcApplication18.Models

 @{

     ViewBag.Title = "Index";

 }

 @foreach (SecondHand_info item in ViewData["list"] as List<SecondHand_info>)

 {

   <p>@item.SecondHand_keyID </p>

      <p>@item.SecondHand_mobile </p>

      <p>@item.SecondHand_title </p>

      <p>@item.SecondHand_context </p>

 }

2)带参数查询

后台代码

 using MvcApplication18.Models;

 using System;

 using System.Collections.Generic;

 using System.Data.SqlClient;

 using System.Linq;

 using System.Web;

 using System.Web.Mvc;

 namespace MvcApplication18.Controllers

 {

     public class HomeController : Controller

     {

         //

         // GET: /Home/

         appDBEntities db = new appDBEntities();

         public ActionResult Index()

         {

             var id = "";

             var mobile = "";

             var sql = "select * from SecondHand_info where SecondHand_keyID =@id and SecondHand_mobile=@mobile";

          List<SecondHand_info> list=   db.Database.SqlQuery<SecondHand_info>(

                 sql,

                 new SqlParameter("@id",id),

                 new SqlParameter("@mobile",mobile)

                 ).ToList();

          db.SaveChanges();

           ViewData["list"] = list;

             return View();

         }

     }

 }

前台代码

 @model MvcApplication18.Models.SecondHand_info

 @using MvcApplication18.Models

 @{

     ViewBag.Title = "Index";

 }

 @foreach (SecondHand_info item in ViewData["list"] as List<SecondHand_info>)

 {

   <p>@item.SecondHand_keyID </p>

      <p>@item.SecondHand_mobile </p>

      <p>@item.SecondHand_title </p>

      <p>@item.SecondHand_context </p>

 }

 <h2>Index</h2>

3)修改

后台代码

using MvcApplication18.Models;

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace MvcApplication18.Controllers

{

    public class HomeController : Controller

    {

        //

        // GET: /Home/

        appDBEntities db = new appDBEntities();

        public ActionResult Index()

        {

            //修改

            List<SecondHand_info> list = db.Set<SecondHand_info>().SqlQuery("select * from SecondHand_info where SecondHand_keyID =88").ToList();

            list.Last().SecondHand_mobile = "";//把SecondHand_keyID =88 的手机号改为18017848011

            db.SaveChanges();

          ViewData["list"] = list;

            return View();

        }

    }

}

前台代码

 @model MvcApplication18.Models.SecondHand_info

 @using MvcApplication18.Models

 @{

     ViewBag.Title = "Index";

 }

 @foreach (SecondHand_info item in ViewData["list"] as List<SecondHand_info>)

 {

   <p>@item.SecondHand_keyID </p>

      <p>@item.SecondHand_mobile </p>

      <p>@item.SecondHand_title </p>

      <p>@item.SecondHand_context </p>

 }

4)带参数的存储过程（所以字段都要列出不然会出现问题）

 create proc P_QuerybyId_secondhand

 @SecondHand_keyID int

 as

 begin

 select SecondHand_keyID, SecondHand_mobile, SecondHand_title, SecondHand_new, SecondHand_type, SecondHand_money, SecondHand_context, SecondHand_pic1, SecondHand_pic2, SecondHand_pic3, SecondHand_pic4, SecondHand_pic5, SecondHand_pic, SecondHand_time, Memo FROM SecondHand_info where SecondHand_keyID=@SecondHand_keyID

 end

 using MvcApplication18.Models;

 using System;

 using System.Collections.Generic;

 using System.Data.SqlClient;

 using System.Linq;

 using System.Web;

 using System.Web.Mvc;

 namespace MvcApplication18.Controllers

 {

     public class HomeController : Controller

     {

         //

         // GET: /Home/

         appDBEntities db = new appDBEntities();

         public ActionResult Index()

         {

             var param = new SqlParameter("SecondHand_keyID", );

             List<SecondHand_info> list = db.Database.SqlQuery<SecondHand_info>("P_QuerybyId_secondhand @SecondHand_keyID", param).ToList();

          db.SaveChanges();

           ViewData["list"] = list;

             return View();

         }

     }

 }

 @model MvcApplication18.Models.SecondHand_info

 @using MvcApplication18.Models

 @{

     ViewBag.Title = "Index";

 }

 @foreach (SecondHand_info item in ViewData["list"] as List<SecondHand_info>)

 {

   <p>@item.SecondHand_keyID </p>

      <p>@item.SecondHand_mobile </p>

      <p>@item.SecondHand_title </p>

      <p>@item.SecondHand_context </p>

 }

 <h2>Index</h2>

5)带输出参数的存储过程

 CREATE PROCEDURE [dbo].[Myproc]

     @Name NVARCHAR(max),

     @PageIndex int,

     @PageSize INT,

     @TotalCount int OUTPUT

 as

     declare @startRow int

     declare @endRow    int

     set @startRow = (@PageIndex - 1) * @PageSize + 1

     set @endRow = @startRow + @PageSize - 1

     select *

     FROM

     (

         select top (@endRow)

            ID,

             Age,

             Name,

             row_number() over(order by [ID] desc) as [RowIndex]

         from dbo.Student

     ) as T

     where [RowIndex] >= @startRow AND T.Name = @Name

     SET @TotalCount=(select count(1) as N

     FROM dbo.Student WHERE Name = @Name) 

 var name = new SqlParameter { ParameterName = "Name", Value = Name };

             var currentpage = new SqlParameter { ParameterName = "PageIndex", Value = currentPage };

             var pagesize = new SqlParameter { ParameterName = "PageSize", Value = pageSize };

             var totalcount = new SqlParameter { ParameterName = "TotalCount", Value = , Direction = ParameterDirection.Output };

             var list = ctx.Database.SqlQuery<Student>("Myproc @Name, @PageIndex, @PageSize, @TotalCount output",

         name, currentpage, pagesize, totalcount);

             totalCount = (int)totalcount.Value;  /*获得要输出参数totalcount的值*/