自动完成搜索列表问题
Autocomplete Search List issue
我目前正在开发一个web应用程序，将有一个自动完成搜索表单，结果正在从Sql数据库更新。整个"功能"都在工作，除了一件事。客户端正在调用一个c# Webservice，它将返回一个数组给客户端。问题是。客户端的列表多次显示相同的结果。如果数据库中只有一个匹配客户端会在垂直列表中显示20-25次匹配。我不知道如何解决这个问题，请帮助我。
c# Webservice:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
namespace WebApplication6
{
public class searchResult
{
public string Title;
public string img;
public string href;
}
/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
[System.Web.Script.Services.GenerateScriptType(typeof(searchResult))]
public class WebService : System.Web.Services.WebService
{
[WebMethod]
public string HelloWorld()
{
return "Hello World";
}
[WebMethod]
public searchResult[] Search(string txtSearch)
{
//Semuler to slow internet connection
//System.Threading.Thread.Sleep(2000);
//Declare collection of searchResult
List<searchResult> resultList = new List<searchResult>();
string constr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; ;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM [DriverInfo], [Teams], [Tracks] WHERE UserName LIKE '" + txtSearch + "%' OR TeamName LIKE '" + txtSearch + "%' OR TrackName LIKE '" + txtSearch + "%'";
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
searchResult DriverResult = new searchResult();
DriverResult.Title = dr["UserName"].ToString();
DriverResult.img = "driver.png";
DriverResult.href = dr["UserId"].ToString();
if (DriverResult.Title.ToLower().Contains(txtSearch.ToLower()))
{
resultList.Add(DriverResult);
}
searchResult TeamResult = new searchResult();
TeamResult.Title = dr["TeamName"].ToString();
TeamResult.img = "team.png";
TeamResult.href = dr["Id"].ToString();
if (TeamResult.Title.ToLower().Contains(txtSearch.ToLower()))
{
resultList.Add(TeamResult);
}
searchResult TrackResult = new searchResult();
TrackResult.Title = dr["TrackName"].ToString();
TrackResult.img = "track.png";
TrackResult.href = dr["TrackId"].ToString();
if (TrackResult.Title.ToLower().Contains(txtSearch.ToLower()))
{
resultList.Add(TrackResult);
}
}
con.Close();
return resultList.ToArray();
}
catch
{
return null;
}
}
}
}
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script src="http://code.jquery.com/jquery-1.4.3.min.js" type="text/javascript></script>
<link href="main.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
function search() {
if ($("#txtSearch").val() != "") {
$(".divResult").show(); //show div block that contains on result
$(".loading").show(); // show loading text while getting result
//call web searvice
$.ajax({ type: "POST",
url: "WebService.asmx/Search", //function that in web service
data: "{txtSearch:'" + $("#txtSearch").val() + "'}",// passing value of txtSearch input
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function(response) {
//declaer client object and set to it returned result from web sevice function
var result = response.d;
$(".record").html(""); // clear previous result
//looping in 'result' array to get data and fill it inside ".record" div as html
$.each(result, function(index, res) {
//append img tag inside ".record" div
$('<img />', {
src: 'Images/' + res.img,
alt: res.Title
}).addClass("img").appendTo('.record');
//append anchor tag inside ".record" div
$('<a></a>', {
href: res.href,
text: res.Title
}).addClass("txtResult").appendTo('.record');
$(".record").append("<hr />");
});
//hide loading div when the data was got
$(".loading").hide();
},
error: function(msg) {
$(".record").html(msg.d);
}
});
}
else {
$(".divResult").hide(); //hide div that contains result when the input text is empty
$(".record").html(""); //also loading text when the input text is empty
}
}
</script>
</head>
<body>
<div class="content">
<input id="txtSearch" onkeyup="search()" type="text" />
<div class="divResult">
<div class="loading">Loading..</div>
<div class="record"></div>
</div>
</div>
</body>
</html>
.content
{
margin:50px auto;
text-align:center;
width:322px;
}
#txtSearch
{
border:solid 1px #cccccc;
width:320px;
color:#555555;
font: 18pt tahoma;
height: 20px;
font-size: 12px;
font-family: "lucida grande",tahoma,verdana,arial,sans-serif;
}
.divResult
{
position:absolute;
background-color:#F2F2FF;
border-style:solid;
border-width:1px;
border-color:#999999;
width:320px;
text-align:left;
display:none;
}
.img
{
padding-top: 2px;
width:30px;
height:30px;
float:left;
}
.txtResult
{
display:block;
width:320px;
height:30px;
color:#3c5899;
font-family: "lucida grande",tahoma,verdana,arial,sans-serif;
font-size: 14px;
font-weight: bold;
text-decoration:none;
}
.txtResult:hover {
background-color: #3c5899;
color: White;
}
.loading
{
font: 10pt tahoma;
text-align:center;
}
.record
{
margin:0px;
}
不推荐您在SQL查询中执行的连接类型，这可能是导致重复结果的原因。我建议这样写:
select 'Driver' as Type, UserId as Id, UserName as Name
from DriverInfo
where UserName like '%foo%'
union
select 'Team' as Type, Id, TeamName
from Teams
where TeamName like '%foo%'
union
select 'Track' as Type, TrackId, TrackName
from Tracks
where TrackName like '%foo%'
结果如下:
<>之前类型Id名称---- -- ----司机23 Foo73号司机小福第27队Foo队64队足球队曲目98 Bar Foo Field之前下面是如何使用这些结果的:
while (dr.Read())
{
resultList.Add(
new searchResult {
Title = dr["Name"].ToString(),
img = dr["Type"].ToString() + ".png",
href = dr["UserId"].ToString() } );
}
事实上，你可能会得到这样的结果(笛卡尔积)，其中结果的数量不是A + B + C，而是A * B * C:
另外，请注意，由于使用了select *，您将获得比需要的更多的列。
:
你说你不确定如何将变量添加到查询中。您应该使用参数化查询，而不是字符串连接，以使您的代码更干净，并防止SQL注入攻击:
cmd.CommandText = "... where UserName like @SearchPattern ... where TeamName like @SearchPattern ...";
cmd.Parameters.Add("@SearchPattern", txtSearch + "%");
这是笛卡尔积查询:SELECT * FROM [DriverInfo], [Teams], [Tracks] WHERE UserName LIKE '" + txtSearch + "%' OR TeamName LIKE '" + txtSearch + "%' OR TrackName LIKE '" + txtSearch + "%'"
(在网上搜索cartesian product join以获得更多信息)
我猜你要么想连接这些表，要么想将它们合并。从你的例子来看，我认为这是一个联盟。这样做的好处是让db服务器完成更多的工作，并且减少了网络流量。
的例子:
command.CommandText = "SELECT UserName FROM [DriverInfo]"
+ "WHERE UserName LIKE '" + txtSearch + "%'"
+ "UNION SELECT TeamName FROM [Teams]"
+ "WHERE TeamName LIKE '" + txtSearch + "%'"
+ "UNION SELECT TrackName FROM [Tracks]"
+ "WHERE TrackName LIKE '" + txtSearch + "%'"
但是您确实必须使用参数而不是内联连接txtSearch值。否则，您很容易受到SQL注入攻击。(在网上搜索SQL injection attack获取更多信息)
相关文章：
- jQuery，引导程序下拉列表问题
- JQuery刷新列表效果-淡入淡出问题
- jQuery拖动到相对位置的可排序列表中时出现可拖动辅助对象位置问题
- jQuery切换列表菜单问题
- 当前用户顶部列表的曲目快照出现间歇性问题
- 为了在列表中搜索，角度过滤器有问题
- JQuery Mobile自定义下拉列表问题
- 引导下拉列表问题.(下拉列表和下拉列表保持下拉列表）
- 下拉列表问题在选择第一个后丢失值“0”
- 移动触摸上的引导下拉列表问题
- 填充数据列表问题中的数据
- 自动完成搜索列表问题
- 掉落列表问题
- 聚合物下拉列表问题
- jQuery克隆元素，然后用连接的结果填充隐藏字段，以避免空列表问题
- 春季自动填充列表问题
- 美味的JSON列表问题
- Powershell web自动化-下拉列表问题
- 淘汰赛.js iPhone上的问题 - 下拉列表问题
- 下拉列表问题
