SQL Server之3：全文搜索(3)
本篇文章主要介绍一下用 SQL Server 做的全文搜索的实际应用，前期的准备工作请参见 SQL Server 2008 R2 全文搜索(1) ，这里有详细的讲解。其中调用存储过程的方法使用 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)
// -——————————————————————————————————————-
//
// 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.
//
// -——————————————————————————————————————-
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
///
/// No Metadata Documentation available.
///
public partial class DBFullTextEntities : ObjectContext
{
#region Constructors
///
/// Initializes a new DBFullTextEntities object using the connection string found in the ‘DBFullTextEntities’ section of the application configuration file.
///
public DBFullTextEntities() : base ( “ name=DBFullTextEntities “ , “ DBFullTextEntities “ )
{
this .ContextOptions.LazyLoadingEnabled = true ;
OnContextCreated();
}
///
/// Initialize a new DBFullTextEntities object.
///
public DBFullTextEntities( string connectionString) : base (connectionString, “ DBFullTextEntities “ )
{
this .ContextOptions.LazyLoadingEnabled = true ;
OnContextCreated();
}
///
/// Initialize a new DBFullTextEntities object.
///
public DBFullTextEntities(EntityConnection connection) : base (connection, “ DBFullTextEntities “ )
{
this .ContextOptions.LazyLoadingEnabled = true ;
OnContextCreated();
}
#endregion
#region Partial Methods
partial void OnContextCreated();
#endregion
#region Function Imports
///
/// No Metadata Documentation available.
///
/// No Metadata Documentation available.
/// No Metadata Documentation available.
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
///
/// No Metadata Documentation available.
///
[EdmComplexTypeAttribute(NamespaceName = “ DBFullTextModel “ , Name = “ GetStudentResult “ )]
[DataContractAttribute(IsReference = true )]
[Serializable()]
public partial class GetStudentResult : ComplexObject
{
#region Factory Method
///
/// Create a new GetStudentResult object.
///
/// Initial value of the familyAddress property.
/// Initial value of the schoolAddress property.
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
///
/// No Metadata Documentation available.
///
[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();
///
/// No Metadata Documentation available.
///
[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();
///
/// No Metadata Documentation available.
///
[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 全文搜索的实例介绍到这里，欢迎留下你的宝贵意见，谢谢！
转载于
//www.cnblogs.com/alvinyue/archive/2011/07/04/2097445.html
今天药忘吃喽~
港控/mmm°
超、凢脫俗
红太狼
柔光的暖阳◎
还没有评论，来说两句吧...