private void InitControl()
{
SearchCompaniesRequest request = new SearchCompaniesRequest();
request.SearchType = SearchCompanyType.List;
request.IsPageModel = true;
request.CompanyName = txtKeyword.Text.NoHTML();
request.CurrentPage = CurrentPageIndex;
request.PageCount = CompanyPage.PageSize;
request.OrderExpression = OrderBy;
request.OrderDirection = OrderDirection;
SearchCompaniesResponse response = companyApp.SearchCompanies(request);
if (response.ResultCount > 0)
{
rptCompanyList.DataSource = response.ResultList;
rptCompanyList.DataBind();
CompanyPage.RecordCount = response.ResultCount;
}
else
{
trNoTickets.Visible = true;
rptCompanyList.Visible = false;
}
}
public SearchCompaniesResponse SearchCompanies(SearchCompaniesRequest request)
{
this.ClearBrokenRuleMessages();
SearchCompaniesResponse response = mgr.SearchCompanies(request);
this.AddBrokenRuleMessages(mgr.BrokenRuleMessages);
return(response);
}
public SearchCompaniesResponse SearchCompanies(SearchCompaniesRequest request)
{
this.ClearBrokenRuleMessages();
SearchCompaniesResponse response = repository.SearchCompanies(request);
if (response == null)
{
this.AddBrokenRuleMessage();
}
return(response);
}
private void InitControl()
{
SearchCompaniesRequest request = new SearchCompaniesRequest();
request.SearchType = SearchCompanyType.List;
request.IsPageModel = true;
request.CompanyName = txtKeyword.Text.NoHTML();
request.CurrentPage = anpCompanies.CurrentPageIndex;
request.PageCount = anpCompanies.PageSize;
request.OrderExpression = hidOrderBy.Value;
request.OrderDirection = hidOrderDirection.Value;
SearchCompaniesResponse response = companyApp.SearchCompanies(request);
rptCompanies.DataSource = response.ResultList;
rptCompanies.DataBind();
anpCompanies.RecordCount = response.ResultCount;
}
private List <CompanysEntity> CachedCompanies()
{
if (cache[CACHECOMPANIES_KEY] == null)
{
SearchCompaniesRequest request = new SearchCompaniesRequest();
request.SearchType = SearchCompanyType.All;
request.IsPageModel = false;
request.OrderExpression = " CompanyName ";
request.OrderDirection = " ASC ";
SearchCompaniesResponse response = repository.SearchCompanies(request);
if (response == null)
{
this.AddBrokenRuleMessage();
}
else
{
cache[CACHECOMPANIES_KEY] = response.ResultList;
}
}
return(cache[CACHECOMPANIES_KEY] as List <CompanysEntity>);
}
public SearchCompaniesResponse SearchCompanies(SearchCompaniesRequest request)
{
int start = request.CurrentPage * request.PageCount + 1 - request.PageCount;
int end = request.CurrentPage * request.PageCount;
string strSelCount = " SELECT COUNT(1) FROM [Companys] ";
string strOrderby = string.Format(" {0} {1} ", request.OrderExpression, request.OrderDirection);
string strSelAttrs = @" SELECT *
,(SELECT COUNT(1) FROM Projects WHERE CompanyID = COM.ComID) AS ProjectsCount
,(SELECT COUNT(1) FROM Users WHERE CompanyID = COM.ComID) AS ClientsCount
FROM [Companys] COM ";
string strSelAttrsOrderBy = string.Format(@" Order BY {0} ", strOrderby);
string strSelPageModel = string.Format(@"SELECT * FROM(
SELECT ROW_NUMBER() OVER(
Order BY {0}) as INDEX_ID,*
,(SELECT COUNT(1) FROM Projects WHERE CompanyID = COM.ComID) AS ProjectsCount
,(SELECT COUNT(1) FROM Users WHERE CompanyID = COM.ComID) AS ClientsCount
FROM [Companys] COM ", strOrderby);
string strWherePageModel = @") NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End;";
StringBuilder strWhere = new StringBuilder();
strWhere.Append(" WHERE 1=1 ");
switch (request.SearchType)
{
case SearchCompanyType.SingleCompany:
strWhere.Append(" AND [ComID]= @ComID ");
break;
case SearchCompanyType.Project:
strWhere.Append(" AND [ComID] IN (SELECT CompanyID FROM Projects WHERE ProjectID =@ProjectID) ");
break;
case SearchCompanyType.User:
strWhere.Append(" AND [ComID] IN (SELECT CompanyID FROM Users WHERE UserID =@UserID) ");
break;
case SearchCompanyType.List:
strWhere.Append(" AND (CompanyName like @CompanyName or State like @CompanyName or City like @CompanyName ) ");
break;
case SearchCompanyType.All:
break;
default:
break;
}
StringBuilder strSql = new StringBuilder();
if (request.IsPageModel)
{
strSql.Append(strSelCount);
strSql.Append(strWhere);
strSql.Append(";");
strSql.Append(strSelPageModel);
strSql.Append(strWhere);
strSql.Append(strWherePageModel);
}
else
{
strSql.Append(strSelAttrs);
strSql.Append(strWhere);
strSql.Append(strSelAttrsOrderBy);
strSql.Append(";");
}
SearchCompaniesResponse response = new SearchCompaniesResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "UserID", DbType.Int32, request.UserID);
db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID);
db.AddInParameter(dbCommand, "ComID", DbType.Int32, request.ComID);
db.AddInParameter(dbCommand, "CompanyName", DbType.String, string.Format("%{0}%", request.CompanyName.FilterSqlString()));
db.AddInParameter(dbCommand, "Strat", DbType.Int32, start);
db.AddInParameter(dbCommand, "End", DbType.Int32, end);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
List <CompanysEntity> list = new List <CompanysEntity>();
if (request.IsPageModel)
{
if (dataReader.Read())
{
response.ResultCount = dataReader.GetInt32(0);
dataReader.NextResult();
}
}
while (dataReader.Read())
{
list.Add(CompanysEntity.ReaderBind(dataReader, true));
}
response.ResultList = list;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}
