SQL查询操作
有7个筛选条件任意一个条件都可以筛选。采用LINQ查询比较繁琐,且操作步骤增加,选择用SQL判断。
public DataTable GetData(string cboCld, string cboFact,string cboPrtN,string txtbCde,string txtbDce,string txtbPdt,string txtbRID) { string strSql = @"select distinct l.Area,l.Facility ,m.vdpartno,l.PartNo,l.ReelID,l.Qty ,l.Location,l.datecd,l.prodcd,l.vendorcd,l.lmdate from VMIReelShelfDetail l left JOIN smtitem m ON m.partno = l.partno where l.ReelID <>''"; if (cboCld != "ALL") { strSql = strSql + " AND l.Area='" + cboCld + "'"; } if (cboFact != "ALL") { strSql = strSql + " AND l.Facility='" + cboFact + "'"; } if (cboPrtN != "ALL") { strSql = strSql + " AND l.PartNo='" + cboPrtN + "'"; } if (txtbCde != "") { strSql = strSql + " AND l.vendorcd='" + txtbCde + "'"; } if (txtbDce != "") { strSql = strSql + " AND l.datecd='" + txtbDce + "'"; } if (txtbPdt != "") { strSql = strSql + " AND l.prodcd='" + txtbPdt + "'"; } if (txtbRID != "") { strSql = strSql + " AND l.ReelID='" + txtbRID + "'"; } return SqlHelper.Query(strSql); }
通过判断这几个界面上的文本框和复选框,来追加SQL中的查询条件最为方便 且简单。