C# IDnaDataReader.Close方法代码示例

 void GetRiskModTestSiteAndForum(IDnaDataReader reader, out int siteid, out int forumid)
 {
     reader.ExecuteWithinATransaction(@"  select top 1 g.forumid,g.siteid 
                                         from topics t
                                         join guideentries g on g.h2g2id=t.h2g2id
                                         order by topicid");
     reader.Read();
     forumid = reader.GetInt32("forumid");
     siteid = reader.GetInt32("siteid");
     reader.Close();
 }

 int CountEventQueueEntries(IDnaDataReader reader)
 {
     reader.ExecuteWithinATransaction("select count(*) as C from eventqueue");
     reader.Read();
     int c = reader.GetInt32("C");
     reader.Close();
     return c;
 }

 void ClearRiskModThreadEntryQueue(IDnaDataReader reader)
 {
     reader.ExecuteWithinATransaction("delete from RiskModThreadEntryQueue");
     reader.Close();
 }

//.........这里部分代码省略.........
EXEC SetItemTypeValInternal 'IT_URL', @URLType OUTPUT

-- Now get all the values for the different events that can happen
DECLARE @ArticleEdit int, @ArticleTagged int, @TaggedArticleEdited int, @ForumEdit int, @NewTeamMember int, @PostRepliedTo int, @NewThread int, @ThreadTagged int
DECLARE @UserTagged int, @ClubTagged int, @LinkAdded int, @VoteAdded int, @VoteRemoved int, @OwnerTeamChange int, @MemberTeamChange int, @MemberApplication int, @ClubEdit int, @NodeHidden int
EXEC SetEventTypeValInternal 'ET_ARTICLEEDITED', @ArticleEdit OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYARTICLETAGGED', @ArticleTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYARTICLEEDITED', @TaggedArticleEdited OUTPUT
EXEC SetEventTypeValInternal 'ET_FORUMEDITED', @ForumEdit OUTPUT
EXEC SetEventTypeValInternal 'ET_NEWTEAMMEMBER', @NewTeamMember OUTPUT
EXEC SetEventTypeValInternal 'ET_POSTREPLIEDTO', @PostRepliedTo OUTPUT
EXEC SetEventTypeValInternal 'ET_POSTNEWTHREAD', @NewThread OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYTHREADTAGGED', @ThreadTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYUSERTAGGED', @UserTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYCLUBTAGGED', @ClubTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_NEWLINKADDED', @LinkAdded OUTPUT
EXEC SetEventTypeValInternal 'ET_VOTEADDED', @VoteAdded OUTPUT
EXEC SetEventTypeValInternal 'ET_VOTEREMOVED', @VoteRemoved OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBOWNERTEAMCHANGE', @OwnerTeamChange OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBMEMBERTEAMCHANGE', @MemberTeamChange OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBMEMBERAPPLICATIONCHANGE', @MemberApplication OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBEDITED', @ClubEdit OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYHIDDEN', @NodeHidden OUTPUT

declare @uid uniqueidentifier,@ItemID int,@ItemType int,@EventType int,@ItemID2 int,@ItemType2 int,@NotifyType int
set @uid=newid()

-- Set up the email alert lists
INSERT INTO [dbo].[EMailAlertList] ([EMailAlertListID],[UserID],[CreatedDate],[LastUpdated],[SiteID]) VALUES (@uid, 6, getdate(), getdate(),1)
INSERT INTO [dbo].InstantEMailAlertList (InstantEMailAlertListID,[UserID],[CreatedDate],[LastUpdated],[SiteID]) VALUES (@uid, 6, getdate(), getdate(),1)

-- Now insert a bunch of email event queue items to test the various branches

-- case 1 & 2
select top 1 @ItemID =h.nodeid,@ItemType [email protected],@EventType [email protected],@ItemID2 =0,@ItemType2 =0,@NotifyType =0
	from Hierarchy h
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)

-- case 3 & 4
-- can't be done because inner joins on threads where threadid=0

--case 5 & 6
select top 1 @ItemID =f.forumid,@ItemType [email protected],@EventType [email protected],@ItemID2 =t.threadid,@ItemType2 [email protected],@NotifyType =0
	from forums f
	join threads t on t.forumid=f.forumid
	where t.VisibleTo IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)

-- case 7 & 8
select top 1 @ItemID =t.threadid,@ItemType [email protected],@EventType [email protected],@ItemID2 =te.entryid,@ItemType2 [email protected],@NotifyType =0
	from forums f
	join threads t on t.forumid=f.forumid
	join threadentries te on te.threadid=t.threadid
	where t.VisibleTo IS NULL and te.hidden is null
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)

