如何在CheckListBox选择项的基础上形成Where子句 [英] How to Form Where Clause on the Basis of CheckListBox Selected Items
本文介绍了如何在CheckListBox选择项的基础上形成Where子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何在CheckListBox所选项目的基础上形成Where子句
这是我的sp
< span class =code-keyword> BEGIN
声明 @ SQL < span class =code-keyword> VARCHAR （MAX）
SET @ SQL = ' 选择[Hadith_Text]
，[Hadith_Urdu]
，[Hadith_English]
，[Chapter_English_Name]
，[Chapter_Urdu_Name]
，[Baab_English_Name]
，[Baab_Ud]
，[Baab_Id]
，[Hadith_Book_Number]
来自Hadiths_old
其中ID< 34192'
IF @ WhereClause<> ' < span class =code-string>'
SET @ SQL = @ SQL + ' ' + @ WhereClause + ' 按ID排序
EXEC （ @ SQL ）
$ C $ b在C＃
private void buttonX1_Click（ object sender ，EventArgs e）
{
if （clbBooksName.GetItemChecked（ 0 ）== true ）
{
BookSelection + = 或Hadith_Book_Number = 1;
}
if （clbBooksName.GetItemChecked（ 1 ）== true ）
{
BookSelection + = 或者Hadith_Book_Number = 2;
}
if （clbBooksName.GetItemChecked（ 2 ）== true ）
{
BookSelection + = 或者Hadith_Book_Number = 3;
}
if （clbBooksName.GetItemChecked（ 3 ）== true ）
{
BookSelection + = 或者Hadith_Book_Number = 4;
}
if （clbBooksName.GetItemChecked（ 4 ）== true ）
{
BookSelection + = 或者Hadith_Book_Number = 5;
}
if （clbBooksName.GetItemChecked（ 5 ）== true ）
{
BookSelection + = 或者Hadith_Book_Number = 6;
}
foreach （DataGridViewRow Datarow in dataGridView1.Rows）
{
if （dataGridView1.RowCount == 1 ） // 当DataGridview只包含一行时
{
if （Datarow.Cells [ 0 ]。值！= null && Datarow.Cells [ 1 ]。值！= null && Datarow.Cells [ 2 ]。值！= null ） // < span class =code-comment>&& Datarow.Cells [3] .Value！= null）
{
WhereClause + = Datarow.Cells [ 0 ]。Value.ToString（）+ + Datarow.Cells [ 1 ]。Value.ToString（）+ + Datarow。单元格[ 2 ]。Value.ToString（）+ ;
SearchQuery = BookSelection + AND + WhereClause;
}
else
{
MessageBox.Show（ 必须填写列 - کالملازمیپرکریں， 错误，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
}
其他 if （dataGridView1 .RowCount > 1 ） // 当DataGridview包含MOre然后1行
{
if （Datarow.Cells [ 0 ]。值！= null && Datarow.Cells [ 1 ]。值！= null && Datarow.Cells [ 2 ]。值！= null && Datarow.Cells [ 3 ]。值！= < span class =code-keyword> null ）
{
WhereClause + = Datarow.Cells [ 0 ] .Value.ToSt ring（）+ + Datarow.Cells [ 1 ]。Value.ToString（）+ + Datarow.Cells [ 2 ]。Value.ToString（）+ + Datarow.Cells [ 3 ]。Value.ToString（）+ ;
SearchQuery = BookSelection + AND + WhereClause;
}
else
{
MessageBox.Show（ 必须填写列 - کالملازمیپرکریں， 错误，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
}
}
// 用于测试搜索查询！这是什么？
MessageBox.Show（SearchQuery）;
// 将值传递给BLL CLass
objSearching.WhereClause = SearchQuery;
// 将搜索到的值传递给Application.Bind的Form1（主窗体）。搜索的选项卡Combobx和文本框
// 这项工作需要做!!!!!
FrmMain.dgvhadiths.DataSource = objSearching.HadithSearching（）;
dgvSearchHadith.DataSource = objSearching.HadithSearching（）;
// 重置搜索查询变量
WhereClause = ; SearchQuery = ; BookSelection = ;
}
但我想直接发送给SP的条款。你可以看到SP已经包含Where关键字但我不想要这个。我想根据清单框项目选择发送Where子句
解决方案
您好，
尝试做这样的事情？
BEGIN
声明 @ SQL VARCHAR （MAX）
SET @ SQL = ' 选择[Hadith_Text]
，[Hadith_Urdu]
，[Hadith_English]
，[Chapter_English_Name]
，[Chapter_Urdu_Name]
，[Baab_English_Name]
，[Baab_Urdu_Name]
，[Baab_Id]
，[Hadith_Book_Number]
来自Hadiths_old
- 其中ID< 34192'
IF @ WhereClause<> ' < span class =code-string>'
SET @ SQL = @ SQL + ' ' + ' 其中ID< 34192 AND' + @ WhereClause + ' 按ID排序'
ELSE
SET @ SQL = @ SQL + ' ' + ' 其中ID< 34192按ID排序'
EXEC （ @ SQL ）
在你的C＃代码中，稍微优化一下......我想你可以做类似以下的事情...
按钮点击......
StringBuilder BookSelection = new StringBuilder（）;
foreach （ int item in checkedListBox1.CheckedIndices）
{
if （BookSelection.Length == 0 ）
BookSelection.Append（ Hadith_Book_Number In（）;
BookSelection.Append（ '）;
BookSelection.Append（（item +） 1）.ToString（））;
BookSelection.Append（ '）;
}
如果（BookSelection.Length > 0 ）
BookSelection.Append（ ））;
How to Form Where Clause on the Basis of CheckListBox Selected Items
This my sp
BEGIN
declare @SQL VARCHAR(MAX)
SET @SQL='Select [Hadith_Text]
,[Hadith_Urdu]
,[Hadith_English]
,[Chapter_English_Name]
,[Chapter_Urdu_Name]
,[Baab_English_Name]
,[Baab_Urdu_Name]
,[Baab_Id]
,[Hadith_Book_Number]
From Hadiths_old
Where ID < 34192 '
IF @WhereClause<>''
SET @SQL=@SQL+' '+ @WhereClause + ' order by ID'
EXEC(@SQL)
while in C#
private void buttonX1_Click(object sender, EventArgs e)
{
if (clbBooksName.GetItemChecked(0) == true)
{
BookSelection += "OR Hadith_Book_Number = 1 ";
}
if (clbBooksName.GetItemChecked(1) == true)
{
BookSelection += "OR Hadith_Book_Number = 2 ";
}
if (clbBooksName.GetItemChecked(2) == true)
{
BookSelection += "OR Hadith_Book_Number = 3 ";
}
if (clbBooksName.GetItemChecked(3) == true)
{
BookSelection += "OR Hadith_Book_Number = 4 ";
}
if (clbBooksName.GetItemChecked(4) == true)
{
BookSelection += "OR Hadith_Book_Number = 5 ";
}
if (clbBooksName.GetItemChecked(5) == true)
{
BookSelection += "OR Hadith_Book_Number = 6 ";
}
foreach (DataGridViewRow Datarow in dataGridView1.Rows)
{
if (dataGridView1.RowCount == 1) //When DataGridview Contain only one row
{
if (Datarow.Cells[0].Value != null && Datarow.Cells[1].Value != null && Datarow.Cells[2].Value != null)//&& Datarow.Cells[3].Value != null)
{
WhereClause += Datarow.Cells[0].Value.ToString() + " " + Datarow.Cells[1].Value.ToString() + " " + Datarow.Cells[2].Value.ToString() + " ";
SearchQuery = BookSelection + " AND " + WhereClause;
}
else
{
MessageBox.Show("Must Fill Column-کالم لازمی پر کریں","Error",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
else if (dataGridView1.RowCount > 1) //When DataGridview Contain MOre then 1 row
{
if (Datarow.Cells[0].Value != null && Datarow.Cells[1].Value != null && Datarow.Cells[2].Value != null && Datarow.Cells[3].Value != null)
{
WhereClause += Datarow.Cells[0].Value.ToString() + " " + Datarow.Cells[1].Value.ToString() + " " + Datarow.Cells[2].Value.ToString()+ " " + Datarow.Cells[3].Value.ToString()+ " ";
SearchQuery = BookSelection + " AND " + WhereClause;
}
else
{
MessageBox.Show("Must Fill Column-کالم لازمی پر کریں", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
//for testing search query ! What is this?
MessageBox.Show(SearchQuery);
//passing value to BLL CLass
objSearching.WhereClause = SearchQuery;
//pass Searched values to Form1 (Main Form) of Application.Bind also Search's tab Combobx and textboxes
//this work require to do!!!!!
FrmMain.dgvhadiths.DataSource = objSearching.HadithSearching();
dgvSearchHadith.DataSource = objSearching.HadithSearching();
//reset the searching query variable
WhereClause = ""; SearchQuery = ""; BookSelection="";
}
But I want To Send Directly Where Clause to SP. YOu can See SP already contain Where keyword But i dont want this. I want to Send Where clause on the basis of Checklist box item selection
解决方案
Hi ,
Try Doing Something like this ?
BEGIN declare @SQL VARCHAR(MAX) SET @SQL='Select [Hadith_Text] ,[Hadith_Urdu] ,[Hadith_English] ,[Chapter_English_Name] ,[Chapter_Urdu_Name] ,[Baab_English_Name] ,[Baab_Urdu_Name] ,[Baab_Id] ,[Hadith_Book_Number] From Hadiths_old --Where ID < 34192 ' IF @WhereClause<>'' SET @SQL=@SQL+' '+'Where ID < 34192 AND'+ @WhereClause + ' order by ID' ELSE SET @SQL=@SQL+' '+'Where ID < 34192 order by ID' EXEC(@SQL)
And In Your C# Code , Optimize it little bit ... I think You can do something like the following ...
On Your Button Click...
StringBuilder BookSelection = new StringBuilder(); foreach (int item in checkedListBox1.CheckedIndices) { if (BookSelection.Length == 0) BookSelection.Append("Hadith_Book_Number In ("); BookSelection.Append(" '"); BookSelection.Append((item+1).ToString()); BookSelection.Append("' "); } if (BookSelection.Length > 0) BookSelection.Append(")");
这篇关于如何在CheckListBox选择项的基础上形成Where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
