ASP.NET MVC和Bootstrap制作一个员工信息表

懒羊羊♛

于 2021-12-19 15:29:42 发布

阅读量909

点赞数

分类专栏： ASP.NET MVC 文章标签： mvc asp.net bootstrap

本文链接：https://blog.youkuaiyun.com/weixin_46596213/article/details/122023214

版权

ASP.NET MVC 专栏收录该内容

2 篇文章

订阅专栏

本文档详细介绍了如何使用ASP.NET MVC和Bootstrap构建一个员工信息表。首先，创建项目并添加BLL、Commons、DAL、DBUtility和Model类库，接着设计员工信息表并编写存储过程。在DBUtility层中建立数据库连接，然后在Model层创建tb_employee类，并在DAL层编写分页查询方法。BLL层负责业务逻辑，Home控制器处理请求并返回数据。最后，前端使用Bootstrap样式并添加JavaScript代码实现表格展示，通过data-formatter属性进行性别显示的格式转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

新建一个asp.net mvc的项目，然后在解决方案下添加以下几个类库：

BLL中是业务逻辑代码，Commons是公共的代码（比如MD5加密)，DAL数据访问代码，DBUtility是数据库连接代码，Model是实体模型

我们知道Web项目需要引用BLL层和Model层以及Common，BLL层需要引入DAL层和Model层、DBUtility层，DAL层需要引入Model层、DBUtility层。

这里的话Common引入了DAL层、Model层以及DBUtility层，DBUtility层引入了Model层

接下来我们先数据库的表设计好，因为我这里仅仅是做一个小demo，所以我只新建了一个员工信息表：

数据库：NlDB

create table tb_employee(
id int primary key identity(1000,1),--主键自增（账号）
employeeName nvarchar(10) not null,--姓名
employeeBirthday datetime,--出生日期
employeeSex int,--性别 0女 1男
employeeTel varchar(20),--电话
employeeIDCard varchar(20),--身份证号
employeeEdu int,--学历 1初中 2高中 3大专 4本科 5研究生 6硕士 7博士 
employeePwd varchar(100),--密码
employeeStatus int,--状态：在职or离职 0离职 1在职
employeeDept int,--部门 
employeePost int--职位
--部门和职位暂未规定
)

创建存储过程用来员工的数据信息：

暂定

添加完数据之后：

回到VS中，首先我们先建立数据库的连接，在DBUtility层中添加一个DbHelperSQL类：

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


