ASP.NET SQL語法用in時,Parameters指令如何下
本範例是為了找出主管所管轄之下的所有資料，故比較複雜，請直接看以下列數：
17,20,30,31,32,33,34,35,36,37,46,47
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM " + VIEWNAME + " ");
if (!string.IsNullOrEmpty(strKeyword))
{
//sbSql.Append(" AND (name +batchnumber + jobtitles + CONVERT(varchar(30),joblevel) + workingfunc + ogno + ogname + ouno + ouname + ename + status + CONVERT(varchar(30),cdt,120) like @strKeyword) ");
//select tblA.* from t_HRM_Evaluation tblA join dbo.FN_SPLIT_TBL('張,陳,王',',') tblB on tblA.name like '%'+tblB.Value+'%'
sbSql.Append(" JOIN dbo.FN_SPLIT_TBL(@strKeyword,',') tblB ON docno + patentcname + patentename + ogno + ogname + ouno + ouname + ouename + status + confirms + CONVERT(varchar(30),cdt,120) LIKE '%'+tblB.Value+'%' ");
cmd.Parameters.AddWithValue("@strKeyword", strKeyword);
}
sbSql.Append(" WHERE 1=1 ");
sbSql.Append(" AND delete_flag=@delete_flag ");
cmd.Parameters.AddWithValue("@delete_flag", delete_flag);
sbSql.Append(" AND ouoid in (");
int i = 1;
if (dasgroup.IsManager(uoid, HRM.STATUS_LIST.ACTIVE, false, 400, 500) > 0)
{
//找出該員所管轄部門
DataTable dtg = dasgroup.GetDataByLeader(HRM.STATUS_LIST.ACTIVE, false, uoid);
foreach (DataRow drg in dtg.Rows)
{
//找出部門下成員
Guid _goid = CUtility.StringToGuid(drg["oid"].ToString());
DataTable dtu = dasuser.GetChildDeptUser(_goid);
foreach (DataRow dru in dtu.Rows)
{
// IN clause
sbSql.Append("@UserId" + i.ToString() + ",");
// parameter
cmd.Parameters.AddWithValue("@UserId" + i.ToString(), CUtility.StringToGuid(dru["oid"].ToString()));
i++;
}
}
}
else
{
sbSql.Append("@UserId" + i.ToString() + ",");
cmd.Parameters.AddWithValue("@UserId" + i.ToString(), uoid);
}
cmd.CommandText = sbSql.ToString();
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 1, 1) + ")";
dt = sdp.GetDataTable(cmd);
return dt;
Ref Url:
https://dotblogs.com.tw/jeff-yeh/2008/09/05/5308
