c#-+SQL2005如何实现简单搜索

今天就跟大家聊聊有关c#-+SQL2005如何实现简单搜索，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

搜索函数

USE [DATABASE]<br/>GO<br/>SET ANSI_NULLS ON<br/>GO<br/>SET QUOTED_IDENTIFIER ON<br/>GO<br/>CREATE function [dbo].[search]<br/>(@Word nvarchar(max),<br/>@Phrase nvarchar(max))<br/>returns smallint<br/>as<br/>begin<br/>if @Word is null or @Phrase is null return 0<br/>DECLARE @BiggerWord VARCHAR(max)<br/> SELECT @BiggerWord = @Word + x<br/> DECLARE @BiggerPhrase VARCHAR(max)<br/> SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord)<br/> RETURN LEN(@BiggerPhrase) - LEN(@Phrase)<br/>END

利用以上函数创建存储过程,提供3个参数查询条件

 USE [DATABASE]<br/>GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATR PROCEDURE [dbo].[SearchDog]

(

@Word1 nVARCHAR(max)=null,

@Word2 nVARCHAR(max)=null,

@Word3 nVARCHAR(max)=null)

AS

DECLARE @Dog TABLE

(

DogHead nvarchar(max),

DogBody nVARCHAR(max),

Rank INT)

INSERT INTO @Dog

SELECT DogHead, DogBody,

3 * dbo.search(@Word1, DogHead) + dbo.search(@Word1, DogBody) +

3 * dbo.search(@Word2, DogHead) + dbo.search(@Word2, DogBody) +

3 * dbo.search(@Word3, DogHead) + dbo.search(@Word3, DogBody)

AS Rank

FROM Dog ORDER BY Rank DESC

SELECT DogHead, DogBody, Rank FROM @Dog

WHERE Rank > 0

ORDER BY Rank DESC

后台文件传入参数

protected void Button1_Click(object sender, EventArgs e)<br/>{<br/>string MyConn = WebConfigurationManager.ConnectionStrings["Conn"].ConnectionString;<br/>SqlConnection Myconnection = new SqlConnection(MyConn);

SqlCommand cmd = new SqlCommand("SearchDog", Myconnection);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Word1", SqlDbType.NVarChar, 50);

cmd.Parameters.Add("@Word2", SqlDbType.NVarChar, 50);

cmd.Parameters.Add("@Word3", SqlDbType.NVarChar, 50);

cmd.Parameters["@Word1"].Value = TextBox1.Text;

cmd.Parameters["@Word2"].Value = TextBox2.Text;

cmd.Parameters["@Word3"].Value = TextBox3.Text;

cmd.Connection.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

Adapter.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

cmd.Connection.Close();

}

看完上述内容，你们对c#-+SQL2005如何实现简单搜索有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注行业资讯频道，感谢大家的支持。