public class DbHelperSQL
{
    #region 通用方法
    // 数据连接池
    private SqlConnection con;
    /// <summary>
    /// 返回数据库连接字符串
    /// </summary>
    /// <returns></returns>
    public static String GetSqlConnection()
    {
        String conn = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
        return conn;
    }
    public static String GetSqlConnectionKK()
    {
        String conn = ConfigurationManager.ConnectionStrings["connection_OfKK"].ToString();
        return conn;
    }
    #endregion
    #region 执行sql字符串
    /// <summary>
    /// 执行不带参数的SQL语句
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <returns></returns>
    public static int ExecuteSql(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandTimeout = 180;//时间设为60秒
            cmd.CommandText = Sqlstr;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            return 1;
        }
    }

    public static int ExecuteSql(String Sqlstr, SqlParameter[] param, ref string error)
    {
        int success = 0;
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                success = 1;
            }
            catch (Exception ex) { error = ex.Message; }
            return success;
        }
    }

    /// <summary>
    /// 执行带参数的SQL语句
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <param name="param">参数对象数组</param>
    /// <returns></returns>
    public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
    {
        int success = 0;
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                success = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return success;
        }
    }
    public static int ExecuteSqlKK(String Sqlstr, SqlParameter[] param)
    {
        int success = 0;
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                success = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return success;
        }
    }
    /// <summary>
    /// 执行SQL语句并返回DataSet
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <returns></returns>
    public static DataSet ExecuteDs(String Sqlstr, SqlParameter[] param)
    {
        String ConnStr = GetSqlConnection();
        SqlCommand cmd = new SqlCommand();
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            cmd.Parameters.AddRange(param);

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {

                conn.Open();
                da.Fill(ds);
                conn.Close();

            }

            return ds;
        }
    }
    public static DataSet Query(String Sqlstr, SqlParameter[] param)
    {
        String ConnStr = GetSqlConnection();
        SqlCommand cmd = new SqlCommand();
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            cmd.Parameters.AddRange(param);

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {

                conn.Open();
                da.Fill(ds);
                conn.Close();

            }

            return ds;
        }
    }
    /// <summary>
    /// 返回DataReader
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <returns></returns>
    public static SqlDataReader ExecuteReader(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            conn.Open();
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection
        }
        catch //(Exception ex)
        {
            return null;
        }
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string SQLString)
    {
        String ConnStr = GetSqlConnection();


        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            using (SqlCommand cmd = new SqlCommand(SQLString, conn))
            {
                try
                {
                    conn.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    conn.Close();
                    throw e;
                }
            }
        }
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
    {
        String ConnStr = GetSqlConnection();

        using (SqlConnection connection = new SqlConnection(ConnStr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
            }
        }
    }
    public static object GetSingleKK(string SQLString, params SqlParameter[] cmdParms)
    {
        String ConnStr = GetSqlConnectionKK();

        using (SqlConnection connection = new SqlConnection(ConnStr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
            }
        }
    }
    /// <summary>
    /// 执行SQL语句并返回数据表
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <returns></returns>
    public static DataTable ExecuteDt(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            conn.Close();
            return dt;
        }
    }
    /// <summary>
    /// 执行SQL语句并返回DataSet
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <returns></returns>
    public static DataSet ExecuteDs(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            return ds;
        }
    }
    /// <summary>
    /// 快速插入大批量数据
    /// </summary>
    /// <param name="dt">需要插入数据库的数据</param>
    /// <param name="tableName">数据库表名称</param>
    public static void BatchInsertBySqlBulkCopy(DataTable dt, string tableName)
    {
        using (SqlBulkCopy sbc = new SqlBulkCopy(GetSqlConnection()))
        {
            sbc.BatchSize = dt.Rows.Count;
            sbc.BulkCopyTimeout = 10;
            sbc.DestinationTableName = tableName;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, i);
            }
            //全部写入数据库
            sbc.WriteToServer(dt);
        }
    }
    /// <summary>
    /// 执行SQL语句并返回DataSet
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <returns></returns>
    public static DataSet Query(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            return ds;
        }
    }

    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>		
    public static int ExecuteSqlTran(List<string> SQLStringList)
    {
        String ConnStr = GetSqlConnection();
        int status = 0;
        string sql1 = "";
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        sql1 = strsql;
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                status = 1;
            }
            catch (Exception ex)
            {
                LogHelper.m_CreateErrorLogTxt("ExecuteSqlTran", "1:" + DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") + ":" + sql1, ex.Message);
                tx.Rollback();
                // throw new Exception(E.Message);
            }
        }

        return status;
    }

    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>		
    public static int ExecuteSqlTran(List<string> SQLStringList, ref string error)
    {
        String ConnStr = GetSqlConnection();
        int status = 0;
        string sql1 = "";
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    LogHelper.m_CreateErrorLogTxt("strsql-" + n + "：" + strsql, "", "");
                    if (strsql.Trim().Length > 1)
                    {
                        sql1 = strsql;
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                status = 1;
            }
            catch (Exception ex)
            {
                error = ex.Message + "sql:" + sql1;
                LogHelper.m_CreateErrorLogTxt("ExecuteSqlTran", "1:" + DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") + ":" + sql1, ex.Message);
                tx.Rollback();
                // throw new Exception(E.Message);
            }
        }

        return status;
    }
    public static int ExecuteSqlTranKK(List<string> SQLStringList, ref string error)
    {
        String ConnStr = GetSqlConnectionKK();
        int status = 0;
        string sql1 = "";
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    //LogHelper.m_CreateErrorLogTxt("strsql-" + n + "：" + strsql, "", "");
                    if (strsql.Trim().Length > 1)
                    {
                        sql1 = strsql;
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                status = 1;
            }
            catch (Exception ex)
            {
                error = ex.Message + "sql:" + sql1;
                LogHelper.m_CreateErrorLogTxt("ExecuteSqlTran", "1:" + DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") + ":" + sql1, ex.Message);
                tx.Rollback();
                // throw new Exception(E.Message);
            }
        }

        return status;
    }
    private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        if (trans != null)
            cmd.Transaction = trans;
        cmd.CommandType = CommandType.Text;//cmdType;
        if (cmdParms != null)
        {


            foreach (SqlParameter parameter in cmdParms)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }
    }
    public static bool Exists(string strSql)
    {
        object obj = GetSingle(strSql);
        int cmdresult;
        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString()); //也可能=0
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    public static bool Exists(string strSql, params SqlParameter[] cmdParms)
    {
        object obj = GetSingle(strSql, cmdParms);
        int cmdresult;
        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString());
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    public static bool ExistsKK(string strSql, params SqlParameter[] cmdParms)
    {
        object obj = GetSingleKK(strSql, cmdParms);
        int cmdresult;
        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString());
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    public static int GetMaxID(string FieldName, string TableName)
    {
        string strsql = "select max(" + FieldName + ")+1 from " + TableName;
        object obj = GetSingle(strsql);
        if (obj == null)
        {
            return 1;
        }
        else
        {
            return int.Parse(obj.ToString());
        }
    }


    #endregion
    #region 操作存储过程
    /// <summary>
    /// 运行存储过程(已重载)
    /// </summary>
    /// <param name="procName">存储过程的名字</param>
    /// <returns>存储过程的返回值</returns>
    public int RunProc(string procName)
    {
        SqlCommand cmd = CreateCommand(procName, null);
        cmd.ExecuteNonQuery();
        this.Close();
        return (int)cmd.Parameters["ReturnValue"].Value;
    }
    /// <summary>
    /// 运行存储过程(已重载)
    /// </summary>
    /// <param name="procName">存储过程的名字</param>
    /// <param name="prams">存储过程的输入参数列表</param>
    /// <returns>存储过程的返回值</returns>
    public string proc11(string procName, string name, string val)
    {



        Open();
        //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
        SqlCommand cmd = new SqlCommand(procName, con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter sqlParme;

        cmd.Parameters.AddWithValue("@" + name, val);


        sqlParme = cmd.Parameters.Add("@res", SqlDbType.VarChar, 100);
        sqlParme.Direction = ParameterDirection.Output;

        SqlParameter parReturn = new SqlParameter("@res", SqlDbType.VarChar);
        parReturn.Direction = ParameterDirection.ReturnValue;   //参数类型为ReturnValue                    cmd.Parameters.Add(parReturn);

        cmd.Parameters.Add(parReturn);



        cmd.ExecuteNonQuery();

        this.Close();


        return (string)cmd.Parameters[1].Value;
    }
    /// <summary>
    /// 运行存储过程(已重载)
    /// </summary>
    /// <param name="procName">存储过程的名字</param>
    /// <param name="prams">存储过程的输入参数列表</param>
    /// <returns>存储过程的返回值</returns>
    public int RunProc(string procName, SqlParameter[] prams)
    {
        SqlCommand cmd = CreateCommand(procName, prams);
        cmd.ExecuteNonQuery();
        this.Close();
        return (int)cmd.Parameters[0].Value;
    }
    /// <summary>
    /// 运行存储过程(已重载)
    /// </summary>
    /// <param name="procName">存储过程的名字</param>
    /// <param name="dataReader">结果集</param>
    public void RunProc(string procName, out SqlDataReader dataReader)
    {
        SqlCommand cmd = CreateCommand(procName, null);
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    /// <summary>
    /// 运行存储过程(已重载)
    /// </summary>
    /// <param name="procName">存储过程的名字</param>
    /// <param name="prams">存储过程的输入参数列表</param>
    /// <param name="dataReader">结果集</param>
    public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
    {
        SqlCommand cmd = CreateCommand(procName, prams);
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    /// <summary>
    /// 创建Command对象用于访问存储过程
    /// </summary>
    /// <param name="procName">存储过程的名字</param>
    /// <param name="prams">存储过程的输入参数列表</param>
    /// <returns>Command对象</returns>
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
        // 确定连接是打开的
        Open();
        //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
        SqlCommand cmd = new SqlCommand(procName, con);
        cmd.CommandType = CommandType.StoredProcedure;
        // 添加存储过程的输入参数列表
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                cmd.Parameters.Add(parameter);
        }
        // 返回Command对象
        return cmd;
    }
    /// <summary>
    /// 创建输入参数
    /// </summary>
    /// <param name="ParamName">参数名</param>
    /// <param name="DbType">参数类型</param>
    /// <param name="Size">参数大小</param>
    /// <param name="Value">参数值</param>
    /// <returns>新参数对象</returns>
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }
    /// <summary>
    /// 创建输出参数
    /// </summary>
    /// <param name="ParamName">参数名</param>
    /// <param name="DbType">参数类型</param>
    /// <param name="Size">参数大小</param>
    /// <returns>新参数对象</returns>
    public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }
    /// <summary>
    /// 创建存储过程参数
    /// </summary>
    /// <param name="ParamName">参数名</param>
    /// <param name="DbType">参数类型</param>
    /// <param name="Size">参数大小</param>
    /// <param name="Direction">参数的方向(输入/输出)</param>
    /// <param name="Value">参数值</param>
    /// <returns>新参数对象</returns>
    public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
        SqlParameter param;
        if (Size > 0)
        {
            param = new SqlParameter(ParamName, DbType, Size);
        }
        else
        {
            param = new SqlParameter(ParamName, DbType);
        }
        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null))
        {
            param.Value = Value;
        }
        return param;
    }
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <param name="tableName">DataSet结果中的表名</param>
    /// <returns>DataSet</returns>
    public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
    {
        using (SqlConnection connection = new SqlConnection(GetSqlConnection()))
        {
            DataSet dataSet = new DataSet();
            SqlCommand com = new SqlCommand(storedProcName, connection);
            com.CommandType = CommandType.StoredProcedure;
            if (parameters != null && parameters.Length > 0)
            {
                foreach (SqlParameter pp in parameters)//把参数集全部加进去
                    com.Parameters.Add(pp);
            }

            SqlDataAdapter adpter = new SqlDataAdapter(com);
            adpter.Fill(dataSet, tableName);
            return dataSet;
        }
    }

    #endregion
    #region 数据库连接和关闭
    /// <summary>
    /// 打开连接池
    /// </summary>
    private void Open()
    {
        // 打开连接池
        if (con == null)
        {
            //这里不仅需要using System.Configuration;还要在引用目录里添加
            con = new SqlConnection(GetSqlConnection());
            con.Open();
        }
    }
    /// <summary>
    /// 关闭连接池
    /// </summary>
    public void Close()
    {
        if (con != null)
            con.Close();
    }
    /// <summary>
    /// 释放连接池
    /// </summary>
    public void Dispose()
    {
        // 确定连接已关闭
        if (con != null)
        {
            con.Dispose();
            con = null;
        }
    }
    #endregion
}

