asp.net+ado.net 表的增删改查

一、全局靠一张图，内容全靠编

2、asxh->asxh->AddUser.asxh代码如下：

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using 一个表的增删改查.Model;
using 一个表的增删改查.Model.Entities;
using 一个表的增删改查.Model.Service;

namespace 一个表的增删改查.ashx.ashx
{
    /// <summary>
    /// AddUser 的摘要说明
    /// </summary>
    public class AddUser : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            UserService us = new UserService();
            User userInfo = new User();
            userInfo.Name = context.Request["Name"];
            userInfo.PhoneNum = context.Request["PhoneNum"];
            userInfo.Email = context.Request["Email"];
            userInfo.PasswordSalt = context.Request["PasswordSalt"];
            userInfo.PasswordHash = context.Request["PasswordHash"];
            userInfo.LoginErrorCount = Int32.Parse(context.Request.Form["LoginErrorCount"]);
            userInfo.LastLoginErrorDateTime = context.Request.Form["LastLoginErrorDateTime"];
            userInfo.CreateDateTime = DateTime.Parse(context.Request["CreateDateTime"]);
            userInfo.IsDeleted = Boolean.Parse(context.Request["IsDeleted"]);
            AjaxResult ar = new AjaxResult();
            if (us.AddUserInfo(userInfo) > 0)
            {
                ar.Status = "ok";
                ar.ErrorMsg = "添加成功";
                context.Response.Write(JsonConvert.SerializeObject(ar));
            }
            else
            {
                ar.Status = "no";
                ar.ErrorMsg = "添加失败";
                context.Response.Write(JsonConvert.SerializeObject(ar));
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

3、ashx->ashx->EditUser.ashx代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Newtonsoft.Json;
using 一个表的增删改查.Model;
using 一个表的增删改查.Model.Entities;
using 一个表的增删改查.Model.Service;

namespace 一个表的增删改查.ashx.ashx
{
    /// <summary>
    /// EditUser 的摘要说明
    /// </summary>
    public class EditUser : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "application/json";
            UserService us = new UserService();
            User userInfo = new User();
            userInfo.Id = Int32.Parse(context.Request["Id"]);
            userInfo.Name = context.Request["Name"];
            userInfo.PhoneNum = context.Request["PhoneNum"];
            userInfo.Email = context.Request["Email"];
            userInfo.PasswordSalt = context.Request["PasswordSalt"];
            userInfo.PasswordHash = context.Request["PasswordHash"];
            userInfo.LoginErrorCount = Int32.Parse(context.Request.Form["LoginErrorCount"]);
            userInfo.LastLoginErrorDateTime = context.Request.Form["LastLoginErrorDateTime"];
            userInfo.CreateDateTime = DateTime.Parse(context.Request["CreateDateTime"]);
            userInfo.IsDeleted =Boolean.Parse(context.Request["IsDeleted"]);
            AjaxResult ar = new AjaxResult();
            if(us.EditUserInfo(userInfo)>0)
            {
                ar.Status = "ok";
                ar.ErrorMsg = "修改成功";
                context.Response.Write(JsonConvert.SerializeObject(ar));
            }
            else
            {
                ar.Status = "no";
                ar.ErrorMsg = "修改失败";
                context.Response.Write(JsonConvert.SerializeObject(ar));
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

4、ashx->ashx->List.ashx代码如下：

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using 一个表的增删改查.Model.Entities;
using 一个表的增删改查.Model.Service;

namespace 一个表的增删改查.ashx.ashx
{
    /// <summary>
    /// List 的摘要说明
    /// </summary>
    public class List : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            UserService user = new UserService();
            List<User> list = user.GetList();
            StringBuilder sb = new StringBuilder();
            foreach(User userInfo in list)
            {
                sb.AppendFormat("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td><td>{8}</td><td>{9}</td><td><a href='../html/ShowDetail.html?uid={0}'>详细</a></td><td><a href='DeleteUser.ashx?id={0}' class='deletes'>删除</a></td><td><a href='../html/ShowEdit.html?id={0}'>编辑</a></td></tr>", userInfo.Id,userInfo.Name,userInfo.PhoneNum,userInfo.Email,userInfo.PasswordSalt,userInfo.PasswordHash,userInfo.LoginErrorCount,userInfo.LastLoginErrorDateTime,userInfo.CreateDateTime,userInfo.IsDeleted);
            }
            //读取模板文件
            string filePach = context.Request.MapPath("../html/List.html");
            string fileContent = File.ReadAllText(filePach);
            fileContent = fileContent.Replace("@tbody", sb.ToString());
            context.Response.Write(fileContent);

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

5、ashx->ashx->ShowEdit.ashx代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Newtonsoft.Json;
using 一个表的增删改查.Model;
using 一个表的增删改查.Model.Entities;
using 一个表的增删改查.Model.Service;

namespace 一个表的增删改查.ashx.ashx
{
    /// <summary>
    /// ShowEdit 的摘要说明
    /// </summary>
    public class ShowEdit : IHttpHandler
    {
        //install-package newtonsoft.json
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "appliction/json";
            int id;
            AjaxResult ar = new AjaxResult();
            string a = context.Request["id"];
            if (int.TryParse(context.Request["id"].ToString(), out id))
            {
                UserService us = new UserService();
                User user = us.GetUserInfo(id);
                if(user ==null)
                {
                    ar.Status = "no";
                    ar.ErrorMsg = "查询失败，暂时没有该数据";
                    context.Response.Write(JsonConvert.SerializeObject(ar));
                }
                else
                {
                    ar.Data = user;
                    ar.Status = "ok";
                    ar.ErrorMsg = "查询成功";
                    context.Response.Write(JsonConvert.SerializeObject(ar));
                }
               
            }
            else
            {
                ar.Status = "no";
                ar.ErrorMsg = "输入的参数有错误，请重新输入";
                context.Response.Write(JsonConvert.SerializeObject(ar));
            }
        
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

6、ashx->html->AddUserInfo.html代码如下：

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <meta charset="utf-8" />
    <script src="../../js/jquery-3.1.1.min.js"></script>
    <script>
        function btn() {
            var formData = $("#formId").serializeArray();
            $.ajax({
                url: "../ashx/AddUser.ashx", dataType: "json", type: "post", data: formData,
                success: function (res) {
                    if (res.Status == "ok") {
                        alert(res.ErrorMsg + ",返回列表页");
                        window.location.href = "../ashx/List.ashx";
                    }
                    else {
                        alert(res.ErrorMsg);
                    }
                },
                error: function () {
                    alert("网络出错");
                }
            })
        }
        function btnF() {
            window.location = "../ashx/List.ashx";
        }
    </script>
</head>
<body>
    <form method="post" id="formId">
        <table>
            <tr>
                <td>用户名：</td>
                <td><input type="text" name="Name" id="Name" v-bind:value="Name" /></td>
            </tr>
            <tr>
                <td>手机号：</td>

                <td><input type="text" name="PhoneNum" id="PhoneNum" v-bind:value="PhoneNum" /></td>
            </tr>
            <tr>
                <td>邮箱：</td>
                <td><input type="text" name="Email" id="Email" v-bind:value="Email"></td>
            </tr>
            <tr>
                <td>密码盐：</td>
                <td><input type="text" name="PasswordSalt" id="PasswordSalt" v-bind:value="PasswordSalt" /></td>
            </tr>
            <tr>

                <td>密码哈希：</td>
                <td><input type="text" name="PasswordHash" id="PasswordHash" v-bind:value="PasswordHash" /></td>
            </tr>
            <tr>
                <td>登录错误的次数：</td>
                <td><input type="text" name="LoginErrorCount" id="LoginErrorCount" v-bind:value="LoginErrorCount" /></td>
            </tr>
            <tr>
                <td>最后登录的时间：</td>
                <td><input type="text" name="LastLoginErrorDateTime" id="LastLoginErrorDateTime" v-bind:value="LastLoginErrorDateTime" /></td>
            </tr>
            <tr>
                <td>创建日期：</td>
                <td><input type="text" name="CreateDateTime" id="CreateDateTime" v-bind:value="CreateDateTime" /></td>
            </tr>
            <tr>
                <td>是否删除：</td>
                <td><input type="text" name="IsDeleted" id="IsDeleted" v-bind:value="IsDeleted" /></td>
            </tr>
        </table>
        <input type="button" value="添加用户" onclick="btn()" />&nbsp;&nbsp;<input type="button" value="返回列表" id="returnList" onclick="btnF()" />
    </form>
</body>
</html>

7、ashx->html->List.html代码如下：

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <meta charset="utf-8" />
    <script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $(".deletes").click(function () {
                if (!confirm("确定要删除吗？")) {
                    return false;
                }
            })
        })
    </script>
</head>
<body>
    <a href="../html/AddUserInfo.html">添加</a>
    <table>
        <tr><th>编号</th><th>用户名</th><th>手机号</th><th>邮箱</th><th>密码盐</th><th>密码哈希</th><th>登录错误的次数</th><th>最后登录的时间</th><th>创建日期</th><th>是否逻辑删除</th><th>详细</th><th>删除</th><th>编辑</th></tr>
        @tbody
    </table>
</body>
</html>

8、ashx->html->ShowEdit.html代码如下：

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
    <meta charset="utf-8" />
    <script src="../../js/jquery-3.1.1.min.js"></script>
    <script src="https://cdn.staticfile.org/vue/2.2.2/vue.min.js"></script>
    <script type="text/javascript">
        $(function () {
            var val = UrlSearch();
            if (val != "no") {
                $.ajax({
                    url: "../ashx/ShowEdit.ashx", dataType: "json", type: "post", data: { id: val },
                    success: function (res) {
                        if (res.Status == "ok") {
                            new Vue({
                                el: '#formId',
                                data: res.Data
                            })
                        }
                        else {
                            alert(res.ErrorMsg);
                        }
                    },
                    error: function () {
                        alert("网络出错");
                    }
                })
            }
            else {
                alert("请求的参数有误，返回列表页");
                document.location = "../ashx/List.ashx";
            }

          
        })
        function btn() {
            var formData = $("#formId").serializeArray();
            $.ajax({
                url: "../ashx/EditUser.ashx", dataType: "json", type: "post", data: formData,
                success: function (res) {
                    if (res.Status == "ok") {
                        alert(res.ErrorMsg + ",返回列表页");
                        window.location.href = "../ashx/List.ashx";
                    }
                    else {
                        alert(res.ErrorMsg);
                    }
                },
                error: function () {
                    alert("网络出错");
                }
            })
        }
        function btnF() {
            document.location = "../ashx/List.ashx";
        }
     
        function UrlSearch() {
            var dict = [], value;
            var str1 = "";
            var url = location.href;
            var num = url.indexOf("?")
            value = url.substr(num + 1);
            var arr = value.split("&");
            for (var i = 0; i < arr.length; i++) {
                dict[arr[i].split('=')[0]] = arr[i].split('=')[1];
            }
            if (dict.id != undefined) {
                return dict.id;
            } else {
                return "no";
            }
        }
    </script>
</head>
<body>
    <form method="post" id="formId">
        <input type="hidden" name="Id" id="Id" v-bind:value="Id" />
        <table>
            <tr>
                <td>用户名：</td>
                <td><input type="text" name="Name" id="Name" v-bind:value="Name" /></td>
            </tr>
            <tr>
                <td>手机号：</td>

                <td><input type="text" name="PhoneNum" id="PhoneNum" v-bind:value="PhoneNum" /></td>
            </tr>
            <tr>
                <td>邮箱：</td>
                <td><input type="text" name="Email" id="Email" v-bind:value="Email"></td>
            </tr>
            <tr>
                <td>密码盐：</td>
                <td><input type="text" name="PasswordSalt" id="PasswordSalt" v-bind:value="PasswordSalt" /></td>
            </tr>
            <tr>

                <td>密码哈希：</td>
                <td><input type="text" name="PasswordHash" id="PasswordHash" v-bind:value="PasswordHash" /></td>
            </tr>
            <tr>
                <td>登录错误的次数：</td>
                <td><input type="text" name="LoginErrorCount" id="LoginErrorCount" v-bind:value="LoginErrorCount" /></td>
            </tr>
            <tr>
                <td>最后登录的时间：</td>
                <td><input type="text" name="LastLoginErrorDateTime" id="LastLoginErrorDateTime" v-bind:value="LastLoginErrorDateTime" /></td>
            </tr>
            <tr>
                <td>创建日期：</td>
                <td><input type="text" name="CreateDateTime" id="CreateDateTime" v-bind:value="CreateDateTime" /></td>
            </tr>
            <tr>
                <td>是否删除：</td>
                <td><input type="text" name="IsDeleted" id="IsDeleted" v-bind:value="IsDeleted" /></td>
            </tr>
        </table>
        <input type="button" value="修改用户" v-on:click="btn()"  />&nbsp;&nbsp;<input type="button" value="返回列表" id="returnList" v-on:click="btnF()" />
    </form>
</body>
</html>

9、Model->Entities->User.cs

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

namespace 一个表的增删改查.Model.Entities
{
    public class User
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string PhoneNum { get; set; }
        public string Email { get; set; }
        public string PasswordSalt { get; set; }
        public string PasswordHash { get; set; }
        public int LoginErrorCount { get; set; }
        public string LastLoginErrorDateTime { get; set; }
        public DateTime CreateDateTime { get; set; }
        public bool IsDeleted { get; set; }
    }
}

10、、Model->Service->UserService.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using 一个表的增删改查.Model.Entities;

namespace 一个表的增删改查.Model.Service
{
    public class UserService
    {
        public List<User> GetList()
        {
            string sql = "select * from T_Users where IsDeleted = 0";
            DataTable dt = SqlHelper.GetDataTable(sql, CommandType.Text);
            List<User> list = null;
            if(dt.Rows.Count > 0)
            {
                list = new List<User>();
                User user = null;
                foreach(DataRow row in dt.Rows)
                {
                    user = new User();
                    LoadEntity(user, row);
                    list.Add(user);
                }
            }
            return list;
        }

        public User GetUserInfo(int id)
        {
            string sql = "select * from T_Users where Id=@Id and IsDeleted = 0";
            SqlParameter p = new SqlParameter("@Id", SqlDbType.BigInt);
            p.Value = id;
            DataTable dt = SqlHelper.GetDataTable(sql, CommandType.Text, p);
            User userInfo = null;
            if(dt.Rows.Count >0)
            {
                userInfo = new User();
                LoadEntity(userInfo, dt.Rows[0]);
            }
            return userInfo;
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int EditUserInfo(User userInfo)
        {
            string sql = "update T_Users set Name=@Name,PhoneNum=@PhoneNum,Email=@Email,PasswordSalt=@PasswordSalt,PasswordHash=@PasswordHash,LoginErrorCount=@LoginErrorCount,LastLoginErrorDateTime=@LastLoginErrorDateTime,CreateDateTime=@CreateDateTime  where Id=@Id and IsDeleted=@IsDeleted";
            SqlParameter[] ps =
            {
                new SqlParameter("@Name",SqlDbType.NVarChar,50),
                new SqlParameter("@PhoneNum",SqlDbType.NVarChar,20),
                new SqlParameter("@Email",SqlDbType.NVarChar,30),
                new SqlParameter("@PasswordSalt",SqlDbType.NVarChar,20),
                new SqlParameter("@PasswordHash",SqlDbType.NVarChar,100),
                new SqlParameter("@LoginErrorCount",SqlDbType.Int),
                new SqlParameter("@LastLoginErrorDateTime",SqlDbType.DateTime),
                new SqlParameter("@CreateDateTime",SqlDbType.DateTime),
                new SqlParameter("@Id",SqlDbType.BigInt),
                new SqlParameter("@IsDeleted",SqlDbType.Bit)
            };
            ps[0].Value = userInfo.Name;
            ps[1].Value = userInfo.PhoneNum;
            ps[2].Value = userInfo.Email;
            ps[3].Value = userInfo.PasswordSalt;
            ps[4].Value = userInfo.PasswordHash;
            ps[5].Value = userInfo.LoginErrorCount;
            ps[6].Value = userInfo.LastLoginErrorDateTime;
            ps[7].Value = userInfo.CreateDateTime;
            ps[8].Value = userInfo.Id;
            ps[9].Value = userInfo.IsDeleted;

            return SqlHelper.ExecuteNonquery(sql, CommandType.Text, ps);

        }
        public int AddUserInfo(User userInfo)
        {
            string sql = "insert into T_Users(Name,PhoneNum,Email,PasswordSalt,PasswordHash,LoginErrorCount,LastLoginErrorDateTime,CreateDateTime,IsDeleted) values(@Name,@PhoneNum,@Email,@PasswordSalt,@PasswordHash,@LoginErrorCount,@LastLoginErrorDateTime,@CreateDateTime,@IsDeleted)";
            SqlParameter[] ps =
            {
                new SqlParameter("@Name",SqlDbType.NVarChar,50),
                new SqlParameter("@PhoneNum",SqlDbType.NVarChar,20),
                new SqlParameter("@Email",SqlDbType.NVarChar,30),
                new SqlParameter("@PasswordSalt",SqlDbType.NVarChar,20),
                new SqlParameter("@PasswordHash",SqlDbType.NVarChar,100),
                new SqlParameter("@LoginErrorCount",SqlDbType.Int),
                new SqlParameter("@LastLoginErrorDateTime",SqlDbType.DateTime),
                new SqlParameter("@CreateDateTime",SqlDbType.DateTime),
                new SqlParameter("@IsDeleted",SqlDbType.Bit)
            };
            ps[0].Value = userInfo.Name;
            ps[1].Value = userInfo.PhoneNum;
            ps[2].Value = userInfo.Email;
            ps[3].Value = userInfo.PasswordSalt;
            ps[4].Value = userInfo.PasswordHash;
            ps[5].Value = userInfo.LoginErrorCount;
            ps[6].Value = userInfo.LastLoginErrorDateTime;
            ps[7].Value = userInfo.CreateDateTime;
            ps[8].Value = userInfo.IsDeleted;

            return SqlHelper.ExecuteNonquery(sql, CommandType.Text, ps);

        }


        private void LoadEntity(User user, DataRow row)
        {
            //不严谨写法：userinfo.UserName = row["UserName"].ToString()
            //原因：如果UserName字段为空，就会抛出异常。
            user.Id = Convert.ToInt32(row["Id"]);
            user.Name = row["Name"] != DBNull.Value ? row["Name"].ToString() : string.Empty;
            user.PhoneNum = row["PhoneNum"] != DBNull.Value ? row["PhoneNum"].ToString() : string.Empty;
            user.Email = row["Email"] != DBNull.Value ? row["Email"].ToString() : string.Empty;
            user.PasswordSalt = row["PasswordSalt"] != DBNull.Value ? row["PasswordSalt"].ToString() : string.Empty;
            user.PasswordHash = row["PasswordHash"] != DBNull.Value ? row["PasswordHash"].ToString() : string.Empty;
            user.LoginErrorCount = Convert.ToInt32(row["LoginErrorCount"]);
            user.LastLoginErrorDateTime = row["LastLoginErrorDateTime"] != DBNull.Value ? row["LastLoginErrorDateTime"].ToString() : string.Empty ;
                 
            user.CreateDateTime = Convert.ToDateTime(row["CreateDateTime"]);
            user.IsDeleted = Convert.ToBoolean(row["IsDeleted"]);

        }
    }
}

11、AjaxResult.cs

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

namespace 一个表的增删改查.Model
{
    //所有ajax都要返回这个类型的对象
    public class AjaxResult
    {
        /// <summary>
        /// 执行的结果
        /// </summary>
        public string Status { get; set; }

        /// <summary>
        /// 错误消息
        /// </summary>
        public string ErrorMsg { get; set; }

        /// <summary>
        /// 执行返回的数据
        /// </summary>
        public object Data { get; set; }
    }
}

12、SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace 一个表的增删改查.Model
{
    public static class SqlHelper
    {
        //添加引用 System.configuration

        /// <summary>
        /// 从配置文件中读取链接字符串->私有;静态;只读;
        /// </summary>
        private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        /// <summary>
        /// 获取结果集，返回数据表类型->公共;静态
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">类型</param>
        /// <param name="ps">参数，可以不输入</param>
        /// <returns>以数据表的形式返回数据集</returns>
        public static DataTable GetDataTable(string sql, CommandType type, params SqlParameter[] ps)
        {
            //创建数据库连接对象
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //构造适配器对象
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                {
                    //判断参数是否为空，不为空执行添加参数
                    if (ps != null)
                    {
                        //添加参数
                        adapter.SelectCommand.Parameters.AddRange(ps);
                    }
                    //给查询命令语句赋予类型
                    adapter.SelectCommand.CommandType = type;
                    //构造数据表，用于接收查询结果
                    DataTable dt = new DataTable();
                    //执行结果
                    adapter.Fill(dt);
                    //返回结果
                    return dt;
                }
            }
        }
        /// <summary>
        /// 执行一个SQL语句，返回受影响的行数，这个方法主要用于执行对数据库执行增加、更新、删除操作，注意查询的时候不是调用这个方法。用于完成insert，delete，update操作。
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">类型</param>
        /// <param name="ps">参数，可以不输入</param>
        /// params 可变参数 目的是省略了手动构造数组的过程，直接指定对象，编译器会帮助我们构造数组，并将对象加入数组中，传递过来
        /// <returns>返回受影响的行数以整数形式返回</returns>
        public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] ps)
        {
            //创建数据库连接对象
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //构造数据库命令对象
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //判断参数是否为空
                    if (ps != null)
                    {
                        //不为空， 添加参数
                        cmd.Parameters.AddRange(ps);
                    }
                    //给查询命令语句赋予类型
                    cmd.CommandType = type;
                    //打开连接
                    conn.Open();
                    //执行命令，并返回受影响的行数
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 主要用于查询单行单列的值，如聚合函数（count,max,min,agv,sum）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">类型</param>
        /// <param name="ps">参数，可以不输入</param>
        /// <returns>这个方法是针对SQL语句执行的结果是一行一列的结果集，这个方法只返回查询结果集的第一行第一列</returns>
        public static object Executescalar(string sql, CommandType type, params SqlParameter[] ps)
        {
            //创建数据库连接对象
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //构造数据库命令对象
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //判断参数是否为空
                    if (ps != null)
                    {
                        //不为空， 添加参数
                        cmd.Parameters.AddRange(ps);
                    }
                    //给查询命令语句赋予类型
                    cmd.CommandType = type;
                    //打开连接
                    conn.Open();
                    //返回查询结果集的第一行第一列
                    return cmd.ExecuteScalar(); ;
                }
            }
        }
    }
}

