asp.net like 組合查詢參數構造及分頁

// 2009-01-15

/// <summary>

/// 獲取SuperSenior下線列表

/// </summary>

/// <param name="companyCode"></param>

/// <param name="userCode"></param>

/// <param name="level"></param>

/// <param name="page"></param>

/// <param name="keywords"></param>

/// <returns></returns>

private DataSet GetSuperSeniorDownlineList(string companyCode, string userCode, LeeSoft.Model.Extend.PageInfo page, Dictionary<string, string> keywords)

{

// 要查詢的級別

string CurrentLevelString = ((int)CompanyUserType.Senior).ToString();

StringBuilder strSql = new StringBuilder();

strSql.Append("select * from (");

strSql.Append("select UserID,UserCompanyCode,UserLoginId,UserIsDefault,UserIsMaster,UserLv,UserLoginPass,UserName,UserRemark1,UserRemark2,UserIsActive,UserHasTran,UserLv2UserCode,UserLv3UserCode,UserLv4UserCode,UserCreditLimit,UserCreditLimitBal,UserCreditLimitKO,UserUplineShare,UserDownlineShare,UserCreateBy,UserCreateDate,UserLastUpdateBy,UserLastUpdateDate,UserLastLoginIP,UserLastLoginDate");

strSql.Append(", ROW_NUMBER() Over(order by [UserID] desc) as rowNum");

strSql.Append(" FROM UserMaster where UserCompanyCode = @UserCompanyCode");

strSql.Append(" and UserLv = " + CurrentLevelString);

strSql.Append(" and UserLv2UserCode = @ParentUserCode");

int beforeCount = 4;// 原參數個數

int beforeCountParams = 2;// 原參數個數

int allParamCount = beforeCount;

int allParamCountTotal = beforeCountParams;

SqlParameter[] parameters;

SqlParameter[] countParams;

string likeResult = string.Empty;

if (keywords != null)

{

// 查詢參數設置

Dictionary<string, SqlParameter> likeParams = new Dictionary<string, SqlParameter>();

Dictionary<string, string> likeFields = new Dictionary<string, string>();

Dictionary<string, string> likeRemove = new Dictionary<string, string>();

likeParams.Add("UserLoginId", new SqlParameter("@LikeUserLoginId", SqlDbType.VarChar, 50));

likeFields.Add("UserLoginId", @" UserLoginId like N'%' + @LikeUserLoginId + '%'");

likeParams.Add("UserName", new SqlParameter("@LikeUserName", SqlDbType.VarChar, 50));

likeFields.Add("UserName", @" UserName like N'%' + @LikeUserName + '%'");

// 初始化查詢參數

StringBuilder strLike = new StringBuilder();

foreach (KeyValuePair<string, SqlParameter> kp in likeParams)

{

if (keywords.ContainsKey(kp.Key) && (keywords[kp.Key] != string.Empty))

{

kp.Value.Value = keywords[kp.Key];

continue;

}

likeRemove.Add(kp.Key, "1");

}

// 移除未設置的條件

foreach (KeyValuePair<string, string> kp in likeRemove)

{

likeParams.Remove(kp.Key);

likeFields.Remove(kp.Key);

}

int likeCount = likeParams.Count; // like 參數個數

allParamCount = likeCount + beforeCount;// 總參數個數

allParamCountTotal = likeCount + beforeCountParams;

// 開始構造查詢參數

parameters = new SqlParameter[allParamCount];

countParams = new SqlParameter[allParamCountTotal];

// 初始化Like參數及sql字符串

int i = 0;

string[] tmp = new string[likeCount];

foreach (KeyValuePair<string, SqlParameter> kp in likeParams)

{

parameters[i + beforeCount] = kp.Value;

countParams[i + beforeCountParams] = kp.Value;

tmp[i] = likeFields[kp.Key];

i++;

}

// 構造最終參數化 WHERE 語句

likeResult = string.Join(" or ", tmp);

if (likeResult != string.Empty) likeResult = " and (" + likeResult + ")";

}

else

{

// 開始構造查詢參數

parameters = new SqlParameter[allParamCount];

countParams = new SqlParameter[allParamCountTotal];

}

string sql = "select count(1) from [UserMaster]";

sql += "where UserCompanyCode = @UserCompanyCode and UserLv = " + CurrentLevelString + " and UserLv2UserCode = @ParentUserCode";

sql += likeResult;

countParams[0] = new SqlParameter("@UserCompanyCode", SqlDbType.VarChar, 10);

countParams[0].Value = companyCode;

countParams[1] = new SqlParameter("@ParentUserCode", SqlDbType.VarChar, 30);

countParams[1].Value = userCode;

int recordCount = (int)SqlHelper.GetSingle(sql, countParams);

page.RecordCount = recordCount;

page.MathPageInfo();

// 設置當前查詢的分頁記錄

parameters[0] = new SqlParameter("@UserCompanyCode", SqlDbType.VarChar, 10);

parameters[0].Value = companyCode;

parameters[1] = new SqlParameter("@ParentUserCode", SqlDbType.VarChar, 30);

parameters[1].Value = userCode;

parameters[2] = new SqlParameter("@startIndex", SqlDbType.Int, 4);

parameters[2].Value = (page.PageNow - 1) * page.PageSize + 1;

parameters[3] = new SqlParameter("@endIndex", SqlDbType.Int, 4);

parameters[3].Value = page.PageNow * page.PageSize;

strSql.Append(likeResult);

strSql.Append(") [tab] where rowNum between @startIndex and @endIndex");

return SqlHelper.Query(strSql.ToString(), parameters);

}