WebService存储过程显示分页、查询

　　最近做了一个关于WebService+MVC跨域的增删改查，就写成了一个案例，用于和大家分享。

WebService项目截图如下：

Model层 用户实体类

商品信息表

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Model
{
    public class Product //商品表
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public string Kind { get; set; }
        public string Color { get; set; }
        public int StoreId { get; set; }
        public string StoreName { get; set; }
        public int StoreNum { get; set; }
        public int WarningNum { get; set; }
    }
}

商品仓库信息

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Model
{
    public class Store
    {
        public int StoreId { get; set; }
        public string StoreName { get; set; }
    }
}

DAL层 

存储过程实现分页功能

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Model;
namespace DAL
{
    public class ProductDAL
    {
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="userPwd"></param>
        /// <returns></returns>
        public int CheckLogin(string userName, string userPwd)
        {
            //连接对象
            SqlConnection sqlconn = new SqlConnection("Data Source =.; Initial Catalog = zonghefuxi; Integrated Security = True");
            //操作对象
            var sqlcomm = new SqlCommand();
            //执行语句
            sqlcomm.Connection = sqlconn;
            sqlconn.Open();
            sqlcomm.CommandText = "sp_checkLogin";//指定存储过程名
            sqlcomm.CommandType = System.Data.CommandType.StoredProcedure;//明确操作类型为存储过程
            //用户名参数
            var yonghuPar = new SqlParameter("@userName", SqlDbType.VarChar, 50);
            yonghuPar.Value = userName;
            //密码参数
            var pwdPar = new SqlParameter("@userPwd", SqlDbType.VarChar, 50);
            pwdPar.Value = userPwd;
            //输出参数 结果
            var result = new SqlParameter("@result", SqlDbType.Int);
            result.Direction = ParameterDirection.Output;//指定为输出参数

            sqlcomm.Parameters.Add(yonghuPar);
            sqlcomm.Parameters.Add(pwdPar);
            sqlcomm.Parameters.Add(result);
            sqlcomm.ExecuteScalar();
            sqlconn.Close();//关闭数据库
            //接收并返回结果
            return Convert.ToInt32(result.Value);
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="pname"></param>
        /// <param name="sid"></param>
        /// <param name="pagecount"></param>
        /// <returns></returns>
        public DataTable GetProducts(int pageindex, int pagesize, string pname, int sid,ref int pagecount)
        {
            var sqlconn = new SqlConnection("Data Source =.; Initial Catalog = zonghefuxi; Integrated Security = True");
            var sqlcmd = new SqlCommand();
            sqlconn.Open();
            sqlcmd.Connection = sqlconn;
            sqlcmd.CommandText = "sp_productpager";
            sqlcmd.CommandType = CommandType.StoredProcedure;

            //页码参数
            var pindex = new SqlParameter("@pageindex", SqlDbType.Int);
            pindex.Value = pageindex;
            //页面条数
            var psize = new SqlParameter("@pagesize", SqlDbType.Int);
            psize.Value = pagesize;
            //商品名称
            var proname = new SqlParameter("@ProName", SqlDbType.VarChar, 50);
            proname.Value = pname;
            //仓库编号
            var storeid = new SqlParameter("@Sid", SqlDbType.Int);
            storeid.Value = sid;

            //一共多少页 输出参数
            var pcount = new SqlParameter("@pagecount", SqlDbType.Int);
            pcount.Direction = ParameterDirection.Output;

            sqlcmd.Parameters.Add(pindex);
            sqlcmd.Parameters.Add(psize);
            sqlcmd.Parameters.Add(proname);
            sqlcmd.Parameters.Add(storeid);
            sqlcmd.Parameters.Add(pcount);
            //结果datatable
            DataTable dt = new DataTable();
            //适配器
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlcmd);
            dataAdapter.Fill(dt);
            pagecount =Convert.ToInt32(pcount.Value);//一共几页返回给BLL
            return dt;
        }
        //获取仓库信息
        public DataTable GetStores()
        {
            return DBhelper.GetTable("select * from store");
        }
        /// <summary>
        /// 删除商品
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelPro(int id)
        {
            string sql = "delete from storehistory where pid=" + id;
            return DBhelper.ExecuteNonQuery(sql);
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="pro"></param>
        /// <returns></returns>
        public int UpdPro(Product pro)
        {
            string sql = string.Format("update storehistory set storeid={0},storenum={1},warningnum={2} where pid={3}", pro.StoreId, pro.StoreNum, pro.WarningNum, pro.ProductId);

            return DBhelper.ExecuteNonQuery(sql);
        }
        /// <summary>
        /// 根据商品id获取单个商品信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetProduct(int id)
        {
            string sql = @"select * from product p inner join storehistory s on 
p.ProductID = s.PID where p.ProductID =" + id;
            return DBhelper.GetTable(sql);
        }
    }
}

BLL业务逻辑层

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DAL;
using System.Data;
using Model;

namespace BLL
{
    public class ProductBLL
    {
        ProductDAL prod = new ProductDAL();
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="userPwd"></param>
        /// <returns></returns>
        public int CheckLogin(string userName, string userPwd)
        {
            return prod.CheckLogin(userName, userPwd);
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="pname"></param>
        /// <param name="sid"></param>
        /// <param name="pagecount"></param>
        /// <returns></returns>
        public DataTable GetProducts(int pageindex, int pagesize, string pname, int sid, ref int pagecount)
        {
            return prod.GetProducts(pageindex, pagesize, pname, sid, ref pagecount);
        }
        /// <summary>
        /// 获取仓库信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetStores()
        {
            return prod.GetStores();
        }
        /// <summary>
        /// 删除商品信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelPro(int id)
        {
            return prod.DelPro(id);
        }
        /// <summary>
        /// 修改商品信息
        /// </summary>
        /// <param name="pro"></param>
        /// <returns></returns>
        public int UpdPro(Product pro)
        {
            return prod.UpdPro(pro);
        }
        /// <summary>
        /// 根据商品Id获取单个商品信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetProduct(int id)
        {
            return prod.GetProduct(id);
        }
    }
}

 

WebFuXiService是使用WebService的项目

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using BLL;
using Model;
using System.Data;
using Newtonsoft.Json;

namespace WebFuXiService
{
    /// <summary>
    /// ProductService 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 
    // [System.Web.Script.Services.ScriptService]
    public class ProductService : System.Web.Services.WebService
    {
        ProductBLL prob = new ProductBLL();
        [WebMethod]
        public string CheckLogin(string uname, string upwd)
        {
            int result = prob.CheckLogin(uname, upwd);
            if (result > 0)
            {
                return "登陆成功";
            }
            else
            {
                return "用户名或密码错误";
            }
        }
        //获取商品信息 分页和查询
        [WebMethod]
        public List<Product> GetProducts(int pageindex, int pagesize, string pname, int sid, ref int clientCount)
        {
            int pagecount = 0;
            DataTable dt = prob.GetProducts(pageindex, pagesize, pname, sid, ref pagecount);
            clientCount = pagecount;
            string jsonStr = JsonConvert.SerializeObject(dt);
            var result = JsonConvert.DeserializeObject<List<Product>>(jsonStr);

            return result;
        }
        /// <summary>
        /// 显示仓库信息
        /// 下拉列表信息
        /// </summary>
        /// <returns></returns>
        [WebMethod]
        public List<Store> GetStores()
        {
            DataTable dtStore = prob.GetStores();
            string jsonStr = JsonConvert.SerializeObject(dtStore);
            List<Store> result = JsonConvert.DeserializeObject<List<Store>>(jsonStr);
            return result;
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [WebMethod]
        public string DelPro(int id)
        {
            int result = prob.DelPro(id);
            if (result > 0)
            {
                return "删除成功";
            }
            else
            {
                return "删除失败";
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="pro"></param>
        /// <returns></returns>
        [WebMethod]
        public string UpdPro(Product pro)
        {
            int result = prob.UpdPro(pro);
            if (result > 0)
            {
                return "修改成功";
            }
            else
            {
                return "修改失败";
            }
        }

        /// <summary>
        /// 根据id获取商品信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [WebMethod]
        public Product GetProduct(int id)
        {
            DataTable dt = prob.GetProduct(id);
            string json = JsonConvert.SerializeObject(dt);//json数组字符串[{'ProductId':2,'Name':'辣条'}]
            List<Product> result = JsonConvert.DeserializeObject<List<Product>>(json);
            return result.FirstOrDefault();//集合中单个的数据
        }
    }
}

MVC的项目截图如下：

从截图中我们可以看出需要引用一个服务引用，点击引用→点击添加服务引用→把地址输入到上面点击转到，然后点击确定就可以了。

WebProductCustom是一个MVC项目

控制器

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebProductCustom.ServiceReference1;
using Newtonsoft.Json;
using WebProductCustom.Models;

namespace WebProductCustom.Controllers
{
    public class ProductController : Controller
    {
        // GET: Product
        [HttpGet]
        public ActionResult Login()
        {

            return View();
        }
        [HttpPost]
        public ActionResult Login(string uname, string upwd)
        {
            ProductServiceSoapClient client = new ProductServiceSoapClient();
            string result = client.CheckLogin(uname, upwd);
            return Content("<script>alert('" + result + "')</script>");
        }
        /// <summary>
        /// 显示查询方法
        /// </summary>
        /// <param name="pname"></param>
        /// <param name="pcid"></param>
        /// <returns></returns>
        public ActionResult Show(string pname="",int pcid=-1)
        {
            ProductServiceSoapClient client = new ProductServiceSoapClient();
            client.GetStores();
            //下拉列表的绑定
            ViewBag.Cks = client.GetStores();
            int count = 0;
            Product[] products = client.GetProducts(1, 3, pname, pcid, ref count);
            ViewBag.count = count;
            ViewBag.cur = 1;
            string jsonStr = JsonConvert.SerializeObject(products);
            var result = JsonConvert.DeserializeObject<List<ProductViewModel>>(jsonStr);
            return View(result);
        }
        /// <summary>
        /// 分页 查询
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="pname"></param>
        /// <param name="pcid"></param>
        /// <returns></returns>
        public ActionResult PageHelper(int pageindex, int pagesize,string  pname="",int pcid=-1)
        {
            ViewBag.cur = pageindex; //更新当前页码
            ProductServiceSoapClient client = new ProductServiceSoapClient();
            ViewBag.Cks = client.GetStores();
            int count = 0;
            Product[] products = client.GetProducts(pageindex, pagesize, pname, pcid, ref count);
            ViewBag.count = count; //获取最后一页页码
            string jsonStr = JsonConvert.SerializeObject(products);
            var result = JsonConvert.DeserializeObject<List<ProductViewModel>>(jsonStr);
            return View("Show", result);//查询到数据后交给Show视图显示
        }
        public ActionResult Delete(int id)
        {
            ProductServiceSoapClient client = new ProductServiceSoapClient();
            string result = client.DelPro(id);
            return Content(result);
        }
        [HttpGet]
        public ActionResult Edit(int id)
        {

            ProductServiceSoapClient client = new ProductServiceSoapClient();
            client.GetStores();
            ViewBag.Cks = client.GetStores();//下拉列表赋值
            Product pro = client.GetProduct(id);//需要修改的商品
            string jsonStr = JsonConvert.SerializeObject(pro);
            ProductViewModel pv = JsonConvert.DeserializeObject<ProductViewModel>(jsonStr);
            return View(pv);
        }
        [HttpPost]
        public ActionResult Edit(ProductViewModel pro)
        {
            ProductServiceSoapClient client = new ProductServiceSoapClient();
            string jsonStr = JsonConvert.SerializeObject(pro);
            Product product = JsonConvert.DeserializeObject<Product>(jsonStr);
            string result = client.UpdPro(product);
            return Content(result);
        }
    }
}

Models

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebProductCustom.Models
{
    public class ProductViewModel
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public string Kind { get; set; }
        public string Color { get; set; }
        public int StoreId { get; set; }
        public string StoreName { get; set; }
        public int StoreNum { get; set; }
        public int WarningNum { get; set; }
    }
}

视图

@model IEnumerable<WebProductCustom.Models.ProductViewModel>
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Show</title>
    <link href="~/Content/bootstrap.css" rel="stylesheet" />
    <script src="~/Scripts/jquery-1.10.2.js"></script>
</head>
<body>
    <div>
        @using (Html.BeginForm())
        {
            <span>商品名称</span><input id="Text1" type="text" name="pname" />
            <span>商品仓库</span>
            <select id="Select1" name="pcid">
                <option value="-1">请选择仓库</option>
                @foreach (var item in ViewBag.Cks)
                {
                    <option value="@item.StoreId">@item.StoreName</option>
                }
            </select>
            <input id="Submit1" type="submit" value="查询" class="btn btn-primary" />
        }
        <table class="table table-bordered">
            <tr>
                <th>商品编号</th>
                <th>商品名称</th>
                <th>仓库名称</th>
                <th>库存数量</th>
                <th>预警数量</th>
                <th>操作</th>
            </tr>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@item.ProductId</td>
                    <td>@item.ProductName</td>
                    <td>@item.StoreName</td>
                    <td>@item.StoreNum</td>
                    <td>@item.WarningNum</td>
                    <td>
                        @Html.ActionLink("修改", "Edit", new { id = item.ProductId })
                        @Html.ActionLink("删除", "Delete", new { id = item.ProductId })
                    </td>
                </tr>
            }
        </table>
        <input id="hidIndex" type="hidden" value="@ViewBag.cur"  />
        <input id="Button1" type="button" value="首页" onclick="FirstPage()" />
        <input id="Button1" type="button" value="上一页" onclick="UpPage()" />
        <input id="Button1" type="button" value="下一页" onclick="DownPage()" />
        <input id="Last" type="button" value="尾页" class="@ViewBag.Count" onclick="LastPage()" />
    </div>
    <script>
        function FirstPage() {
            location.href = "/Product/PageHelper?pageindex=1&pagesize=3";
        }
        function DownPage() {
            var curPageIndex = $('#hidIndex').val();
            var pageindex = Number(curPageIndex) + 1;//获取需要访问的页码
            $('#hidIndex').val(pageindex);//更新当前页码
            location.href = "/Product/PageHelper?pageindex=" + pageindex + "&pagesize=3";
        }
        function UpPage() {
            var curPageIndex = $('#hidIndex').val();
            var pageindex = Number(curPageIndex) - 1;//获取需要访问的页码
            $('#hidIndex').val(pageindex);//更新当前页码
            location.href = "/Product/PageHelper?pageindex=" + pageindex + "&pagesize=3";
        }
        function LastPage() {
            var lastNum = $('#Last').prop('class');//最后一页页码
            location.href = "/Product/PageHelper?pageindex=" + lastNum + "&pagesize=3";
        }
    </script>
</body>
</html>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Login</title>
    <link href="~/Content/bootstrap.css" rel="stylesheet" />
</head>
<body>
    <div>
        @using (Html.BeginForm())
        {


            <table class="table table-bordered">
                <tr>
                    <td>用户名</td>
                    <td><input id="Text1" name="uname" type="text" /></td>
                </tr>
                <tr>
                    <td>密码</td>
                    <td><input id="Password1" name="upwd" type="password" /></td>
                </tr>
                <tr>
                    <td></td>
                    <td><input id="Submit1" type="submit" value="登陆" class="btn btn-primary" /></td>
                </tr>
            </table>
        }
    </div>
</body>
</html>

@model WebProductCustom.Models.ProductViewModel

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Edit</title>
    <script src="~/Scripts/jquery-1.10.2.js"></script>
    <script src="~/Scripts/jquery.validate.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.js"></script>
</head>
<body>
    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
        
