在 C# 中搜索数据集中的记录
本文关键字：集中 记录 数据集 数据 搜索 | 更新日期: 2023-09-27 18:33:08
我正在尝试设置它，以便用户可以通过在文本框和组合框中输入详细信息来搜索数据网格视图中的特定记录。这是我的代码：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace RRAS
{
public partial class formRRAS : Form
{
public OleDbConnection DataConnection = new OleDbConnection();
public formRRAS()
{
InitializeComponent();
}
private void formRRAS_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'database1DataSet.tblReject_test' table. You can move, or remove it, as needed.
this.tblReject_testTableAdapter.Fill(this.database1DataSet.tblReject_test);
}
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnSearch_Click(object sender, EventArgs e)
{
//This creates the String Publisher which grabs the information from the combo box on the form.
//Select and Dataconnection are also defined here.
string Select = "SELECT * FROM tblReject_test";
string Department = txtDepartment.Text;
string Start_Date = txtStart.Text;
string End_Date = txtEnd.Text;
string Anatomy = txtAnatomy.Text;
string RFR = cmbRFR.Text;
string Comment = txtComment.Text;
//Select defines what should be loaded on to the dataset.
if (Department != "")
{
Select = Select + " WHERE department_id =" + "'" + Department + "'";
if (Anatomy != "")
{
Select = Select + "AND body_part_examined =" + "'" + Anatomy + "'";
if (Start_Date != "")
{
Select = Select + " AND study_date =" + "'" + Start_Date + "'";
if (End_Date != "")
{
Select = Select + " AND study_date =" + "'" + End_Date + "'";
if (RFR != "")
{
Select = Select + " AND reject_category =" + "'" + RFR + "'";
if(Comment != "")
{
Select = Select + " AND reject_comment =" + "'" + Comment + "'";
}
}
}
}
}
}
else
{
Select = "SELECT * FROM tblReject_test";
}
//DataConnection connects to the database.
string connectiontring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|''Database1.mdb";
DataConnection = new OleDbConnection(connectiontring);
//The DataAdapter is the code that ensures both the data in the Select and DataConnection strings match.
OleDbDataAdapter rdDataAdapter = new OleDbDataAdapter(Select, DataConnection);
try
{
//It then clears the datagridview and loads the data that has been selected from the DataAdapter.
database1DataSet.tblReject_test.Clear();
rdDataAdapter.Fill(this.database1DataSet.tblReject_test);
}
catch (OleDbException exc)
{
System.Windows.Forms.MessageBox.Show(exc.Message);
}
}
private void btnLoadChart_Click(object sender, EventArgs e)
{
{
string connectiontring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|''Database1.mdb";
DataConnection = new OleDbConnection(connectiontring);
try
{
int count = database1DataSet.Tables["tblReject_test"].Rows.Count;
DataConnection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = DataConnection;
string query = "SELECT * FROM tblReject_test";
command.CommandText = query;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
charRejections.Series["RFR"].Points.AddXY(reader["reject_category"].ToString(), reader[count].ToString());
}
DataConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
}
}
}
目前，该程序确实在一定程度上搜索记录。它只会按 if 语句的顺序搜索记录。所以我不能在部门中输入详细信息，然后输入 RFR。如果我这样做，datagridview 只会更新以显示我输入的部门的所有记录，它将忽略我在 RFR 中输入的信息。
我确信这不是最好的方法，但您可以考虑使用以下方法：
private void btnSearch_Click(object sender, EventArgs e)
{
//This creates the String Publisher which grabs the information from the combo box on the form.
//Select and Dataconnection are also defined here.
//here you can check if the textbox.Text is empty and if it is use the % char to match anything
string Department = String.IsNullOrEmpty(txtDepartment.Text)? "%" : txtDepartment.Text;
string Start_Date = String.IsNullOrEmpty(txtStart.Text)? "%": txtStart.Text;
string End_Date = String.IsNullOrEmpty(txtEnd.Text)? "%" : txtEnd.Text;
string Anatomy = String.IsNullOrEmpty(txtAnatomy.Text)? "%":txtAnatomy.Text;
string RFR = String.IsNullOrEmpty(cmbRFR.Text)? "%" : cmbRFR.Text;
string Comment = String.IsNullOrEmpty(txtComment.Text)? "%":txtComment.Text;
//the query could look like this:
string Select = "SELECT * FROM tblReject_test WHERE department_id LIKE '" +Department +"'" + "AND body_part_examined like'" + Anatomy +"'"+"AND study_date like'"+ Start_Date +"'";//and so on
//rest of the code
}
在select中，如果其中一个文本框不包含任何文本，我们将使用'%'通配符。
如果任何文本框不包含任何文本，则通过返回该列的任何值，该字段将在查询中"忽略"。如果所有文本框均为 null，则查询将返回tblReject_test表中的所有值。
另外，请注意SQL注入。
不要把你的ifs放在那种级联的方式。尝试这样的事情：
string Select = "SELECT * FROM tblReject_test";
string Department = txtDepartment.Text;
string Start_Date = txtStart.Text;
string End_Date = txtEnd.Text;
string Anatomy = txtAnatomy.Text;
string RFR = cmbRFR.Text;
string Comment = txtComment.Text;
List<string> parameters = new List<string>();
bool first = true;
if (Department != "")
parameters.Add("department_id =" + "'" + Department + "'");
if (Anatomy != "")
parameters.Add("body_part_examined =" + "'" + Anatomy + "'");
if (Start_Date != "")
parameters.Add("study_date =" + "'" + Start_Date + "'");
if (End_Date != "")
parameters.Add("study_date =" + "'" + End_Date + "'");
if (RFR != "")
parameters.Add("reject_category =" + "'" + RFR + "'");
if (Comment != "")
parameters.Add("reject_comment =" + "'" + Comment + "'");
if (parameters.Count == 0)
Select = "SELECT * FROM tblReject_test";
for (int i = 0; i < parameters.Count(); i++)
{
if(i == 0)
Select += " WHERE ";
else
Select += " AND ";
Select += parameters[i];
}