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