public static string packJson(string countStr, string queryStr)
{
//string jsonHead = "{\"total\":";
//string jsonMid = ",\"rows\":";
//string jsonTail = "}";
//DatabaseAccess da = new DatabaseAccess();
//int count = da.queryCount(countStr);
//StringBuilder rJsonVal = new StringBuilder();
//rJsonVal.Append(jsonHead);
//rJsonVal.Append(count);
//rJsonVal.Append(jsonMid);
//DataTable dt = da.queryDatatable(queryStr);
//string mainJsonBody = JsonHelper.DataTableToJSON(dt);
//rJsonVal.Append(mainJsonBody);
//rJsonVal.Append(jsonTail);
//return rJsonVal.ToString();
//version2
DatabaseAccess da = new DatabaseAccess();
int count = da.queryCount(countStr);
DataTable dt = da.queryDatatable(queryStr);
return JsonHelper.ToEasyUIJson(dt, count);
}
/// <summary>
/// 优化过的查询方式，返回easyUI的格式
/// </summary>
/// <param name="countStr">查询总数量语句,SELECT COUNT</param>
/// <param name="queryStr">查询语句</param>
/// <param name="pageNum">页数</param>
/// <param name="rows">每页容量</param>
/// <returns></returns>
public static string packJson(string countStr, string queryStr,int pageNum ,int rows)
{
DatabaseAccess da = new DatabaseAccess();
int count = da.queryCount(countStr);
string sqlStr = string.Format(@"select top {0} TABLE_TEMP.*
from (
select row_number() over(order by (select 0)) as rownumber,* from
(
{1}
)AS TEMP ) AS TABLE_TEMP
WHERE rownumber>={2}", rows.ToString(), queryStr, ((pageNum-1) * rows + 1).ToString());
DataTable dt = da.queryDatatable(sqlStr);
return JsonHelper.ToEasyUIJson(dt, count);
}
/// <summary>
/// 查询该人是否曾经加入到其它队伍
/// </summary>
/// <param name="userID">参与者的ID</param>
/// <returns>bool值，若找到相关记录则为真</returns>
public bool hasAlreadyJoined(string userID)
{
string queryStr = string.Format("SELECT COUNT(*) FROM WEB_TEAM_MEMBER WHERE usercode=N'{0}'", userID);
DatabaseAccess da = new DatabaseAccess();
return (da.queryCount(queryStr) > 0);
}
public string queryTeamID(string teamName)
{
string queryStr = string.Format("SELECT TEAMID FROM WEB_TEAM_PROFILE WHERE TEAMNAME=N'{0}'", teamName);
DatabaseAccess da = new DatabaseAccess();
return da.queryCount(queryStr).ToString();
}
/// <summary>
/// 查询团队名称及（或）ID是否合法
/// </summary>
/// <param name="teamID">团队ID</param>
/// <param name="teamName">团队名称</param>
/// <returns>bool值，合法与否</returns>
public bool isTeamValid(string teamID, string teamName)
{
string queryStr = @"SELECT COUNT(*) FROM WEB_TEAM_PROFILE WHERE ";
string strID = string.Format("teamID=N'{0}'", teamID);
string strName = string.Format("teamName=N'{0}'", teamName);
if (!string.IsNullOrEmpty(teamID))
{
queryStr += strID;
if (!string.IsNullOrEmpty(teamName))
queryStr += " AND " + strName;
}
else
queryStr += strName;
DatabaseAccess da = new DatabaseAccess();
return (da.queryCount(queryStr) == 1);
}