asp.net ADO DBUtility

 工具类  DataHandler.cs

使用DBUtility 需要添加相关引用DBUtility.dll

using System;
using System.Collections.Generic;
using Maticsoft.DBUtility;//Please add references
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;


namespace DAL
{
    public class DataHandler
    {
        /// <summary>
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="getFields">需要返回的列</param>
        /// <param name="orderName">排序的字段名</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="isGetCount">返回记录总数,非 0 值则返回</param>
        /// <param name="orderType">设置排序类型,0表示升序非0降序</param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataSet GetList(string tableName, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                  new SqlParameter("@PageSize", SqlDbType.Int),
               new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@doCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar)
                                     };
            parameters[0].Value = tableName;
            parameters[1].Value = getFields;
            parameters[2].Value = orderName;
            parameters[3].Value = pageSize;
            parameters[4].Value = pageIndex;
            parameters[5].Value = isGetCount ? 1 : 0;
            parameters[6].Value = orderType ? 1 : 0;
            parameters[7].Value = strWhere;
            var ds= DbHelperSQL.RunProcedure("QueryByPage", parameters, "ds");
            if (ds!=null)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }
        //public static DataSet GetList(string tableName, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)
        //{
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@TableName", SqlDbType.NVarChar, 1000),
        //            new SqlParameter("@ShowColumn", SqlDbType.NVarChar, 2000),
        //            new SqlParameter("@SortColumn", SqlDbType.NVarChar, 100),
        //          new SqlParameter("@PageSize", SqlDbType.Int),
        //       new SqlParameter("@PageIndex", SqlDbType.Int),
        //        new SqlParameter("@Count", SqlDbType.Bit),
        //            new SqlParameter("@IsAsc", SqlDbType.Bit),
        //            new SqlParameter("@Filter", SqlDbType.NVarChar,2000)
        //                             };
        //    parameters[0].Value = tableName;
        //    parameters[1].Value = getFields;
        //    parameters[2].Value = orderName;
        //    parameters[3].Value = pageSize;
        //    parameters[4].Value = pageIndex;
        //    parameters[5].Value = isGetCount ? 1 : 0;
        //    parameters[6].Value = orderType ? 1 : 0;
        //    parameters[7].Value = strWhere;
        //    return DbHelperSQL.RunProcedure("QueryByPage", parameters, tableName);
        //}

        /// <summary>
        /// 根据表、关键字段删除数据
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="ID"></param>
        /// <param name="tabKey"></param>
        /// <returns></returns>
        public static int DelData(string tabName, string ID, string tabKey)
        {
            if (ID != string.Empty && ID != "0")
            {
                string sql = string.Format("delete from {0}  WHERE (" + tabKey + " IN ({1}))", tabName, ID);
                int delNum = DbHelperSQL.ExecuteSql(sql);
                return delNum;
            }
            return 0;
        }

        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 = DbHelperSQL.QueryDT(sql);
            DataTable dt2 = DbHelperSQL.QueryDT(sql1);
            count = dt2.Rows.Count;

            return dt;
        }

        public static DataTable DataPage1(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,a.*,b.result from " + tableName + " where " + strWhere + ") r where r.row between " + pageStart + " and " + pageEnd;
            string sql1 = "select " + getFields + " from ( select ROW_NUMBER() over(order by a.id) as row,a.*,b.result from " + tableName + " where " + strWhere + ") r  order by r." + orderName + " " + desc;
            DataTable dt = DbHelperSQL.QueryDT(sql);
            DataTable dt2 = DbHelperSQL.QueryDT(sql1);
            count = dt2.Rows.Count;

            return dt;
        }


        public static DataTable HDataPage(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 StaffId) as row,* from " + tableName + " where " + strWhere + ") r where r.row between " + pageStart + " and " + pageEnd + " order by r." + orderName + " " + desc;
            string sql = "select " + getFields + " from ( select ROW_NUMBER() over(order by id) as row,* from " + tableName + " where " + strWhere + ") r where r.row between " + pageStart + " and " + pageEnd + " order by r." + orderName + " " + desc;
            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 = DbHelperSQL.QueryDT(sql);

            DataTable dt2 = DbHelperSQL.QueryDT(sql1);
            count = dt2.Rows.Count;
            return dt;
        }

        public static DataSet GetList(string tableName, string tableName1, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)
        {
            DataSet ds = new DataSet();
            try
            {
                string sqlString = " (select b.id, b.name,b.idCard,b.jaddress,b.code from " + tableName + " a  inner join " + tableName1 + " b on a.idCard=b.idCard) c ";

                SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                  new SqlParameter("@PageSize", SqlDbType.Int),
               new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@doCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar)
                                     };
                parameters[0].Value = sqlString;
                parameters[1].Value = getFields;
                parameters[2].Value = orderName;
                parameters[3].Value = pageSize;
                parameters[4].Value = pageIndex;
                parameters[5].Value = isGetCount ? 1 : 0;
                parameters[6].Value = orderType ? 1 : 0;
                parameters[7].Value = strWhere;
                ds = DbHelperSQL.RunProcedure("pro_pageList", parameters, "ds");
            }
            catch (Exception ex)
            {
                ds.Tables.Add(new DataTable());
            }
            return ds;
        }

        public static DataSet GetLists(string sqlString, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)
        {
            DataSet ds = new DataSet();
            try
            {
                //string sqlString = " (select b.id, b.name,b.idCard,b.jaddress,b.code from " + tableName + " a  inner join " + tableName1 + " b on a.idCard=b.idCard) c ";

                SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.NVarChar ),
                    new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                  new SqlParameter("@PageSize", SqlDbType.Int),
               new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@doCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar)
                                     };
                parameters[0].Value = sqlString;
                parameters[1].Value = getFields;
                parameters[2].Value = orderName;
                parameters[3].Value = pageSize;
                parameters[4].Value = pageIndex;
                parameters[5].Value = isGetCount ? 1 : 0;
                parameters[6].Value = orderType ? 1 : 0;
                parameters[7].Value = strWhere;
                ds = DbHelperSQL.RunProcedure("pro_pageList_person", parameters, "ds");
            }
            catch (Exception ex)
            {
                ds.Tables.Add(new DataTable());
            }
            return ds;
        }
    }
}