【原创源码】(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/
否则谢绝转载！