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的值*/
05-11 13:26