SQL Server之3：全文搜索(3)

本篇文章主要介绍一下用 SQL Server 做的全文搜索的实际应用，前期的准备工作请参见 SQL Server 2008 R2 全文搜索(1) ，这里有详细的讲解。其中调用存储过程的方法使用 Entity Framework,如果有对此不熟悉的朋友，可以参见 .net 4.0 用Entity Framework调用存储过程 (转) ，下面一步步介绍这个demo。

 

第一步：建立搜索存储过程

 

ALTERprocedure[dbo].[GetStudent]
@fAddressnvarchar(100),
@sAddressnvarchar(100)
as 
set nocount off
begin
if@fAddress=''and@sAddress<>''
select[name],[familyAddress],[schoolAddress]from[DBFullText].[dbo].[Student]
wherecontains ([schoolAddress],@sAddress)
elseif@fAddress<>''and@sAddress=''
select[name],[familyAddress],[schoolAddress]from[DBFullText].[dbo].[Student]
wherecontains ([familyAddress],@fAddress)
elseif@fAddress<>''and@sAddress<>''
select[name],[familyAddress],[schoolAddress]from[DBFullText].[dbo].[Student]
wherecontains ([familyAddress],@fAddress)
andcontains ([schoolAddress],@sAddress)
else
select[name],[familyAddress],[schoolAddress]from[DBFullText].[dbo].[Student]
end

 

 

第二步：使用 Entity Framework，添加 .edmx 文件并把刚才做好的存储过程引用到方法中

 

//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.EntityClient;
using System.ComponentModel;
using System.Xml.Serialization;
using System.Runtime.Serialization;

[assembly: EdmSchemaAttribute()]

namespace WebApplicationFullText
{
#region Contexts

///<summary>
/// No Metadata Documentation available.
///</summary>
publicpartialclass DBFullTextEntities : ObjectContext
{
#region Constructors

///<summary>
/// Initializes a new DBFullTextEntities object using the connection string found in the 'DBFullTextEntities' section of the application configuration file.
///</summary>
public DBFullTextEntities() : base("name=DBFullTextEntities", "DBFullTextEntities")
{
this.ContextOptions.LazyLoadingEnabled =true;
OnContextCreated();
}

///<summary>
/// Initialize a new DBFullTextEntities object.
///</summary>
public DBFullTextEntities(string connectionString) : base(connectionString, "DBFullTextEntities")
{
this.ContextOptions.LazyLoadingEnabled =true;
OnContextCreated();
}

///<summary>
/// Initialize a new DBFullTextEntities object.
///</summary>
public DBFullTextEntities(EntityConnection connection) : base(connection, "DBFullTextEntities")
{
this.ContextOptions.LazyLoadingEnabled =true;
OnContextCreated();
}

#endregion

#region Partial Methods

partialvoid OnContextCreated();

#endregion

#region Function Imports

///<summary>
/// No Metadata Documentation available.
///</summary>
///<param name="fAddress">No Metadata Documentation available.</param>
///<param name="sAddress">No Metadata Documentation available.</param>
public ObjectResult<GetStudentResult> GetStudentLst(global::System.String fAddress, global::System.String sAddress)
{
ObjectParameter fAddressParameter;
if (fAddress !=null)
{
fAddressParameter =new ObjectParameter("fAddress", fAddress);
}
else
{
fAddressParameter =new ObjectParameter("fAddress", typeof(global::System.String));
}

ObjectParameter sAddressParameter;
if (sAddress !=null)
{
sAddressParameter =new ObjectParameter("sAddress", sAddress);
}
else
{
sAddressParameter =new ObjectParameter("sAddress", typeof(global::System.String));
}

returnbase.ExecuteFunction<GetStudentResult>("GetStudentLst", fAddressParameter, sAddressParameter);
}

#endregion
}


#endregion

#region ComplexTypes

///<summary>
/// No Metadata Documentation available.
///</summary>
[EdmComplexTypeAttribute(NamespaceName="DBFullTextModel", Name="GetStudentResult")]
[DataContractAttribute(IsReference=true)]
[Serializable()]
publicpartialclass GetStudentResult : ComplexObject
{
#region Factory Method

///<summary>
/// Create a new GetStudentResult object.
///</summary>
///<param name="familyAddress">Initial value of the familyAddress property.</param>
///<param name="schoolAddress">Initial value of the schoolAddress property.</param>
publicstatic GetStudentResult CreateGetStudentResult(global::System.String familyAddress, global::System.String schoolAddress)
{
GetStudentResult getStudentResult =new GetStudentResult();
getStudentResult.familyAddress = familyAddress;
getStudentResult.schoolAddress = schoolAddress;
return getStudentResult;
}

#endregion
#region Primitive Properties

///<summary>
/// No Metadata Documentation available.
///</summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
publicglobal::System.String name
{
get
{
return _name;
}
set
{
OnnameChanging(value);
ReportPropertyChanging("name");
_name = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("name");
OnnameChanged();
}
}
privateglobal::System.String _name;
partialvoid OnnameChanging(global::System.String value);
partialvoid OnnameChanged();

///<summary>
/// No Metadata Documentation available.
///</summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
publicglobal::System.String familyAddress
{
get
{
return _familyAddress;
}
set
{
OnfamilyAddressChanging(value);
ReportPropertyChanging("familyAddress");
_familyAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("familyAddress");
OnfamilyAddressChanged();
}
}
privateglobal::System.String _familyAddress;
partialvoid OnfamilyAddressChanging(global::System.String value);
partialvoid OnfamilyAddressChanged();

///<summary>
/// No Metadata Documentation available.
///</summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
publicglobal::System.String schoolAddress
{
get
{
return _schoolAddress;
}
set
{
OnschoolAddressChanging(value);
ReportPropertyChanging("schoolAddress");
_schoolAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("schoolAddress");
OnschoolAddressChanged();
}
}
privateglobal::System.String _schoolAddress;
partialvoid OnschoolAddressChanging(global::System.String value);
partialvoid OnschoolAddressChanged();

#endregion
}

#endregion

}

 

 