 二、添加东西

1、asp->ShowList.aspx代码如下：

前端：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ShowList.aspx.cs" Inherits="一个表的增删改查.asp.ShowList" %>
<%@ Import Namespace="一个表的增删改查.Model.Entities" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>

  <a href="AddUser.aspx">添加</a>
    <table>
        <tr><th>编号</th><th>用户名</th><th>手机号</th><th>邮箱</th><th>密码盐</th><th>密码哈希</th><th>登录错误的次数</th><th>最后登录的时间</th><th>创建日期</th><th>是否逻辑删除</th><th>详细</th><th>删除</th><th>编辑</th></tr>
         <%foreach (User u in UserList) {%>
        <tr><th><%=u.Id %></th><th><%=u.Name %></th><th><%=u.PhoneNum %></th><th><%=u.Email %></th><th><%=u.PasswordSalt %></th><th><%=u.PasswordHash %></th><th><%=u.LoginErrorCount %>
         </th><th><%=u.LastLoginErrorDateTime %></th><th><%=u.CreateDateTime %></th><th><%=u.IsDeleted %></th><th><a>详情</a></th><th><a>删除</a></th><th><a href="UserEdit.aspx?id=<%=u.Id %>">编辑</a></th></tr>
        <%} %>

    </table>
</body>
</html>

后端：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using 一个表的增删改查.Model.Entities;
using 一个表的增删改查.Model.Service;

namespace 一个表的增删改查.asp
{
   
