How to realize simple search by c#.net+SQL2005
Today, I will talk to you about how to achieve simple search on c#.net+SQL2005. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.
Search function
USE [DATABASE] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE function [dbo]. [search] (@ Word nvarchar (max), @ Phrase nvarchar (max)) returns smallintasbeginif @ Word is null or @ Phrase is null return 0DECLARE @ BiggerWord VARCHAR (max) SELECT @ BiggerWord = @ Word +'x' DECLARE @ BiggerPhrase VARCHAR (max) SELECT @ BiggerPhrase = REPLACE (@ Phrase, @ Word, @ BiggerWord) RETURN LEN (@ BiggerPhrase)-LEN (@ Phrase) END
Use the above functions to create stored procedures and provide query conditions for three parameters
USE [DATABASE] 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
Pass parameters to the stage
Protected void Button1_Click (object sender, EventArgs e) {string MyConn = WebConfigurationManager.ConnectionStrings ["Conn"] .ConnectionString; 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 ()
}
After reading the above, do you have any further understanding of how c#.net+SQL2005 implements simple search? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
