ASP.NET中利用存储过程实现模糊查询

建表脚本

USE [TestDB]

GO

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblCustomer](

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

[name] [nvarchar](100) NULL,

[dat] [date] NULL

) ON [PRIMARY]

GO

SearchCustomer.sql

CREATE PROCEDURE SearchCustomer

-- Add the parameters for the stored procedure here

@name nvarchar(100)

AS

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

GO

模糊搜索代码

代码如下:

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

{

cn.Open();

string str = "关键字";

//str = null;

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

cmd.CommandType = CommandType.StoredProcedure;

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd);

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

da.Fill(dt);

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

GridView1.DataSource=dt;

GridView1.Bind();

cn.Close();

}

更多信息请查看IT技术专栏