存储过程动态条件查询

存储过程实现动态查询（即查询的条件的个数是不确定的）的方法：

首先建一个Demo表，用于测试，

 

创建存储过程（该技术的核心地方）：

 

ALTER  procedure usp_selectDemo 
@DemoId varchar( 50 )  =   null ,
@DemoName varchar( 50 )  =   null ,
@ListPrice  decimal   =   null ,
@Quantity  int   =   null ,
@LastUpdatedDate datetime  =   null ,
@LastUpdatedDateBegin datetime  =   null ,
@LastUpdatedDateEnd datetime  =   null

as

-- select  *  from demo
-- usp_selectDemo  ' 1 '

/**/ /**/ /**/ /* Powered by taeheelive@hotmail.com 
declare @sql varchar(500)
set @sql = ' select DemoId, DemoName, ListPrice, Quantity, LastUpdatedDate from Demo where 1=1'

if @DemoId is not null
begin set @sql = @sql + ' AND DemoId = '''+@DemoId+''''  end

if @DemoName is not null
begin set @sql = @sql + ' AND DemoName = '''+@DemoName+'''' end

if @ListPrice is not null
begin set @sql = @sql + ' AND ListPrice = '+convert(varchar(10),@ListPrice)+'' end

if @Quantity is not null
begin set @sql = @sql + ' AND Quantity = '+convert(varchar(10),@Quantity)+'' end

if @LastUpdatedDate is not null
begin set @sql = @sql + ' AND LastUpdatedDate = '''+convert(varchar(10),@LastUpdatedDate,120)++''' ' end

if @LastUpdatedDateBegin is not null
begin set @sql = @sql + ' AND LastUpdatedDate >= '''+convert(varchar(10),@LastUpdatedDateBegin,120)++''' ' end

if @LastUpdatedDateEnd is not null
begin set @sql = @sql + ' AND LastUpdatedDate < '''+convert(varchar(10),@LastUpdatedDateEnd,120)+''' ' end

--print (@sql)
exec (@sql)
*/

/**/ /**/ /**/ /* Powered by 江千帆(cnblogs.com) */
SELECT  *  FROM Demo
  where   1 = 1  
and (@DemoId  is   null  or DemoId  =  @DemoId)
and (@DemoName  is   null  or DemoName  =  @DemoName)
and (@ListPrice  is   null  or ListPrice  =  @ListPrice)
and (@Quantity  is   null  or Quantity  =  @Quantity)
and (@LastUpdatedDate  is   null  or LastUpdatedDate  =  @LastUpdatedDate)
and (@LastUpdatedDateBegin  is   null  or LastUpdatedDate  >=  @LastUpdatedDateBegin)
and (@LastUpdatedDateEnd  is   null  or LastUpdatedDate  <  @LastUpdatedDateEnd)


RETURN

 

测试页面aspx的代码如下：

 

<% @ Page Language = " C# "  AutoEventWireup = " true "  CodeFile = " test.aspx.cs "  Inherits = " admin_car_test "   %>

<! DOCTYPE html PUBLIC  " -//W3C//DTD XHTML 1.0 Transitional//EN "   " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >

< html xmlns = " http://www.w3.org/1999/xhtml "   >
< head runat = " server " >
     < title > 无标题页 </ title >
