SEARCH对SQL数据库表的操作 [英] SEARCH Operation on SQL database tables
问题描述
亲爱的所有人，
我使用ASP.NET的Gridview控件来绑定和访问sql数据库表。我尝试使用以下代码实现搜索记录操作：
Dear All,
I am using Gridview control of ASP.NET to bind and access sql database table. I tried to implement "Search" records operation using the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace PIMS
{
public partial class Default : Page
{
public SqlConnection con;
public string constr;
public void connection()
{
constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
con = new SqlConnection(constr);
con.Open();
}
protected void Page_Load(object sender, EventArgs e)
{
Label1.Visible = false;
}
private void rep_bind()
{
using (SqlConnection con = new SqlConnection("Data Source=MEHDI-PC\\SQLEXPRESS;Initial Catalog=PIMS;Integrated Security=True"))
{
using (SqlCommand cmd = new SqlCommand())
{
String sql = "SELECT DocumentsID, Ref,Subject,Src,Dst,Medium,Date_Printed,Date_Received,Document_Type,Action_Required,Due_Date,Actual_Date,Content,Tag,Issue_No,Attachment,Notes,Assigned_To,Reply_Ref,Priority,Status,Response FROM dbo.Documents";
cmd.Connection = con;
cmd.CommandText = sql;
//cmd.CommandType = CommandType.StoredProcedure;
con.Open();
//dataset object to get all select statement results
DataSet ds = new DataSet();
//sql dataadoptor to fill dataset
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
//here all select statements are fill in dataset object
adp.Fill(ds);
}
this.GridView1.DataSource = ds.Tables[0]; // first select statement result
this.GridView1.DataBind();
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
connection();
string query = "select Ref from dbo.Documents where Ref like'" + TextBox1.Text + "%'";
SqlCommand com = new SqlCommand(query, con);
SqlDataReader dr;
dr = com.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
rep_bind();
GridView1.Visible = true;
TextBox1.Text = "";
Label1.Text = "";
}
else
{
GridView1.Visible = false;
Label1.Visible = true;
Label1.Text = "The search Term " + TextBox1.Text + " Is Not Available in the Records"; ;
}
}
}
}
但它现在正在桌面上执行任何搜索操作。它都没有抛出任何错误。
请帮忙。
But its now performing any search operations on the table. Neither it is throwing any errors.
Please help.
推荐答案
测试在Sql server上运行的查询查询命令提示符并检查是否有是否有其他可用的结果？
Test your query running on Sql server Query command prompt and check is There any Other Result available or not?
select Ref from dbo.Documents where Ref like 'Your searchText%';
检查应用程序中提供的CoonectionString。
尝试使用异常处理机制来测试代码。
尝试查找错误的实际位置并发布所有步骤结果。
Check CoonectionString provided in your application.
Try to Use Exception Handling mechanism to test your code.
try to find actual place of errors and post all the steps result.
您好，
您的代码是可能正如你所写的那样工作。快速浏览一下，我可以看到你的Button1_Click事件的一个基本缺陷（我假设你的搜索功能是什么？）。
你已经把这部分代码：
Hi there,
Your code is probably working exactly as you have written it. At a quick glance I can see a basic flaw in your "Button1_Click" event (which I have assumed is your search function?).
You have put this section of code in:
if (dr.HasRows)
{
dr.Read();
rep_bind();
GridView1.Visible = true;
TextBox1.Text = "";
Label1.Text = "";
}
这意味着如果搜索结果有行，那么它只调用标准的rep_bind方法。 />
此rep_bind方法不会根据您的搜索条件进行任何过滤，因此我怀疑您看到该列表已填充但未过滤。
我建议使用filter参数定义rep_bind方法，该参数将附加到SQL查询的末尾。然后在你的过滤搜索中，你可以传递过滤代码。
这不是最好的方法，因为这意味着你最终会多次查询数据库。
另一种方法是重新编写代码，以便即使在搜索时，您只查询数据一次，以执行搜索并返回结果。
此外，我建议您查看以下一些链接，因为您当前的搜索查询容易受到SQL注入攻击。
在VB.NET / C＃中使用SQLParameters a> [ ^ ]
http://csharp-station.com/Tutorial/AdoDotNet/Lesson06 [ ^ ]
< a href =http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/> http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/ [ ^ ]
Which, will mean that if the search result has rows then it just calls the standard rep_bind method.
This rep_bind method does not do any filtering based on your search criteria, hence I suspect you are seeing that the list populates but does not filter.
I would suggest having the rep_bind method defined with a "filter" parameter that gets appended to the end of your SQL query. Then in your filter search, you could pass the "filter" code in.
This is not the best method, because it means you end up querying the database multiple times.
An alternative would be to re-work the code so that even when you are "searching" you only query the data one to both perform the search and to return the results.
Also, I suggest you look at some of the following links as your current search query is susceptible to SQL Injection attacks.
Using SQLParameters with VB.NET/C#[^]
http://csharp-station.com/Tutorial/AdoDotNet/Lesson06[^]
http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/[^]
此解决方案是对解决方案2的评论的回应。
好的，所以这部分代码永远不会起作用：
This solution is in response to the comment on Solution 2.
Okay, so this section of code is never going to work:
bool flag = false;
if (!txtRef0.Text.Equals(""))
{
if (flag == false)
{
sql = sql + " where Ref =N'" + txtRef0.Text + "'";
flag = true;
}
else
{
sql = sql + " and Ref =N'" + txtRef0.Text + "'";
}
原因是每次方法结束时你都会失去标志的范围。因此，每次触发它都会失败。
此外，您似乎想要创建一个结合多个选项的搜索方法，在这种情况下，我会建议查找SQL的IN方法。
当您执行代码时，在执行它生成的SQL语句之前，请使用以下工具visual studio复制语句，然后尝试在SSMS中运行，看看会发生什么。
The reason for this is that you are loosing the scope of the flag each time the method ends. So every time it gets triggered it will start false.
Also, it seems like you are tying to create a search method that combines multiple options, in which case I would suggest to look up the "IN" method for SQL.
When you step through your code, before you execute the SQL statement that it has produced, use the tools in visual studio to copy the statement and then try running in SSMS and see what happens.
这篇关于SEARCH对SQL数据库表的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
