asp.netlike組合查詢參數(shù)構(gòu)造及分頁(yè)

// 2009-01-15

按需定制設(shè)計(jì)可以根據(jù)自己的需求進(jìn)行定制，成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)構(gòu)思過(guò)程中功能建設(shè)理應(yīng)排到主要部位公司成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)的運(yùn)用實(shí)際效果公司網(wǎng)站制作網(wǎng)站建立與制做的實(shí)際意義

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

{

// 要查詢的級(jí)別

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;// 原參數(shù)個(gè)數(shù)

int beforeCountParams = 2;// 原參數(shù)個(gè)數(shù)

int allParamCount = beforeCount;

int allParamCountTotal = beforeCountParams;

SqlParameter[] parameters;

SqlParameter[] countParams;

string likeResult = string.Empty;

if (keywords != null)

{

// 查詢參數(shù)設(shè)置

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 + '%'");

// 初始化查詢參數(shù)

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

}

// 移除未設(shè)置的條件

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

{

likeParams.Remove(kp.Key);

likeFields.Remove(kp.Key);

}

int likeCount = likeParams.Count; // like 參數(shù)個(gè)數(shù)

allParamCount = likeCount + beforeCount;// 總參數(shù)個(gè)數(shù)

allParamCountTotal = likeCount + beforeCountParams;

// 開始構(gòu)造查詢參數(shù)

parameters = new SqlParameter[allParamCount];

countParams = new SqlParameter[allParamCountTotal];

// 初始化Like參數(shù)及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++;

}

// 構(gòu)造最終參數(shù)化 WHERE 語(yǔ)句

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

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

}

else

{

// 開始構(gòu)造查詢參數(shù)

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

// 設(shè)置當(dāng)前查詢的分頁(yè)記錄

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

}

文章名稱：asp.netlike組合查詢參數(shù)構(gòu)造及分頁(yè)
鏈接地址：http://bm7419.com/article12/psdjdc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、App設(shè)計(jì)、網(wǎng)站維護(hù)、電子商務(wù)、搜索引擎優(yōu)化、自適應(yīng)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都網(wǎng)頁(yè)設(shè)計(jì)公司