asp.net layui数据表格数据交互实现动态数据表格 asp.net 真分页 第四步

5.ADO 操作数据库类 sqlhelper.cs

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

namespace DQPA.DAL
{
    public class SqlHelper
    {
        static string constr = System.Configuration.ConfigurationSettings.AppSettings["sqlcon"];
        /// <summary>
        /// 数据库连接字符串
        /// Data Source=数据库地址;Initial Catalog=数据库名称;Persist Security Info=True;User ID=用户名;Password=密码
        /// </summary>
        //static string constr = "Data Source=.;Initial Catalog=;Persist Security Info=True;User ID=sa;Password=";
        //执行增删改操作 返回受影响行数
        public static int ExcuteSql(string sql)
        {
            int result = 0;
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                result = cmd.ExecuteNonQuery();
            }
            return result;
        }
        //执行查询操作 返回datatable 查询所有
        public static DataTable Search(string field, string tableName)
        {
            DataTable dt = null;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string sql = "select" + field + "from " + tableName;
                SqlDataAdapter adp = new SqlDataAdapter(sql, con);
                con.Open();
                DataSet ds = new DataSet();
                if (ds != null)
                {
                    adp.Fill(ds);
                    dt = ds.Tables[0];
                }
            }
            return dt;
        }
        //执行查询操作 返回datatable 条件查询
        public static DataTable Search(string field, string tableName, string where)
        {
            DataTable dt = null;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string sql = "select " + field + " from " + tableName + " where 1=1" + where + "";
                SqlDataAdapter adp = new SqlDataAdapter(sql, con);
                con.Open();
                DataSet ds = new DataSet();
                if (ds != null)
                {
                    adp.Fill(ds);
                    dt = ds.Tables[0];
                }
            }
            return dt;
        }
        //执行查询操作 返回datatable sql语句查询
        public static DataTable Search(string sql)
        {

            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter(sql, con);

                DataSet ds = new DataSet();
                DataTable dt = null;
                if (ds != null)
                {
                    adp.Fill(ds);
                    dt = ds.Tables[0];
                }
                return dt;
            }

        }
        //执行存储过程操作 返回datatable 分页查询
        public static DataTable ExcuteProc(string procName, SqlParameter[] parameters)
        {
            //DataTable dt = null;
            using (SqlConnection con = new SqlConnection(constr))
            {
                //声明执行sql语句对象
                SqlCommand cmd = new SqlCommand(procName, con);
                //制定SqlCommand执行的是存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                con.Open();
                DataSet ds = new DataSet();
                DataTable dt = null;
                if (ds != null)
                {
                    adp.Fill(ds);
                    dt = ds.Tables[0];
                }
                return dt;
            }

        }
        //执行事务操作 
        public static int ExcuteTran(List<string> sqls)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                SqlTransaction tran = con.BeginTransaction();//开始一个事物
                int result = 0;
                try
                {
                    foreach (string sql in sqls)
                    {
                        SqlCommand cmd = new SqlCommand(sql, con);
                        cmd.Transaction = tran;

                        result = cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch (Exception)

                {
                    tran.Rollback();
                }

                return result;
            }
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="getFields"></param>
        /// <param name="orderName"></param>
        /// <param name="desc"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static DataTable DataPage(string tableName, string getFields, string orderName, string desc, int pageSize, int pageIndex, string strWhere, out int count)
        {
            int pageStart, pageEnd = 0;
            pageEnd = pageSize * pageIndex;
            pageStart = pageEnd - pageSize + 1;

            string sql = "select " + getFields + " from ( select ROW_NUMBER() over(order by " + orderName + "  " + desc + ") as row,* from " + tableName + " where " + strWhere + ") r where r.row between " + pageStart + " and " + pageEnd;
            string sql1 = "select " + getFields + " from ( select ROW_NUMBER() over(order by id) as row,* from " + tableName + " where " + strWhere + ") r  order by r." + orderName + " " + desc;
            DataTable dt = Search(sql);
            DataTable dt2 = Search(sql);
            count = dt2.Rows.Count;

            return dt;
        }
    }
}