最近写一个模糊综合多个字段的数据表查询，又要保持gridview的现有的功能。如添加，删除，更新，排序。为了简单少些代码，发现如果文本框不填的化，它一般会返回null 或空值，这样就可以使用 like ‘% @name %' 实现模糊查询，也可实现多个字段的查询。

数据库表：

CREATE TABLE [dbo].[tq_xianhuo](
 [supplyuint] [nvarchar](250) NULL,
 [telephone] [nvarchar](50) NULL,
 [name] [nvarchar](255) NULL,
 [quality] [nvarchar](255) NULL,
 [specific] [nvarchar](255) NULL,
 [sum] [nvarchar](255) NULL,
 [place] [nvarchar](255) NULL,
 [factory] [nvarchar](255) NULL,
 [data] [datetime] NULL,
 [beizhu] [nvarchar](250) NULL,
 [id] [int] NOT NULL,
 [dp_id] [int] NULL,
 CONSTRAINT [PK_tq_xianhuo] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

存储过程：

ALTER PROCEDURE dbo.xian_list
  @supplyuint nvarchar(100),
  @name nvarchar(50),
  @quality nvarchar(50),
  @specific nvarchar(50),
  @place nvarchar(50),
  @factory nvarchar(50)
 
AS

if @supplyuint is null
    set @supplyuint=''
   
 if @name is null
    set @name=''
if @quality is null
    set @quality=''
   
if @specific is null
    set @specific=''   
   
   
if @place is null
    set @place=''
   
if @factory is null
    set @factory=''
   
 
  select * from tq_xianhuo
  where
    supplyuint like '%'+@supplyuint+'%' and
    [name] like '%'+@name+'%' and
    quality like '%'+@quality+'%' and
    specific like '%'+@specific+'%' and
     place like '%'+@place+'%' and
     factory like '%'+@factory+'%'
 
 
 RETURN
default.aspx 显示页面
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:tq_fenleiConnectionString %>"
        SelectCommand="xian_list" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:ControlParameter ControlID="TextBox1" ConvertEmptyStringToNull="False"
                Name="supplyuint" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="TextBox2" ConvertEmptyStringToNull="False"
                DefaultValue="" Name="name" PropertyName="Text" />
            <asp:ControlParameter ControlID="TextBox3" ConvertEmptyStringToNull="False"
                Name="quality" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="TextBox4" ConvertEmptyStringToNull="False"
                Name="specific" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="TextBox5" ConvertEmptyStringToNull="False"
                Name="place" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="TextBox6" ConvertEmptyStringToNull="False"
                Name="factory" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
    
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
     <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
     <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
     <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
     <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
     <asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="id" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="data" HeaderText="data" SortExpression="data" />
            <asp:BoundField DataField="beizhu" HeaderText="beizhu"
                SortExpression="beizhu" />
            <asp:BoundField DataField="id" HeaderText="id" ReadOnly="True"
                SortExpression="id" />
            <asp:BoundField DataField="dp_id" HeaderText="dp_id" SortExpression="dp_id" />
        </Columns>
    </asp:GridView>