    public partial class ShowList : System.Web.UI.Page
    {
        public List<User> UserList { get; set; }
        protected void Page_Load(object sender, EventArgs e)
        {
            //IsPostBack:如果POST请求该属性的值为true,如果是GET请求该属性的值为false.
            if (!IsPostBack)
            {
                UserService us = new UserService();
                UserList = us.GetList();
                
            }
            else
            {

            }
        }
    }
}

2、asp->UserEdit.aspx

前端：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UserEdit.aspx.cs" Inherits="一个表的增删改查.asp.UserEdit" %>
<%@ Import Namespace="一个表的增删改查.Model.Entities" %>
<%@ Import Namespace="一个表的增删改查" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <script src="../js/jquery-3.1.1.min.js"></script>
    <script>
        function btnF() {
            window.location = "ShowList.aspx";
        }
        function btn() {
            var formData = $("#formId").serializeArray();
            $.ajax({
                url: "UserEdit.aspx", dataType: "json", type: "post", data: formData,
                success: function (res) {
                    if (res.Status == "ok") {
                        alert(res.ErrorMsg + ",返回列表页");
                        window.location.href = "ShowList.aspx";
                    }
                    else {
                        alert(res.ErrorMsg);
                    }
                },
                error: function () {
                    alert("网络出错");
                }
            })
        }

