/// <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);
}