然后我们需要web项目下的web.config文件中配置一个节点：

<connectionStrings>
		<add name="connectionString" connectionString="Data Source=;Initial Catalog=NlDB;Integrated Security=True;" providerName="System.Data.SqlClient" />
	</connectionStrings>

在DBUtility层还用到了另外一个LogHelper类：

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;


public class LogHelper
{

    #region  创建错误日志
    ///-----------------------------------------------------------------------------
    /// <summary>创建错误日志 在c:\ErrorLog\</summary>
    /// <param name="strFunctionName">strFunctionName,调用方法名</param>
    /// <param name="strErrorNum">strErrorNum,错误号</param>
    /// <param name="strErrorDescription">strErrorDescription,错误内容</param>
    /// <returns></returns>
    /// <history>2009-05-29 Created</history>
    public static void m_CreateErrorLogTxt(string strErrorDescription)
    {
        string strMatter; //错误内容
        string strPath; //错误文件的路径
        DateTime dt = DateTime.Now;
        try
        {
            //Server.MapPath("./") + "File"; 服务器端路径
            strPath = System.AppDomain.CurrentDomain.BaseDirectory + "\\ErrorLog";
            // strPath = Directory.GetDirectoryRoot() + "\\ErrorLog";   //winform工程\bin\目录下 创建日志文件夹 
            //strPath = "c:" + "\\ErrorLog";//暂时放在c:下

            if (Directory.Exists(strPath) == false)  //工程目录下 Log目录 '目录是否存在,为true则没有此目录
            {
                Directory.CreateDirectory(strPath); //建立目录　Directory为目录对象
            }
            strPath = strPath + "\\" + dt.ToString("yyyyMM");

            if (Directory.Exists(strPath) == false)  //目录是否存在  '工程目录下 Log\月 目录   yyyymm
            {
                Directory.CreateDirectory(strPath);  //建立目录//日志文件，以 日 命名 
            }
            strPath = strPath + "\\" + dt.ToString("dd") + ".txt";

            strMatter = strErrorDescription;//生成错误信息

            StreamWriter FileWriter = new StreamWriter(strPath, true); //创建日志文件
            FileWriter.WriteLine("Time: " + dt.ToString("HH:mm:ss") + "  Err: " + strMatter);
            FileWriter.Close(); //关闭StreamWriter对象
        }
        catch (Exception ex)
        {

            string str = ex.Message.ToString();
        }
    }
    public static void m_CreateErrorLogTxt(string strFunctionName, string strErrorNum, string strErrorDescription)
    {
        string strMatter; //错误内容
        string strPath; //错误文件的路径
        DateTime dt = DateTime.Now;
        try
        {
            //Server.MapPath("./") + "File"; 服务器端路径
            strPath = System.AppDomain.CurrentDomain.BaseDirectory + "\\ErrorLog";
            // strPath = Directory.GetDirectoryRoot() + "\\ErrorLog";   //winform工程\bin\目录下 创建日志文件夹 
            //strPath = "c:" + "\\ErrorLog";//暂时放在c:下

            if (Directory.Exists(strPath) == false)  //工程目录下 Log目录 '目录是否存在,为true则没有此目录
            {
                Directory.CreateDirectory(strPath); //建立目录　Directory为目录对象
            }
            strPath = strPath + "\\" + dt.ToString("yyyyMM");

            if (Directory.Exists(strPath) == false)  //目录是否存在  '工程目录下 Log\月 目录   yyyymm
            {
                Directory.CreateDirectory(strPath);  //建立目录//日志文件，以 日 命名 
            }
            strPath = strPath + "\\" + dt.ToString("dd") + ".txt";

            strMatter = strFunctionName + " , " + strErrorNum + " , " + strErrorDescription;//生成错误信息

            StreamWriter FileWriter = new StreamWriter(strPath, true); //创建日志文件
            FileWriter.WriteLine("Time: " + dt.ToString("HH:mm:ss") + "  Err: " + strMatter);
            FileWriter.Close(); //关闭StreamWriter对象
        }
        catch (Exception ex)
        {

            string str = ex.Message.ToString();
        }
    }
    #endregion
    #region 数据日志
    public static void m_CreateDataLogTxt(string strFunctionName, string strErrorNum, string strErrorDescription)
    {
        string strMatter; //错误内容
        string strPath; //错误文件的路径
        DateTime dt = DateTime.Now;
        try
        {
            //Server.MapPath("./") + "File"; 服务器端路径
            strPath = System.AppDomain.CurrentDomain.BaseDirectory + "\\DataLog";
            // strPath = Directory.GetDirectoryRoot() + "\\ErrorLog";   //winform工程\bin\目录下 创建日志文件夹 
            //strPath = "c:" + "\\ErrorLog";//暂时放在c:下

            if (Directory.Exists(strPath) == false)  //工程目录下 Log目录 '目录是否存在,为true则没有此目录
            {
                Directory.CreateDirectory(strPath); //建立目录　Directory为目录对象
            }
            strPath = strPath + "\\" + dt.ToString("yyyyMM");

            if (Directory.Exists(strPath) == false)  //目录是否存在  '工程目录下 Log\月 目录   yyyymm
            {
                Directory.CreateDirectory(strPath);  //建立目录//日志文件，以 日 命名 
            }
            strPath = strPath + "\\" + dt.ToString("dd") + ".txt";

            strMatter = strFunctionName + " , " + strErrorNum + " , " + strErrorDescription;//生成错误信息

            StreamWriter FileWriter = new StreamWriter(strPath, true); //创建日志文件
            FileWriter.WriteLine("Time: " + dt.ToString("HH:mm:ss") + "  Info: " + strMatter);
            FileWriter.Close(); //关闭StreamWriter对象
        }
        catch (Exception ex)
        {

            string str = ex.Message.ToString();
        }
    }


