一些通用的代码
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()); } } }
循环web页面的控件，以text为例
/// <summary> /// 界面中所有的TextBox，并设置值 /// </summary> /// <param name="controls"></param> /// <param name="enable"></param> void SetTextBoxEnalbe(Control control, string[] str, int index) { string id = "txtresult" + (index+1); foreach (System.Web.UI.Control ctl in this.Form.Controls) { if (ctl is System.Web.UI.WebControls.TextBox) { System.Web.UI.WebControls.TextBox tb = (System.Web.UI.WebControls.TextBox)ctl; if (ctl.ID.Equals(id)) { tb.Text = str[index]; } } } }
