MVC API Sql 多条件 存储过程 导出

数据库

create database Exam_Week3
GO
USE Exam_Week3
GO
create table Classs
(
ClaID  int identity(1001,1),
ClassName varchar(100),
Counts int
)
go
insert into Classs values
('1706B',5),
('1706A',5),
('1705B',5),
('1705A',5),
('1704B',5)
go
create table Student
(
SID   int identity(2001,1),
ClaID int, --班级id
Sname varchar(100), --学生姓名
Sage   datetime,
Ssex   bit
)
go
insert into Student values
(1001,'张三1',2013-10-02,1),
(1001,'张三2',2013-10-02,1),
(1001,'张三3',2013-10-02,1),
(1001,'张三4',2013-10-02,1),
(1001,'张三5',2013-10-02,1),
(1002,'李四1',2013-10-02,1),
(1002,'李四2',2013-10-02,1),
(1002,'李四3',2013-10-02,1),
(1002,'李四4',2013-10-02,1),
(1002,'李四5',2013-10-02,1),
(1003,'李艳1',2013-10-02,0),
(1003,'李艳2',2013-10-02,0),
(1003,'李艳3',2013-10-02,0),
(1003,'李艳4',2013-10-02,0),
(1003,'李艳5',2013-10-02,0),
(1004,'王五1',2013-10-02,1),
(1004,'王五2',2013-10-02,1),
(1004,'王五3',2013-10-02,1),
(1004,'王五4',2013-10-02,1),
(1004,'王五5',2013-10-02,1),
(1005,'李流1',2013-10-02,1),
(1005,'李流2',2013-10-02,1),
(1005,'李流3',2013-10-02,1),
(1005,'李流4',2013-10-02,1),
(1005,'李流5',2013-10-02,1),
(1005,'李流6',2013-10-02,1)
go
create table Course
(
CID int identity(3001,1),
Cname varchar(100),
TID int --教室编号
)
go
insert into Course values
('C#',4001),
('php',4002),
('Ajax',4003),
('Webseave',4004),
('网站',4005)
go
create table Teacher
(
TID int  identity(4001,1),
Tname varchar(100),
)
go
insert into Teacher values
('王红'),
('陆兵'),
('霍建华'),
('李冰冰'),
('宋小宝')
go
create table SC
(
scid int identity(5001,1),
SID int,
CID int, --课程编号
score int
)
go
insert into SC values
(2001,3001,93),
(2001,3002,85),
(2001,3003,57),
(2001,3004,98),
(2001,3005,99),
(2002,3001,80),
(2002,3002,100),
(2002,3003,55),
(2002,3004,77),
(2002,3005,43),
(2003,3001,85),
(2003,3002,86),
(2003,3003,88),
(2003,3004,90),
(2003,3005,91),
(2004,3001,79),
(2004,3002,80),
(2004,3003,64),
(2004,3004,63),
(2004,3005,62),
(2005,3001,74),
(2005,3002,73),
(2005,3003,96),
(2005,3004,89),
(2005,3005,92),
(2005,3001,92)

select * from 
(
select Classs.ClaID,Classs.ClassName,Classs.Counts,Student.Sage,Student.SID,Student.Sname,Student.Ssex,
SC.scid,SC.score,Course.CID,Course.Cname,Teacher.TID,Teacher.Tname,ROW_NUMBER() over(order by score) as rid
from Classs
join Student on Classs.ClaID=Student.ClaID 
join SC      on sc.SID=Student.SID
join Course  on Course.CID=sc.CID
join Teacher on Teacher.TID=Course.TID

) as temp
where rid>


-----分页存储过程
--判断存储过程是否存在
if OBJECT_ID('proc_Page') is not null
--删除存储过程
drop proc proc_Page
go
--创建存储过程
create  proc proc_Page
--参数
@ClaId int=0,               --班级Id
@Sage datetime=null,        --出生日期
@Sname nvarchar(10)=null,   --学生名称
@SmallScore int=0,          --小成绩
@BigScore int=0,            --大成绩
@CID int=0,                 --课程Id
@Tname nvarchar(10)=null,   --讲师名字
@PageIndex int,                --当前页
@PageSize int,                --每页条数
@TotalCount int out,        --总条数
@order int=1                --1升序  0降序
as
--变量
declare 
@sql varchar(max),      --总sql
@sqlWhere varchar(max), --条件
@rid int,              --分页条件
@countSql nvarchar(max)  --个数sql