    public static void m_CreateInfoLogTxt(string strFunctionName, string strErrorNum, string strErrorDescription)
    {
        string strMatter; //错误内容
        string strPath; //错误文件的路径
        DateTime dt = DateTime.Now;
        try
        {
            //Server.MapPath("./") + "File"; 服务器端路径
            strPath = System.AppDomain.CurrentDomain.BaseDirectory + "\\InfoLog";
            // strPath = Directory.GetDirectoryRoot() + "\\ErrorLog";   //winform工程\bin\目录下 创建日志文件夹 
            //strPath = "c:" + "\\ErrorLog";//暂时放在c:下

            if (Directory.Exists(strPath) == false)  //工程目录下 Log目录 '目录是否存在,为true则没有此目录
            {
                Directory.CreateDirectory(strPath); //建立目录　Directory为目录对象
            }
            strPath = strPath + "\\" + dt.ToString("yyyyMM");

            if (Directory.Exists(strPath) == false)  //目录是否存在  '工程目录下 Log\月 目录   yyyymm
            {
                Directory.CreateDirectory(strPath);  //建立目录//日志文件，以 日 命名 
            }
            strPath = strPath + "\\" + dt.ToString("dd") + ".txt";

            strMatter = strFunctionName + " , " + strErrorNum + " , " + strErrorDescription;//生成错误信息

            StreamWriter FileWriter = new StreamWriter(strPath, true); //创建日志文件
            FileWriter.WriteLine("Time: " + dt.ToString("HH:mm:ss") + "  Err: " + strMatter);
            FileWriter.Close(); //关闭StreamWriter对象
        }
        catch (Exception ex)
        {

            string str = ex.Message.ToString();
        }
    }

