Mocking a user

After lots of searching and testing, I finally ended up with piece of code to mock a user for unit testing.

            this.Principal = new Mock<IPrincipal>();

            List<Claim> claims = new List<Claim>{
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", username), 
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier", userid)
            };

            var fakeIdentity = new GenericIdentity(username);
            //fakeIdentity.RemoveClaim(new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", ""));
            fakeIdentity.AddClaims(claims);

            var fakePrincipal = new GenericPrincipal(fakeIdentity, roles);
            Principal.Setup(n => n.Identity).Returns(fakeIdentity);

            Http.Setup(x => x.User).Returns(fakePrincipal);
            Http.Setup(x => x.User.Identity.Name).Returns(username);
            Http.Setup(x => x.User.Identity).Returns(fakeIdentity);

Find a field in SQL Server database

Sometimes you need to find a field and don't know what table or view it might be coming from. A very useful script to pull out:

SELECT b.name, c.name
FROM sys.schemas a              
JOIN sys.all_objects b ON b.schema_id = a.schema_id 
JOIN sys.columns c ON c.object_id = b.object_id 
WHERE c.NAME LIKE '%Find Field%'

Search for fields on ReportServer

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

/* * Get a list of reports in a specific path/folder */ ;WITH ItemContentBinaries AS ( SELECT ItemID ,Name ,[Type] ,CASE [Type] WHEN 2 THEN 'Report' WHEN 5 THEN 'Data Source' WHEN 7 THEN 'Report Part' WHEN 8 THEN 'Shared Dataset' ELSE 'Other' END AS TypeDescription ,CONVERT(varbinary(max),Content) AS Content FROM ReportServer.dbo.[Catalog] with (nolock) WHERE [PATH] LIKE '/Department Level/%' ) ---ADD WORDS, USED IN FIELDS, TO SEARCH FOR ,WordSearchCTE AS ( SELECT 'SSN' AS Word UNION ALL SELECT 'AGE' UNION ALL SELECT 'DOB' UNION ALL SELECT 'EPMP' UNION ALL SELECT 'PAY' UNION ALL SELECT 'COMP' ) --This CTE strips off the BOM if it exists... ,ItemContentNoBOM AS ( SELECT ItemID ,Name ,[Type] ,TypeDescription ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content))) ELSE Content END AS Content FROM ItemContentBinaries ) --The old outer query is now a CTE to get the content in its xml form only... ,ItemContentXML AS ( SELECT ItemID,Name,[Type],TypeDescription ,CONVERT(xml,Content) AS ContentXML FROM ItemContentNoBOM ) --now use the XML data type to extract the queries, and their command types and text.... --and only select from those that contain the search terms SELECT * FROM ( SELECT ItemID,Name,[Type],TypeDescription,ContentXML ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText FROM ItemContentXML --Get all the Query elements (The "*:" ignores any xml namespaces) CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query) )x JOIN ( select '%'+Word+'%' as pattern from WordSearchCTE ) w on CommandText like w.pattern