set @sql='';
set @sqlWhere=' where 1=1';
set @rid=(@PageIndex-1)*@PageSize;
set @countSql='select @total=count(1)
from Classs
join Student on Classs.ClaID=Student.ClaID 
join SC      on sc.SID=Student.SID
join Course  on Course.CID=sc.CID
join Teacher on Teacher.TID=Course.TID';



--条件班级Id
if @ClaId!=0
begin
set @sqlWhere+=' and Classs.ClaId='+str(@ClaId);
end
----出生日期
if @Sage is not null
begin
set @sqlWhere+=' and Sage='+@Sage;
end
 --学生名称
if @Sname is not null
begin
set @sqlWhere+=' and Sname like ''%'+@Sname+'%''';
end
 --小成绩
if @SmallScore !=0
begin
set @sqlWhere+=' and Score>='+STR(@SmallScore);
end

 --大成绩
 if @BigScore !=0
begin
set @sqlWhere+=' and Score<='+STR(@BigScore);
end

 --课程Id
if @CID!=0
begin
set @sqlWhere+=' and CID='+str(@CID);
end
 --讲师名字
if @Tname is not null
begin
set @sqlWhere+=' and Tname like ''%'+@Tname+'%''';
end


--总条数
set @countSql+=@sqlWhere;

--执行计算条数的sql语句并且给输出参数赋值
exec sp_executesql  @countSql, N'@total int out',@total=@TotalCount out



--排序
declare @o varchar(4);
if @order=1
begin
set @o='asc';
end
if @order=0
begin
set @o='desc';
end
--总sql
set @sql='select top '+str(@PageSize)+' * from 
(
select Classs.ClaID,Classs.ClassName,Classs.Counts,Student.Sage,Student.SID,Student.Sname,Student.Ssex,
SC.scid,SC.score,Course.CID,Course.Cname,Teacher.TID,Teacher.Tname,ROW_NUMBER() over(order by score '+@o+') as rid
from Classs
join Student on Classs.ClaID=Student.ClaID 
join SC      on sc.SID=Student.SID
join Course  on Course.CID=sc.CID
join Teacher on Teacher.TID=Course.TID';

--条件
set @sql+=@sqlWhere;

set @sql+=' ) as temp
where rid>'+STR(@rid);

exec (@sql);

API

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using ExamApi.Models;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using Newtonsoft.Json;
using System.Text;

namespace ExamApi.Controllers
{
    public class StudentController : ApiController
    {

        
        public ReturnModel GetStudent(string json)
        {
            ParamsModel model = JsonConvert.DeserializeObject<ParamsModel>(json);

            //连接对象
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["StudentConn"].ToString()))
            {
                //命令对象，
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;//存储过程类型
                cmd.CommandText = "proc_Page";//存储过程名称
                //参数  ParameterName参数名字   SqlDbType参数类型   SqlValue参数值  Direction参数描述（输出、输入）
                SqlParameter[] sqls = new SqlParameter[] {
                    new SqlParameter{ ParameterName="BigScore", SqlDbType=SqlDbType.Int,SqlValue=model.BigScore },
                    new SqlParameter{ ParameterName="CID", SqlDbType=SqlDbType.Int,SqlValue=model.CID },
                    new SqlParameter{ ParameterName="ClaId", SqlDbType=SqlDbType.Int,SqlValue=model.ClaId },
                    new SqlParameter{ ParameterName="order", SqlDbType=SqlDbType.Int,SqlValue=model.order },
                    new SqlParameter{ ParameterName="PageIndex", SqlDbType=SqlDbType.Int,SqlValue=model.PageIndex },
                    new SqlParameter{ ParameterName="PageSize", SqlDbType=SqlDbType.Int,SqlValue=model.PageSize },
                    new SqlParameter{ ParameterName="Sage", SqlDbType=SqlDbType.VarChar,SqlValue=model.Sage },
                    new SqlParameter{ ParameterName="SmallScore", SqlDbType=SqlDbType.Int,SqlValue=model.SmallScore },
                    new SqlParameter{ ParameterName="Sname", SqlDbType=SqlDbType.VarChar,Size=20,SqlValue=model.Sname },
                    new SqlParameter{ ParameterName="Tname", SqlDbType=SqlDbType.VarChar,Size=20,SqlValue=model.Tname },
                    new SqlParameter{ ParameterName="TotalCount", SqlDbType=SqlDbType.Int, Direction=ParameterDirection.Output},
                };
                cmd.Parameters.AddRange(sqls);
                //适配器
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable("Student");
                sda.Fill(dt);
                //总条数
                int totalCount = Convert.ToInt32(cmd.Parameters["TotalCount"].Value);

                ReturnModel returnModel = new ReturnModel
                {
                    Dt = dt,
                    TotalCount= totalCount
                };
              return returnModel;
            }
        }
    }
}