    public static void m_CreateTestLogTxt(string strFunctionName, string strErrorNum, string strErrorDescription)
    {
        string strMatter; //错误内容
        string strPath; //错误文件的路径
        DateTime dt = DateTime.Now;
        try
        {
            //Server.MapPath("./") + "File"; 服务器端路径
            strPath = System.AppDomain.CurrentDomain.BaseDirectory + "\\TestLog";
            // strPath = Directory.GetDirectoryRoot() + "\\ErrorLog";   //winform工程\bin\目录下 创建日志文件夹 
            //strPath = "c:" + "\\ErrorLog";//暂时放在c:下

            if (Directory.Exists(strPath) == false)  //工程目录下 Log目录 '目录是否存在,为true则没有此目录
            {
                Directory.CreateDirectory(strPath); //建立目录　Directory为目录对象
            }
            strPath = strPath + "\\" + dt.ToString("yyyyMM");

            if (Directory.Exists(strPath) == false)  //目录是否存在  '工程目录下 Log\月 目录   yyyymm
            {
                Directory.CreateDirectory(strPath);  //建立目录//日志文件，以 日 命名 
            }
            strPath = strPath + "\\" + dt.ToString("dd") + ".txt";

            strMatter = strFunctionName + " , " + strErrorNum + " , " + strErrorDescription;//生成错误信息

            StreamWriter FileWriter = new StreamWriter(strPath, true); //创建日志文件
            FileWriter.WriteLine("Time: " + dt.ToString("HH:mm:ss") + "  Err: " + strMatter);
            FileWriter.Close(); //关闭StreamWriter对象
        }
        catch (Exception ex)
        {

            string str = ex.Message.ToString();
        }
    }


