本篇文章主要介绍一下用 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) //------------------------------------------------------------------------------
//
// 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; _disibledevent=>///
/// Initialize a new DBFullTextEntities object. /// public DBFullTextEntities(string connectionString) : base(connectionString, "DBFullTextEntities") { this.ContextOptions.LazyLoadingEnabled = true; _disibledevent=>///
/// Initialize a new DBFullTextEntities object. /// public DBFullTextEntities(EntityConnection connection) : base(connection, "DBFullTextEntities") { this.ContextOptions.LazyLoadingEnabled = true; _disibledevent=>#endregion #region Partial Methods partial void _disibledevent=>#endregion #region Function Imports ///
/// No Metadata Documentation available. /// public ObjectResult
GetStuddentAll() { return base.ExecuteFunction("GetStuddentAll"); } /// /// No Metadata Documentation available. /// /// No Metadata Documentation available.
/// No Metadata Documentation available.
public ObjectResult 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("GetStudentLst", fAddressParameter, sAddressParameter); } #endregion } #endregion #region ComplexTypes /// /// No Metadata Documentation available. /// [EdmComplexTypeAttribute(NamespaceName="DBFullTextModel", Name="GetStuddentAll_Result")] [DataContractAttribute(IsReference=true)] [Serializable()] public partial class GetStuddentAll_Result : ComplexObject { #region Factory Method /// /// Create a new GetStuddentAll_Result object. /// /// Initial value of the id property.
/// Initial value of the familyAddress property.
/// Initial value of the schoolAddress property.
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 /// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)] [DataMemberAttribute()] public global::System.Int32 id { get { return _id; } set { _disibledevent=>"id"); _id = StructuralObject.SetValidValue(value); ReportPropertyChanged("id"); _disibledevent=>private global::System.Int32 _id; partial void _disibledevent=>global::System.Int32 value); partial void _disibledevent=>/// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)] [DataMemberAttribute()] public global::System.String name { get { return _name; } set { _disibledevent=>"name"); _name = StructuralObject.SetValidValue(value, true); ReportPropertyChanged("name"); _disibledevent=>private global::System.String _name; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>/// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)] [DataMemberAttribute()] public global::System.String familyAddress { get { return _familyAddress; } set { _disibledevent=>"familyAddress"); _familyAddress = StructuralObject.SetValidValue(value, false); ReportPropertyChanged("familyAddress"); _disibledevent=>private global::System.String _familyAddress; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>/// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)] [DataMemberAttribute()] public global::System.String schoolAddress { get { return _schoolAddress; } set { _disibledevent=>"schoolAddress"); _schoolAddress = StructuralObject.SetValidValue(value, false); ReportPropertyChanged("schoolAddress"); _disibledevent=>private global::System.String _schoolAddress; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>#endregion } /// /// No Metadata Documentation available. /// [EdmComplexTypeAttribute(NamespaceName="DBFullTextModel", Name="GetStuddentResult")] [DataContractAttribute(IsReference=true)] [Serializable()] public partial class GetStuddentResult : ComplexObject { #region Factory Method /// /// Create a new GetStuddentResult object. /// /// Initial value of the familyAddress property.
/// Initial value of the schoolAddress property.
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 /// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)] [DataMemberAttribute()] public global::System.String name { get { return _name; } set { _disibledevent=>"name"); _name = StructuralObject.SetValidValue(value, true); ReportPropertyChanged("name"); _disibledevent=>private global::System.String _name; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>/// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)] [DataMemberAttribute()] public global::System.String familyAddress { get { return _familyAddress; } set { _disibledevent=>"familyAddress"); _familyAddress = StructuralObject.SetValidValue(value, false); ReportPropertyChanged("familyAddress"); _disibledevent=>private global::System.String _familyAddress; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>/// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)] [DataMemberAttribute()] public global::System.String schoolAddress { get { return _schoolAddress; } set { _disibledevent=>"schoolAddress"); _schoolAddress = StructuralObject.SetValidValue(value, false); ReportPropertyChanged("schoolAddress"); _disibledevent=>private global::System.String _schoolAddress; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>#endregion } /// /// 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 { _disibledevent=>"name"); _name = StructuralObject.SetValidValue(value, true); ReportPropertyChanged("name"); _disibledevent=>private global::System.String _name; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>/// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)] [DataMemberAttribute()] public global::System.String familyAddress { get { return _familyAddress; } set { _disibledevent=>"familyAddress"); _familyAddress = StructuralObject.SetValidValue(value, false); ReportPropertyChanged("familyAddress"); _disibledevent=>private global::System.String _familyAddress; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>/// /// No Metadata Documentation available. /// [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)] [DataMemberAttribute()] public global::System.String schoolAddress { get { return _schoolAddress; } set { _disibledevent=>"schoolAddress"); _schoolAddress = StructuralObject.SetValidValue(value, false); ReportPropertyChanged("schoolAddress"); _disibledevent=>private global::System.String _schoolAddress; partial void _disibledevent=>global::System.String value); partial void _disibledevent=>#endregion } #endregion }
第三步：做好刚才两步后，就可以在页面上直接应用了
页面代码
Default.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplicationFullText.Default" %>
Demo of Full Text Search
页面的后台代码
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 students = new List(); 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 全文搜索的实例介绍到这里，欢迎留下你的宝贵意见，谢谢！
延伸阅读
最新评论