asp.net|server|架构|数据存储过程:
/**********************************
功能:根据一定条件读取功能记录
作者:Rexsp
创建日期:2004-01-13
修改者:
修改日期:
**********************************/
ALTER PROCEDURE GetScoreSetting
(
ScoreSettingID INT=-1, ---设置ID
FunctionID INT=-1, ---功能ID
OperationID INT=-1, ---操作ID
RoleTypeID INT=-1, ---角色类型
BBSTypeID INT=-1, ---版块类型
Score INT=-1, ---积分设置
BB INT=-1, ---币币设置
BuyType INT=-1, ---购买类型 0:不是购买类型 1:一次性购买 2:反复购买
FunctionState INT=-1 ---功能状态
)
AS
SET NOCOUNT ON
DECLARE strSQL NVARCHAR(1000)
SET strSQL = 'SELECT * FROM [ScoreSetting] WHERE ckScoreSettingID = ckScoreSettingID'
--- Add KeyWords Begin ---
IF ScoreSettingID<> -1
BEGIN
SET strSQL = strSQL + ' AND ScoreSettingID= ckScoreSettingID'
END
IF FunctionID<> -1
BEGIN
SET strSQL = strSQL + ' AND FunctionID= ckFunctionID'
END
IF OperationID<>-1
BEGIN
SET strSQL = strSQL + ' AND OperationID = ckOperationID'
END
IF RoleTypeID<>-1
BEGIN
SET strSQL = strSQL + ' AND RoleTypeID = ckRoleTypeID'
END
IF BBSTypeID<>-1
BEGIN
SET strSQL = strSQL + ' AND BBSTypeID = ckBBSTypeID'
END
IF Score<>-1
BEGIN
SET strSQL = strSQL + ' AND Score = ckScore'
END
IF BB<>-1
BEGIN
SET strSQL = strSQL + ' AND BB= ckBB'
END
IF BuyType<>-1
BEGIN
SET strSQL = strSQL + ' AND BuyType= ckBuyType'
END
IF FunctionState<>-1
BEGIN
SET strSQL = strSQL + ' AND FunctionState= ckFunctionState'
END
--- Add Where Key Word ---
--- Run SQL Begin ---
EXECUTE sp_executesql strSQL,
N' ckScoreSettingID INT,
ckFunctionID INT,
ckOperationID INT,
ckRoleTypeID INT,
ckBBSTypeID INT,
ckScore INT,
ckBB INT,
ckBuyType INT,
ckFunctionState INT',
ckScoreSettingID=ScoreSettingID,
ckFunctionID=FunctionID,
ckOperationID = OperationID,
ckRoleTypeID = RoleTypeID,
ckBBSTypeID = BBSTypeID,
ckScore = Score,
ckBB = BB,
ckBuyType = BuyType,
ckFunctionState = FunctionState
--- Run SQL End ---
一点说明:
此存储过程会根据数据层的类有没有传递相应的参数值进来而动态创建查询语句,然后用系统自带的存储过程执行sql语句,用系统存储过程执行sql语句的好处是可以自动转义字符。而动态创建查询语句的好处,就非常大了,这会省下我们写很多种条件判断,尤其是对那些字段比较多的表来讲,一个排列组合下来的情况太多了,而利用存储过程动态创建sql语句所作的判断数和字段数基本上是一致的,这里会给入参赋初始值,如果不等于初始值就说明数据层类有传递参数进来,这样就加上相应条件字符的查询条件。
Asp.Net(C#)+Sql Server三层架构下数据存取方案(二)
80酷酷网 80kuku.com