引用
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
ADD添加数据
#region ==============ADD添加数据===============
/// <summary>
/// 添加数据
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/add/")]
public string Add()
{
string status =
string.Empty;
//初始化（获取）数据
Product product =
new Product()
{
ProductName =
"测试数据",
Price =
1300,
TypeId =
1
};
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
//标记数据
taobaoEntity.Product.Add(product);
//执行数据事务 （类似执行操作）
if (taobaoEntity.SaveChanges() >
0)
{
status =
"添加数据成功";
}
else
{
status =
"添加数据失败";
}
}
return status;
}
#endregion
delete删除数据
#region ==============delete删除数据===============
/// <summary>
/// delete删除数据
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/Delete/{productID:int}")]
public string Delete(
int productID)
{
string status =
string.Empty;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
Product product =
null;
try
{
//查询数据
product = taobaoEntity.Product.Single(m => m.ProductNo ==
productID);
//标记数据为删除
taobaoEntity.Product.Remove(product);
//执行数据事务 （类似执行操作）
if (taobaoEntity.SaveChanges() >
0)
{
status =
"删除数据成功";
}
else
{
status =
"删除数据失败";
}
}
catch (Exception)
{
status =
"数据不存在,无法删除";
}
return status;
}
}
#endregion
Modify修改[单条]数据
#region ==============Modify修改[单条]数据===============
/// <summary>
/// Modify修改[单条]数据
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/Modify/")]
//[HttpPost]
public string Modify()
{
string status =
string.Empty;
//数据条件
int productID =
32;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
//读取数据
//Product product2 = taobaoEntity.Product.Single(m => m.ProductNo == productID);
Product product = taobaoEntity.Product.FirstOrDefault(m => m.ProductNo ==
productID);
//修改数据
product.ProductName =
"测试修改数据Modify";
//执行数据事务 （类似执行操作）
if (taobaoEntity.SaveChanges() >
0)
{
status =
"修改数据成功";
}
else
{
status =
"修改数据失败;原因：源数据修改数据一致";
}
}
catch (Exception ex)
{
status =
"数据错误，修改失败;原因：" +
ex.Message;
}
}
return status;
}
#endregion
Modify修改[多条]数据
#region ==============Modify修改[多条]数据===============
/// <summary>
/// Modify修改[多条]数据
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/ModifyALL/")]
//[HttpPost]
public string ModifyALL()
{
string status =
string.Empty;
//数据条件
int typeid =
1;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
//读取数据
List<Product> productList = taobaoEntity.Product.Where(m => m.TypeId == typeid).ToList<Product>
();
//修改数据
int i =
0;
foreach (
var item
in productList)
{
item.Price = i *
1000;
i++
;
}
//执行数据事务 （类似执行操作）
if (taobaoEntity.SaveChanges() >
0)
{
status =
"修改数据成功";
}
else
{
status =
"修改数据失败;原因：源数据修改数据一致";
}
}
catch (Exception ex)
{
status =
"数据错误，修改失败;原因：" +
ex.Message;
}
}
return status;
}
#endregion
GetProduct查询单表数据[升序]
#region ==============GetProduct查询单表数据[升序]===============
/// <summary>
/// GetProduct查询单表数据[升序]
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/GetProductOrderBy/")]
//[HttpPost]
public string GetProductOrderBy()
{
string status =
string.Empty;
//数据条件
int typeid =
1;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
//读取数据
List<Product> productList = taobaoEntity.Product.Where(p => p.TypeId == typeid).OrderBy(p => p.Price).ToList<Product>
();
//逐条渲染数据
int i =
0;
foreach (
var item
in productList)
{
status += $
"{item.ProductName}--{item.Price}<br/>";
}
}
catch (Exception ex)
{
status =
"数据错误;原因：" +
ex.Message;
}
}
return status;
}
#endregion
GetProduct查询单表数据[降序]
#region ==============GetProduct查询单表数据[降序]===============
/// <summary>
/// GetProduct查询单表数据[降序]
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/GetProductOrderByDescending/")]
//[HttpPost]
public string GetProductOrderByDescending()
{
string status =
string.Empty;
//数据条件
int typeid =
1;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
//读取数据
List<Product> productList = taobaoEntity.Product.Where(p => p.TypeId == typeid).OrderByDescending(p => p.Price).ToList<Product>
();
//逐条渲染数据
int i =
0;
foreach (
var item
in productList)
{
status += $
"{item.ProductName}--{item.Price}<br/>";
}
}
catch (Exception ex)
{
status =
"数据错误;原因：" +
ex.Message;
}
}
return status;
}
#endregion
Get多表联查数据
#region ==============Get多表联查数据===============
/// <summary>
/// Get多表联查数据
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/GetProductJoin/")]
//[HttpPost]
public string GetProductJoin()
{
string status =
string.Empty;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
//读取数据
var productList =
from a
in taobaoEntity.Product
join b in taobaoEntity.ProductType
on a.TypeId equals b.typeid
select new { a.ProductName, b.typename };
//逐条渲染数据
int i =
0;
foreach (
var item
in productList)
{
status += $
"{item.ProductName}--{item.typename}<br/>";
}
}
catch (Exception ex)
{
status =
"数据错误;原因：" +
ex.Message;
}
}
return status;
}
#endregion
EF用原生SQL语句增、删、改 通用方法
#region ==============EF用原生SQL语句增、删、改 通用方法===============
/// <summary>
/// EF用原生SQL语句增、删、改 通用方法
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/ExecuteNonQuery/")]
//[HttpPost]
public string ExecuteNonQuery()
{
string status =
string.Empty;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
SqlParameter[] paras =
{
new SqlParameter(
"@name",
"测试")
};
//可以写增、删、改的SQL 语句
int result = taobaoEntity.Database.ExecuteSqlCommand(
@"insert into ProductType(typename) values(@name)", paras);
if (result >
0)
{
status =
"成功";
}
}
catch (Exception ex)
{
status =
"数据错误，;原因：" +
ex.Message;
}
}
return status;
}
#endregion
EF用原生SQL语句查 多条数据
#region ==============EF用原生SQL语句查 多条数据===============
/// <summary>
/// EF用原生SQL语句查 多条数据
/// </summary>
/// <returns></returns>
[Route(
"EFProduct/ExecuteReader/")]
//[HttpPost]
public string ExecuteReader()
{
string status =
string.Empty;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
List<Product> productList = taobaoEntity.Database.SqlQuery<Product>(
"select * from Product").ToList();
//所有查询、存储过程调用
foreach (
var item
in productList)
{
status = status + item.ProductName +
"<br/>";
}
}
catch (Exception ex)
{
status =
"数据错误，;原因：" +
ex.Message;
}
}
return status;
}
#endregion
EF调用数据库存储过程 多条数据--【分页】-首页参数可空
#region ==============EF调用数据库存储过程 多条数据--【分页】-首页参数可空===============
/// <summary>
/// EF调用数据库存储过程 多条数据--【分页】-首页参数可空
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <returns></returns>
[Route(
"EFProduct/ExecuteProcReader/{pageIndex?}")]
//[HttpPost]
public string ExecuteProcReader(
int pageIndex=
1)
{
string status =
string.Empty;
int pageSize =
10;
//每页显示条数
int pageCount=
0;
//总页数
int start=(pageIndex-
1)*pageSize+
1;
int end=pageIndex*
pageSize;
/*
* 假如当前是第一页
* start=（1-1）*10+1
* start=1
* end=1*10
* end=10
* 也就是第1条到第10条是第一页的数据
* ****************************************
* 假如当前是第二页
* start=（2-1）*10+1
* start=11
* end=2*10
* end=20
* 也就是第11条到第20条是第二页的数据
*/
int minnum =
start;
int maxnum =
end;
using (TAOBAODBEntities taobaoEntity =
new TAOBAODBEntities())
{
try
{
SqlParameter[] paras =
{
new SqlParameter(
"@minnum",minnum),
new SqlParameter(
"@maxnum",maxnum),
new SqlParameter(
"@ProductCount",System.Data.SqlDbType.Int)
//指定输出参数类型
};
paras[2].Direction = ParameterDirection.Output;
//指明第3个参数为输出参数
List<Product> productList = taobaoEntity.Database.SqlQuery<Product>(
"exec proc_product_page @minnum,@maxnum,@ProductCount output", paras).ToList();
//所有查询、存储过程调用
int ProductCount=(
int)paras[
2].Value;
//计算显示页数
pageCount=ProductCount/
pageSize;
if (ProductCount%pageSize!=
0)
{
pageCount++
;
}
foreach (
var item
in productList)
{
status = status +$
"[{item.ProductNo}]"+ item.ProductName +
"[分页数据]<br/>";
}
status = status +
"<br/><hr/>";
status = status +
"当前页[" +pageIndex+
"]页 ";
status = status +
"总页数[" +pageCount+
"]页 ";
status = status +
"总条数[" +ProductCount+
"]条 ";
}
catch (Exception ex)
{
status =
"数据错误;原因：" +
ex.Message;
}
}
return status;
}
#endregion
下面这个是存储过程的写法
SQL Server数据库存储过程分页，带总条数
https://www.cnblogs.com/cplvfx/articles/11573488.html
转载于:https://www.cnblogs.com/cplvfx/articles/11573583.html
相关资源：25个经典网站源代码
