ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

2021-11-27 02:32:19

草图

 ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

真正的后端是不管前端是什么平台，用什么语言的，JSON格式的数据应该可以应对。用ASP.NET WEBAPI尝试做一个后端，实现最基本的CURD,业务逻辑和数据库操作都放在后端，前端只需要正确访问和提供必要的数据就行。

个人习惯

我喜欢先意淫一下需求，然后找出需要的实体和逻辑，而不是先设计数据库，现在EF那么牛逼，完全可以在代码上生成表和表之间的关系，各种查询语句效率并不一定比你写sql语句差。所以我在做东西的时候从来不先考虑数据的。但是今天主要是练习api和复习一下ado.net的CURD，所以没有用EF。长时间用EF不写sql，都忘光了啊。

ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

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

namespace Model
{
    public class Employee
    {
        public int Id { get; set; }
        public int Age { get; set; }
        public string Number { get; set; }
        public string Name { get; set; }
        public int DepartmentId { get; set; }
        public string Gender { get; set; }
        public string Job { get; set; }
        public string Telephone { get; set; }
        public float Salary { get; set; }
        public DateTime EntryTime { get; set; }
    }
}

Employee

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

namespace Model
{
    public class Department
    {
        public int Id { get; set; }
        public string Number { get; set; }
        public string Name { get; set; }
        public string Manager { get; set; }
        public bool IsDel { get; set; }
    }
}

Department

using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System;
using System.Configuration;

namespace DAL
{
    public abstract class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static readonly string connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();

        // 线程安全的哈希表
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 为执行命令准备参数
        /// </summary>
        /// <param name="cmd">SqlCommand 命令</param>
        /// <param name="conn">已经存在的数据库连接</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param>
        /// <param name="cmdParms">返回带参数的命令</param>
        private static void PrepareCommand(SqlCommand cmd,
            SqlConnection conn, CommandType cmdType,
            string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
                cmd.Parameters.AddRange(cmdParms);
            cmd.CommandText = cmdText;
        }

        public static int NonQuery(CommandType type,string sql,params SqlParameter[]pars)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, con, type, sql, pars);
                int n = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return n;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns a resultset against the database
        /// </summary>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>一个SqlDataReader的结果</returns>
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);
            try
            {
                PrepareCommand(cmd, conn,cmdType, cmdText, commandParameters);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return sdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static DataSet GetDataSet(CommandType type,string sql,params SqlParameter[]pars)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, con, type, sql, pars);
                using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                    adp.Fill(ds);
                    //cmd.Parameters.Clear();
                }
            }
            return ds;
        }

        #region ExecuteScalar方法

        /// <summary>
        /// 返回第一行的第一列
        /// </summary>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个对象</returns>
        public static object Scalar(CommandType type, string sql, params SqlParameter[] pars)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, con, type, sql, pars);
                object o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return o;
            }
        }

        #endregion

        /// <summary>
        /// 缓存参数
        /// </summary>
        /// <param name="cacheKey">索引键</param>
        /// <param name="cmdParms">参数数组</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// 通过索引键获取哈希表中的sql参数
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
            if (cachedParms == null)
                return null;
            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
            , j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
            return clonedParms;
        }

        /// <summary>
        /// 检查是否存在
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        /// <returns>bool结果</returns>
        public static bool Exists(string strSql)
        {
            int cmdresult = Convert.ToInt32(Scalar(CommandType.Text, strSql, null));
            )
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 检查是否存在
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>bool结果</returns>
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            int cmdresult = Convert.ToInt32(Scalar(CommandType.Text, strSql, cmdParms));
            )
            {
                return false;
            }
            else
            {
                return true;
            }
        }
    }

}

SqlHelper

有一个常用的视图

ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

USE [Demo]
GO

