你的位置:首页 > ASP.net教程

[ASP.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());    }  }}

分页