帮助类 调用API

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net.Http;
using System.Net.Http.Headers;

namespace ExamMvc.Controllers
{
    public class HttpClientHelper
    {
        public HttpClientHelper(string baseAddr)
        {
            this.BaseAddr = baseAddr;
        }
        public  string BaseAddr { get; set; }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="Url">控制器的名+参数</param>
        /// <returns>返回json字符串</returns>
        public  string Get(string Url)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            //发送GET请求
            HttpResponseMessage msg =  client.GetAsync(Url).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }

        public  string Delete(string Url)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            //发送GET请求
            HttpResponseMessage msg = client.DeleteAsync(Url).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="Url">控制器名</param>
        /// <param name="JsonData">json数据</param>
        /// <returns></returns>
        public  string Post(string Url,string JsonData)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            //设置消息体
            HttpContent content = new StringContent(JsonData);
            content.Headers.ContentType = new MediaTypeHeaderValue("application/json");

            //发送Post请求
            HttpResponseMessage msg = client.PostAsync(Url,content).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }

        public  string Put(string Url, string JsonData)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            //设置消息体
            HttpContent content = new StringContent(JsonData);
            content.Headers.ContentType = new MediaTypeHeaderValue("application/json");

            //发送Post请求
            HttpResponseMessage msg = client.PutAsync(Url, content).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }


    }
}

MVC 后台

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ExamMvc.Models;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace ExamMvc.Controllers
{
    public class DefaultController : Controller
    {
        HttpClientHelper helper = new HttpClientHelper("https://localhost:44378/API/STUDENT/");
        // GET: Default


        #region 显示
        /// <summary>
        /// 显示
        /// </summary>
        /// <returns></returns>
        public ActionResult Index()
        {
            ParamsModel model = new ParamsModel
            {
                PageIndex = 1
            };
            StuViewModel stuViewModel = Show(model);
            return View(stuViewModel.Dt);
        }
        #endregion

        #region 查询
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult Index(ParamsModel model)
        {
            model.PageIndex = 1;
            Session["ParamsModel"] = model;//保存条件，分页时需要按照条件分页
            StuViewModel stuViewModel = Show(model);
            return PartialView("_PartialPage1", stuViewModel.Dt);
        }
        #endregion

        #region 分页
        /// <summary>
        /// 分页
        /// 调用show方法需要传ParamsModel
        /// ParamsModel里面有当前第几页，条件（Session["ParamsModel"]）
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ActionResult Pager(int PageIndex = 1)
        {
            ParamsModel queryModel = new ParamsModel();
            //如果有条件按照条件分页
            if (Session["ParamsModel"] != null)
            {
                queryModel = (ParamsModel)Session["ParamsModel"];
            }
            queryModel.PageIndex = PageIndex;
            StuViewModel stuViewModel = Show(queryModel);
            return PartialView("_PartialPage1", stuViewModel.Dt);

        }
        #endregion

        #region 获取数据
        /// <summary>
        /// 显示  需要当前第几页1  
        /// 分页  需要当前第几页?    查询条件
        /// 查询  需要当前第几页1    查询条件
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private StuViewModel Show(ParamsModel model)
        {
            if (model.PageSize == 0)
            {
                model.PageSize = 3;
            }
            //最小不能小于第一页
            model.PageIndex = model.PageIndex < 1 ? 1 : model.PageIndex;
            //最大不能大于最后一页
            model.PageIndex = model.PageIndex > Convert.ToInt32(Session["MaxPage"]) ? Convert.ToInt32(Session["MaxPage"]) : model.PageIndex;

            //调用Api获取数据
            string json = helper.Get("GetStudent?json=" + JsonConvert.SerializeObject(model));
            //json数据反序列化  （数据DataTable、TotalCount总条数）
            StuViewModel stuViewModel = JsonConvert.DeserializeObject<StuViewModel>(json);
            Session["TotalCount"] = stuViewModel.TotalCount;//总条数
            Session["PageIndex"] = model.PageIndex;//当前页
            Session["MaxPage"] = stuViewModel.TotalCount / model.PageSize + (stuViewModel.TotalCount % model.PageSize > 0 ? 1 : 0);//最大页

            return stuViewModel;
        }
        #endregion

        #region 导出
        public FileResult DownLoadExcel()
        {
            //获取导出数据                                               
            ParamsModel queryModel = new ParamsModel();
            //如果有条件按照条件分页
            if (Session["ParamsModel"] != null)
            {
                queryModel = (ParamsModel)Session["ParamsModel"];
            }
            queryModel.PageIndex = 1;
            queryModel.PageSize = Convert.ToInt32(Session["TotalCount"]);
            StuViewModel stuViewModel = Show(queryModel);
            DataTable dt = stuViewModel.Dt;
            //创建Excel对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet
            ISheet sheet = workbook.CreateSheet("学生信息");
            //创建表头
            IRow headerRow = sheet.CreateRow(0);//创建第1行
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);//创建每一列
                cell.SetCellValue(EnglishToChinese(dt.Columns[i].ColumnName));//添加每一列的列名

            }
            //添加行数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建每一行
                IRow dataRow = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    //创建每一列
                    ICell dataCell = dataRow.CreateCell(j);   //创建每一列
                    dataCell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //创建流
            MemoryStream stream = new MemoryStream();
            //Excel对象给流
            workbook.Write(stream);
            //stream.Seek(0, SeekOrigin.Begin);
            //流转化为文件返回
            string date = DateTime.Now.ToFileTime().ToString();
            return File(stream.GetBuffer(), "applocation/excel", date + ".xls");
        }
        #endregion



        public string EnglishToChinese(string name) {

            string newName = "";
            switch (name.Trim())
            {
                case "ClaID": newName = "班级编号"; break;
                case "ClassName": newName = "班级名称"; break;
                case "Counts": newName = "班级人数"; break;
                case "Sage": newName = "出生日期"; break;
                case "SID": newName = "学生编号"; break;
                case "Sname": newName = "学生"; break;
                case "Ssex": newName = "性别"; break;
                case "scid": newName = "成绩编号"; break;
                case "score": newName = "成绩"; break;
                case "CID": newName = "课程编号"; break;
                case "Cname": newName = "课程"; break;
                case "TID": newName = "讲师Id"; break;
                case "Tname": newName = "讲师"; break;
                case "rid": newName = "序号"; break;

            }
            return newName;
        }

    }
}

