SQL 判断是否用where或and-优快云博客

本文介绍了一个使用StringBuilder动态构建SQL查询语句的例子，包括如何根据输入条件选择性地添加WHERE子句及参数化处理，以实现对DeskInfo表中DeskId、DeskName等字段的有效检索。

StringBuilder sql = new StringBuilder();
sql.Append("select DeskId,DeskName,DeskNamePinYin,DeskNum from DeskInfo ");
bool flag = false;
List<SqlParameter> pms = new List<SqlParameter>();
#region 判断是否用where或and
if (!string.IsNullOrEmpty(tbName.Text.Trim()))
{
if (!flag)
{
sql.Append(" where DeskName like @DeskName ");
flag = true;
}
else
{
sql.Append(" and DeskName like @DeskName ");
}
pms.Add(new SqlParameter("@DeskName", "%" + tbName.Text.Trim() + "%"));
}
if (!string.IsNullOrEmpty(tbPinYin.Text.Trim()))
{
if (!flag)
{
sql.Append(" where DeskNamePinYin like @DeskNamePinYin ");
flag = true;
}
else
{
sql.Append(" and DeskNamePinYin like @DeskNamePinYin ");
}
pms.Add(new SqlParameter("@DeskNamePinYin", "%" + tbPinYin.Text.Trim() + "%"));
}
if (!string.IsNullOrEmpty(tbNum.Text.Trim()))
{
if (!flag)
{
sql.Append(" where DeskNum like @DeskNum ");
flag = true;
}
else
{
sql.Append(" and DeskNum like @DeskNum ");
}
pms.Add(new SqlParameter("@DeskNum", "%" + tbNum.Text.Trim() + "%"));
}
#endregion
DataTable dt = SqlHelper.ExecuteTable(sql.ToString(), pms.ToArray());
List<DeskInfo> list = ChangeDtToList(dt);
dgvDeskInfo.DataSource = list;