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; }
// create procedure fetcharticlemoderationhistory @h2g2id int /* use smallguide begin tran select j1.forumid,u1.* FROM Users u1 INNER JOIN Journals J1 ON J1.UserID = u1.UserID and J1.SiteID = 1 --INNER JOIN FaveForums f ON f.ForumID = J1.ForumID --INNER JOIN Users u ON f.UserID = u.UserID --INNER JOIN Journals J on J.UserID = u.UserID --and J.SiteID = 1 --INNER JOIN Forums fo ON fo.ForumID = J.ForumID insert FaveForums values (5819,5) exec watchingusers @userid =5, @siteid =1, @skip = 0, @show = 100000 rollback tran use master */ #endregion #region Helper methods EmailValues GetUserEmailValues(IDnaDataReader reader, int userId) { string sql = string.Format(@"SELECT DecryptedEmail = dbo.udf_decryptemailaddress(EncryptedEmail,UserID), EncryptedEmail, HashedEmail FROM Users WHERE UserId=" + userId); reader.ExecuteWithOpenKey(sql); reader.Read(); EmailValues emailValues = new EmailValues(); emailValues.DecryptedEmail = reader.GetString("DecryptedEmail"); emailValues.EncryptedEmail = reader.GetSqlBinary("EncryptedEmail"); emailValues.HashedEmail = reader.GetSqlBinary("HashedEmail"); reader.Close(); return emailValues; }