    #endregion



}



/// <summary>
/// 优先级
/// </summary>
public enum EPriority
{
    /// <summary>
    /// 高
    /// </summary>
    High = 16,

    /// <summary>
    /// 普通
    /// </summary>
    Normal = 10,

    /// <summary>
    /// 低
    /// </summary>
    Low = 1,
}

这样数据库的连接就建立好了

然后我们在Model层添加一个tb_employee类：

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

namespace AjaxPageModel
{
    public class tb_employee
    {
        private int id;
        private string employeeName;
        private DateTime employeeBirthday;
        private int employeeSex;
        private string employeeTel;
        private string employeeIDCard;
        private int employeeEdu;
        private string employeePwd;
        private int employeeStatus;
        private int employeeDept;
        private int employeePost;

        public int Id { get => id; set => id = value; }
        public string EmployeeName { get => employeeName; set => employeeName = value; }
        public DateTime EmployeeBirthday { get => employeeBirthday; set => employeeBirthday = value; }
        public int EmployeeSex { get => employeeSex; set => employeeSex = value; }
        public string EmployeeTel { get => employeeTel; set => employeeTel = value; }
        public string EmployeeIDCard { get => employeeIDCard; set => employeeIDCard = value; }
        public int EmployeeEdu { get => employeeEdu; set => employeeEdu = value; }
        public string EmployeePwd { get => employeePwd; set => employeePwd = value; }
        public int EmployeeStatus { get => employeeStatus; set => employeeStatus = value; }
        public int EmployeeDept { get => employeeDept; set => employeeDept = value; }
        public int EmployeePost { get => employeePost; set => employeePost = value; }
    }
}

在DAL层建一个tb_employeeDAL类：

因为要实现分页的功能，所以需要写两个方法，其中GetListByPage用来获取数据，GetRecordCount用来获取总行数

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

namespace AjaxPageDAL
{
    public class tb_employeeDAL
    {
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by " + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from tb_employee  T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE  " + strWhere + "");
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary> 
        /// 获得记录条数 
        /// </summary> 
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) ");
            strSql.Append(" FROM  tb_employee");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere + " and id>=1");
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj != null && obj.ToString() != "")
                return Convert.ToInt32(obj);
            else
                return 0;
        }
    }
}

在BLL层添加一个tb_employeeBLL类：

用DataSet保存查询到的数据，然后转换成List集合

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AjaxPageModel;
using AjaxPageDAL;

