Dapper

public class ShopController : ApiController
{
//数据库连接字符串
string connstr = "Data Source=.;Initial Catalog=Month6;Integrated Security=True";
APIFileHelp help = new APIFileHelp();

//上传文件
[HttpPost]
public FileResult UpLoad()
{
return help.UpLoad();
}

//分页导出
[HttpGet]
public void Export1(int index, int size = 2)
{
SqlConnection conn = new SqlConnection(connstr);
var list = conn.Query<Goods>("select * from Goods").ToList();
list = list.OrderBy(x => x.GId).Skip((index - 1) * size).Take(size).ToList();

Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("GId", "商品编号");
dic.Add("GName", "商品名称");
dic.Add("GColor", "商品颜色");
dic.Add("GSize", "商品尺码");
dic.Add("GPrice", "商品价格");
help.ExportExcel<Goods>("a.xls", list, dic);
}

//添加商品
[HttpPost]
public int AddGood(Goods goods)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
return conn.Execute("insert into Goods values(@GName,@GImg,@GColor,@GSize,@GPrice)", goods);
}
}
//显示商品
[HttpGet]
public PageDate GetGoods(int index, int size)
{
SqlConnection conn = new SqlConnection(connstr);
var list = conn.Query<Goods>("select * from Goods").ToList();

PageDate page = new PageDate();
page.List = list.OrderBy(x => x.GId).Skip((index - 1) * size).Take(size).ToList();
var count = list.Count();
page.PageCount = count / size + (count % size == 0 ? 0 : 1);
return page;
}
//商品详情
[HttpGet]
public Goods GetGood(Goods goods)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
return conn.Query<Goods>("select * from Goods where GId=@GId", goods).SingleOrDefault();
}
}
//删除商品
[HttpPost]
public int DeleteGood(Goods goods)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
return conn.Execute("delete from Goods where GId=@GId", goods);
}
}
//修改商品
[HttpPost]
public int UpdateGood(Goods goods)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
return conn.Execute("update Goods set GName=@GName,GColor=@GColor,GSize=@GSize,GPrice=@GPrice where GId=@GId", goods);
}
}

//存储过程分页
[HttpGet]
public PageDate GetGoods2(int index, int size)
{
var p = new DynamicParameters();
p.Add("@index", index);
p.Add("@size", size);
p.Add("@totalcount", dbType: DbType.Int32, direction: ParameterDirection.Output); //总数据数
p.Add("@pagecount", dbType:DbType.Int32,direction:ParameterDirection.Output); //总页数

List<Goods> list = new List<Goods>();
using (SqlConnection conn=new SqlConnection(connstr))
{
list = conn.Query<Goods>("sp_Show",p,commandType:CommandType.StoredProcedure).ToList();
}
PageDate page = new PageDate();
page.List = list;
page.PageCount = p.Get<int>("@pagecount");
return page;
}

 

//用户表
public class UserInfo
{
public int UId { get; set; }
public string UName { get; set; }
public string UPwd { get; set; }
}

//商品表
public class Goods
{
public int GId { get; set; }
public string GName { get; set; }
public string GImg { get; set; }
public string GColor { get; set; }
public string GSize { get; set; }
public int GPrice { get; set; }
}

public class PageDate
{
public List<Goods> List { get; set; }
public int PageCount { get; set; }
}

 

public class ShopDB:DbContext
{
public DbSet<Goods> Goods { get; set; }
}