/// <summary> /// 执行分页数据查询操作,返回PageResponseData /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connectionStr"></param> /// <param name="request"></param> /// <returns></returns> public override PageResponseData GetPageList <T>(string connectionStr, PageRequestData pageRequest) { PageResponseData responsResult = new PageResponseData(); try { #region 相关变量定义 Type type = typeof(T); string tableName = type.Name; int startNum = pageRequest.PageSize * (pageRequest.PageIndex - 1) + 1; int endNum = pageRequest.PageSize * pageRequest.PageIndex; var searchEntity = pageRequest.SearchEntityObj; #endregion #region 分页查询处理 EntityColumn idColumn = new EntityColumnUtility().GetIdColumn <T>(null); if (idColumn != null) { responsResult.TotalCount = GetCount <T>(connectionStr, searchEntity); if (responsResult.TotalCount > 0) { if (string.IsNullOrEmpty(searchEntity.ColumnSql)) { searchEntity.ColumnSql = "b.*"; } if (string.IsNullOrEmpty(searchEntity.SortColumn)) { searchEntity.SortColumn = idColumn.ColumnName; searchEntity.SortMethod = "asc"; } StringBuilder cmdText = new StringBuilder(); cmdText.AppendFormat("select {0} from ", searchEntity.ColumnSql); cmdText.Append("("); cmdText.AppendFormat("select ROW_NUMBER() over(order by {0} {1}) num,{2} from {3} ", searchEntity.SortColumn, searchEntity.SortMethod, idColumn.ColumnName, tableName); if (!string.IsNullOrEmpty(searchEntity.WhereSql)) { cmdText.AppendFormat("where {0} ", searchEntity.WhereSql); } cmdText.Append(") "); cmdText.AppendFormat("a inner join {0} b on a.{1}=b.{1} and a.num between {2} and {3} ", tableName, idColumn.ColumnName, startNum, endNum); IDataReader dataReader; if (searchEntity.WhereParam != null) { dataReader = SqlDbBase.ExecuteReaderWithParam(connectionStr, cmdText.ToString(), CommandType.Text, searchEntity.WhereParam); } else { dataReader = SqlDbBase.ExecuteReader(connectionStr, cmdText.ToString(), CommandType.Text, null); } responsResult.Data = Map <T>(dataReader); } } #endregion } catch (Exception ex) { throw ex; } return(responsResult); }
/// <summary> /// 根据实体主键值查询数据,返回单个实体对象 /// </summary> /// <typeparam name="T">实体模型名称</typeparam> /// <param name="connectionStr">数据库字符串连接</param> /// <param name="Id">主键值</param> /// <returns>T</returns> public virtual T QueryFirst <T>(string connectionStr, object Id) { T result = default(T); try { EntityColumn column = new EntityColumnUtility().GetIdColumn <T>(Id); if (column != null) { var dbBase = new DbUtility(DbType); Type type = typeof(T); string tableName = type.Name; StringBuilder cmdText = new StringBuilder(); cmdText.AppendFormat("select * from {0} where {1}={2}{3}", tableName, column.ColumnName, DbPramStr, column.ColumnName); IDbDataParameter[] dbParameters = { new AdoNetUtility(DbType).CreateDbParameter(column.ColumnName, column.ColumnValue) }; IDataReader dataReader = ExecuteReader(connectionStr, cmdText.ToString(), CommandType.Text, dbParameters); List <T> list = Map <T>(dataReader); if (list.Count > 0) { result = list[0]; } } } catch (Exception ex) { throw ex; } return(result); }
/// <summary> /// 执行删除数据操作,返回影响行数 /// </summary> /// <typeparam name="T">实体模型名称</typeparam> /// <param name="connectionStr">数据库字符串连接</param> /// <param name="Id">主键值</param> /// <returns>int</returns> public virtual int Delete <T>(string connectionStr, object Id) { int result = 0; try { EntityColumn column = new EntityColumnUtility().GetIdColumn <T>(Id); if (column != null) { Type type = typeof(T); string tableName = type.Name; List <IDbDataParameter> dbParamList = new List <IDbDataParameter>(); StringBuilder cmdText = new StringBuilder(); cmdText.AppendFormat("delete from {0} where {1}={2}", tableName, column.ColumnName, DbPramStr + column.ColumnName); IDbDataParameter[] dbParameters = { new AdoNetUtility(DbType).CreateDbParameter(column.ColumnName, column.ColumnValue) }; result = Execute(connectionStr, cmdText.ToString(), CommandType.Text, dbParameters); } } catch (Exception ex) { throw ex; } return(result); }
/// <summary> /// 执行分页数据查询操作,返回PageResponseData /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connectionStr"></param> /// <param name="request"></param> /// <returns></returns> public override PageResponseData GetPageList <T>(string connectionStr, PageRequestData pageRequest) { PageResponseData responsResult = new PageResponseData(); try { #region 相关变量定义 Type type = typeof(T); string tableName = type.Name; int startNum = pageRequest.PageSize * (pageRequest.PageIndex - 1); var searchEntity = pageRequest.SearchEntityObj; #endregion #region 分页查询处理 EntityColumn idColumn = new EntityColumnUtility().GetIdColumn <T>(null); if (idColumn != null) { //查询记录结果数 responsResult.TotalCount = GetCount <T>(connectionStr, searchEntity); if (responsResult.TotalCount > 0) { StringBuilder cmdText = new StringBuilder(); if (string.IsNullOrEmpty(searchEntity.ColumnSql)) { searchEntity.ColumnSql = "*"; } if (string.IsNullOrEmpty(searchEntity.SortColumn)) { searchEntity.SortColumn = idColumn.ColumnName; } cmdText.AppendFormat("select {0} from {1} ", searchEntity.ColumnSql, tableName); if (string.IsNullOrEmpty(searchEntity.WhereSql)) { cmdText.AppendFormat("where {0} ", searchEntity.WhereSql); } cmdText.AppendFormat("order by {0} {1} ", searchEntity.SortColumn, searchEntity.SortMethod); cmdText.AppendFormat("limit {0},{1} ", startNum, pageRequest.PageSize); IDataReader dataReader; if (searchEntity.WhereParam != null) { dataReader = SqlDbBase.ExecuteReaderWithParam(connectionStr, cmdText.ToString(), CommandType.Text, searchEntity.WhereParam); } else { dataReader = SqlDbBase.ExecuteReader(connectionStr, cmdText.ToString(), CommandType.Text, null); } responsResult.Data = Map <T>(dataReader); } } #endregion } catch (Exception ex) { throw ex; } return(responsResult); }
/// <summary> /// 执行编辑操作,返回影响行数 /// </summary> /// <typeparam name="T">实体模型名称</typeparam> /// <param name="connectionStr">数据库字符串连接</param> /// <param name="t">实体模型实例化对象</param> /// <returns>int</returns> public virtual int Update <T>(string connectionStr, T t) { int result = 0; try { #region 初始相关变量定义 var dbOperator = new AdoNetUtility(DbType); EntityColumn idColumn = null; List <EntityColumn> noneIdColumnList = null; bool isHaveColumn = false; bool isHaveId = false; #endregion #region 获取字段名称、值 List <EntityColumn> columnList = new EntityColumnUtility().GetColumnList <T>(t); if (columnList != null && columnList.Count > 0) { var idList = (from p in columnList where p.IsPk == true select p).ToList <EntityColumn>(); if (idList != null && idList.Count > 0) { isHaveId = true; idColumn = idList[0]; } var noneIdList = (from p in columnList where p.IsPk == false select p).ToList <EntityColumn>(); if (noneIdList != null && noneIdList.Count > 0) { isHaveColumn = true; noneIdColumnList = noneIdList; } } #endregion #region 拼接SQL及对数据库操作 if (isHaveColumn && isHaveId) { Type type = typeof(T); string tableName = type.Name; StringBuilder cmdText = new StringBuilder(); cmdText.AppendFormat("update {0} set ", tableName); List <IDbDataParameter> dbParamList = new List <IDbDataParameter>(); for (int i = 0; i < noneIdColumnList.Count; i++) { EntityColumn column = noneIdColumnList[i]; cmdText.AppendFormat("{0}={1}", column.ColumnName, DbPramStr + column.ColumnName); if (i != (noneIdColumnList.Count - 1)) { cmdText.Append(","); } var dbParam = dbOperator.CreateDbParameter(column.ColumnName, column.ColumnValue); dbParamList.Add(dbParam); } cmdText.AppendFormat(" where {0}={1}", idColumn.ColumnName, DbPramStr + idColumn.ColumnName); var idDbParam = dbOperator.CreateDbParameter(idColumn.ColumnName, idColumn.ColumnValue); dbParamList.Add(idDbParam); result = Execute(connectionStr, cmdText.ToString(), CommandType.Text, dbParamList.ToArray()); } #endregion } catch (Exception ex) { throw ex; } return(result); }
/// <summary> /// 执行添加操作,返回object(如果设置获取自动增长值,返回为自动增长值,否则返回值为影响行数) /// </summary> /// <typeparam name="T">实体模型名称</typeparam> /// <param name="connectionStr">数据库字符串连接</param> /// <param name="t">实体模型实例化对象</param> /// <returns>object</returns> public virtual object Insert <T>(string connectionStr, T t) { object result = null; try { #region 初始相关变量定义 //var dbBase = new DbBase(dbType); bool isHaveColumn = false; bool isGetIdentityValue = false; List <EntityColumn> noneIdentityColumns = null; #endregion #region 获取字段名称、值 List <EntityColumn> columnList = new EntityColumnUtility().GetColumnList <T>(t); if (columnList != null && columnList.Count > 0) { var identityList = (from p in columnList where (p.IsIdentity == true && p.IsGetIdentityValue == true) select p).ToList <EntityColumn>(); if (identityList != null && identityList.Count > 0) { isGetIdentityValue = true; } var noneIdentityColumnList = (from p in columnList where (p.IsIdentity == false) select p).ToList <EntityColumn>(); if (noneIdentityColumnList != null && noneIdentityColumnList.Count > 0) { isHaveColumn = true; noneIdentityColumns = noneIdentityColumnList; } } #endregion #region 拼接SQL及对数据库操作 if (isHaveColumn) { #region 模板及相关变量定义 Type type = typeof(T); string tableName = type.Name; string tmpColumnNames = "[[ColumnNames]]"; string tmpColumnValues = "[[ColumnValues]]"; List <IDbDataParameter> dbParamList = new List <IDbDataParameter>(); StringBuilder fieldStr = new StringBuilder(); StringBuilder paramStr = new StringBuilder(); StringBuilder sqlBuild = new StringBuilder(); sqlBuild.AppendFormat("insert into {0}({1}) values({2});", tableName, tmpColumnNames, tmpColumnValues); #endregion #region 对非自动增长字段操作 int i = 0; foreach (EntityColumn column in noneIdentityColumns) { fieldStr.Append(column.ColumnName); paramStr.Append(DbPramStr + column.ColumnName); if (i != (noneIdentityColumns.Count - 1)) { fieldStr.Append(","); paramStr.Append(","); } var dbParam = new AdoNetUtility(DbType).CreateDbParameter(column.ColumnName, column.ColumnValue); dbParamList.Add(dbParam); i++; } sqlBuild.Replace(tmpColumnNames, fieldStr.ToString()); sqlBuild.Replace(tmpColumnValues, paramStr.ToString()); #endregion #region 对要获取自动增长字段操作 if (isGetIdentityValue) { if ((int)DbType == (int)GcEnumDbType.MsSql) { sqlBuild.Append("select @@identity;"); } else if ((int)DbType == (int)GcEnumDbType.MsSql) { sqlBuild.Append("select @@identity;"); } result = ExecuteScalar(connectionStr, sqlBuild.ToString(), CommandType.Text, dbParamList.ToArray()); } else { result = Execute(connectionStr, sqlBuild.ToString(), CommandType.Text, dbParamList.ToArray()); } #endregion } #endregion } catch (Exception ex) { throw ex; } return(result); }