About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, June 11, 2015

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);

Tuesday, June 09, 2015

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