一些通用的代码_.NET_编程开发_程序员俱乐部

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Reflection;
using Page;
using Common;
using System.Data.SqlClient;

namespace Test{

public class BaseDAL<T>
    {

        public string TableName { get; set; }
        /// <summary>
        /// 添加数据Model
        /// </summary>
        /// <param name="model">Model：数据库model实体</param>
        /// <returns></returns>
        public int Add(T model)
        {
            #region
            Type type = model.GetType();
            PropertyInfo[] pro = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            StringBuilder st = new StringBuilder();
            st.AppendFormat("INSERT INTO [Wooaimei].[dbo].[{0}] (", TableName);
            for (int i = 0; i < pro.Length; i++)
            {
                if (i < pro.Length - 1)
                {
                    if (pro[i].Name != "Id")
                    {
                        st.AppendFormat("{0},", pro[i].Name);
                    }
                }
                else
                {
                    if (pro[i].Name != "Id")
                    {
                        st.AppendFormat("{0}", pro[i].Name);
                    }
                }
            }
            st.Append(")  VALUES (");
            for (int i = 0; i < pro.Length; i++)
            {
                if (i < pro.Length - 1)
                {
                    if (pro[i].Name != "Id")
                    {
                        if (pro[i].PropertyType == typeof(string))
                        {
                            st.AppendFormat("\'{0}\',", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(DateTime))
                        {
                            st.AppendFormat("CONVERT(varchar(300),'{0}', 120),", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(bool))
                        {
                            st.AppendFormat("{0},", (bool)pro[i].GetValue(model, null) == false ? 0 : 1);
                        }
                        else
                        {
                            st.AppendFormat("{0},", pro[i].GetValue(model, null) ?? "");
                        }

                    }
                }
                else
                {
                    if (pro[i].Name != "Id")
                    {
                        if (pro[i].PropertyType == typeof(string))
                        {
                            st.AppendFormat("\'{0}\'", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(DateTime))
                        {
                            st.AppendFormat("CONVERT(varchar(300),'{0}', 120)", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(bool))
                        {
                            st.AppendFormat("{0}", (bool)pro[i].GetValue(model, null) == false ? 0 : 1);
                        }
                        else
                        {
                            st.AppendFormat("{0}", pro[i].GetValue(model, null) ?? "");
                        }
                    }
                }
            }
            st.Append(") ");
            return DbHelperSQL.ExecuteSql(st.ToString());
            #endregion
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <param name="i"></param>
        public void Add(T model, out int a)
        {
            #region
            Type type = model.GetType();
            PropertyInfo[] pro = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            StringBuilder st = new StringBuilder();
            st.AppendFormat("INSERT INTO [Wooaimei].[dbo].[{0}] (", TableName);
            for (int i = 0; i < pro.Length; i++)
            {
                if (i < pro.Length - 1)
                {
                    if (pro[i].Name != "Id")
                    {
                        st.AppendFormat("{0},", pro[i].Name);
                    }
                }
                else
                {
                    if (pro[i].Name != "Id")
                    {
                        st.AppendFormat("{0}", pro[i].Name);
                    }
                }
            }
            st.Append(")  VALUES (");
            for (int i = 0; i < pro.Length; i++)
            {
                if (i < pro.Length - 1)
                {
                    if (pro[i].Name != "Id")
                    {
                        if (pro[i].PropertyType == typeof(string))
                        {
                            st.AppendFormat("\'{0}\',", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(DateTime))
                        {
                            st.AppendFormat("CONVERT(varchar(300),'{0}', 120),", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(bool))
                        {
                            st.AppendFormat("{0},", (bool)pro[i].GetValue(model, null) == false ? 0 : 1);
                        }
                        else
                        {
                            st.AppendFormat("{0},", pro[i].GetValue(model, null) ?? "");
                        }

                    }
                }
                else
                {
                    if (pro[i].Name != "Id")
                    {
                        if (pro[i].PropertyType == typeof(string))
                        {
                            st.AppendFormat("\'{0}\'", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(DateTime))
                        {
                            st.AppendFormat("CONVERT(varchar(300),'{0}', 120)", pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(bool))
                        {
                            st.AppendFormat("{0}", (bool)pro[i].GetValue(model, null) == false ? 0 : 1);
                        }
                        else
                        {
                            st.AppendFormat("{0}", pro[i].GetValue(model, null) ?? "");
                        }
                    }
                }
            }
            st.Append(");SELECT @@IDENTITY ");
            object obje = DbHelperSQL.GetSingle(st.ToString());
            if (obje != null)
            {
                a = Convert.ToInt32(obje);
            }
            else
            {
                a = 0;
            }
            #endregion
        }
        /// <summary>
        /// 查询行数
        /// </summary>
        /// <param name="strWhere">strWhere：根据strWhere查询行数</param>
        /// <returns>返回i行数值</returns>
        public int Count(string strWhere)
        {
            StringBuilder sbstr = new StringBuilder();
            sbstr.AppendFormat("SELECT COUNT(0) FROM [Wooaimei].[dbo].[{0}]", TableName);
            sbstr.AppendFormat(" Where {0}", strWhere);
            //return DbHelperSQL.ExecuteSql(sbstr.ToString());
            object obj = DbHelperSQL.GetSingle(sbstr.ToString());
            if (obj!=null)
            {
                return Convert.ToInt32(obj);
            }
            else
            {
                return 0;
            }
        }
        public List<T> DataTableToList(DataTable dt)
        {
            throw new NotImplementedException();

        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="strWhere">strWhere：根据strWhere删除行数</param>
        /// <returns></returns>
        public int DeleteList(string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("DELETE FROM [Wooaimei].[dbo].[{0}] ", TableName);
            sb.AppendFormat("WHERE {0} ", strWhere);
            return DbHelperSQL.ExecuteSql(sb.ToString());
        }

        public bool Exists(string strWhere)
        {
            throw new NotImplementedException();

        }
        /// <summary>
        /// 查询集合：List<T>
        /// </summary>
        /// <param name="Top">Top：查询条数，为空或null查询全部</param>
        /// <param name="strWhere">strWhere：查询条件不带Where</param>
        /// <param name="filedOrder">filedOrder：排序条件不带Order by，</param>
        /// <returns></returns>
        public List<T> GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder sbstr = new StringBuilder();
            sbstr.AppendFormat("SELECT ");
            if (Top != 0)
            {
                sbstr.AppendFormat("top {0}", Top);
            }
            sbstr.AppendFormat(" * FROM [Wooaimei].[dbo].[{0}]", TableName);
            sbstr.AppendFormat(" WHERE {0} ", strWhere);
            sbstr.AppendFormat(" order by {0}", filedOrder);
            SqlDataReader sReader = DbHelperSQL.ExecuteReader(sbstr.ToString());
            List<T> t = KycFunction.FillModels<T>(sReader);
            sReader.Close();
            return t;
        }
        /// <summary>
        /// 查询集合：List<T>
        /// </summary>
        /// <param name="Top">Top：查询条数，为空或null查询全部</param>
        /// <param name="strWhere">strWhere：查询条件不带Where</param>
        /// <param name="filedOrder">filedOrder：排序条件不带Order by，字段加DESC，ASC</param>
        /// <param name="column">查询的字段</param>
        /// <returns></returns>
        public List<T> GetList(int Top, string column, string strWhere, string filedOrder)
        {
            StringBuilder sbstr = new StringBuilder();
            sbstr.AppendFormat("SELECT ");
            if (Top != 0)
            {
                sbstr.AppendFormat("top {0}", Top);
            }
            sbstr.AppendFormat(" {0} FROM [Wooaimei].[dbo].[{1}]", column, TableName);
            sbstr.AppendFormat(" WHERE {0} ", strWhere);
            sbstr.AppendFormat(" order by {0}", filedOrder);
            SqlDataReader sReader = DbHelperSQL.ExecuteReader(sbstr.ToString());
            List<T> t = KycFunction.FillModels<T>(sReader);
            sReader.Close();
            return t;
        }
        /// <summary>
        /// 查询model
        /// </summary>
        /// <param name="strWhere">strWhere：条件不带WHERE</param>
        /// <returns></returns>
        public T GetModelBystrWhere(string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM [Wooaimei].[dbo].[{0}]", TableName);
            sb.AppendFormat(" WHERE {0}", strWhere);
            SqlDataReader sReader = DbHelperSQL.ExecuteReader(sb.ToString());
            T t = KycFunction.FillModel<T>(sReader);
            sReader.Close();
            return t;
        }
        /// <summary>
        /// 查询model
        /// </summary>
        /// <param name="column">column：查询字段</param>
        /// <param name="strWhere">strWhere：条件不带WHERE</param>
        /// <returns></returns>
        public T GetModelBystrWhere(string column, string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT {0} FROM [Wooaimei].[dbo].[{1}]", column, TableName);
            sb.AppendFormat(" WHERE {0}", strWhere);
            SqlDataReader sReader = DbHelperSQL.ExecuteReader(sb.ToString());
            T t = KycFunction.FillModel<T>(sReader);
            sReader.Close();
            return t;
        }
        /// <summary>
        /// 更新Model
        /// </summary>
        /// <param name="model">Model：更新的model</param>
        /// <param name="strWhere">strWhere：更新条件</param>
        /// <returns></returns>
        public int Update(T model, string strWhere)
        {
            Type type = model.GetType();
            PropertyInfo[] pro = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            StringBuilder st = new StringBuilder();
            st.AppendFormat("UPDATE [Wooaimei].[dbo].[{0}] SET ", TableName);
            for (int i = 0; i < pro.Length; i++)
            {
                if (i < pro.Length - 1)
                {
                    if (pro[i].Name != "Id")
                    {
                        if (pro[i].PropertyType == typeof(string))
                        {
                            st.AppendFormat("[{0}] =  \'{1}\',", pro[i].Name, pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(DateTime))
                        {
                            st.AppendFormat("[{0}] =CONVERT(varchar(300),'{1}', 120),", pro[i].Name, pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(bool))
                        {
                            st.AppendFormat("{0}={1},", pro[i].Name, (bool)pro[i].GetValue(model, null) == false ? 0 : 1);
                        }
                        else
                        {
                            st.AppendFormat("{0}={1},", pro[i].Name, pro[i].GetValue(model, null) ?? "");
                        }
                    }
                }
                else
                {
                    if (pro[i].Name != "Id")
                    {
                        if (pro[i].PropertyType == typeof(string))
                        {
                            st.AppendFormat("[{0}] =  \'{1}\' ", pro[i].Name, pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(DateTime))
                        {
                            st.AppendFormat("[{0}] =CONVERT(varchar(300),'{1}', 120) ", pro[i].Name, pro[i].GetValue(model, null) ?? "");
                        }
                        else if (pro[i].PropertyType == typeof(bool))
                        {
                            st.AppendFormat("{0}={1} ", pro[i].Name, (bool)pro[i].GetValue(model, null) == false ? 0 : 1);
                        }
                        else
                        {
                            st.AppendFormat("{0}={1} ", pro[i].Name, pro[i].GetValue(model, null) ?? "");
                        }
                    }
                }
            }
            st.AppendFormat(" WHERE {0}", strWhere);
            return DbHelperSQL.ExecuteSql(st.ToString());
        }
        /// <summary>
        /// 千万数量级分页存储过程 **
        /// </summary>
        /// <param name="PageInx">PageInx :当前页码</param>
        /// <param name="PageSize">.PageSize :分页尺寸</param>
        /// <param name="strWehre">strWehre :过滤语句，不带Where </param>
        /// <param name="FileOreder">FileOreder :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc</param>
        /// <param name="QueryFieldName">QueryFieldName：查询字段</param>
        /// <returns>PageData<T></returns>
        public PageData<T> GetPageData(int PageInx, int PageSize, string strWehre, string FileOreder, string QueryFieldName)
        {
            PageData<T> page = new PageData<T>();
            page.TableName = TableName;
            page.PageIndex = PageInx;
            page.PageSize = PageSize;
            page.PrimaryKey = "Id";
            page.OrderStr = FileOreder ?? " Id desc";
            page.QueryCondition = strWehre ?? " 1=1 and IsDelete ='false' ";
            page.QueryFieldName = QueryFieldName ?? " * ";
            page.Models = Converter<T>.Convert(page.QueryDataTable(), 0);
            return page;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace WooIDAL.Page
{


    //使用该存储过程得到数据，将数据绑定到数据控件，提供了一个pageData类
    /// 数据源提供
    public class PageData<T>
    {
        private int _PageSize = 10;
        private int _PageIndex = 1;
        private int _PageCount = 0;
        private int _TotalCount = 0;
        private string _TableName;//表名
        private string _QueryFieldName = "*";//表字段FieldStr
        private string _OrderStr = string.Empty; //排序_SortStr
        private string _QueryCondition = string.Empty;//查询的条件 RowFilter
        private string _PrimaryKey = string.Empty;//主键
        public List<T> Models { get; set; }
        /// 显示页数
        public int PageSize
        {
            get
            {
                return _PageSize;
            }
            set
            {
                _PageSize = value;
            }
        }
        /// 当前页
        public int PageIndex
        {
            get
            {
                return _PageIndex;
            }
            set
            {
                _PageIndex = value;
            }
        }
        /// 总页数
        public int PageCount
        {
            get
            {
                return _PageCount;
            }
        }
        /// 总记录数
        public int TotalCount
        {
            get
            {
                return _TotalCount;
            }
            set { _TotalCount = value; }
        }
        /// 表名，包括视图
        public string TableName
        {
            get
            {
                return _TableName;
            }
            set
            {
                _TableName = value;
            }
        }
        /// 表字段FieldStr
        public string QueryFieldName
        {
            get
            {
                return _QueryFieldName;
            }
            set
            {
                _QueryFieldName = value;
            }
        }
        /// 排序字段
        public string OrderStr
        {
            get
            {
                return _OrderStr;
            }
            set
            {
                _OrderStr = value;
            }
        }
        /// 查询条件
        public string QueryCondition
        {
            get
            {
                return _QueryCondition;
            }
            set
            {
                _QueryCondition = value;
            }
        }
        /// 主键
        public string PrimaryKey
        {
            get
            {
                return _PrimaryKey;
            }
            set
            {
                _PrimaryKey = value;
            }
        }

        public DataSet QueryDataTable()
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@Tables", SqlDbType.VarChar, 255),
                   new SqlParameter("@PrimaryKey" , SqlDbType.VarChar , 255),    
                    new SqlParameter("@Sort", SqlDbType.VarChar , 255 ),
                    new SqlParameter("@CurrentPage", SqlDbType.Int),
                    new SqlParameter("@PageSize", SqlDbType.Int),                                    
                    new SqlParameter("@Fields", SqlDbType.VarChar, 255),
                    new SqlParameter("@Filter", SqlDbType.VarChar,1000),
                    new SqlParameter("@Group" ,SqlDbType.VarChar , 1000 ),
                    };
            parameters[0].Value = _TableName;
            parameters[1].Value = _PrimaryKey;
            parameters[2].Value = _OrderStr;
            parameters[3].Value = PageIndex;
            parameters[4].Value = PageSize;
            parameters[5].Value = _QueryFieldName;
            parameters[6].Value = _QueryCondition;
            parameters[7].Value = string.Empty;
            DataSet ds = DbHelperSQL.RunProcedure("USP_Pagination", parameters, "Tables");
            //DataSet ds = DbHelperSQL.RunProcedure(CommandType.StoredProcedure, "USP_Pagination", parameters);
            _TotalCount = GetTotalCount();
            if (_TotalCount == 0)
            {
                _PageIndex = 0;
                _PageCount = 0;
            }
            else
            {
                _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1;
                if (_PageIndex > _PageCount)
                {
                    _PageIndex = _PageCount;
                    parameters[4].Value = _PageSize;
                    ds = QueryDataTable();
                }
            }
            return ds;
        }

        public int GetTotalCount()
        {
            string strSql = " select count(1) from " + _TableName;
            if (_QueryCondition != string.Empty)
            {
                strSql += " where " + _QueryCondition;
            }
            return int.Parse(DbHelperSQL.GetSingle(strSql).ToString());
            //return int.Parse(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, strSql, null).ToString());
        }
    }
}