第三步：做好刚才两步后，就可以在页面上直接应用了

 

页面代码

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplicationFullText.Default"%>

<!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 runat="server">
<title>Demo of Full Text Search</title>
<style type="text/css">
.style1
{
width: 120px;
}
.style2
{
width: 200px;
}
.style4
{
width: 150px;
}
.style6
{
width: 350px;
}
.style7
{
width: 256px;
}
#btnSearch
{
width: 79px;
}
</style>
</head>
<body bgcolor="#99ccff">
<form id="form1" runat="server">
<div>
<div id="search">
<table>
<tr>
<td class="style1">
<div>
<label>
Family Address :</label></div>
</td>
<td class="style7">
<input type="text" runat="server" id="txtFAddress" style="width: 237px"/>
</td>
<td rowspan="2" class="style2">
<input type="button" runat="server" id="btnSearch" value="Search" onserverclick="Search"/>
</td>
</tr>
<tr>
<td class="style1">
<div>
<label>
School Address :</label></div>
</td>
<td class="style7">
<input type="text" runat="server" id="txtSAddress" style="width: 237px"/>
</td>
</tr>
</table>
</div>
<div id="result" style="margin-top:20px">
<table runat="server" id="tblResult" bgcolor="#6699FF" border="1">
<tr bgcolor="Aqua"><td class="style4">Name</td><td class="style6">Family Address</td>
<td class="style6">School Address</td></tr>
</table>
</div>
</div>
</form>
</body>
</html>

 

 

页面的后台代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Linq;
using System.Linq.Expressions;
using System.Data.Linq;
using System.Web.UI.HtmlControls;

namespace WebApplicationFullText 
{
publicpartialclass Default : System.Web.UI.Page
{
protectedvoid Page_Load(object sender, EventArgs e)
{
this.tblResult.Visible =false;
}

protectedvoid Search(object sender, EventArgs e)
{
List<GetStudentResult> students =new List<GetStudentResult>();
DBFullTextEntities entities =new DBFullTextEntities();

string fAddress=(string.IsNullOrEmpty(this.txtFAddress.Value))?"":this.txtFAddress.Value;
string sAddress = (string.IsNullOrEmpty(this.txtSAddress.Value)) ?"" : this.txtSAddress.Value;

students = entities.GetStudentLst(fAddress, sAddress).ToList();
if (students.Count >0)
{
this.tblResult.Visible =true;
}

foreach (GetStudentResult student in students)
{
HtmlTableCell cellName =new HtmlTableCell();
HtmlTableCell cellFAddress =new HtmlTableCell();
HtmlTableCell cellSAddress =new HtmlTableCell();

cellName.InnerText = student.name;
cellFAddress.InnerText = student.familyAddress;
cellSAddress.InnerText = student.schoolAddress;

HtmlTableRow row =new HtmlTableRow();
row.Cells.Add(cellName);
row.Cells.Add(cellFAddress);
row.Cells.Add(cellSAddress);

this.tblResult.Rows.Add(row);
}
}
}
}

 

 

做好后的运行效果如下：

 

 

好了，关于 SQL Server 全文搜索的实例介绍到这里，欢迎留下你的宝贵意见，谢谢！