效率优化或思路优化?

[kkun_3yue3]

根据代码能看出我的需求来,
1,参数可变,可以传一个,可以传两个,也可以全部都传,更可以全部都不传
2,如果有主键则更新,没主键则不更新
3,有主键的情况下,传那个参数更新那个参数,不传不更新

然后,我的版本如下,求效率优化或思路优化

create procedure Msg_Message_Update(
    @ID Int = null,
    @MsgTitle NVarChar = null,
    @MsgContent NVarChar = null,
    @UserID Int = null,
    @OrderID Int = null,
    @InsertTime DateTime = null,
    @IsRead Bit = null,
    @MsgType Int = null,
    @MsgEx VarChar = null,
    @IsCancel Bit = null,
)
as 
begin
	if( nullif(@ID,0) is null ) return;
	UPDATE Msg_Message
	SET
    MsgTitle = ISNULL(@MsgTitle,MsgTitle),
    MsgContent = ISNULL(@MsgContent,MsgContent),
    UserID = ISNULL(@UserID,UserID),
    OrderID = ISNULL(@OrderID,OrderID),
    InsertTime = ISNULL(@InsertTime,InsertTime),
    IsRead = ISNULL(@IsRead,IsRead),
    MsgType = ISNULL(@MsgType,MsgType),
    MsgEx = ISNULL(@MsgEx,MsgEx),
    IsCancel = ISNULL(@IsCancel,IsCancel),
	WHERE ID = @ID
end

[ai_li7758521]

关注

[kkun_3yue3]

顶一顶

[让你望见影子的墙]

关注

[kkun_3yue3]

同样的,SELECT代码
这里新增了分页参数,同上一样,可传可不传

create procedure Msg_Msg_Message_Select(
    @ID Int = null,
    @MsgTitle NVarChar = null,
    @MsgContent NVarChar = null,
    @UserID Int = null,
    @OrderID Int = null,
    @InsertTime DateTime = null,
    @IsRead Bit = null,
    @MsgType Int = null,
    @MsgEx VarChar = null,
    @IsCancel Bit = null,

	@PageIndex	int = null,
	@PageSize	int = null,
	@totalRecords int = null output
)
as 
begin
/* 总记录数 */
	select @totalRecords = count(1)
		from Msg_Message
		where 1 = 1
    		and (@ID is null or ID = @ID)
    		and (@MsgTitle is null or MsgTitle = @MsgTitle)
    		and (@MsgContent is null or MsgContent = @MsgContent)
    		and (@UserID is null or UserID = @UserID)
    		and (@OrderID is null or OrderID = @OrderID)
    		and (@InsertTime is null or InsertTime = @InsertTime)
    		and (@IsRead is null or IsRead = @IsRead)
    		and (@MsgType is null or MsgType = @MsgType)
    		and (@MsgEx is null or MsgEx = @MsgEx)
    		and (@IsCancel is null or IsCancel = @IsCancel)

	if( @PageIndex > ( @totalRecords / isnull(nullif(@PageSize,0),1)) )
	begin
		set @PageIndex = @PageIndex - 1
	end;
	
	if( @PageIndex < 0 )
	begin
		set @PageIndex = 0
	end;
	
	
	/* 查询 + 分页 */
	with Temp_Msg_Message as (
		SELECT ID,MsgTitle,MsgContent,UserID,OrderID,INsertTime AS InsertTime,IsRead,MsgType,MsgEx,IsCancel FROM [Msg_Message] WHERE ID=@ID,
		row_number() over (order by id) as rownumber
		from [Msg_Message]
		where 1 = 1
    		and (@ID is null or ID = @ID)
    		and (@MsgTitle is null or MsgTitle = @MsgTitle)
    		and (@MsgContent is null or MsgContent = @MsgContent)
    		and (@UserID is null or UserID = @UserID)
    		and (@OrderID is null or OrderID = @OrderID)
    		and (@InsertTime is null or InsertTime = @InsertTime)
    		and (@IsRead is null or IsRead = @IsRead)
    		and (@MsgType is null or MsgType = @MsgType)
    		and (@MsgEx is null or MsgEx = @MsgEx)
    		and (@IsCancel is null or IsCancel = @IsCancel)
	)
	select * from Temp_Msg_Message 
	where 1 = 1
		and (
				(@PageIndex is null or NULLIF(@PageSize,0) is null )
			or
				rownumber between @PageIndex * @PageSize and (@PageIndex + 1 ) * @PageSize
			);
