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模糊查询

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();
}