</ head >
< body style = " font-size:12px; " >
     < form id = " form1 "  runat = " server " >
     < div >
        id: < asp:TextBox ID = " id "  runat = " server "  Width = " 100px " ></ asp:TextBox >
         < br  />
        名称： < asp:TextBox ID = " name "  runat = " server "  Width = " 94px " ></ asp:TextBox >
         < br  />
        价格： < asp:TextBox ID = " price "  runat = " server "  Width = " 93px " ></ asp:TextBox >
         < br  />
        数量： < asp:TextBox ID = " quantity "  runat = " server "  Width = " 103px " ></ asp:TextBox >
         < br  />
        时间： < asp:TextBox ID = " time "  runat = " server "  Width = " 96px " ></ asp:TextBox >
         < br  />
        开始： < asp:TextBox ID = " begin "  runat = " server "  Width = " 109px " ></ asp:TextBox >< br  />
        结束： < asp:TextBox ID = " end "  runat = " server "  Width = " 111px " ></ asp:TextBox >< br  />
         < asp:Button ID = " btn "  runat = " server "  Text = " 提交 "  OnClick = " btn_Click "   />< br  />
         < asp:DataGrid ID = " demo "  runat = " server "  AutoGenerateColumns = " False "  Height = " 156px "  Width = " 370px " >
             < Columns >
                 < asp:BoundColumn DataField = " DemoId "  HeaderText = " DemoId " ></ asp:BoundColumn >
                 < asp:BoundColumn DataField = " DemoName "  HeaderText = " DemoName " ></ asp:BoundColumn >
                 < asp:BoundColumn DataField = " ListPrice "  HeaderText = " ListPrice " ></ asp:BoundColumn >
                 < asp:BoundColumn DataField = " Quantity "  HeaderText = " Quantity " ></ asp:BoundColumn >
                 < asp:BoundColumn DataField = " LastUpdatedDate "  HeaderText = " LastUpdatedDate " ></ asp:BoundColumn >
             </ Columns >
        
         </ asp:DataGrid >
     </ div >
     </ form >
</ body >
</ html >

 

cs代码如下：

 

using  System;
using  System.Data;
using  System.Data.SqlClient;
using  System.Configuration;
using  System.Collections;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;

public   partial   class  admin_car_test : System.Web.UI.Page
... {
    protected void Page_Load(object sender, EventArgs e)
    ...{

    }
    protected void btn_Click(object sender, EventArgs e)
    ...{
        SqlConnection oConn = op_db.DbConn();
        oConn.Open();
        SqlCommand oCmd = new SqlCommand("usp_selectDemo", oConn);
        oCmd.CommandType = CommandType.StoredProcedure;
        if (id.Text.Trim() == "" || id.Text.Trim() == null)
        ...{
            oCmd.Parameters.Add("@DemoId", SqlDbType.VarChar, 50).Value = null;
        }
        else
        ...{
            oCmd.Parameters.Add("@DemoId", SqlDbType.VarChar, 50).Value = id.Text.Trim();
        }
        if (name.Text.Trim() == "" || name.Text.Trim() == null)
        ...{
            oCmd.Parameters.Add("@DemoName", SqlDbType.VarChar, 100).Value = null;
        }
        else
        ...{
            oCmd.Parameters.Add("@DemoName", SqlDbType.VarChar, 100).Value = name.Text.Trim();
        }
        if (price.Text.Trim() == "" || price.Text.Trim() == null)
        ...{
            oCmd.Parameters.Add("@ListPrice", SqlDbType.Decimal).Value = null;
        }
        else
        ...{
            oCmd.Parameters.Add("@ListPrice", SqlDbType.Decimal).Value = price.Text.Trim();
        }
        if (quantity.Text.Trim() == "" || quantity.Text.Trim() == null)
        ...{
            oCmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = null;
        }
        else
        ...{
            oCmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = quantity.Text.Trim();
        }
        if (time.Text.Trim() == "" || time.Text.Trim() == null)
        ...{
            oCmd.Parameters.Add("@LastUpdatedDate", SqlDbType.DateTime).Value = null;
        }
        else
        ...{
            oCmd.Parameters.Add("@LastUpdatedDate", SqlDbType.DateTime).Value = time.Text.Trim();
        }
        if (begin.Text.Trim() == "" || begin.Text.Trim() == null)
        ...{
            oCmd.Parameters.Add("@LastUpdatedDateBegin", SqlDbType.DateTime).Value = null;
        }
        else
        ...{
            oCmd.Parameters.Add("@LastUpdatedDateBegin", SqlDbType.DateTime).Value = begin.Text.Trim();
        }
        if (end.Text.Trim() == "" || end.Text.Trim() == null)
        ...{
            oCmd.Parameters.Add("@LastUpdatedDateEnd", SqlDbType.DateTime).Value = null;
        }
        else
        ...{
            oCmd.Parameters.Add("@LastUpdatedDateEnd", SqlDbType.DateTime).Value = end.Text.Trim();
        }
        SqlDataAdapter oAda = new SqlDataAdapter();
        oAda.SelectCommand = oCmd;
        DataSet oDs = new DataSet();
        oAda.Fill(oDs,"Demo");
        demo.DataSource = oDs;
        demo.DataBind();
    }
}