C＃多重搜索文本框（错误） [英] C# multiple search textbox (error)
问题描述
亲爱的：
朋友
如果你有时间解决我的问题请
i有很多文本框在我的表单中有一个按钮和一个datagridview
i使用此代码进行搜索
Dear:
friends
please if you have time to solve my problem
i have many textbox in my form with one button and one datagridview
i use this code to make the search
<pre>DataTable dt = new DataTable();
if (txtCIVILIDD.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from Tabl1 where CIVILIDD = '" + txtCIVILIDD.Text.Trim() + "'", con);
sda.Fill(dt);
con.Close();
}
else if (txtName_Arabic.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where Name_Arabic like '%" + txtName_Arabic.Text + "%'", con);
sda.Fill(dt);
con.Close();
}
else if (txtusername.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from Tabl1 where username = '" + txtusername.Text.Trim() + "'", con);
sda.Fill(dt);
con.Close();
}
else if (comboBox1.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where status = '" + comboBox1.Text.Trim() + "'", con);
sda.Fill(dt);
con.Close();
}
else if (comboBox2.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where confirmation = '" + comboBox2.Text.Trim() + "'", con);
sda.Fill(dt);
con.Close();
}
else if (CBgender.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where gender like '%" + CBgender.Text + "%'", con);
sda.Fill(dt);
con.Close();
}
else if (CBNATIONALITY.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where NATIONALITY like '" + CBNATIONALITY.Text + "%'", con);
sda.Fill(dt);
con.Close();
}
else if (comboBoxGovernorate.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where Governorate = '" + comboBoxGovernorate.Text.Trim() + "'", con);
sda.Fill(dt);
con.Close();
}
else if (comboBoxCity.Text.Length > 0)
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where City = '" + comboBoxCity.Text.Trim() + "'", con);
sda.Fill(dt);
con.Close();
}
dataGridView1.DataSource = dt;
}
我的尝试：
如果我想使用2个或更多文本框中的值执行搜索，该怎么办？如果我在名称文本框中键入r然后在城市文本框中键入NY，该怎么办？我想看看gridview给我的结果。
我想找到的东西，我没找到任何东西
代码工作如果我只在一个文本框中搜索
热情的问候
What I have tried:
What if i want to perform a search using values from 2 or more text boxes. what if I typed in "r" in the Name text box then also typed "NY" in the city text box. I want to see the gridview give me the results of that.
that what i try to find and i didn't find anything
the code is working if i search in one textbox only
warm regards
推荐答案
不是那样的！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。
连接字符串时会导致问题，因为SQL会收到如下命令：
Not like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'哪个SQL看作三个单独的命令：
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';完全有效的SELECT
A perfectly valid SELECT
DROP TABLE MyTable;完全有效的删除表格通讯和
A perfectly valid "delete the table" command
--'其他一切都是评论。
所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。
所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？
除此之外，还有一个创建相应查询的案例：
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
Other than that, it's a case of creating the appropriate query:
string select = "SELECT * FROM Tabl1 WHERE Name_Arabic LIKE '%' + @NM + '%' AND City LIKE '%' + @CT + '%'";使用StringBuilder构建它，根据文本框的内容将条件组合在一起。
Build it using a StringBuilder to AND conditions together based on what textboxes have content.
您需要先检查所有文本框。通过组合值来收集文本条目并构建搜索语句，以匹配数据库中的相关字段。例如，您可以执行以下操作：
You need to check all your textboxes first. Collect the text entries and build your search statement by combining the values to match the relevant fields in the database. For example you could do something like:
StringBuilder sqlcommand = "SELECT * FROM tabl1 WHERE ";
if (!string.IsNullOrEmpty(CBgender.Text))
{
sqlcommand.Append("GENDER LIKE '%");
sqlcommand.Append(CBgender.Text);
sqlcommand.Append("%'");
}
// repeat for other textbox fields
当所有字段都已填写检查你提交命令。
正如OriginalGriff正确地指出（我应该）：总是使用参数化查询。
[/ edit]
When all fields have been checked you submit the command.
[edit]
And as OriginalGriff rightly points out (and I should have): ALWAYS use parameterized queries.
[/edit]
我使用此代码获得解决方案我希望它可以帮助任何人
i get the solution with this code i hope it help anyone
<pre>private string CreateSqlFilter(string fieldName, Control userInputControl, SqlCommand command, bool exactMatch)
{
string searchValue = null;
if (userInputControl is TextBox) searchValue = ((TextBox)userInputControl).Text;
if (userInputControl is ComboBox) searchValue = ((ComboBox)userInputControl).Text;
if (String.IsNullOrWhiteSpace(searchValue)) return null;
if (exactMatch)
{
command.Parameters.Add(new SqlParameter("@" + fieldName, searchValue));
return fieldName + " = @" + fieldName;
}
else
{
command.Parameters.Add(new SqlParameter("@" + fieldName, "%" + searchValue + "%"));
return fieldName + " LIKE @" + fieldName;
}
}
private void button2_Click(object sender, EventArgs e)
{
SqlCommand selectCommand = new SqlCommand();
var filterConditions = new[] {
CreateSqlFilter("Name_Arabic", txtName_Arabic, selectCommand, false),
CreateSqlFilter("gender", CBgender, selectCommand, false),
CreateSqlFilter("CIVILIDD", txtCIVILIDD, selectCommand, true),
CreateSqlFilter("NATIONALITY", cbNationality, selectCommand, false)
// etc.
};
string filterCondition = filterConditions.Any(a => a != null) ? filterConditions.Where(a => a != null).Aggregate((filter1, filter2) => String.Format("{0} AND {1}", filter1, filter2)) : (string)null;
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myDatabase"].ConnectionString))
{
selectCommand.Connection = connection;
selectCommand.CommandText = filterCondition == null ? "SELECT * FROM tabl1" : "SELECT * FROM tabl1 WHERE " + filterCondition;
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
DataTable dataSource = new DataTable();
adapter.Fill(dataSource);
dataGridView1.DataSource = dataSource;
}
}
这篇关于C＃多重搜索文本框（错误）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
