/// <summary> /// 从数据库得到来源数据 /// </summary> /// <returns></returns> public DataTable GetSourceDataFromDB(Hashtable parameters, TableMap tableMap) { DataTable dtSource = null; DataAccessBroker brokerSource = DataAccessFactory.Instance(this.SourceDataAccessCfg); try { string sqlString = tableMap.GetSourceSelectSQL(this.MaxCount); // 替换sql中的宏变量 sqlString = BaseCommand.ReplaceParameters(this.InitialParameters, sqlString); DataSet dataSetSource = brokerSource.FillSQLDataSet(sqlString); // 源数据为空时,不做任何操作 if (dataSetSource == null || dataSetSource.Tables.Count == 0) { return(null); } // 取第一个表 dtSource = dataSetSource.Tables[0]; } finally { brokerSource.Close(); } // 写日志 LogManager.Current.WriteCommonLog(this.JobCode, "从数据表" + tableMap.SourceTable.TableName + "获得" + dtSource.Rows.Count.ToString() + "笔记录。", this.ThreadName); return(dtSource); }
public static LigerGrid ExcuteTable <T>(string hql, DataAccessParameterCollection lstParameter, LigerGrid grid, DataAccessBroker broker) where T : EntityBase, new() { object total = broker.ExecuteScalar("select count(1) as Total from (" + hql + ") as ttable", lstParameter, CommandType.Text); StringBuilder strsql = new StringBuilder(); if (grid.pageIndex != 0 && grid.pageSize != 0) { strsql.Append("select * from ( select top " + (grid.pageIndex * grid.pageSize) + " row_number() over(order by " + grid.sortName + " " + grid.sortOrder.ToLower() + ") as Row_Number, "); strsql.Append(hql.Replace(":", "@").Substring(7)); if (!string.IsNullOrEmpty(grid.Where)) { strsql.Append(" and (" + grid.Where.Replace(":", "@") + ")");; }//as strsql.Append(" ) ttable where Row_Number between " + (((grid.pageIndex - 1) * grid.pageSize) + 1) + " and " + ((grid.pageIndex) * grid.pageSize)); } else { strsql.Append(hql); if (!string.IsNullOrEmpty(grid.sortName)) { strsql.Append(" order by " + grid.sortName); if (!string.IsNullOrEmpty(grid.sortOrder)) { strsql.Append(" " + grid.sortOrder); } else { strsql.Append(" ASC"); } } } DataSet ds = broker.FillSQLDataSet(strsql.ToString(), lstParameter); EntityCollection <T> lstEntity = new EntityCollection <T>(); List <Dictionary <string, object> > dicLstRow = new List <Dictionary <string, object> >(); ToEntityList <T>(ds.Tables[0], ref dicLstRow, ref lstEntity); if (grid.IsDataEntity) { grid.Rows = lstEntity; } else { grid.Rows = dicLstRow; } grid.Total = (total != null ? Convert.ToInt32(total) : 0); return(grid); }
/// <summary> /// 根据Data ID List取数据 /// </summary> /// <returns></returns> public DataTable GetSourceByIDList(TableMap tableMap, string fieldNames, List <string> dataIDList, string dataSplitOperator) { DataTable retTable = new DataTable(); DataAccessBroker brokerSource = DataAccessFactory.Instance(this.SourceDataAccessCfg); try { for (int x = 0; x < dataIDList.Count;) { // 每批50个 int count = 50; if (x + count > dataIDList.Count) { count = dataIDList.Count - x; } // 多条数据以or拼起来 List <string> tempDataIDList = new List <string>(); for (int y = 0; y < count; y++) { tempDataIDList.Add(dataIDList[x + y]); } x += count; // 得到where语句 string whereSql = BaseCommand.MakeDataIDWhereSql(fieldNames, tempDataIDList, dataSplitOperator); string sql = "select " + tableMap.SourceTable.FieldNames + " from " + tableMap.SourceTable.TableName + whereSql; DataSet dataSet = brokerSource.FillSQLDataSet(sql); if (dataSet == null || dataSet.Tables.Count == 0) { continue;// 继续下一批50 } DataTable dtTemp = dataSet.Tables[0]; // 第一次时初始化列 if (retTable.Columns.Count == 0) { for (int x1 = 0; x1 < dtTemp.Columns.Count; x1++) { retTable.Columns.Add(dtTemp.Columns[x1].ColumnName, dtTemp.Columns[x1].DataType); } } // 将临时表的数据复制到返回的表中 for (int i = 0; i < dtTemp.Rows.Count; i++) { DataRow row = retTable.NewRow(); DataRow dtRow = dtTemp.Rows[i]; for (int j = 0; j < retTable.Columns.Count; j++) { row[j] = dtRow[j]; } retTable.Rows.Add(row); } } } catch (Exception ex) { throw ex; } finally { brokerSource.Close(); } return(retTable); }
private static JqGrid ExcuteTable <T>(string hql, DataAccessParameterCollection lstParameter, JqGrid grid, DataAccessBroker broker) where T : EntityBase, new() { int index = 0; StringBuilder whereSql = new StringBuilder(hql); if (grid.QueryField["_search"] == "true") { foreach (KeyValuePair <string, string> kv in grid.QueryField) { if (index > 5) { DateTime result = DateTime.Now; if (DateTime.TryParse(kv.Value, out result)) { whereSql.AppendLine($" and datediff(day,{kv.Key},'{kv.Value}')=0 "); } else { whereSql.AppendLine($" and {kv.Key} like N{Utils.ToSqlLikeStr(kv.Value)} "); } } index++; } } //as object total = broker.ExecuteScalar("select count(1) as Total from (" + whereSql + ") ttable", lstParameter, CommandType.Text); StringBuilder strsql = new StringBuilder(); if (grid.page != 0 && grid.rows != 0) {//top " + (grid.page * grid.rows) + " strsql.Append("select * from ( select row_number() over(order by " + grid.sidx + " " + grid.sord.ToLower() + ") as Row_Number, "); strsql.Append(whereSql.ToString().Substring(7)); if (!string.IsNullOrEmpty(grid.Where)) { strsql.Append(" and (" + grid.Where + ")");; } //as strsql.Append(" ) ttable where Row_Number between " + (((grid.page - 1) * grid.rows) + 1) + " and " + ((grid.page) * grid.rows)); } else { strsql.Append(whereSql); if (!string.IsNullOrEmpty(grid.sidx)) { strsql.Append(" order by " + grid.sidx); if (!string.IsNullOrEmpty(grid.sord)) { strsql.Append(" " + grid.sord); } else { strsql.Append(" ASC"); } } } DataSet ds = broker.FillSQLDataSet(strsql.ToString(), lstParameter); EntityCollection <T> lstEntity = new EntityCollection <T>(); List <Dictionary <string, object> > dicLstRow = new List <Dictionary <string, object> >(); ToEntityList <T>(ds.Tables[0], ref dicLstRow, ref lstEntity); if (grid.IsDataEntity) { grid.RowsData = lstEntity; } else { grid.RowsData = dicLstRow; } grid.Total = (total != null ? Convert.ToInt32(total) : 0); return(grid); }