asp.netlike组合查询参数构造及分页

// 2009-01-15

按需定制设计可以根据自己的需求进行定制，成都做网站、成都网站设计构思过程中功能建设理应排到主要部位公司成都做网站、成都网站设计的运用实际效果公司网站制作网站建立与制做的实际意义

///

///

///

///

///

///

///

private DataSet GetSuperSeniorDownlineList(string companyCode, string userCode, LeeSoft.Model.Extend.PageInfo page, Dictionary 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 likeParams = new Dictionary();

Dictionary likeFields = new Dictionary();

Dictionary likeRemove = new Dictionary();

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

}