private static string CountSqlBuilder(TableQueryCriteria queryCriteria) { string strWhere = string.Empty; if (!string.IsNullOrWhiteSpace(queryCriteria.Where)) { strWhere = "Where " + queryCriteria.Where; } string sql = string.Format("Select Count(*) From [{0}].[dbo].[{1}] {2}", queryCriteria.DBName, queryCriteria.TableName, strWhere); return(sql); }
private static string PageSqlBuilder(TableQueryCriteria queryCriteria) { string colNames = "*"; int startIndex = (queryCriteria.PageNumber - 1) * ConfigHelper.PageSize + 1; int endIndex = queryCriteria.PageNumber * ConfigHelper.PageSize; string strWhere = string.Empty; if (!string.IsNullOrWhiteSpace(queryCriteria.Where)) { strWhere = "Where " + queryCriteria.Where; } string sql = string.Format("Select {0} From (Select {0}, row_number() over(Order By [{1}]) as rowNumber From [{2}].[dbo].[{3}] {4}) XCode_T1 Where rowNumber Between {5} And {6}", colNames, queryCriteria.DefaultOrderByColumn, queryCriteria.DBName, queryCriteria.TableName, strWhere, startIndex, endIndex); return(sql); }
private TableQueryCriteria GetTableQueryCriteria(int selectedIndex, int pageNumber, int cityId, DateTime beginDate, DateTime endDate) { TableQueryCriteria queryCriteria = new TableQueryCriteria(); queryCriteria.DBServer = "FxtData_Case"; queryCriteria.DBName = "FxtData_Case"; queryCriteria.PageNumber = pageNumber; if (selectedIndex == 0) { queryCriteria.TableName = "ImportRecord"; queryCriteria.DefaultOrderByColumn = "ID"; queryCriteria.Where = string.Format("[ID]={0} and ([CaseBeginDate]>='{1}' and [CaseBeginDate]<='{2}' or [CaseEndDate]>='{1}' and [CaseEndDate]<='{2}')", cityId, beginDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd")); } else { queryCriteria.TableName = "住宅案例_出售_异常案例"; queryCriteria.DefaultOrderByColumn = "城市ID"; queryCriteria.Where = string.Format("[城市ID]={0} and [案例时间]>='{1}' and [案例时间]<='{2}'", cityId, beginDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd")); } return(queryCriteria); }
public static TableQueryResponseModel PageQueryTable(TableQueryCriteria queryCriteria, TableQueryResponseModel queryResult) { if (queryCriteria.PageNumber < 1) { queryCriteria.PageNumber = 1; } queryResult.PageNumber = queryCriteria.PageNumber; queryResult.PageSize = ConfigHelper.PageSize; var countSql = CountSqlBuilder(queryCriteria); var pageSql = PageSqlBuilder(queryCriteria); #region query code #region db server string serverName = queryCriteria.DBServer; string dbName = queryCriteria.DBName; var dbConnList = ConfigHelper.GetConnectionStrings(); if (dbConnList == null || !dbConnList.ContainsKey(serverName)) { throw new Exception("connectionStrings不存在的数据库服务器名称:" + serverName); } ConnectionStringSettings connSettings = dbConnList[serverName]; SqlConnectionStringBuilder cnBuilder = new SqlConnectionStringBuilder(connSettings.ConnectionString); if (cnBuilder.InitialCatalog != dbName) { cnBuilder.InitialCatalog = dbName; } #endregion DbProviderFactory factory = null; DbConnection conn = null; DbCommand command = null; try { factory = DbProviderFactories.GetFactory(connSettings.ProviderName); conn = factory.CreateConnection(); conn.ConnectionString = cnBuilder.ConnectionString; conn.Open(); command = conn.CreateCommand(); command.CommandType = CommandType.Text; #region read table //读取总记录数 command.CommandText = countSql; queryResult.TotalCount = (int)command.ExecuteScalar(); //分页读取 command.CommandText = pageSql; using (DbDataAdapter reader = factory.CreateDataAdapter()) { reader.SelectCommand = command; DataSet ds = new DataSet(); reader.Fill(ds); reader.Dispose(); queryResult.Result = ds.Tables[0]; } //移除rowNumber列 queryResult.Result.Columns.Remove("rowNumber"); #endregion } finally { #region dispose if (command != null) { command.Dispose(); command = null; } if (conn != null) { conn.Close(); conn = null; } #endregion } #endregion return(queryResult); }