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

柔光的暖阳◎ 2021-12-03 03:47 260阅读 0赞

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

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

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 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 文件并把刚才做好的存储过程引用到方法中

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 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>   
 /// <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 = " 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   
  
\}

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

页面代码

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 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 >

页面的后台代码

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 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);  
\}  
\}  
\}  
\}

做好后的运行效果如下：

![2011070415232735.jpg][]

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

转载于:https://www.cnblogs.com/alvinyue/archive/2011/07/04/2097445.html

[SQL Server 2008 R2 _1]: http://www.cnblogs.com/alvinyue/archive/2011/06/21/2085972.html
[.net 4.0 _Entity Framework_]: http://www.cnblogs.com/alvinyue/archive/2011/07/04/2097421.html
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211203/5a5052920086465f9eecf8cf7399c391.png
[2011070415232735.jpg]: /images/20211203/58ebe8a34283427699b42c8d4dad4fef.png