namespace AjaxPageBLL
{
    public class tb_employeeBLL
    {
        private readonly tb_employeeDAL dal = new tb_employeeDAL();
        public List<tb_employee> GetModelList(string strWhere, string orderby, int startIndex, int endIndex)
        {
            DataSet ds = dal.GetListByPage(strWhere, orderby, startIndex, endIndex);
            return DataTableToList1(ds.Tables[0]);
        }
        public List<tb_employee> DataTableToList1(DataTable dt)
        {
            List<tb_employee> modelList = new List<tb_employee>();
            if (dt != null)
            {
                int rowsCount = dt.Rows.Count;
                if (rowsCount > 0)
                {
                    tb_employee model;
                    for (int n = 0; n < rowsCount; n++)
                    {
                        model = new tb_employee();
                        if (dt.Rows[n]["Id"] != null && dt.Rows[n]["Id"].ToString() != "")
                        {
                            model.Id = int.Parse(dt.Rows[n]["Id"].ToString());
                        }
                        if (dt.Rows[n]["EmployeeName"] != null && dt.Rows[n]["EmployeeName"].ToString() != "")
                        {
                            model.EmployeeName = dt.Rows[n]["EmployeeName"].ToString();
                        }
                        if (dt.Rows[n]["EmployeeBirthday"] != null && dt.Rows[n]["EmployeeBirthday"].ToString() != "")
                        {
                            model.EmployeeBirthday = DateTime.Parse(dt.Rows[n]["EmployeeBirthday"].ToString());
                        }
                        if (dt.Rows[n]["EmployeeSex"] != null && dt.Rows[n]["EmployeeSex"].ToString() != "")
                        {
                            model.EmployeeSex = int.Parse(dt.Rows[n]["EmployeeSex"].ToString());
                        }
                        if (dt.Rows[n]["EmployeeTel"] != null && dt.Rows[n]["EmployeeTel"].ToString() != "")
                        {
                            model.EmployeeTel = dt.Rows[n]["EmployeeTel"].ToString();
                        }
                        if (dt.Rows[n]["EmployeeIDCard"] != null && dt.Rows[n]["EmployeeIDCard"].ToString() != "")
                        {
                            model.EmployeeIDCard = dt.Rows[n]["EmployeeIDCard"].ToString();
                        }
                        if (dt.Rows[n]["EmployeeEdu"] != null && dt.Rows[n]["EmployeeEdu"].ToString() != "")
                        {
                            model.EmployeeEdu = int.Parse(dt.Rows[n]["EmployeeEdu"].ToString());
                        }
                        if (dt.Rows[n]["EmployeePwd"] != null && dt.Rows[n]["EmployeePwd"].ToString() != "")
                        {
                            model.EmployeePwd = dt.Rows[n]["EmployeePwd"].ToString();
                        }
                        if (dt.Rows[n]["EmployeeStatus"] != null && dt.Rows[n]["EmployeeStatus"].ToString() != "")
                        {
                            model.EmployeeStatus =int.Parse( dt.Rows[n]["EmployeeStatus"].ToString());
                        }
                        if (dt.Rows[n]["EmployeeDept"] != null && dt.Rows[n]["EmployeeDept"].ToString() != "")
                        {
                            model.EmployeeDept = int.Parse(dt.Rows[n]["EmployeeDept"].ToString());
                        }
                        if (dt.Rows[n]["EmployeePost"] != null && dt.Rows[n]["EmployeePost"].ToString() != "")
                        {
                            model.EmployeePost = int.Parse(dt.Rows[n]["EmployeePost"].ToString());
                        }
                        modelList.Add(model);
                    }
                }
            }
            return modelList;
        }
        public int GetRecordCount(string strWhere)
        {

            return dal.GetRecordCount(strWhere);
        }
    }
}

在Home控制器中新建一个操作方法并添加视图（不需要基于布局页面）：

写一个返回数据的操作方法：

 public ActionResult EmployeeInfoPageDate(int pageNumber, int pageSize, string where, string orderby)
        {
            _EmployeeBLL = new tb_employeeBLL();
            BootstrapModel<tb_employee> model = new BootstrapModel<tb_employee>();
            string where1 = "";
            if (where != null && where != "")
            {
                where1 = "1=1 " + where;
            }
            else
            {
                where1 = "1=1 ";
            }
            //当前页数 
            int limit = pageNumber;
            //分页数 
            int offset = pageSize;
            int startIndex = (limit - 1) * offset + 1;
            int endIndex = limit * offset;

            int total = _EmployeeBLL.GetRecordCount(where1);
            List<tb_employee> ls = _EmployeeBLL.GetModelList(where1, "", startIndex, endIndex);
            model.rows = ls;
            model.total = total;
            IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
            timeFormat.DateTimeFormat = "yyyy-MM-dd";
            string json = JsonConvert.SerializeObject(model, Newtonsoft.Json.Formatting.Indented, timeFormat);
            return Content(json);
        }

 public class BootstrapModel<T>
        {
            public List<T> rows { get; set; }
            public int total { get; set; }
        }

回到前台页面：

以下是我们需要引入的css文件以及