-- case 9 & 10
select top 1 @ItemID =h.nodeid,@ItemType [email protected],@EventType [email protected],@ItemID2 =t.threadid,@ItemType2 [email protected],@NotifyType =0
	from forums f
	join hierarchy h on nodeid=nodeid
	join threads t on t.forumid=f.forumid
	where t.VisibleTo IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)

-- case 11, 12, 13, 14, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28 - clubs, not in use so not tested

-- case 15 & 16
select top 1 @ItemID =h.nodeid,@ItemType [email protected],@EventType [email protected],@ItemID2 =g.h2g2id,@ItemType2 [email protected],@NotifyType =0
	from hierarchy h, guideentries g
	where g.hidden IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)

-- case 17 & 18
select top 1 @ItemID =g.h2g2id,@ItemType [email protected],@EventType [email protected],@ItemID2 =0,@ItemType2 =0,@NotifyType =0
	from guideentries g
	where g.hidden IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)

-- case 19 & 20
select top 1 @ItemID =g.h2g2id,@ItemType [email protected],@EventType [email protected],@ItemID2 =0,@ItemType2 =0,@NotifyType =0
	from guideentries g
	where g.hidden IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)

-- case 29 & 30
select top 1 @ItemID =h.nodeid,@ItemType =0,@EventType [email protected],@ItemID2 =h.nodeid,@ItemType2 =0,@NotifyType =0
	from hierarchy h
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
     VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)";

            reader.ExecuteWithinATransaction(sql);
            reader.Close();
        }

 void ClearEventQueue(IDnaDataReader reader)
 {
     reader.ExecuteWithinATransaction("delete from eventqueue");
     reader.Close();
 }

        string GetNextIndentityId(IDnaDataReader reader)
        {
            string sql = @"select IdentityUserID from signinuseridmapping 
                           where cast(identityuserid as bigint) = (select max(cast(identityuserid as bigint)) from signinuseridmapping)";
            reader.ExecuteWithinATransaction(sql);
            reader.Read();
            string identityId = reader.GetString("IdentityUserID");
            reader.Close();

            Int64 id = Int64.Parse(identityId);

            return (id+1).ToString();
        }

        void EmailEnc_SetUp_fetchrecommendationdetails(IDnaDataReader reader, out int recId, out int entryId)
        {
            // Find a Scout Recommendation that matches the SP's join requirements
            string sql = @"select top 1 RecommendationID, SR.EntryId" + NL +
                            "from ScoutRecommendations SR" + NL +
                            "inner join GuideEntries G on G.EntryID = SR.EntryID" + NL +
                            "inner join Users U1 on U1.UserID = SR.ScoutID" + NL +
                            "inner join Users U2 on U2.UserID = G.Editor" + NL +
                            "INNER JOIN Journals J1 on J1.UserID = U1.UserID and J1.SiteID = G.SiteID" + NL +
                            "INNER JOIN Journals J2 on J2.UserID = U2.UserID and J2.SiteID = G.SiteID";
            reader.ExecuteWithinATransaction(sql);
            reader.Read();
            recId = reader.GetInt32("RecommendationID");
            entryId = reader.GetInt32("EntryId");
            reader.Close();

            // Set up the scout and editor user ids to something predictable
            reader.ExecuteWithinATransaction("UPDATE ScoutRecommendations SET ScoutId=42 WHERE RecommendationID=" + recId);
            reader.ExecuteWithinATransaction("UPDATE GuideEntries SET Editor=6 WHERE EntryId=" + entryId);

            // Set up the email adresses in the user accounts
            UpdateUserEmailAddress(reader, 42, "[email protected]");
            UpdateUserEmailAddress(reader, 6, "[email protected]");

            reader.Close();
        }

 SqlBinary HashEmailAddress(IDnaDataReader reader,string email)
 {
     reader.ExecuteWithOpenKey("select dbo.udf_hashemailaddress('" + email + "') AS HashedEmail");
     reader.Read();
     var hashedEmail = reader.GetSqlBinary("HashedEmail");
     reader.Close();
     return hashedEmail;
 }

 void AddEmailToBannedList(IDnaDataReader reader, string email, int siginBanned, int complaintBanned)
 {
     string sql = @"EXEC addemailtobannedlist @email='" + email + "', @signinbanned =" + siginBanned + ", @complaintbanned =" + complaintBanned + ", @editorid = 6";
     reader.ExecuteWithinATransaction(sql);
     reader.Close();
 }

        GuideEntryInfo GetGuideEntryInfo(IDnaDataReader reader,int entryId)
        {
            string sql = @"select * from guideentries where entryid="+entryId;
            reader.ExecuteWithinATransaction(sql);
            reader.Read();
            var info = new GuideEntryInfo();
            info.EntryId = reader.GetInt32("entryid");
            info.Editor = reader.GetInt32("editor");
            reader.Close();

            return info;
        }

        void UpdateUserEmailAddress(IDnaDataReader reader, int userId, string email)
        {
            string sql;
            
            if (email != null)
                sql = string.Format(@"update users set encryptedemail=dbo.udf_encryptemailaddress('{0}',{1}) where userid={1}",email,userId);
            else
                sql = string.Format(@"update users set encryptedemail=dbo.udf_encryptemailaddress(NULL,{0}) where userid={0}",userId);

            reader.ExecuteWithOpenKey(sql);
            reader.Close();
        }

 int FindGuideEntry(IDnaDataReader reader)
 {
     string [email protected]"select top 1 * from guideentries where text like '<GUIDE%' and siteid=1";
     reader.ExecuteWithinATransaction(sql);
     reader.Read();
     int entryId = reader.GetInt32("entryid");
     reader.Close();
     return entryId;
 }

        int CreateNewUser(IDnaDataReader reader, string email)
        {
            string id = GetNextIndentityId(reader);

            string sql = string.Format(@"EXEC [dbo].[createnewuserfromidentityid]	@identityuserid ='{0}',"+NL+
										"@legacyssoid = null,"+NL+
										"@username = 'test',"+NL+
										"@email = '{1}'",id, email);

            reader.ExecuteWithinATransaction(sql);

            sql = "SELECT DnaUserId FROM SignInUserIdMapping WHERE IdentityUserId='" + id + "'";
            reader.ExecuteWithinATransaction(sql);
            reader.Read();
            int userId = reader.GetInt32("DnaUserId");
            reader.Close();
            return userId;
        }

 int GetNextUserId(IDnaDataReader reader)
 {
     reader.ExecuteWithinATransaction(@"select max(userid) AS maxuserid from users");
     reader.Read();
     int maxuserId = reader.GetInt32("maxuserid");
     reader.Close();
     return maxuserId + 1;
 }

        void RiskModTestPostHelper(IDnaDataReader reader, int forumid, int? threadid, int? inreplyto, int userid, string content, out int? newthreadid, out int? newthreadentryid, bool ignoremoderation, bool forcepremodposting)
        {
            string sql = string.Format(@"
                
                -- Pretend this user has never posted to this forum, to make sure the check in posttoforuminternal
                -- that stops the same user from creating a new conversation within a minute of the last one, doesn't stop the post
                update threadentries set forumid=-{0} where forumid={0} and userid={3}

                declare @hash uniqueidentifier, @returnthread int, @returnpost int, @premodpostingmodid int, @ispremoderated int
                set @hash=newid()
                exec posttoforuminternal    @userid= {3}, 
										    @forumid = {0}, 
										    @inreplyto = {2}, 
										    @threadid = {1}, 
										    @subject ='The Cabinet', 
										    @content ='{4}', 
										    @poststyle =1, 
										    @hash [email protected],
										    @keywords ='', 
										    @nickname ='the furry one', 
										    @returnthread = @returnthread OUTPUT, 
										    @returnpost = @returnpost OUTPUT, 
										    @type = NULL, 
										    @eventdate = NULL,
										    @forcemoderate = 0, 
										    @forcepremoderation = 0,
										    @ignoremoderation = {6}, 
										    @allowevententries = 1, 
										    @nodeid = 0, 
										    @ipaddress = 'testip',
										    @queueid = null, 
										    @clubid = 0, 
										    @premodpostingmodid [email protected] OUTPUT,
										    @ispremoderated [email protected] OUTPUT, 
										    @bbcuid = '{5}',
										    @isnotable = 0, 
										    @iscomment = 0,
										    @modnotes = NULL,
										    @isthreadedcomment = 0,
										    @ignoreriskmoderation = 0,
										    @forcepremodposting = {7},
										    @forcepremodpostingdate = NULL,
										    @riskmodthreadentryqueueid = NULL;

                -- put the userids back
                update threadentries set forumid={0} where forumid =-{0} and userid={3}

                select @returnthread AS returnthread, @returnpost as returnpost",
                                            forumid,
                                            threadid.HasValue ? threadid.ToString() : "NULL",
                                            inreplyto.HasValue ? inreplyto.ToString() : "NULL",
                                            userid,
                                            content,
                                            testGUID.ToString(),
                                            ignoremoderation ? 1 : 0,
                                            forcepremodposting ? 1 : 0);

            reader.ExecuteWithinATransaction(sql);
            reader.Read();
            newthreadid = reader.GetNullableInt32("returnthread");
            newthreadentryid = reader.GetNullableInt32("returnpost");
            reader.Close();
        }