[轉貼] 利用存儲過程實現模糊查詢

出處：http://www.oschina.net/code/snippet_222150_32766
 

1. [代碼]建表腳本     

01	USE [TestDB]

02

GO

03

04	/****** Object:  Table [dbo].[tblCustomer]    Script Date: 01/18/2014 22:01:53 ******/

05	SET ANSI_NULLS ON

06

GO

07

08	SET QUOTED_IDENTIFIER ON

09

GO

10

11	CREATE TABLE [dbo].[tblCustomer](

12	[id] [int] IDENTITY(1,1) NOT NULL,

13	[name] [nvarchar](100) NULL,

14	[dat] [date] NULL

15	) ON [PRIMARY]

16

17

GO

2. [文件] SearchCustomer.sql ~ 182B     下載(1)     

1	CREATE PROCEDURE SearchCustomer

2	-- Add the parameters for the stored procedure here

3	@name nvarchar(100)

4

5

AS

6	SELECT * FROM dbo.tblCustomer WHERE name LIKE '%'+@name+'%'

7

GO

3. [代碼]模糊搜索代碼     跳至 [1] [2] [3] [全屏預覽]

view source

print?

01	using (SqlConnection cn = newSqlConnection("Server=localhost;Database=TestDB;Trusted_Connection=True;"))

02

{

03	cn.Open();

04	string str = "關鍵字";

05	//str = null;

06	SqlCommand cmd = new SqlCommand("SearchCustomer", cn);

07	cmd.CommandType = CommandType.StoredProcedure;

08	DataTable dt = new DataTable();

09	SqlDataAdapter da = new SqlDataAdapter(cmd);

10	da.SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = str;

11	da.Fill(dt);

12	Debug.Assert(dt.Rows.Count > 0);

13	GridView1.DataSource=dt;

14	GridView1.Bind();

15	cn.Close();

16

}