SQL Server 2008 R2 全文搜索(3)
本篇文章主要介绍一下用 SQL Server 做的全文搜索的实际应用，其中调用存储过程的方法使用 Entity Framework,如果有对此不熟悉的朋友，可以参见.net 4.0 用Entity Framework调用存储过程 (转)，下面一步步介绍这个demo。
第一步：建立搜索存储过程
SP
ALTER procedure [dbo].[GetStudent]
@fAddress nvarchar(100),
@sAddress nvarchar(100)
as
set nocount off
begin
if @fAddress = '' and @sAddress <> ''
select [name],[familyAddress],[schoolAddress] from [DBFullText].[dbo].[Student]
where contains ([schoolAddress],@sAddress)
else if @fAddress <> '' and @sAddress = ''
select [name],[familyAddress],[schoolAddress] from [DBFullText].[dbo].[Student]
where contains ([familyAddress],@fAddress)
else if @fAddress <> '' and @sAddress <> ''
select [name],[familyAddress],[schoolAddress] from [DBFullText].[dbo].[Student]
where contains ([familyAddress],@fAddress)
and contains ([schoolAddress],@sAddress)
else
select [name],[familyAddress],[schoolAddress] from [DBFullText].[dbo].[Student]
end
第二步：使用 Entity Framework，添加 .edmx 文件并把刚才做好的存储过程引用到方法中
Entity Framework (DBFullTextEntities)
//------------------------------------------------------------------------------
// <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>
public partial class 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
partial void OnContextCreated();
#endregion
#region Function Imports
/// <summary>
/// No Metadata Documentation available.
/// </summary>
public ObjectResult<GetStudentResult> GetStuddentAll()
{
return base.ExecuteFunction<GetStudentResult>("GetStuddentAll");
}
/// <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));
}
return base.ExecuteFunction<GetStudentResult>("GetStudentLst", fAddressParameter, sAddressParameter);
}
#endregion
}
#endregion
#region ComplexTypes
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmComplexTypeAttribute(NamespaceName="DBFullTextModel", Name="GetStuddentAll_Result")]
[DataContractAttribute(IsReference=true)]
[Serializable()]
public partial class GetStuddentAll_Result : ComplexObject
{
#region Factory Method
/// <summary>
/// Create a new GetStuddentAll_Result object.
/// </summary>
/// <param name="id">Initial value of the id property.</param>
/// <param name="familyAddress">Initial value of the familyAddress property.</param>
/// <param name="schoolAddress">Initial value of the schoolAddress property.</param>
public static GetStuddentAll_Result CreateGetStuddentAll_Result(global::System.Int32 id, global::System.String familyAddress, global::System.String schoolAddress)
{
GetStuddentAll_Result getStuddentAll_Result = new GetStuddentAll_Result();
getStuddentAll_Result.id = id;
getStuddentAll_Result.familyAddress = familyAddress;
getStuddentAll_Result.schoolAddress = schoolAddress;
return getStuddentAll_Result;
}
#endregion
#region Primitive Properties
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Int32 id
{
get
{
return _id;
}
set
{
OnidChanging(value);
ReportPropertyChanging("id");
_id = StructuralObject.SetValidValue(value);
ReportPropertyChanged("id");
OnidChanged();
}
}
private global::System.Int32 _id;
partial void OnidChanging(global::System.Int32 value);
partial void OnidChanged();
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String name
{
get
{
return _name;
}
set
{
OnnameChanging(value);
ReportPropertyChanging("name");
_name = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("name");
OnnameChanged();
}
}
private global::System.String _name;
partial void OnnameChanging(global::System.String value);
partial void OnnameChanged();
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.String familyAddress
{
get
{
return _familyAddress;
}
set
{
OnfamilyAddressChanging(value);
ReportPropertyChanging("familyAddress");
_familyAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("familyAddress");
OnfamilyAddressChanged();
}
}
private global::System.String _familyAddress;
partial void OnfamilyAddressChanging(global::System.String value);
partial void OnfamilyAddressChanged();
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.String schoolAddress
{
get
{
return _schoolAddress;
}
set
{
OnschoolAddressChanging(value);
ReportPropertyChanging("schoolAddress");
_schoolAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("schoolAddress");
OnschoolAddressChanged();
}
}
private global::System.String _schoolAddress;
partial void OnschoolAddressChanging(global::System.String value);
partial void OnschoolAddressChanged();
#endregion
}
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmComplexTypeAttribute(NamespaceName="DBFullTextModel", Name="GetStuddentResult")]
[DataContractAttribute(IsReference=true)]
[Serializable()]
public partial class GetStuddentResult : ComplexObject
{
#region Factory Method
/// <summary>
/// Create a new GetStuddentResult object.
/// </summary>
/// <param name="familyAddress">Initial value of the familyAddress property.</param>
/// <param name="schoolAddress">Initial value of the schoolAddress property.</param>
public static GetStuddentResult CreateGetStuddentResult(global::System.String familyAddress, global::System.String schoolAddress)
{
GetStuddentResult getStuddentResult = new GetStuddentResult();
getStuddentResult.familyAddress = familyAddress;
getStuddentResult.schoolAddress = schoolAddress;
return getStuddentResult;
}
#endregion
#region Primitive Properties
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.String name
{
get
{
return _name;
}
set
{
OnnameChanging(value);
ReportPropertyChanging("name");
_name = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("name");
OnnameChanged();
}
}
private global::System.String _name;
partial void OnnameChanging(global::System.String value);
partial void OnnameChanged();
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.String familyAddress
{
get
{
return _familyAddress;
}
set
{
OnfamilyAddressChanging(value);
ReportPropertyChanging("familyAddress");
_familyAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("familyAddress");
OnfamilyAddressChanged();
}
}
private global::System.String _familyAddress;
partial void OnfamilyAddressChanging(global::System.String value);
partial void OnfamilyAddressChanged();
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.String schoolAddress
{
get
{
return _schoolAddress;
}
set
{
OnschoolAddressChanging(value);
ReportPropertyChanging("schoolAddress");
_schoolAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("schoolAddress");
OnschoolAddressChanged();
}
}
private global::System.String _schoolAddress;
partial void OnschoolAddressChanging(global::System.String value);
partial void OnschoolAddressChanged();
#endregion
}
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmComplexTypeAttribute(NamespaceName="DBFullTextModel", Name="GetStudentResult")]
[DataContractAttribute(IsReference=true)]
[Serializable()]
public partial class 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>
public static 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()]
public global::System.String name
{
get
{
return _name;
}
set
{
OnnameChanging(value);
ReportPropertyChanging("name");
_name = StructuralObject.SetValidValue(value, true);
ReportPropertyChanged("name");
OnnameChanged();
}
}
private global::System.String _name;
partial void OnnameChanging(global::System.String value);
partial void OnnameChanged();
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.String familyAddress
{
get
{
return _familyAddress;
}
set
{
OnfamilyAddressChanging(value);
ReportPropertyChanging("familyAddress");
_familyAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("familyAddress");
OnfamilyAddressChanged();
}
}
private global::System.String _familyAddress;
partial void OnfamilyAddressChanging(global::System.String value);
partial void OnfamilyAddressChanged();
/// <summary>
/// No Metadata Documentation available.
/// </summary>
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.String schoolAddress
{
get
{
return _schoolAddress;
}
set
{
OnschoolAddressChanging(value);
ReportPropertyChanging("schoolAddress");
_schoolAddress = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("schoolAddress");
OnschoolAddressChanged();
}
}
private global::System.String _schoolAddress;
partial void OnschoolAddressChanging(global::System.String value);
partial void OnschoolAddressChanged();
#endregion
}
#endregion
}
第三步：做好刚才两步后，就可以在页面上直接应用了
页面代码
Default.aspx
<%@ 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>
页面的后台代码
Default.aspx.cs
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
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.tblResult.Visible = false;
}
protected void 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 全文搜索的实例介绍到这里，欢迎留下你的宝贵意见，谢谢！
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架