c# 公共类SqlHelper

悠悠 2022-05-19 10:05 241阅读 0赞

ADO.NET操作数据库公共方法

**一示例**

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data;
    using System.Configuration;
    
    namespace Components.DBUtility
    {
        public class SqlHelper
        {
            private string strConnectionString = "";
            public static SqlConnection cnn;
    
            /// <summary>
            /// 初始化构造函数
            /// </summary>
            public SqlHelper()
            {
                strConnectionString = ConfigurationManager.AppSettings["Conn"];
            }
    
            /// <summary>
            ///打开数据库连接
            /// </summary>
            public void Open()
            {
                if (cnn == null)
                {
                    //cnn = new SqlConnection(ConfigurationManager.AppSettings["Conn"]);
                    cnn = new SqlConnection(strConnectionString);
    
                }
                if (cnn.State == ConnectionState.Closed)
                {
                    try
                    {
                        cnn.Open();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
    
                }
            }
    
            /// <summary>
            /// 关闭数据库连接
            /// </summary>
            public void close()
            {
                if (cnn != null)
                {
                    if (cnn.State == ConnectionState.Open)
                    {
                        cnn.Close();
                    }
                }
    
            }
    
            /// <summary>
            /// 释放资源
            /// </summary>
            public void Dispose()
            {
                // 确认连接是否已经关闭
                if (cnn != null)
                {
                    cnn.Dispose();
    
                    cnn = null;
                }
            }
    
            /// <summary>
            /// 执行添加、更新、删除等Sql语句
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <returns>返回受影响的行数</returns>
            public int ExecuteNonQuery(string query)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                int retval;
                try
                {
                    cnn.Open();
                    retval = cmd.ExecuteNonQuery();
                    cnn.Close();
                }
                catch (Exception exp)
                {
                    throw exp;
                }
                finally
                {
                    if (cnn.State == ConnectionState.Open)
                    {
                        cnn.Close();
                    }
                }
                return retval;
            }
    
            /// <summary>
            /// 执行添加、更新、删除等Sql语句
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <param name="parameters">参数</param>
            /// <returns>返回受影响的行数</returns>
            public int ExecuteNonQuery(string query, params SqlParameter[] parameters)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                for (int i = 0; i <= parameters.Length - 1; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
                cnn.Open();
                int retval = cmd.ExecuteNonQuery();
                cnn.Close();
                return retval;
            }
            public Exception Execute(string sql)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cmd.Connection.Open();
                SqlTransaction tran = cmd.Connection.BeginTransaction();
                cmd.Transaction = tran;
                Exception exret = null;
                try
                {
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    exret = ex;
                }
                finally
                {
                    cmd.Connection.Close();
                }
                return exret;
            }
            /// <summary>
            /// 查询第一行第一列
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <returns></returns>
            public object ExecuteScalar(string query)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                cnn.Open();
                object retval = cmd.ExecuteNonQuery();
                cnn.Close();
                return retval;
            }
    
            /// <summary>
            /// 查询第一行第一列
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <param name="parameters">参数</param>
            /// <returns></returns>
            public object ExecuteScalar(string query, params SqlParameter[] parameters)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                for (int i = 0; i <= parameters.Length - 1; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
                cnn.Open();
                object retval = cmd.ExecuteScalar();
                cnn.Close();
                return retval;
            }
    
            /// <summary>
            /// Reader数据库查询
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <returns>返回SqlDataReader</returns>
            public SqlDataReader ExecuteReader(string query)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                    cnn.Open();
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cnn.Open();
                }
                SqlDataReader dr;
                try
                {
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    return dr;
                }
                catch (Exception ee)
                {
                    cnn.Close();
                    throw ee;
                }
    
            }
    
            /// <summary>
            /// Reader数据库查询
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <param name="parameters">参数</param>
            /// <returns>返回SqlDataReader</returns>
            public SqlDataReader ExecuteReader(string query, params SqlParameter[] parameters)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                for (int i = 0; i <= parameters.Length - 1; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
                cnn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
    
            /// <summary>
            /// DataSet离线数据库保存数据
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <returns>返回DataSet</returns>
            public DataSet ExecuteDataSet(string query)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
    
            /// <summary>
            /// DataSet离线数据库保存数据
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <param name="parameters">参数</param>
            /// <returns>返回DataSet</returns>
            public DataSet ExecuteDataSet(string query, params SqlParameter[] parameters)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                for (int i = 0; i <= parameters.Length - 1; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
    
            /// <summary>
            /// DataTable离线数据库保存数据
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <returns>返回DataSet</returns>
            public DataTable ExecuteDataTable(string query)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
    
            /// <summary>
            /// DataTable离线数据库保存数据
            /// </summary>
            /// <param name="query">sql语句或者存储过程</param>
            /// <param name="parameters">参数</param>
            /// <returns>返回DataSet</returns>
            public DataTable ExecuteDataTable(string query, params SqlParameter[] parameters)
            {
                cnn = new SqlConnection(strConnectionString);
                SqlCommand cmd = new SqlCommand(query, cnn);
                if (query.StartsWith("SELECT") | query.StartsWith("select"))
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                for (int i = 0; i <= parameters.Length - 1; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
    
            /// <summary>
            /// 执行多条SQL语句，实现数据库事务。
            /// </summary>
            /// <param name="SQLStringList">多条SQL语句</param>		
            public  int ExecuteSqlTran(List<String> SQLStringList)
            {
                using (SqlConnection conn = new SqlConnection(strConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    SqlTransaction tx = conn.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        for (int n = 0; n < SQLStringList.Count; n++)
                        {
                            string strsql = SQLStringList[n];
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                        return count;
                    }
                    catch
                    {
                        tx.Rollback();
                        return 0;
                    }
                }
            }
    
            /// <summary>
            /// 执行一条计算查询结果语句，返回查询结果（object）。
            /// </summary>
            /// <param name="SQLString">计算查询结果语句</param>
            /// <returns>查询结果（object）</returns>
            public  object GetSingle(string SQLString)
            {
                using (SqlConnection connection = new SqlConnection(strConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.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)
                        {
                            connection.Close();
                            throw e;
                        }
                    }
                }
            }
        }
    }

**二示例**

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections;
    
    
    namespace DbHelperNamespace
    {
        /// <summary>
        /// 数据库访问通用类
        /// </summary>
        public class DbHelper
        {
            private string connectionString;
    
            /// <summary>
            /// 设定数据库访问字符串
            /// </summary>
            public string ConnectionString
            {
    
                set { connectionString = value; }
            }
    
            /// <summary>
            /// 构造函数
            /// </summary>
            /// <param name="connectionString">数据库访问字符串</param>
            public DbHelper(string connectionString)
            {
                this.connectionString = connectionString;
            }
    
            /// <summary>
            /// 执行一个查询，并返回查询结果
            /// </summary>
            /// <param name="sql">要执行的sql语句</param>
            /// <param name="commandType">要执行的查询语句的类型，如存储过程或者sql文本命令</param>
            /// <returns>返回查询结果集</returns>
            public DataTable ExecuteDataTable(string sql, CommandType commandType)
            {
                return ExecuteDataTable(sql, commandType, null);
            }
    
            /// <summary>
            /// 执行一个查询，并返回结果集
            /// </summary>
            /// <param name="sql">要执行的sql文本命令</param>
            /// <returns>返回查询的结果集</returns>
            public DataTable ExecuteDataTable(string sql)
            {
                return ExecuteDataTable(sql, CommandType.Text, null);
            }
    
    
            /// <summary>
            /// 执行一个查询，并返回查询结果
            /// </summary>
            /// <param name="sql">要执行的sql语句</param>
            /// <param name="commandtype">要执行查询语句的类型，如存储过程或者sql文本命令</param>
            /// <param name="parameters">Transact-SQL语句或者存储过程参数数组</param>
            /// <returns></returns>
            public DataTable ExecuteDataTable(string sql, CommandType commandtype, SqlParameter[] parameters)
            {
                DataTable data = new DataTable(); //实例化datatable，用于装载查询结果集
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        cmd.CommandType = commandtype;//设置command的commandType为指定的Commandtype
                        //如果同时传入了参数，则添加这些参数
                        if (parameters != null)
                        {
                            foreach (SqlParameter parameter in parameters)
                            {
                                cmd.Parameters.Add(parameter);
                            }
                        }
    
                        //通过包含查询sql的sqlcommand实例来实例化sqldataadapter
                        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                        adapter.Fill(data);//填充datatable
    
                    }
                }
                return data;
            }
    
            /// <summary>
            /// 返回一个SqlDataReader对象的实例
            /// </summary>
            /// <param name="sql">要执行的SQl查询命令</param>
            /// <returns></returns>
            public SqlDataReader ExecuteReader(string sql)
            {
                return ExecuteReader(sql, CommandType.Text, null);
            }
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="sql">要执行的sql语句</param>
            /// <param name="commandType">要执行查询语句的类型，如存储过程或者SQl文本命令</param>
            /// <returns></returns>
            public SqlDataReader ExecuteReader(string sql, CommandType commandType)
            {
                return ExecuteReader(sql, commandType, null);
            }
    
            /// <summary>
            /// 返回一个sqldatareader对象的实例
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="commandType"></param>
            /// <param name="parameters"></param>
            /// <returns></returns>
            public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
            {
                SqlConnection con = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand(sql, con);
    
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameters);
                    }
                }
                con.Open();
                //CommandBehavior.CloseConnection参数指示关闭reader对象时关闭与其关联的Connection对象
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
    
            /// <summary>
            /// 执行一个查询，返回结果集的首行首列。忽略其他行，其他列
            /// </summary>
            /// <param name="sql">要执行的SQl命令</param>
            /// <returns></returns>
            public Object ExecuteScalar(string sql)
            {
                return ExecuteScalar(sql, CommandType.Text, null);
            }
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
            public Object ExecuteScalar(string sql, CommandType commandType)
            {
                return ExecuteScalar(sql, commandType, null);
            }
    
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="commandType">参数类型</param>
            /// <param name="parameters"></param>
            /// <returns></returns>
            public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
            {
                Object result = null;
                SqlConnection con = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SqlParameter parapmeter in parameters)
                    {
                        cmd.Parameters.Add(parapmeter);
                    }
                }
    
                con.Open();
                result = cmd.ExecuteScalar();
                con.Close();
                return result;
            }
    
            /// <summary>
            /// 对数据库进行增删改的操作
            /// </summary>
            /// <param name="sql">要执行的sql命令</param>
            /// <returns></returns>
            public int ExecuteNonQuery(string sql)
            {
                return ExecuteNonQuery(sql, CommandType.Text, null);
            }
    
            /// <summary>
            /// 数据库进行增删改的操作
            /// </summary>
            /// <param name="sql">对数据库进行操作的sql命令</param>
            /// <param name="commandType">要执行查询语句的类型，如存储过程或者sql文本命令</param>
            /// <returns></returns>
            public int ExecuteNonQuery(string sql, CommandType commandType)
            {
                return ExecuteNonQuery(sql, commandType, null);
            }
    
            /// <summary>
            /// 对数据库进行增删改的操作
            /// </summary>
            /// <param name="sql">要执行的sql语句</param>
            /// <param name="commandType">要执行的查询语句类型，如存储过程或者sql文本命令</param>
            /// <param name="parameters">Transact-SQL语句或者存储过程的参数数组</param>
            /// <returns></returns>
            public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
            {
                int count = 0;
                SqlConnection con = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }
    
                con.Open();
                count = cmd.ExecuteNonQuery();
                con.Close();
                return count;
            }
    
            / <summary> 执行多条SQL语句，实现数据库事务。        
            / </summary>        
            / <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>        
            public bool ExecuteSqlTranT(Hashtable SQLStringList)
            {
                bool result = false;
                using(SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    SqlTransaction tran = conn.BeginTransaction();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = tran;
                    try
                    {
                        foreach (DictionaryEntry myde in SQLStringList)
                        {
                            cmd.CommandText = myde.Key.ToString();
                            if (myde.Value.ToString() != "")
                            {
                                SqlParameter[] parameters = (SqlParameter[])myde.Value;
                                if (parameters != null)
                                {
                                    foreach (SqlParameter parameter in parameters)
                                    {
                                        cmd.Parameters.Add(parameter);
                                    }
                                }
                            }
                            int tmpresult = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        tran.Commit();
                        result = true;
                        return result;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        return result;
                        
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
    
                }
            }
    
            /// <summary>
            /// 执行多条Sql语句，如果有一条返回影响行数为0行则回滚
            /// </summary>
            /// <param name="SQLStringList"></param>
            /// <returns></returns>
            public bool ExecuteSqlTran(Hashtable SQLStringList)
            {
                bool result = false;
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    SqlTransaction tran = conn.BeginTransaction();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = tran;
                    try
                    {
                        foreach (DictionaryEntry myde in SQLStringList)
                        {
                            cmd.CommandText = myde.Key.ToString();
                            if (myde.Value.ToString() != "")
                            {
                                SqlParameter[] parameters = (SqlParameter[])myde.Value;
                                if (parameters != null)
                                {
                                    foreach (SqlParameter parameter in parameters)
                                    {
                                        cmd.Parameters.Add(parameter);
                                    }
                                }
                            }
                            int tmpresult = cmd.ExecuteNonQuery();
                            if (tmpresult == 0)
                            {
                                tran.Rollback();
                                return result;
                            }
                            cmd.Parameters.Clear();
                        }
                        tran.Commit();
                        result = true;
                        return result;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        return result;
    
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
    
                }
            }
    
            /// <summary>
            /// 执行多条Sql语句，如果有一条返回影响行数为0行则回滚
            /// </summary>
            /// <param name="SQLStringList"></param>
            /// <returns></returns>
            public bool ExecuteSqlTranSort(Dictionary<string, string> SQLStringList)
            {
                bool result = false;
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    SqlTransaction tran = conn.BeginTransaction();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = tran;
                    try
                    {
                        SQLStringList.OrderBy(x => x.Key);
                        foreach (var myde in SQLStringList)
                        {
                            if (myde.Key.ToString() != "")
                            {
                                cmd.CommandText = myde.Value.ToString();
                            }
                            int tmpresult = cmd.ExecuteNonQuery();
                            if (tmpresult == 0)
                            {
                                tran.Rollback();
                                return result;
                            }
                            cmd.Parameters.Clear();
                        }
                        tran.Commit();
                        result = true;
                        return result;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        return result;
    
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
    
                }
            }
    
            /// <summary>
            /// 执行多条Sql语句，如果有一条返回影响行数为0行则回滚
            /// </summary>
            /// <param name="SQLStringList"></param>
            /// <returns></returns>
    
            /// <summary>
            /// 分页
            /// </summary>
            /// <returns></returns>
            public string Pagination(PageInfos page, string sql1, string sql2)
            {
                string strJson = "";
                try
                {
                    object obj = ExecuteScalar(sql1);
                    if ((int)obj == 0)
                    {
                        page.RowsCount = 0;
                    }
                    else
                    {
                        page.RowsCount = Convert.ToInt32(obj);
                    }
                    //计算总页数
                    page.PageCount = (int)(page.RowsCount / page.OnePageRows) + 1;
                    //如果当前页大于总页数，设最后一页为当前页
                    if (page.ThisPage > page.PageCount) { page.ThisPage = page.PageCount; }
                    //如果当前页小于一，设第一页为当前页
                    if (Convert.ToInt32(page.ThisPage) < 1)
                    {
                        page.ThisPage = 1;
                    }
                    page.EndRow = page.ThisPage * page.OnePageRows;    //结束行数
                    page.StateRow = page.EndRow - page.OnePageRows + 1; //起始行数 
    
                    StringBuilder strSqList = new StringBuilder();
    
                    strSqList.Append(sql2);
                    strSqList.Append(" "+page.StateRow + " and " + page.EndRow);
    
                    DataTable dt = ExecuteDataTable(strSqList.ToString());
    
                    //序列化本页数据
                    strJson = "{\"totalRow\":\"" + page.RowsCount + "\",\"pageCurrent\":\"" + page.ThisPage + "\",\"pageSize\":\"" + page.OnePageRows + "\",\"list\":";
                    strJson += JsonSerializer.Serialize(dt); //引用Newtonsoft.Json; 转换为JSON格式;
                    strJson += "}";
                }
                catch (Exception ex)
                {
                    strJson = "{\"statusCode\":\"300\",\"message\":\"" + ex.Message + "\"}";
                }
                return strJson;
            }
        }
    
        public class PageInfos
        {
            int _ThisPage;      //当前页
            int _OnePageRows;   //每页显示行数
            int _PageCount;     //总页数
            int _RowsCount;     //总行数
            int _MaxID;      //最大ID
            int _MinID;      //最小ID
            int _StateRow;      //StateRow
            int _EndRow;      //EndRow
    
            public int MinID
            {
                get { return _MinID; }
                set { _MinID = value; }
            }
            public int MaxID
            {
                get { return _MaxID; }
                set { _MaxID = value; }
            }
            /// <summary>
            /// 当前页
            /// </summary>
            public int ThisPage
            {
                get { return _ThisPage; }
                set { _ThisPage = value; }
            }
            /// <summary>
            /// 每页显示行数
            /// </summary>
            public int OnePageRows
            {
                get { return _OnePageRows; }
                set { _OnePageRows = value; }
            }
            /// <summary>
            /// 总页数
            /// </summary>
            public int PageCount
            {
                get { return _PageCount; }
                set { _PageCount = value; }
            }
            /// <summary>
            /// 总行数
            /// </summary>
            public int RowsCount
            {
                get { return _RowsCount; }
                set { _RowsCount = value; }
            }
            /// <summary>
            /// StateRow
            /// </summary>
            public int StateRow
            {
                get { return _StateRow; }
                set { _StateRow = value; }
            }
            /// <summary>
            /// EndRow
            /// </summary>
            public int EndRow
            {
                get { return _EndRow; }
                set { _EndRow = value; }
            }
        }
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220519/d87ddbde70ef4c3a86c3573a1d146cad.png