C# 中datatable的select对同一列用大小或小于筛选时，有一些坑
在开发的过程中为了方便，很多时候都用datatable作为离线数据库使用，但是在筛选的时候，对同一列多次筛选时，结果出现了错误。在此记录一下这个坑，希望知道原因的大佬给出答案。
同时，我这里也用其他方法跳过这个坑，基本思路就是，先筛除大于等于5的，然后在得到的结果上继续筛选小于等于15的，更多条件都可以这样筛选下去(这个坑只是在大于小于，或者大于等于和小于等于上面发现，其他筛选条件：比如包含，开头，结尾时用到的like都没这个问题)：
用如下数据测试：
筛选目的很简单，想筛选序号在5-15之间的数据，按照正常的筛选的代码为：
sqlStr="(F1_序号>=5) and (F1_序号<=15)";
DataRow[] drs= importData.Select(sqlStr);
得到的结果如下图所示：
根据上面的结果，可以看到筛选出来的结果已经出错了。根据上面的错误，将代码修改为分开分步筛选，得到的结果就正常了，筛选的结果如下：
下面给出错误和正确筛选代码的完整代码：
错误代码，可以看到sql是完整构建之后在进行筛选，这样得到的结果是错误的（主要看查询的地方和代码）：
/// <summary>
/// 执行筛选
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void run_Click(object sender, RoutedEventArgs e)
{
string sqlStr = "";
if (valFile.Children.Count > 0)
{
foreach(Label item in valFile.Children)
{
string temStr = "";//构建单个标签内部的sql字符串
OneFilerP filterObj = (OneFilerP)item.Tag;
string filterTxt = filterObj.FilterTxt.Text;
if (filterTxt != "")
{
string filterStr = filterObj.FilterStr;
filterTxt = filterTxt.Replace('；', ';');//考虑中文分号的情况
string[] fts = filterTxt.Split(';');
foreach (string key in fts)
{
if(filterStr == "精确")
{
if(temStr == "")
temStr = "(" + item.Content + "='" + key + "'";
else
temStr += " or " + item.Content + "='" + key + "'";
}
else if (filterStr == "包含")
{
if (temStr == "")
temStr = "(" + item.Content + " like '%" + key + "%'";
else
temStr += " or " + item.Content + " like '%" + key + "%'";
}
else if (filterStr == "开头")
{
if (temStr == "")
temStr = "(" + item.Content + " like '" + key + "%'";
else
temStr += " or " + item.Content + " like '" + key + "%'";
}
else if (filterStr == "结尾")
{
if (temStr == "")
temStr = "(" + item.Content + " like '%" + key + "'";
else
temStr += " or " + item.Content + " like '%" + key + "'";
}
else if (filterStr == "不包含")
{
if (temStr == "")
temStr = "(" + item.Content + " not like '%" + key + "%'";
else
temStr += " or "+ item.Content + " not like '%" + key + "%'";
}
else if (filterStr == "大于")
{
if (temStr == "")
temStr = "(" + item.Content + ">" + key;
else
temStr += " or " + item.Content + ">" + key;
}
else if (filterStr == "小于")
{
if (temStr == "")
temStr = "(" + item.Content + "<" + key;
else
temStr += " or " + item.Content + "<" + key;
}
else if (filterStr == "大于等于")
{
if (temStr == "")
temStr = "(" + item.Content + ">=" + key;
else
temStr += " or " + item.Content + ">=" + key;
}
else if (filterStr == "小于等于")
{
if (temStr == "")
temStr = "(" + item.Content + "<=" + key;
else
temStr += " or " + item.Content + "<=" + key;
}
}
temStr += ")";
}
//组合成全sql字符串
if (temStr != "")
{
if (sqlStr == "")
{
sqlStr = temStr;
}
else
{
sqlStr += " and " + temStr;
}
}
}
}
//查询
DataRow[] drs= importData.Select(sqlStr);
DataTable ResultDt = null;
if (drs.Count() > 0)
{
ResultDt = drs.CopyToDataTable();
}
else if (drs.Count() == 0)
{
ResultDt = new DataTable();
}
//显示sql代码
if(importData==null)
SqlStr.Text = "select * from tableName where " + sqlStr;
else
SqlStr.Text = "select * from " + importData.TableName + " where " + sqlStr;
//保存
if (ResultDt != null)
{
//保存结果
_data.AddTable(this.grid.Tag.ToString(), "2", ResultDt);
runstatus.Text = "运行成功!";
}
else
{
ResultDt = new DataTable();
runstatus.Text = "转换后无数据!";
}
//显示到界面
string rekey = this.grid.Tag.ToString();
MoveToggleButtons mvtglbtn = work.allBtns[rekey];
Btn_OneFilter_S showResult = (Btn_OneFilter_S)mvtglbtn.ResultUserControl;
showResult.ShowData.ItemsSource = ResultDt.DefaultView;
}
根据修改思路，将筛选分段进行，正确的代码如下（主要看查询的位置和代码）：
/// <summary>
/// 执行筛选
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void run_Click(object sender, RoutedEventArgs e)
{
string sqlStr = "";
DataTable ResultDt = importData.Copy(); ;
if (valFile.Children.Count > 0)
{
foreach(Label item in valFile.Children)
{
string temStr = "";//构建单个标签内部的sql字符串
OneFilerP filterObj = (OneFilerP)item.Tag;
string filterTxt = filterObj.FilterTxt.Text;
if (filterTxt != "")
{
string filterStr = filterObj.FilterStr;
filterTxt = filterTxt.Replace('；', ';');//考虑中文分号的情况
string[] fts = filterTxt.Split(';');
foreach (string key in fts)
{
if(filterStr == "精确")
{
if(temStr == "")
temStr = "(" + item.Content + "='" + key + "'";
else
temStr += " or " + item.Content + "='" + key + "'";
}
else if (filterStr == "包含")
{
if (temStr == "")
temStr = "(" + item.Content + " like '%" + key + "%'";
else
temStr += " or " + item.Content + " like '%" + key + "%'";
}
else if (filterStr == "开头")
{
if (temStr == "")
temStr = "(" + item.Content + " like '" + key + "%'";
else
temStr += " or " + item.Content + " like '" + key + "%'";
}
else if (filterStr == "结尾")
{
if (temStr == "")
temStr = "(" + item.Content + " like '%" + key + "'";
else
temStr += " or " + item.Content + " like '%" + key + "'";
}
else if (filterStr == "不包含")
{
if (temStr == "")
temStr = "(" + item.Content + " not like '%" + key + "%'";
else
temStr += " or "+ item.Content + " not like '%" + key + "%'";
}
else if (filterStr == "大于")
{
if (temStr == "")
temStr = "(" + item.Content + ">" + key;
else
temStr += " or " + item.Content + ">" + key;
}
else if (filterStr == "小于")
{
if (temStr == "")
temStr = "(" + item.Content + "<" + key;
else
temStr += " or " + item.Content + "<" + key;
}
else if (filterStr == "大于等于")
{
if (temStr == "")
temStr = "(" + item.Content + ">=" + key;
else
temStr += " or " + item.Content + ">=" + key;
}
else if (filterStr == "小于等于")
{
if (temStr == "")
temStr = "(" + item.Content + "<=" + key;
else
temStr += " or " + item.Content + "<=" + key;
}
}
temStr += ")";
}
//组合成全sql字符串
if (temStr != "")
{
//查询
DataRow[] drs = ResultDt.Select(temStr);
if (drs.Count() > 0)
{
ResultDt = drs.CopyToDataTable();
}
else
{
ResultDt = new DataTable();
break;
}
//组成sql
if (sqlStr == "")
{
sqlStr = temStr;
}
else
{
sqlStr += " and " + temStr;
}
}
}
}
//显示sql代码
if(importData==null)
SqlStr.Text = "select * from tableName where " + sqlStr;
else
SqlStr.Text = "select * from " + importData.TableName + " where " + sqlStr;
//保存
if (ResultDt != null)
{
//保存结果
_data.AddTable(this.grid.Tag.ToString(), "2", ResultDt);
runstatus.Text = "运行成功!";
}
else
{
ResultDt = new DataTable();
runstatus.Text = "转换后无数据!";
}
//显示到界面
string rekey = this.grid.Tag.ToString();
MoveToggleButtons mvtglbtn = work.allBtns[rekey];
Btn_OneFilter_S showResult = (Btn_OneFilter_S)mvtglbtn.ResultUserControl;
showResult.ShowData.ItemsSource = ResultDt.DefaultView;
}
最好，希望知道原因的大佬不吝赐教，谢谢~！