cs_LinkCategory_UpdateSortOrder ALTERprocedure[dbo].cs_LinkCategory_UpdateSortOrder ( @LinkCategoryIDint, @SettingsIDint, @MoveUpbit ) AS SETTransactionIsolationLevelReadUNCOMMITTED /**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁, 也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更 改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有 语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/ BEGIN set nocount on /**//*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。 当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。*/ DECLARE@currentSortValueint DECLARE@replaceSortValueint DECLARE@replaceLinkCategoryIDint DECLARE@SectionIDint -- Get the current sort order -- 获取当前排序类型 SELECT@currentSortValue= SortOrder, @SectionID= SectionID FROM cs_LinkCategories WHERE LinkCategoryID =@LinkCategoryIDand SettingsID =@SettingsID -- Move the item up or down? IF (@MoveUp=1) BEGIN SELECT@replaceSortValue=coalesce(f.SortOrder, -1), @replaceLinkCategoryID=coalesce(f.LinkCategoryID, -1) FROM cs_LinkCategories f innerjoin ( selecttop1* from cs_LinkCategories WHERE SectionID =@SectionIDand SortOrder <@currentSortValueand SettingsID =@SettingsIDorderby SortOrder DESC ) as pf on pf.LinkCategoryID = f.LinkCategoryID and f.SettingsID =@SettingsID if( @replaceSortValue!=-1And@replaceSortValueisNotNull ) begin UPDATE cs_LinkCategories SET SortOrder =@currentSortValueWHERE LinkCategoryID =@replaceLinkCategoryIDand SettingsID =@SettingsID UPDATE cs_LinkCategories SET SortOrder =@replaceSortValueWHERE LinkCategoryID =@LinkCategoryIDand SettingsID =@SettingsID END END ELSE BEGIN SELECT@replaceSortValue=coalesce(f.SortOrder, -1), @replaceLinkCategoryID=coalesce(f.LinkCategoryID, -1) FROM cs_LinkCategories f innerjoin ( selecttop1* FROM cs_LinkCategories WHERE SectionID =@SectionIDand SortOrder >@currentSortValueand SettingsID =@SettingsIDorderby SortOrder ASC ) as pf on pf.LinkCategoryID = f.LinkCategoryID and f.SettingsID =@SettingsID if( @replaceSortValue!=-1And@replaceSortValueisNotNull ) begin UPDATE cs_LinkCategories SET SortOrder =@currentSortValueWHERE LinkCategoryID =@replaceLinkCategoryIDand SettingsID =@SettingsID UPDATE cs_LinkCategories SET SortOrder =@replaceSortValueWHERE LinkCategoryID =@LinkCategoryIDand SettingsID =@SettingsID end END END
cs_LinkCategory_CreateUpdateDelete ALTERPROCEDURE dbo.cs_LinkCategory_CreateUpdateDelete @DeleteLinkCategorybit=0, @SectionIDint, @Namenvarchar(256)='', @Descriptionnvarchar(2000)=null, @IsEnabledbit=1, @SortOrderint=0, @SettingsIDint, @LinkCategoryIDint=0 out AS SETTransactionIsolationLevelReadUNCOMMITTED -- Are we deleting? -- 判断删除 if@DeleteLinkCategory=1 begin DELETEFROM cs_Links WHERE LinkCategoryID =@LinkCategoryIDand SettingsID =@SettingsID DELETEFROM cs_LinkCategories WHERE LinkCategoryID =@LinkCategoryIDand SectionID =@SectionIDand SettingsID =@SettingsID RETURN end -- Are we updating? -- 判断更新 if@LinkCategoryID>0 begin UPDATE cs_LinkCategories SET [Name]=@Name, [Description]=@Description, IsEnabled =@IsEnabled, SortOrder =@SortOrder WHERE LinkCategoryID =@LinkCategoryIDand SectionID =@SectionIDand SettingsID =@SettingsID end else begin-- 下面是添加 If( @SortOrder=0 ) Begin Select@SortOrder=max(SortOrder) +1from cs_LinkCategories where SectionID =@SectionIDand SettingsID =@SettingsID if(@SortOrderisnull) Select@SortOrder=0 End INSERTINTO cs_LinkCategories (SectionID, [Name], [Description], IsEnabled, SortOrder, SettingsID) VALUES (@SectionID, @Name, @Description, @IsEnabled, @SortOrder, @SettingsID) set@LinkCategoryID=@@IDENTITY end
cs_LinkCategories_Get ALTERPROCEDURE dbo.cs_LinkCategories_Get ( @SectionIDint, @SettingsIDint, @PreLoadLinksbit ) AS SETTransactionIsolationLevelReadUNCOMMITTED /**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁, 也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更 改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有 语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/ -- Categories 查询种类的信息 SELECT[LinkCategoryID], [SectionID], [Name], [Description], [IsEnabled], [SortOrder], [SettingsID] FROM cs_LinkCategories WHERE SectionID =@SectionIDand SettingsID =@SettingsID OrderBy SortOrder -- Do we need to return links as well? -- 是否也要返回连接? IF (@PreLoadLinks=1) BEGIN SELECT[LinkID], l.[LinkCategoryID], [Title], [Url], l.[IsEnabled], l.[SortOrder], l.[SettingsID], [Rel], l.[Description], [DateCreated] FROM cs_Links l innerjoin cs_LinkCategories c on l.LinkCategoryID = c.LinkCategoryID WHERE c.SectionID =@SectionIDand c.SettingsID =@SettingsID OrderBy c.SortOrder, l.SortOrder END