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)

// ------------------------------------------------------------------------------
// <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

}

 

 

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

 

页面代码

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 全文搜索的实例介绍到这里，欢迎留下你的宝贵意见，谢谢！

 

 

 

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