/// <summary> /// 得到SQL语句,order by的语句 /// </summary> /// <param name="filters"></param> /// <returns></returns> internal virtual string GetOrderByString(QueryPageFilter filters) { string orderBy = string.Empty; if (filters != null && filters.OrderFieldNames != null && filters.OrderFieldNames.Length > 0) { StringBuilder builder = new StringBuilder(); for (int i = 0; i < filters.OrderFieldNames.Length; i++) { builder.Append(filters.OrderFieldNames[i]); if (i != filters.OrderFieldNames.Length - 1) { builder.Append(","); } } if (filters.OrderByType == SQLOrderBy.Desc) { orderBy = " order by " + builder.ToString() + " desc "; } else { orderBy = " order by " + builder.ToString() + " asc "; } } return(orderBy); }
/// <summary> /// 得到条件右边的值 /// </summary> /// <param name="fieldValue"></param> /// <returns></returns> internal virtual string GetFieldValue(object fieldValue) { if (QueryPageFilter.IsSqlFilter(fieldValue) == true) { throw new Exception("数据中含SQL注入,请误使用!"); } if (fieldValue is string) { string str = fieldValue.ToString(); if (str.IndexOf("'") > -1) { str = str.Replace("'", "''"); } return(string.Format("'{0}'", str)); } if (fieldValue is DBNull || fieldValue is DateTime || fieldValue is Guid) { return(string.Format("'{0}'", fieldValue.ToString())); } if (fieldValue is Boolean) { bool t = (Boolean)fieldValue; return(t ? 1.ToString() : 0.ToString()); } else { if (fieldValue == null) { return(null); } return(fieldValue.ToString()); } }
/// <summary> /// 得到where语句 /// </summary> /// <param name="filters"></param> /// <returns></returns> internal virtual string GetWhereString(QueryPageFilter filters) { StringBuilder builder = new StringBuilder(); int index = 0; if (filters.Filters != null && filters.Filters.Length > 0) { foreach (var item in filters.Filters) { if (index != 0) { builder.Append(" " + filters.FilterAndOrType.ToString() + " "); } string str = string.Empty; if (item is QueryFilter) { str = GetSqlSign(item as QueryFilter); } if (item is SpatialQueryFilter) { str = GetSqlSign(item as SpatialQueryFilter); } if (item is AndOrQueryFilter) { str = GetSqlSign(item as AndOrQueryFilter); } builder.Append(str); index++; } //foreach (var item in filters.Filters) //{ // builder.Append(GetSqlSign(item)); // index++; // if (index != filters.Filters.Length) // builder.Append(" " + filters.FilterAndOrType.ToString() + " "); //} } //if (filters.SpatialFilters != null&& filters.SpatialFilters.Length > 0) //{ // if (index > 0) // { // index = 0; // builder.Append(" " + filters.FilterAndOrType.ToString() + " "); // } // foreach (var item in filters.SpatialFilters) // { // builder.Append(GetSqlSign(item)); // index++; // if (index != filters.SpatialFilters.Length) // builder.Append(" " + filters.FilterAndOrType.ToString() + " "); // } //} if (index == 0) { return("1=1"); } return(builder.ToString()); }
/// <summary> /// 查询数据库 /// </summary> /// <param name="filters">过滤条件filter对象</param> /// <returns>FilterQueryResult结果对象</returns> internal virtual FilterQueryResult GetQueryResult(QueryPageFilter filters) { FilterQueryResult result = new FilterQueryResult(); string whereStr = GetWhereString(filters); string orderBy = GetOrderByString(filters); string groupByFields = GetGroupByFields(filters); var fields = GetQueryFields(filters); string sql = "select count(*) from " + filters.TableName + " where " + whereStr; string strSql = string.Empty; if (filters.IsPage == true) { var index = filters.PageSize * (filters.PageIndex - 1); if (index < 0) { index = 0; } strSql = "select top " + filters.PageSize + fields + " from " + filters.TableName + " where Id Not In ( select top " + index + " Id from " + filters.TableName + " where " + whereStr + orderBy + ") and ( " + whereStr + " ) " + orderBy; if (CurrPrividerType == SqlPrividerType.Sqlite) { strSql = "select " + fields + " from " + filters.TableName + " where Id Not In ( select Id from " + filters.TableName + " where " + whereStr + orderBy + " limit 0," + index + " ) and ( " + whereStr + " ) " + orderBy + " limit 0," + filters.PageSize; } } else { var groupBy = string.Empty; if (!string.IsNullOrEmpty(groupByFields)) { fields = groupByFields; groupBy = "group by " + groupByFields; } strSql = "select " + fields + " from " + filters.TableName + " where " + whereStr + groupBy + orderBy; } var conn = DBCoreHelper.OpenConnect(CurrConnectionString, DBHelperFactory.GetSqlPrividerTypeName(CurrPrividerType)); var tableCount = DBCoreHelper.ExecuteQueryToDataTable(sql, conn); int count = int.Parse(tableCount.Rows[0][0].ToString()); if (filters.IsReturnCount == false) { var table = DBCoreHelper.ExecuteQueryToDataTable(strSql, conn); result.Result = table; } result.TotalCount = count; conn.Close(); return(result); }
/// <summary> /// 得到查询字段 /// </summary> /// <param name="filters"></param> /// <returns></returns> internal virtual string GetQueryFields(QueryPageFilter filters) { if (filters.ReturnFieldNames == null || filters.ReturnFieldNames.Length == 0) { return(" * "); } StringBuilder str = new StringBuilder(); for (int i = 0; i < filters.ReturnFieldNames.Length; i++) { if (i != 0) { str.Append(","); } str.Append(filters.ReturnFieldNames[i]); } return(str.ToString()); }
/// <summary> /// 得到SQL语句,group by的语句 /// </summary> /// <param name="filters"></param> /// <returns></returns> internal virtual string GetGroupByFields(QueryPageFilter filters) { string groupBy = string.Empty; if (filters != null && filters.GroupByFieldNames != null && filters.GroupByFieldNames.Length > 0) { StringBuilder builder = new StringBuilder(); for (int i = 0; i < filters.GroupByFieldNames.Length; i++) { builder.Append(filters.GroupByFieldNames[i]); if (i != filters.GroupByFieldNames.Length - 1) { builder.Append(","); } } groupBy = builder.ToString(); } return(groupBy); }
} //TableToTableNameDic /// <summary> /// 查询数据库 /// </summary> /// <param name="filter"></param> /// <param name="count"></param> /// <returns>dictionary数组</returns> public Dictionary <string, object>[] GetQueryResult(QueryPageFilter filter, ref int count) { string tableName1 = filter.TableName; if (TableNameDic != null && TableNameDic.ContainsKey(filter.TableName)) { tableName1 = TableNameDic[filter.TableName]; } if (!DateBaseKeyFieldDic.ContainsKey(tableName1)) { throw new Exception("表名不存在主键,请尝试重启服务!"); } filter.TableName = tableName1; var result = GetQueryResult(filter); var queryResult = new QueryFilterResult(); queryResult.TotalCount = result.TotalCount; queryResult.TableName = filter.TableName; count = result.TotalCount; if (filter.IsReturnCount != true) { if (result.Result != null) { var dic = new Dictionary <string, object> [result.Result.Rows.Count]; int index = 0; foreach (DataRow row in result.Result.Rows) { var dd = ConvertDataRow(row, false); dic[index] = dd; index++; } return(dic); } } return(null); }