/// <summary> /// 构造函数 /// </summary> public BaseDAL() { Type t = typeof(TClass); this.tableName = "[" + t.Name + "]"; object[] tableAtts = t.GetCustomAttributes(typeof(TableAttribute), true); // 获取表名称 if (tableAtts.Length > 0) { this.tableName = "[" + ((TableAttribute)tableAtts[0]).Name.Replace("dbo.", "") + "]"; } PropertyInfo[] propertyInfos = ModelPropertyInfoCache.Instance.GetProperties <TClass>(); // 获取实体的属性 foreach (PropertyInfo pi in propertyInfos) { object[] colAtts = pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (colAtts.Length == 0) { continue; } ColumnAttribute colAtt = colAtts[0] as ColumnAttribute; ColumnDbType column = new ColumnDbType(); column.GetDbType(colAtt.DbType); column.IsPrimaryKey = colAtt.IsPrimaryKey; column.ColumnName = pi.Name; colList.Add(column); propList.Add(pi); } isLogicDelete = t.GetProperty("DelStatus") != null; }
/// <summary> /// 批量删除(只有一个主键的) /// </summary> /// <param name="keys"></param> /// <returns></returns> public int Delete(ICollection keys) { ColumnDbType cdt = colList.Where(c => c.IsPrimaryKey).Single(); List <SqlParameter> paras = new List <SqlParameter>(); StringBuilder inParas = new StringBuilder(); int i = 0; foreach (object obj in keys) { SqlParameter para = new SqlParameter(); para.ParameterName = "@P" + i; para.SqlDbType = cdt.SqlDbType; para.Value = obj; inParas.Append(para.ParameterName + ","); paras.Add(para); i++; } inParas.Remove(inParas.Length - 1, 1); string strSql = ""; if (isLogicDelete) { strSql = string.Format("Update {0} Set DelStatus=1 Where {1} in ({2})", tableName, cdt.ColumnName, inParas); } else { strSql = string.Format("Delete From {0} Where {1} in ({2})", tableName, cdt.ColumnName, inParas); } return(NonQuery(strSql, paras.ToArray())); }
/// <summary> /// 单笔删除(只有一个主键的) /// </summary> /// <param name="keys"></param> /// <returns></returns> public int Delete(object key) { if (key is ICollection) { return(Delete(key as ICollection)); } ColumnDbType cdt = colList.Where(c => c.IsPrimaryKey).Single(); List <SqlParameter> paras = new List <SqlParameter>(); StringBuilder inParas = new StringBuilder(); SqlParameter para = new SqlParameter(); para.ParameterName = "@" + cdt.ColumnName; para.SqlDbType = cdt.SqlDbType; para.Value = key; paras.Add(para); string strSql = ""; if (isLogicDelete) { strSql = string.Format("Update {0} Set DelStatus=1 Where {1}=@{1}", tableName, cdt.ColumnName); } else { strSql = string.Format("Delete From {0} Where {1}=@{1}", tableName, cdt.ColumnName); } return(NonQuery(strSql, paras.ToArray())); }
/// <summary> /// 根据多键实体判断是否存在记录 /// </summary> /// <param name="keyProps"></param> /// <returns></returns> public virtual bool IsHas(Dictionary <string, object> keyProps) { List <ColumnDbType> cList = new List <ColumnDbType>(); foreach (string key in keyProps.Keys) { ColumnDbType cdt = null; for (int i = 0; i < colList.Count; i++) { if (colList[i].ColumnName.ToLower() == key.ToLower()) { cdt = colList[i]; break; } } if (cdt == null) { throw new Exception(string.Format("表{0}未找到属性{1}", tableName, key)); } cdt.Value = keyProps[key]; cList.Add(cdt); } StringBuilder whereList = new StringBuilder(); List <SqlParameter> paras = new List <SqlParameter>(); for (int i = 0; i < cList.Count; i++) { whereList.Append(" " + cList[i].ColumnName + "=@" + cList[i].ColumnName + " And"); SqlParameter para = new SqlParameter(); para.ParameterName = "@" + cList[i].ColumnName; para.SqlDbType = cList[i].SqlDbType; if (cList[i].Size > 0) { para.Size = cList[i].Size; } para.Value = cList[i].Value; paras.Add(para); } whereList.Remove(whereList.Length - 4, 4); string strSql = string.Format("Select 1 From {0} Where {1} {2}", tableName, isLogicDelete ? "DelStatus=0 And " : "", whereList); using (DataAccess da = new DataAccess(ConnString)) { SqlDataReader sdr = da.ExecuteReader(strSql, paras.ToArray()); return(sdr.HasRows); } }
private ColumnDbType GetColumnDbType(string paraName, string tableColumn) { Type t = typeof(TClass); string columnName = ""; if (tableColumn == "") { t = typeof(TClass); columnName = paraName; } else { string[] tabs = tableColumn.Split('.'); if (tabs.Length != 1 && tabs.Length != 2) { return(null); } if (tabs.Length == 2) { t = t.Assembly.GetType(typeNameSpace + "." + tabs[0], false, true); columnName = tabs[1]; } else { t = typeof(TClass); columnName = tabs[0]; } } PropertyInfo pi = t.GetProperty(columnName); if (pi == null) { return(null); } object[] colAtts = pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (colAtts.Length == 0) { return(null); } ColumnAttribute colAtt = colAtts[0] as ColumnAttribute; ColumnDbType column = new ColumnDbType(); column.GetDbType(colAtt.DbType); column.IsPrimaryKey = colAtt.IsPrimaryKey; column.ColumnName = pi.Name; return(column); }
/// <summary> /// /// </summary> /// <param name="dbType"></param> /// <param name="isJsonSerialize"></param> public EntityFieldAttribute(bool isJsonSerialize, ColumnDbType dbType) : this() { IsJsonSerialize = isJsonSerialize; DbType = dbType; }
private static IDataParameter CreateParameter(DbBaseProvider dbProvider, string columnName, ColumnDbType dbType, object value) { IDataParameter parameter; switch (dbType) { case ColumnDbType.UniqueIdentifier: parameter = dbProvider.CreateParameterByGuid(columnName, (Guid)value); break; case ColumnDbType.LongText: parameter = dbProvider.CreateParameterByLongText(columnName, value); break; case ColumnDbType.Text: parameter = dbProvider.CreateParameterByText(columnName, value); break; case ColumnDbType.LongBlob: parameter = dbProvider.CreateParameterLongBlob(columnName, value); break; case ColumnDbType.Blob: parameter = dbProvider.CreateParameterByBlob(columnName, value); break; default: parameter = dbProvider.CreateParameter(columnName, value); break; } return(parameter); }
/// <summary> /// /// </summary> /// <param name="dbType"></param> /// <param name="isJsonSerialize"></param> public EntityFieldAttribute(bool isJsonSerialize, ColumnDbType dbType) : this() { IsJsonSerialize = isJsonSerialize; DbType = dbType; }
/// <summary> /// 分页读取数据 /// </summary> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">分页大小</param> /// <param name="whereCollection">条件值,例如:key为UserId>=@UserId,值为20</param> /// <param name="orderBy">排序字段</param> /// <returns></returns> public virtual PagedResult GetPaged(int pageIndex, int pageSize, Dictionary <string, object> whereCollection, string orderBy) { List <ColumnDbType> cList = new List <ColumnDbType>(); StringBuilder whereList = new StringBuilder(); List <SqlParameter> paras = new List <SqlParameter>(); if (whereCollection != null) { foreach (string key in whereCollection.Keys) { ColumnDbType cdt = null; for (int i = 0; i < colList.Count; i++) { string colName = this.GetFieldName(key.Trim()); if (colList[i].ColumnName.ToLower() == colName.ToLower()) { cdt = colList[i]; break; } } if (cdt == null) { throw new Exception(string.Format("表{0}未找到属性{1}", tableName, key)); } whereList.Append(" " + key + " = @" + key + " And"); SqlParameter para = new SqlParameter(); para.ParameterName = "@" + key; para.SqlDbType = cdt.SqlDbType; if (cdt.Size > 0) { para.Size = cdt.Size; } para.Value = whereCollection[key]; paras.Add(para); } } if (whereList.Length > 0) { whereList.Remove(whereList.Length - 4, 4); whereList.Insert(0, "Where "); } if (whereList.Length > 0) { if (isLogicDelete) { whereList.Append(" And DelStatus=0"); } } else { if (isLogicDelete) { whereList.Append("Where DelStatus=0"); } } string strSql = ""; if (pageIndex == 1) { strSql = string.Format("Select Count(1) From {1} {2};Select Top {0} * From {1} {2} Order By {3}", pageSize, tableName, whereList, orderBy); } else { int startRowNum = (pageIndex - 1) * pageSize; StringBuilder innerSql = new StringBuilder(); innerSql.AppendFormat("Select ROW_NUMBER() OVER (Order By {0}) AS [ROW_NUMBER],* From {1} {2}", orderBy, tableName, whereList); strSql = string.Format("Select Count(1) From {1} {2};Select * From ({0}) as [t1] Where [t1].[ROW_NUMBER] BETWEEN @CRMPageIndex + 1 AND @CRMPageIndex + @CRMPageSize ORDER BY [t1].[ROW_NUMBER]", innerSql, tableName, whereList); SqlParameter para = new SqlParameter(); para.ParameterName = "@CRMPageIndex"; para.SqlDbType = SqlDbType.Int; para.Size = 4; para.Value = startRowNum; paras.Add(para); para = new SqlParameter(); para.ParameterName = "@CRMPageSize"; para.SqlDbType = SqlDbType.Int; para.Size = 4; para.Value = pageSize; paras.Add(para); } DataSet ds = null; using (DataAccess da = new DataAccess(this.ConnString)) { ds = da.ExecuteDataSet(strSql, paras.ToArray()); } if (ds != null && ds.Tables.Count == 2) { return(new PagedResult(ds.Tables[1], Convert.ToInt32(ds.Tables[0].Rows[0][0]))); } return(new PagedResult()); }
/// <summary> /// 查询多键实体 /// </summary> /// <param name="keyProps"></param> /// <returns></returns> public virtual TClass Get(Dictionary <string, object> keyProps) { List <ColumnDbType> cList = new List <ColumnDbType>(); foreach (string key in keyProps.Keys) { ColumnDbType cdt = null; for (int i = 0; i < colList.Count; i++) { if (colList[i].ColumnName.ToLower() == key.ToLower()) { cdt = colList[i]; break; } } if (cdt == null) { throw new Exception(string.Format("表{0}未找到属性{1}", tableName, key)); } cdt.Value = keyProps[key]; cList.Add(cdt); } StringBuilder whereList = new StringBuilder(); List <SqlParameter> paras = new List <SqlParameter>(); for (int i = 0; i < cList.Count; i++) { whereList.Append(" " + cList[i].ColumnName + "=@" + cList[i].ColumnName + " And"); SqlParameter para = new SqlParameter(); para.ParameterName = "@" + cList[i].ColumnName; para.SqlDbType = cList[i].SqlDbType; if (cList[i].Size > 0) { para.Size = cList[i].Size; } para.Value = cList[i].Value; paras.Add(para); } whereList.Remove(whereList.Length - 4, 4); string strSql = string.Format("Select * From {0} Where {1} {2}", tableName, isLogicDelete ? "DelStatus=0 And " : "", whereList); DataSet ds = null; using (DataAccess da = new DataAccess(ConnString)) { ds = da.ExecuteDataSet(strSql, paras.ToArray()); } List <TClass> list = new List <TClass>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { list = EntityHelper.GetEntityListByDT <TClass>(ds.Tables[0], null); } if (list.Count > 1) { throw new Exception(string.Format("表{0}返回不只一条记录", tableName)); } return(list.Count == 1 ? list[0] : null); }