        <div class="form-horizontal">
            <h4>编辑商品</h4>
            <hr />
            @Html.ValidationSummary(true, "", new { @class = "text-danger" })
            <div class="form-group">
                @Html.LabelFor(model => model.ProductId, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.ProductId, new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.ProductId, "", new { @class = "text-danger" })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.ProductName, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.ProductName, new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.ProductName, "", new { @class = "text-danger" })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.Kind, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.Kind, new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.Kind, "", new { @class = "text-danger" })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.Color, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.Color, new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.Color, "", new { @class = "text-danger" })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.StoreId, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    <select id="Select1" name="StoreId">
                        <option value="-1">请选择仓库</option>
                        @foreach (var item in ViewBag.Cks)
                        {
                            <option value="@item.StoreId" @if (item.StoreId == Model.StoreId) { @: selected
                                    }>
                                @item.StoreName
                            </option>
                        }
                    </select>
                </div>
            </div>
    
    
            <div class="form-group">
                @Html.LabelFor(model => model.StoreNum, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.StoreNum, new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.StoreNum, "", new { @class = "text-danger" })
                </div>
            </div>
    
            <div class="form-group">
                @Html.LabelFor(model => model.WarningNum, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.WarningNum, new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.WarningNum, "", new { @class = "text-danger" })
                </div>
            </div>
    
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <input type="submit" value="提交" class="btn btn-default" />
                </div>
            </div>
        </div>
    }
    
    <div>
        @Html.ActionLink("Back to List", "Index")
    </div>
</body>
</html>

Web.config

还没有确定在

 

<system.webServer>

    <validation validateIntegratedModeConfiguration="false" />//有没有不确定

  </system.webServer>

 

源码下载：

https://share.weiyun.com/5wSNNvr