一代码：
public DataSet GetAllProduct(string productNameCode,byte productType,byte productState,
short organizationID, int size, int index, ref int? count)
{
//组装每件语句
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(" WHERE 1=1 ");
if (!string.Empty.Equals(productNameCode))
{
productNameCode = productNameCode.Replace("'", "''");
sbCondition.Append(" and ((AllProduct.ProductCode like '%" + productNameCode + "%') or ");
sbCondition.Append(" (AllProduct.ProductName like '%" + productNameCode + "%'))");
}
if (productType != 0)
{
sbCondition.Append(" and AllProduct.ProductCategory = ");
sbCondition.Append(productType);
}
if (productState != 0)
{
sbCondition.Append(" and AllProduct.ProductStatus = ");
sbCondition.Append(productState);
}
if (organizationID != -1)
{
sbCondition.Append(" and AllProduct.BelongTo = ");
sbCondition.Append(organizationID);
}
SqlParameter[] paras = new SqlParameter[4];
paras[0] = new SqlParameter("@strCondition", sbCondition.ToString());
paras[1] = new SqlParameter("@Size", size);
paras[2] = new SqlParameter("@index", index - 1);
paras[3] = new SqlParameter("@count", count);
paras[3].Direction = ParameterDirection.Output;
DataSet result = SqlHelper.ExecuteDataset(conSisManage, CommandType.StoredProcedure, "Proc_GetAllProduct", paras);
if (paras[3].Value != DBNull.Value)
{
count = (int)paras[3].Value;
}
return result;
}
二数据库：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Ylem
-- Create date: 2009-07-20
-- Create date: 2009-07-24
-- Description: 得到基本产品，附加产品，特殊产品组合列表
-- =============================================
ALTER PROCEDURE [dbo].[Proc_GetAllProduct]
@strCondition varchar(2000), --查询条件
@Size int,
@index varchar(10),
@count int out
AS
BEGIN
print @strCondition
Create Table #TempRecord (IndexID int /*IDENTITY (1, 1) NOT NULL primary key*/,
ProductID uniqueidentifier,
ProductCode varchar(20),
ProductName nvarchar(64),
ProductStatus smallint,
BelongTo smallint,
ProductCategory smallint,
CreateOn datetime
)
insert into #TempRecord exec(' SELECT IndexID = ROW_NUMBER() OVER(ORDER BY AllProduct.CreateOn desc), * from
(SELECT p.ProductID, p.ProductCode, p.ProductName,p.ProductStatus,p.BelongTo, 1 ProductCategory,p.CreatedDate as CreateOn
FROM Product p --基本产品
UNION ALL
SELECT p.ID ProductID, p.Code ProductCode, p.Name ProductName,-1 ProductStatus,-1 BelongTo, 2 ProductCategory,''1900-1-1'' CreateOn
FROM AttachedProduct p --附加产品
UNION ALL
SELECT p.ID ProductID, p.ProductCode, p.ProductName,-1 ProductStatus,-1 BelongTo, 3 ProductCategory,''1900-1-1'' CreateOn
FROM SpecialProduct p --特殊产品
) as AllProduct
' + @strCondition --+' order by AllProduct.CreateOn desc'
)
--返回统计数
select @count = max(IndexID) from #TempRecord
--返回数据
select * from #TempRecord tmp where IndexID > (@index*@Size) and IndexID<=((@index + 1) * @Size)
drop table #TempRecord
END
/*
declare @strCondition varchar(2000)
declare @count int
set @strCondition=' WHERE 1=1 --and ProductCategory=1-- and AllProduct.ProductStatus = 1 and AllProduct.BelongTo=1'
exec [Proc_GetAllProduct] @strCondition,25,0,@count output
*/