    </script>
</head>
<body>
    <%if (AR.Status == "ok")
        {%>
     <form method="post" id="formId">
       
          <input type="hidden" name="Id" id="Id" value="<%=((User)(AR.Data)).Id%>" />
        <table>
            <tr>
                <td>用户名：</td>
                <td><input type="text" name="Name" id="Name" value="<%=((User)(AR.Data)).Name%>" /></td>
            </tr>
            <tr>
                <td>手机号：</td>

                <td><input type="text" name="PhoneNum" id="PhoneNum" value="<%=((User)(AR.Data)).PhoneNum%>" /></td>
            </tr>
            <tr>
                <td>邮箱：</td>
                <td><input type="text" name="Email" id="Email" value="<%=((User)(AR.Data)).Email%>"></td>
            </tr>
            <tr>
                <td>密码盐：</td>
                <td><input type="text" name="PasswordSalt" id="PasswordSalt" value="<%=((User)(AR.Data)).PasswordSalt%>" /></td>
            </tr>
            <tr>

                <td>密码哈希：</td>
                <td><input type="text" name="PasswordHash" id="PasswordHash" value="<%=((User)(AR.Data)).PasswordHash%>" /></td>
            </tr>
            <tr>
                <td>登录错误的次数：</td>
                <td><input type="text" name="LoginErrorCount" id="LoginErrorCount" value="<%=((User)(AR.Data)).LoginErrorCount%>" /></td>
            </tr>
            <tr>
                <td>最后登录的时间：</td>
                <td><input type="text" name="LastLoginErrorDateTime" id="LastLoginErrorDateTime" value="<%=((User)(AR.Data)).LastLoginErrorDateTime%>" /></td>
            </tr>
            <tr>
                <td>创建日期：</td>
                <td><input type="text" name="CreateDateTime" id="CreateDateTime" value="<%=((User)(AR.Data)).CreateDateTime%>" /></td>
            </tr>
            <tr>
                <td>是否删除：</td>
                <td><input type="text" name="IsDeleted" id="IsDeleted" value="<%=((User)(AR.Data)).IsDeleted%>" /></td>
            </tr>
        </table>
        <input type="button" value="添加用户" onclick="btn()" />&nbsp;&nbsp;<input type="button" value="返回列表" id="returnList" onclick="btnF()" />
    </form>
    <%}else {%>
    <%=AR.ErrorMsg %>
    <% } %>
</body>
</html>

后端：

using System;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using 一个表的增删改查.Model;
using 一个表的增删改查.Model.Service;
using 一个表的增删改查.Model.Entities;

namespace 一个表的增删改查.asp
{
    public partial class UserEdit : System.Web.UI.Page
    {
        public AjaxResult AR { get; set; }
        protected void Page_Load(object sender, EventArgs e)
        {
            AjaxResult ar = new AjaxResult();
            
            if (!IsPostBack)
            {
                try
                {
                    int id;
                    if (int.TryParse(Request["id"].ToString(), out id))
                    {
                        UserService us = new UserService();
                        User user = us.GetUserInfo(id);
                        if (user == null)
                        {
                            ar.Status = "no";
                            ar.ErrorMsg = "查询失败，暂时没有该数据";
                            AR = ar;

                        }
                        else
                        {
                            ar.Data = user;
                            ar.Status = "ok";
                            ar.ErrorMsg = "查询成功";
                            AR = ar;
                        }

                    }
                    else
                    {
                        ar.Status = "no";
                        ar.ErrorMsg = "输入的参数有错误，请重新输入";
                        AR = ar;
                    }
                }
                catch
                {
                    Response.Redirect("ShowList.aspx");
                }
               
            }
            else
            {
                try
                {
                    UserService us = new UserService();
                    User userInfo = new User();
                    userInfo.Id = Int32.Parse(Request["Id"]);
                    userInfo.Name = Request["Name"];
                    userInfo.PhoneNum = Request["PhoneNum"];
                    userInfo.Email = Request["Email"];
                    userInfo.PasswordSalt = Request["PasswordSalt"];
                    userInfo.PasswordHash = Request["PasswordHash"];
                    userInfo.LoginErrorCount = Int32.Parse(Request.Form["LoginErrorCount"]);
                    userInfo.LastLoginErrorDateTime = Request.Form["LastLoginErrorDateTime"];
                    userInfo.CreateDateTime = DateTime.Parse(Request["CreateDateTime"]);
                    userInfo.IsDeleted = Boolean.Parse(Request["IsDeleted"]);
                    if (us.EditUserInfo(userInfo) > 0)
                    {
                        ar.Status = "ok";
                        ar.ErrorMsg = "修改成功";
                        Response.Write(JsonConvert.SerializeObject(ar));
                    }
                    else
                    {
                        ar.Status = "no";
                        ar.ErrorMsg = "修改失败";
                        Response.Write(JsonConvert.SerializeObject(ar));
                    }
                }
                catch
                {
                    ar.Status = "no";
                    ar.ErrorMsg = "参数有误，请输入正确的参数";
                    Response.Write(JsonConvert.SerializeObject(ar));
                }
            }
        }
    }
}