/****** Object:  View [dbo].[EmpView]    Script Date: 2016/8/22 16:06:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[EmpView]
as
select Employee.Number as 编号,
       Employee.Name as 姓名,
       Department.Name as 部门,
       Employee.Age as 年龄,
       Employee.Gender as 性别,
       Employee.Job as 职位,
       Employee.Telephone as 电话,
       Employee.Salary as 工资,
       Employee.EntryTime as 入职时间

from Employee ,Department
where Department.Id=Employee.DepartmentId

GO

EmpView

开始意淫

分页查询所有的员工信息，以前面的视图为模型。写一个简单的，没有过滤的分页存储过程

USE [Demo]
GO
/****** Object:  StoredProcedure [dbo].[Pager]    Script Date: 2016/8/22 16:26:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[Pager]
@index int ,@size int,@count int output
as
begin
declare @c int
)*@size
select @c=count(*) from EmpView
set @count=@c
end

Pager

准备把所有的业务放在API项目中

ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

using Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DAL;
using System.Data;
using Newtonsoft.Json;
using Newtonsoft;
using System.Data.SqlClient;

namespace WebApiTest
{
    public class BLL
    {
        private dynamic GetLineEmpView(DataRow row)
        {
            return new
            {
                Age = row["年龄"] == DBNull.Value ? string.Empty : row["年龄"].ToString(),
                Salary = row["工资"] == DBNull.Value ? string.Empty : row["工资"].ToString(),
                Name = row["姓名"] == DBNull.Value ? string.Empty : row["姓名"].ToString(),
                Job = row["职位"] == DBNull.Value ? string.Empty : row["职位"].ToString(),
                Number = row["编号"] == DBNull.Value ? string.Empty : row["编号"].ToString(),
                Telephone = row["电话"] == DBNull.Value ? string.Empty : row["电话"].ToString(),
                Gender = row["性别"] == DBNull.Value ? string.Empty : row["性别"].ToString(),
                EntryTime = row["入职时间"] == DBNull.Value ? string.Empty :row["入职时间"].ToString(),
                DepName = row["部门"].ToString()
            };

        }
        public List<dynamic> GetEmpView()
        {
            List<dynamic> list = new List<dynamic>();
            string sql = "select * from EmpView";
            DataTable dt = SqlHelper.GetDataSet(CommandType.Text, sql, ];
            if(dt!=null)
            {
                foreach (DataRow row in dt.Rows)
                    list.Add(GetLineEmpView(row));
                return list;
            }

            return list;
        }
        public List<dynamic> GetEmpView(int index,int size,out int count)
        {
            string sql = "Pager";
            SqlParameter[] pars =
            {
                new SqlParameter("@index",SqlDbType.Int),
                new SqlParameter("@size",SqlDbType.Int),
                new SqlParameter("@count",SqlDbType.Int)
            };
            pars[].Value = index; pars[].Direction = ParameterDirection.Input;
            pars[].Value = size; pars[].Direction = ParameterDirection.Input;
            pars[].Direction = ParameterDirection.Output;
            DataTable dt = SqlHelper.GetDataSet(CommandType.StoredProcedure, sql, pars).Tables[];
            ].Value.ToString(), out count);
            List<dynamic> list = new List<dynamic>();
            if(dt!=null)
            {
                foreach (DataRow row in dt.Rows)
                    list.Add(GetLineEmpView(row));
            }
            return list;
        }
    }
}

BLL

apicontroller调用这个这个类，对外提供2个GET的restful响应api，返回json格式的数据。这里的JSON数据没有处理好，用的.net的JsonResult格式，而不是直接用json.net返回，原因是直拉用json.net返回的时候，把原本排好的json格式又加了“”号和转义\。这个以后再想办法，现在也只是多了几个.net默认的几条数据，不要它就行了。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace WebApiTest.Controllers
{
    public class CompanyController : ApiController
    {
        private BLL bll = new BLL();
        public JsonResult Get()
        {
            return new JsonResult { Data = bll.GetEmpView() };
        }
        public JsonResult Get(int pageIndex,int pageSize)
        {
            int count;
            string data=JsonConvert.SerializeObject( bll.GetEmpView(pageIndex, pageSize, out count));
            var json=new JObject(new JProperty("PageTotalCount", count), new JProperty("EmpList", data));
            return new JsonResult { Data = json };
        }
    }
}

CompanyController

哈哈哈哈，说好的CURD可是从早上到现在只完成一个简单的分页查询，不过我觉得把这个做好了，其它的只是照搬了。那么对于这个简单需求来说，后端的工作已经完成了。再就是前端调用展示了。

前端调用和展示

客户端暂时封装一个对服务的GET请求

ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Web;

namespace TestMVCApp
{
    public class RestClient
    {
        private string BaseUri;
        public RestClient(string baseUri)
        {
            this.BaseUri = baseUri;
        }
        public string Get(string uri)
        {
            //Web访问服务
            string serviceUrl = string.Format("{0}/{1}", this.BaseUri, uri);

            //构造一个Web请求的对象
            HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(serviceUrl);

            //获取web请求的响应的内容
            HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse();

            //通过响应流构造一个StreamReader
            StreamReader reader = new StreamReader(myResponse.GetResponseStream(), Encoding.UTF8);
            //string ReturnXml = HttpUtility.UrlDecode(reader.ReadToEnd());
            string ReturnXml = reader.ReadToEnd();
            reader.Close();
            myResponse.Close();
            return ReturnXml;
        }
    }
}

RestClient

虽然我是在一个解决方案里做的，但我假设前端对后端一无所知，只知道自己需要什么，后端能给什么。所以另外搞个视图的模型类，接收数据（Models文件夹中)

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

namespace TestMVCApp.Models
{

    public class EmpView
    {
        public string Name { get; set; }
        public string Age { get; set; }
        public string DepName { get; set; }
        public string Salary { get; set; }
        public string Job { get; set; }
        public string Number { get; set; }
        public string Telephone { get; set; }
        public string Gender { get; set; }
        public string EntryTime { get; set; }

    }
    public class EmpData
    {
        public int PageTotalCount { get; set; }
        public List<EmpView> EmpList { get; set; }
    }
}

TestMVCApp.Models

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

namespace TestMVCApp.Controllers
{
    public class HomeController : Controller
    {
        private RestClient client = new RestClient("http://localhost:2154/");
        // GET: Home
        public ActionResult Index()
        {
            string uriGet = "api/Company?pageIndex=1&pageSize=5";
            string json = JObject.Parse(client.Get(uriGet))["Data"].ToString();
            string count = JObject.Parse(json)["PageTotalCount"].ToString();
            string emp = JObject.Parse(json)["EmpList"].ToString();
            List<EmpView> empList = JsonConvert.DeserializeObject<List<EmpView>>(emp);
            EmpData ed = new EmpData();
            ed.PageTotalCount = int.Parse(count);
            ed.EmpList = empList;
            return View(ed);
        }
    }
}

TestMVCApp.Controllers

@{
    Layout = null;
}
@model TestMVCApp.Models.EmpData
<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
    <script src="~/Scripts/jquery-1.9.1.min.js"></script>
    <script src="~/Scripts/bootstrap.min.js"></script>
    <title>Index</title>
    <script type="text/javascript">
        $(function () {
            $("#btn").click(function () {
                $.get("http://localhost:2154/api/conpany?pageIndex=" + $("#pageIndex").val() + "&pageSize=" + $("#pageSize").val(),
                    {},
                    function (data) {
                        $("#empBody tr").remove();
                        var json = data.Data
                        var empList = $.parseJSON(json.EmpList)
                        $.each(empList, function (i, item) {
                            $("#empBody").append("<tr><td>"
                                +item.Name+"</td><td>"
                                + item.Number + "</td><td>"
                                + item.DepName + "</td><td>"
                                + item.Gender + "</td><td>"
                                + item.Age + "</td><td>"
                                + item.Telephone + "</td><td>"
                                + item.Salary + "</td><td>"
                                + item.Job + "</td><td>"
                                +item.EntryTime+
                                "</td></tr>")
                        })
                    })
            })
        })
    </script>
</head>
<body>
    <div>
        <table id="empList" class="table">
            <thead>
                <tr>
                    <td>姓名</td>
                    <td>编号</td>
                    <td>部门</td>
                    <td>性别</td>
                    <td>年龄</td>
                    <td>电话</td>
                    <td>工资</td>
                    <td>职位</td>
                    <td>入职时间</td>
                </tr>
            </thead>
            <tbody id="empBody">
                @foreach(var item in Model.EmpList)
                {
                    <tr>
                        <td> @item.Name</td>
                        <td> @item.Number</td>
                        <td> @item.DepName</td>
                        <td> @item.Gender</td>
                        <td> @item.Age</td>
                        <td> @item.Telephone</td>
                        <td> @item.Salary</td>
                        <td> @item.Job</td>
                        <td> @item.EntryTime</td>
                    </tr>
                }
            </tbody>
        </table>

        <input id="pageIndex" type="text" /><input type="text" id="pageSize" />
        <button id="btn">GO</button>
    </div>
</body>
</html>

View

ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

用控制台也测试了一下的

namespace TestConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            RestClient client = new RestClient("http://localhost:2154/");
            string uriGet = "api/Company?pageIndex=1&pageSize=6";
            JObject data =(JObject)JObject.Parse( client.Get(uriGet))["Data"];
            string ed = data.ToString();
            string count = data["PageTotalCount"].ToString();
            List<EmpView> list = JsonConvert.DeserializeObject<List<EmpView>>(data["EmpList"].ToString());
            Console.WriteLine(count);
            Console.WriteLine(list.Count);
            Console.WriteLine(ed);
            Console.ReadKey();
        }
    }
}

TestConsoleApp

ASP.NET WEBAPI 简单CURD综合测试（asp.net MVC,json.net,sql基础存储过程和视图，sqlhelper,json解析）

其实那个MVC要不要都行，对于这个简单的需求来说，完全可以一个html页面用JQ的异步请求页就可以完成。我靠，几乎全部都没写注释，过段时间估计我自己都理解不了的。看来写注释是很有必要的啊。ASP.NET WEBAPI相对WCF来说还是简单多了，对于简单的应用完全可取代MVC的控制器了嘛，感觉用WEBAPI加JS(JQ)才是真正的MVC啊

上一篇：[转]ASP.NET MVC Json()处理大数据异常解决方法 json maxjsonlength


下一篇：Asp.NET MVC JSON序列化问题