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技术专栏