end

[JonasFeng]

稍微修改了下.
另外建议ID上加索引。

begin
    if(ISNULL(@ID,0) = 0) return 
    UPDATE Msg_Message
    SET
    MsgTitle = ISNULL(@MsgTitle,MsgTitle),
    MsgContent = ISNULL(@MsgContent,MsgContent),
    UserID = ISNULL(@UserID,UserID),
    OrderID = ISNULL(@OrderID,OrderID),
    InsertTime = ISNULL(@InsertTime,InsertTime),
    IsRead = ISNULL(@IsRead,IsRead),
    MsgType = ISNULL(@MsgType,MsgType),
    MsgEx = ISNULL(@MsgEx,MsgEx),
    IsCancel = ISNULL(@IsCancel,IsCancel),
    WHERE ID = @ID  
end

[kkun_3yue3]

这样真的没有问题吗?
难道说大家都在使用这种方式?
我想要的效果是
在代码里传实体类,如果是默认值或空值则不考虑之,传啥考虑啥,这样我的代码只写一个方法即可'
可以干掉N多类似下面的方法
SelectXXXXByID
SelectXXXXByName
SelectXXXXByEmail
SelectXXXXByIdentity
SelectXXXXBySex
SelectXXXXBy等等
改后只有一个方法
SelectXXXX(实体 e)

写是写出来了,也能运行,效率上还没细里考证,其它没考虑到的情况也没细里考量,所以发此贴,
想听听不同的意见,有什么问题,如何修正
下面是一个调用存储过程的方法

#region Update
        /// <summary>
        /// 更新操作
        /// </summary>
        /// <param name="e">ET_Order_MessageEntity实体</param>
        /// <returns>成功或失败</returns>
        public override bool Update( ET_Order_MessageEntity e ) {
            //构造SQL语句
            string sql = "Msg_Message_Update";

            //构造参数
            SqlParameter[] parameters = { 
				new SqlParameter("@ID",SqlDbType.Int),
				new SqlParameter("@MsgTitle",SqlDbType.NVarChar),
				new SqlParameter("@MsgContent",SqlDbType.NVarChar),
				new SqlParameter("@UserID",SqlDbType.Int),
				new SqlParameter("@OrderID",SqlDbType.Int),
				new SqlParameter("@InsertTime",SqlDbType.DateTime),
				new SqlParameter("@IsRead",SqlDbType.Bit),
				new SqlParameter("@MsgType",SqlDbType.Int),
				new SqlParameter("@MsgEx",SqlDbType.VarChar),
				new SqlParameter("@IsCancel",SqlDbType.Bit),
			};
			
			//为参数赋值
			int i = 0;
            parameters[ i++ ].Value = e.MsgTitle;
            parameters[ i++ ].Value = e.MsgContent;
            parameters[ i++ ].Value = e.UserID;
            parameters[ i++ ].Value = e.OrderID;
			parameters[ i++ ].Value = e.InsertTime == DateTime.MinValue ? (object)null : (object)e.InsertTime;;
            parameters[ i++ ].Value = e.IsRead;
            parameters[ i++ ].Value = e.MsgType;
            parameters[ i++ ].Value = e.MsgEx;
            parameters[ i++ ].Value = e.IsCancel;

            //执行SQL语句
            try {
                return db.ExecuteNonQuery( CommandType.StoredProcedure, sql, parameters ) > 0;
            } catch( Exception exp ) {
                throw exp;
            }
        }
        #endregion

[zenowolf]

不会，帮忙顶。。

[kkun_3yue3]

再顶...

[claro]

帮顶。

[xing020010]

不会。帮着顶

[十八道胡同]

顶下，不会。。