MVC view

@{
    ViewBag.Title = "Index";
}
@using System.Data
@model DataTable
<h2>Index</h2>
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
@using (Ajax.BeginForm("Index", new AjaxOptions { UpdateTargetId = "divData" }))
{
    <input id="Text1" type="text" name="Sname" placeholder="请输入学生姓名" />
    <input id="Text1" type="text" name="Tname" placeholder="请输入讲师姓名" />
    <input id="Text1" type="text" name="CID" />
    <input id="Submit1" type="submit" value="查询" />
}
@Html.ActionLink("导出","DownLoadExcel")
<div id="divData">
    @Html.Partial("_PartialPage1", Model)
</div>

MVC view 分部页

@using System.Data
@model DataTable
<table class="table-bordered table">
    <tr>
        <td>学生编号</td>
        <td>学生姓名</td>
        <td>班级名称</td>
        <td>课程</td>
        <td>成绩</td>
        <td>讲师</td>
    </tr>
    @foreach (DataRow item in Model.Rows)
    {
        <tr>
            <td>@item["SID"]</td>
            <td>@item["Sname"]</td>
            <td>@item["ClassName"]</td>
            <td>@item["Cname"]</td>
            <td>@item["score"]</td>
            <td>@item["Tname"]</td>
        </tr>
    }
</table>
@Ajax.ActionLink("首页", "Pager", "Default", new { PageIndex = 1 }, new AjaxOptions { UpdateTargetId = "divData" }, new { })
@Ajax.ActionLink("上一页", "Pager", "Default", new { PageIndex = Convert.ToInt32(Session["PageIndex"]) - 1 }, new AjaxOptions { UpdateTargetId = "divData" }, new { })
@Ajax.ActionLink("下一页", "Pager", "Default", new { PageIndex = Convert.ToInt32(Session["PageIndex"]) + 1 }, new AjaxOptions { UpdateTargetId = "divData" }, new { })
@Ajax.ActionLink("最后一页", "Pager", "Default", new { PageIndex = Convert.ToInt32(Session["MaxPage"]) }, new AjaxOptions { UpdateTargetId = "divData" }, new { })

2020-01-07