c#.net+SQL2005如何實現簡單搜索

今天就跟大家聊聊有關c#.net+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#.net+SQL2005如何實現簡單搜索有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。