【原创源码】(02)：通用查询存储过程，可应用于多表关联的动态条件查询。

【原创源码】(02)：通用查询存储过程，可应用于多表关联的动态条件查询。

欢迎大家发表意见（漏洞，性能等）。在博客园社区以外转载，请注明作者和出处。谢谢！

1，创建数据表：

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[Demo] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ Demo ]
GO

CREATE   TABLE   [ dbo ] . [ Demo ]  (
  [ DemoId ]   [ varchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NOT   NULL  ,
  [ DemoName ]   [ varchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
  [ ListPrice ]   [ decimal ] ( 18 ,  0 )  NULL  ,
  [ Quantity ]   [ int ]   NULL  ,
  [ LastUpdatedDate ]   [ datetime ]   NULL  
)  ON   [ PRIMARY ]
GO

ALTER   TABLE   [ dbo ] . [ Demo ]   WITH   NOCHECK   ADD  
  CONSTRAINT   [ PK_ApplePie ]   PRIMARY   KEY    CLUSTERED  
 (
   [ DemoId ]
 )   ON   [ PRIMARY ]  
GO

2，创建存储过程：

CREATE           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  DemoId, DemoName, ListPrice, Quantity, LastUpdatedDate  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 )


GO

3，查询方法：

查询所有记录：usp_selectDemo
查询DemoId='1'的记录：usp_selectDemo '1'
查询DemoName='maxdemo'的记录：usp_selectDemo null,'maxdemo'
等等。

4，Sorry~! 临时修改了5处Bug
change '+@ListPrice+''   to   mailto:'+@ListPrice+''
change mailto:'+@ListPrice+''   to   mailto:'+@ListPrice+''
change '''+@LastUpdatedDate+'''   to   mailto:'+@ListPrice+''
change '''+@LastUpdatedDateBegin+'''   to   mailto:'+@ListPrice+''
change '''+@LastUpdatedDateEnd+'''   to   mailto:'+@ListPrice+''
汗~~,类型转换写的好差，不知哪位高手愿意补完美。谢谢！
ps：为什么上面的字都是当成蓝色的email了，不晓得怎么去掉蓝色和底线，郁闷中。
2006-10-18

5，个人感觉是比较好的一个思路，特大胆放到首页，希望大家讨论哈，优缺点。谢谢！
灵感来自于master数据库中的存储过程“sp_tables”
2006-10-19

6，添加一段数据访问层的应用代码SQLServerDAL，PetShop4.0结构

  1 /**//// <summary>
  2        /// Query for Demo s by condition.        
  3        /// </summary>
  4        /// <remarks>
  5        /// 查询多条记录（IList范型存储）。
  6        /// </remarks>
  7        /// <param name="demoModel">A Demo model.</param>
  8        /// <returns>Interface to model Demo collection generic by condition.</returns>
  9        public IList<DemoModel> QueryCollection(DemoModel demoModel)
 10        {
 11            IList<DemoModel> DemoModelCollection = new List<DemoModel>();
 12
 13            try
 14            {
 15                StringBuilder strSql = new StringBuilder();
 16                strSql.Append("usp_selectDemo");
 17
 18                SqlParameter[] parameters = new SqlParameter[7];
 19
 20                parameters[0] = new SqlParameter("@DemoId", SqlDbType.VarChar, 50);
 21                if (demoModel.DemoId != null && demoModel.DemoId.Length > 0)
 22                    parameters[0].Value = demoModel.DemoId;
 23                else
 24                    parameters[0].Value = null;
 25
 26                parameters[1] = new SqlParameter("@DemoName", SqlDbType.VarChar, 50);
 27                if (demoModel.DemoName != null && demoModel.DemoName.Length > 0)
 28                    parameters[1].Value = demoModel.DemoName;
 29                else
 30                    parameters[1].Value = null;
 31
 32                parameters[2] = new SqlParameter("@ListPrice", SqlDbType.Decimal, 8);
 33                if (demoModel.ListPrice != 0)
 34                    parameters[2].Value = demoModel.ListPrice;
 35                else
 36                    parameters[2].Value = null;
 37
 38                parameters[3] = new SqlParameter("@Quantity", SqlDbType.Int, 4);
 39                if (demoModel.Quantity != 0)
 40                    parameters[3].Value = demoModel.Quantity;
 41                else
 42                    parameters[3].Value = null;
 43
 44                parameters[4] = new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime, 8);
 45                if (demoModel.LastUpdatedDate != DateTime.Parse("1900-1-1 0:00:00"))
 46                    parameters[4].Value = demoModel.LastUpdatedDate;
 47                else
 48                    parameters[4].Value = null;
 49
 50                parameters[5] = new SqlParameter("@LastUpdatedDateBegin", SqlDbType.DateTime, 8);
 51                if (demoModel.LastUpdatedDateBegin != DateTime.Parse("1900-1-1 0:00:00"))/**//*[t]*/
 52                    parameters[5].Value = demoModel.LastUpdatedDateBegin;
 53                else
 54                    parameters[5].Value = null;
 55
 56                parameters[6] = new SqlParameter("@LastUpdatedDateEnd", SqlDbType.DateTime, 8);
 57                if (demoModel.LastUpdatedDateEnd != DateTime.Parse("1900-1-1 0:00:00"))/**//*[t]*/
 58                    parameters[6].Value = demoModel.LastUpdatedDateEnd;
 59                else
 60                    parameters[6].Value = null;
 61
 62
 63
 64                //foreach (DataRow dr in SqlHelper.Query(strSql.ToString()).Tables[0].Rows)
 65                //{
 66                //    DemoModel demo = new DemoModel(dr[0].ToString(), dr[1].ToString(), Convert.ToDecimal(dr[2]), Int32.Parse(dr[3].ToString()), DateTime.Parse(dr[4].ToString()));
 67                //    DemoModelCollection.Add(demo);
 68                //}
 69
 70                //Execute a query to read the products
 71                using (SqlDataReader rdr = SqlHelper.ExecuteReader(strSql.ToString(), parameters))
 72                {
 73                    while (rdr.Read())
 74                    {
 75                        DemoModel demo = new DemoModel();
 76
 77                        if (!rdr.IsDBNull(0))
 78                            demo.DemoId = rdr.GetString(0);
 79
 80                        if (!rdr.IsDBNull(1))
 81                            demo.DemoName = rdr.GetString(1);
 82
 83                        if (!rdr.IsDBNull(2))
 84                            demo.ListPrice = rdr.GetDecimal(2);
 85
 86                        if (!rdr.IsDBNull(3))
 87                            demo.Quantity = rdr.GetInt32(3);
 88
 89                        if (!rdr.IsDBNull(4))
 90                            demo.LastUpdatedDate = rdr.GetDateTime(4);
 91
 92                        //DemoModel demo = new DemoModel(rdr.GetString(0), rdr.GetString(1), rdr.GetDecimal(2), rdr.GetInt32(3), rdr.GetDateTime(4));
 93                        DemoModelCollection.Add(demo);
 94                    }
 95                }
 96
 97                return DemoModelCollection;
 98            }
 99            catch (Exception ex)
100            {
101                throw new Exception(ex.Message, ex);
102            }
103        }

2006-10-19

7，存储过程添加了/* Powered by 江千帆(cnblogs.com) */的方法（默认方法），
因为感觉比我的要优秀,感谢江千帆的意见！2006-10-19

 
注意！在博客园社区以外转载，必须注明：
作者：Clark Chan
和原文出处：http://clarkchan.cnblogs.com/
否则谢绝转载！