然后我们在body中添加一个div:

 <div class="wrapper-md">
        <div class="row">
            <div class="col-sm-12">

                <div class="panel panel-default">
                    <div class="panel-heading">
                        <strong>以下是员工的信息：</strong>
                    </div>
                    <div class="table-responsive" style="margin:10px 15px 10px 15px;">
                        <table id="tb1">
                            <thead>
                                <tr>
                                    <th data-field="Id" data-editable='true'> ID </th>
                                    <th data-field="EmployeeName" data-editable='true'> 姓名 </th>
                                    <th data-field="EmployeeBirthday" data-editable='true'> 出生日期</th>
                                    <th data-field="EmployeeSex" data-editable='true'> 性别</th>
                                    <th data-field="EmployeeTel" data-editable='true'>电话</th>
                                    <th data-field="EmployeeIDCard" data-editable='true'> 身份证号码</th>
                                    <th data-field="EmployeeEdu" data-editable='true'> 学历</th>
                                    <th data-field="EmployeePwd" data-editable='true'> 密码</th>
                                    <th data-field="EmployeeDept" data-editable='true'> 部门</th>
                                    <th data-field="EmployeePost" data-editable='true'> 职位</th>
                                    <th data-field="EmployeeStatus" data-editable='true'>状态</th>
                            </thead>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>

script标签的代码：

<script type="text/javascript">
        $(function () {
            getDataList();
        });
        //1.获取列表
        function getDataList() {
            $('#tb1').bootstrapTable({
                url: '../Home/EmployeeInfoPageDate',
                method: 'get',                      //请求方式（*）
                //  toolbar: '#toolbar',                //工具按钮用哪个容器
                striped: false,                      //是否显示行间隔色
                cache: false,                       //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
                pagination: true,                   //是否显示分页（*）
                sortable: true,                     //是否启用排序
                singleSelect: true,                  // 单选checkbox
                sortOrder: "desc",                   //排序方式
                queryParams: queryParams,//传递参数（*）
                pageNumber: 1,//
                //每页的记录行数（*）
                pageSize: 10,
                //可供选择的每页的行数（*）
                pageList: [5, 10, 25, 50],
                sidePagination: 'server',            //
                queryParamsType: 'limit',
                search: false,                       //是否显示表格搜索，此搜索是客户端搜索，不会进服务端，所以，个人感觉意义不大
                contentType: "application/x-www-form-urlencoded",
                strictSearch: false,                //精准搜索
                showColumns: false,                  //是否显示所有的列
                showRefresh: false,                  //是否显示刷新按钮
                minimumCountColumns: 2,             //最少允许的列数
                clickToSelect: true,                //是否启用点击选中行
                // height: 700,                        //行高，如果没有设置height属性，表格自动根据记录条数觉得表格高度
                uniqueId: "no",                     //每一行的唯一标识，一般为主键列
                showToggle: false,                    //是否显示详细视图和列表视图的切换按钮
                cardView: false,                    //是否显示详细视图
                detailView: false                //是否显示父子表
            });
        }
        //2.查询条件
        function queryParams(params) {
            var pageNum = 1;
            var pageSize = params.limit;
            if (params.offset > 0) {
                pageNum = params.offset / params.limit + 1;
            }
            var where1 = "";
            //得到查询的参数
            var temp = {   //这里的键的名字和控制器的变量名必须一直，这边改动，控制器也需要改成一样的
                pageNumber: pageNum,
                pageSize: pageSize,
                where: where1
            };
            return temp;
        }
    </script>

最终的效果图：

如果我们想要性别显示男和女该怎么做呢？

在需要进行格式转换的列上添加一个data-formatter属性

 function getEmployeeSex(value, row, index) {
            var EmployeeSex = "";
            if (row.EmployeeSex == 0)
                EmployeeSex = "女";
            if (row.EmployeeSex == 1)
                EmployeeSex = "男";
            return EmployeeSex;
        }
        function getEmployEdu(value, row, index) {
            var employeeEdu = "";
            //1初中 2高中 3大专 4本科 5研究生 6硕士 7博士
            if (row.EmployeeEdu==1) {
                employeeEdu = "初中";
            }
            if (row.EmployeeEdu == 2) {
                employeeEdu = "高中";
            }
            if (row.EmployeeEdu == 3) {
                employeeEdu = "大专";
            }
            if (row.EmployeeEdu == 4) {
                employeeEdu = "本科";
            }
            if (row.EmployeeEdu == 5) {
                employeeEdu = "研究生";
            }
            if (row.EmployeeEdu == 6) {
                employeeEdu = "硕士";
            }
            if (row.EmployeeEdu == 7) {
                employeeEdu = "博士";
            }
            return employeeEdu;
        }