Thursday, November 19, 2009

SharePoint 2007 – FIVE very helpful (yet unsupported) stored procedures for querying membership data

I do quite a bit of SharePoint development and have always relied on the object model or web services. Unfortunately, I have ran into situations where using either option was less than optimal because obtaining the data I needed might cause performance problems since they required me to get recursive data. One of these situations is pulling membership data out of SharePoint for reporting or authentication purposes. Some of these examples were gleaned off of other examples I found on the web, but were cleaned up or added additional information. In all cases I have ensured that they on SELECT data, and always use NOLOCK for all tables.

1. Select Groups By URL

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrl]

@url NVARCHAR(255)

AS

BEGIN

SELECT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle", Webs.Title AS WebTitle, Webs.FullURL AS WebURL, Roles.Title AS "RoleTitle"

FROM RoleAssignment WITH (NOLOCK)

INNER JOIN Roles WITH (NOLOCK) ON Roles.SiteId = RoleAssignment.SiteId

AND Roles.RoleId = RoleAssignment.RoleId

INNER JOIN Groups WITH (NOLOCK) ON Groups.SiteId = RoleAssignment.SiteId

AND Groups.ID = RoleAssignment.PrincipalId

INNER JOIN Sites WITH (NOLOCK) ON Sites.Id = RoleAssignment.SiteId

INNER JOIN Perms WITH (NOLOCK) ON Perms.SiteId = RoleAssignment.SiteId

AND Perms.ScopeId = RoleAssignment.ScopeId

INNER JOIN Webs WITH (NOLOCK) ON Webs.Id = Perms.WebId

WHERE ((Webs.FullUrl = @url) OR (@url IS NULL))

ORDER BY

Groups.Title, Webs.Title, Perms.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrl 'Sites/HR/Pay'

Results

GroupID

GroupTitle

WebTitle

WebURL

RoleTitle

71

Manager

Accounting

Sites/HR/PAY

View Only

74

Acct Sr Manager

Accounting

Sites/HR/PAY

Full Control

75

Member

Accounting

Sites/HR/PAY

View Only

76

Viewer

Accounting

Sites/HR/PAY

View Only

2. Select Groups By URL and User

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrlUser]

@login NVARCHAR(255),

@url NVARCHAR(255)

AS

BEGIN

SELECT G.ID AS "GroupID", G.Title AS "GroupTitle", W.Title AS WebTitle, W.FullURL AS WebURL, R.Title AS "RoleTitle"

FROM RoleAssignment AS RA WITH (NOLOCK)

INNER JOIN Roles AS R WITH (NOLOCK) ON R.SiteId = RA.SiteId

AND R.RoleId = RA.RoleId

INNER JOIN Groups AS G WITH (NOLOCK) ON G.SiteId = RA.SiteId

AND G.ID = RA.PrincipalId

INNER JOIN Sites AS S WITH (NOLOCK) ON S.Id = RA.SiteId

INNER JOIN Perms AS P WITH (NOLOCK) ON P.SiteId = RA.SiteId

AND P.ScopeId = RA.ScopeId

INNER JOIN Webs AS W WITH (NOLOCK) ON W.Id = P.WebId

WHERE ((W.FullUrl = @url) OR (@url IS NULL))

AND G.ID IN

(

SELECT Groups.ID FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

)

ORDER BY

G.Title, W.Title, P.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrlUser 'acmedomain\jdoe', 'Sites/HR/Pay'

Results

GroupID

GroupTitle

WebTitle

WebURL

RoleTitle

74

Sr Manager

Accounting

Sites/HR/PAY

Full Control

3. Select Groups By User Login

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUserLogin]

@login NVARCHAR(255)

AS

BEGIN

SELECT DISTINCT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle"

FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo WITH (NOLOCK) ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

ORDER BY Groups.Title

END

Sample Query

EXEC usp_SelectGroupsByUserLogin 'acmedomain\jdoe'

Results

GroupID

GroupTitle

74

Sr Manager

52

Corporate Viewer

14

HR Team Member

4. Select Users By Group

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroup]

@groupTitle NVARCHAR(255)

AS

BEGIN

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, dbo.Groups.ID AS GroupsID, dbo.Groups.Title AS GroupsTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

WHERE ((dbo.Groups.Title = @groupTitle) OR (@groupTitle IS NULL))

ORDER by dbo.UserInfo.tp_Login

END

Sample Query

EXEC usp_SelectUsersByGroup 'HR Team Member'

Results

UserID

UserTitle

UserLogin

UserEmail

GroupsID

GroupsTitle

23

Doe, John

acmedomain\jdoe

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

14

HR Team Member

5 Select Users By Groups

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroups]

@groupTitles VARCHAR(1000)

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @groupTitles

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, Groups.Title AS GroupTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

JOIN OPENXML(@idoc, '/Root/Group', 0) WITH (Name VARCHAR(100)) AS g

ON dbo.Groups.Title = g.Name

ORDER by dbo.Groups.Title, dbo.UserInfo.tp_Title

END

Sample Query

EXEC usp_SelectUsersByGroups '<Root><Group Name="HR Team Member"></Group><Group Name="Acct Sr Managers"></Group></Root>'

Results

UserID

UserTitle

UserLogin

UserEmail

GroupsID

GroupsTitle

23

Doe, John

acmedomain\jdoe

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

14

HR Team Member

7

Horowitz, Rick

acmedomain\rhorowitz

rhorowitz@acmedomain.com

15

Acct Sr Managers

3

Davis, Jiro

acmedomain\jdavis

jdavis@acmedomain.com

15

Acct Sr Managers

Silverlight - Get the top-level UserControl root element using two different approaches

I recently found a situation where I needed to get the root element of a Silverlight control. Unfortunately, Silverlight controls only have a Parent member and not a Top or Root member. If your control is embedded in multiple controls, you cannot easily get the root and will probably end up writing some ugly casting code that looks like this.

DO NOT USE THIS CODE...KEEP READING!

private UserControl GetParent()
{
return (UserControl)((Grid)((Border)((StackPanel)((UserControl)((Grid)((StackPanel)this.Parent).Parent).Parent).Parent).Parent).Parent).Parent;
}

The problem with this code (besides being ugly) is that if you ever change the containing hierarchy, you’re going to end up having to update this method. After finding a couple of different posts that didn’t work for me, I finally came up with two possible solutions:

Solution 1 – Implement a recursive lookup that finds the top parent and returns it as a UserControl.

1.) Add a method extensions that returns the parent UserControl.

public static class SilverlightExtensions
{
public static UserControl GetRoot(this FrameworkElement child)
{
var parent = child.Parent as FrameworkElement;
if (parent == null)
if (child is UserControl)
{
return (UserControl)child;
}
else
{
throw new Exception("The root element is an unexpected type. The root element should be a UserControl.");
}
return parent.GetRoot();
}}

2.) Access the parent by using the method extension:

this.GetRoot().Resources["SampleText"] = "x y z";
((TextBlock)this.GetRoot().FindName("TitlePage")).Text = "New Title Page";

Solution 2 – Add a property to the child control that references the parent. (Recommended)

This is less expensive than a recursive loop, but you do have to always remember step 1 or else it will not work.

1.) Add a MyParent property of type UserControl in the child control’s class.

public UserControl MyParent { get; set; }
2.) In the parent control’s constructor, add a line that populates the child control’s MyParent property with itself.

public ParentUserControl()
{
InitializeComponent();

((ChildUserControl)this.FindName("childControl")).MyParent = (UserControl)this;
}
3.) Access the parent root using the MyParent property.

MyParent.Resources["SampleText"] = "x y z";
((TextBlock)MyParent.FindName("TitlePage")).Text = "New Title Page";

Note: You should be able to take a similar approach with WPF. One other alternative is to raise an event to the parent UserControl. Unfortunately, I haven’t seen any easy or clear-cut examples on how to do this.

Tuesday, November 3, 2009

Silverlight 3 + SharePoint: A wrapper web part that makes SharePoint user information available to Silverlight.

A few weeks ago I posted a “quick and dirty” way to integrate a Silverlight application into SharePoint. This was great for basic apps, but has no way of accessing a user’s information. After reviewing a number of products and a bunch of different approaches, I believe I have come up with a solution that works.

Disclaimer: This is mostly code and I haven’t included too many comments. This post assumes you are fairly familiar with Silverlight and SharePoint development. I use WSPBuilder for deploying my SharePoint web parts.

Web Part Code

This can also work with a standard ASP.NET Web Part, but you will need to derive from the System.Web.UI.WebControls.WebParts.WebPart base class and change SPContext references to HttpContext.Current.User.Identity.

Note: Instead of a bunch of crazy string concatenation, you could build all of the objects individually and then set their respective properties. You may also opt to omit or edit the style and script text.

using System;
using System.ComponentModel;
using System.Runtime.InteropServices;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;

namespace Acme.SharePoint.WebPart
{
[Guid("B7BD8441-FA27-43ab-8F92-708094D3FC28")]
public class SilverlightHostWp : Microsoft.SharePoint.WebPartPages.WebPart
{
[Personalizable(PersonalizationScope.Shared), WebBrowsable(true), Category("Silverlight Settings"), WebDisplayName("XAP Path")]
public string XapPath { get; set; }

[Personalizable(PersonalizationScope.Shared), WebBrowsable(true), Category("Silverlight Settings"), WebDisplayName("Parent Width")]
public string ParentWidth { get; set; }

[Personalizable(PersonalizationScope.Shared), WebBrowsable(true), Category("Silverlight Settings"), WebDisplayName("Parent Height")]
public string ParentHeight { get; set; }

[Personalizable(PersonalizationScope.Shared), WebBrowsable(true), Category("Silverlight Settings"), WebDisplayName("Parent Border Enabled")]
public bool ParentBorderEnabled { get; set; }

public SilverlightHostWp()
{
this.ExportMode = WebPartExportMode.All;
}

protected override void CreateChildControls()
{
try
{
base.CreateChildControls();
if (string.IsNullOrEmpty(XapPath))
{
this.Controls.Add(new LiteralControl() { Text = "Update \"XAP Path\" in Silverlight Settings." });
}
else
{
SPUser user = SPContext.Current.Web.CurrentUser;
string fullName = user.Name.Replace(", ", "_"); //Silverlight expects a comma delimited value. This is parsed back in the Silverlight Appplication_Startup method. See below.
string loginName = user.LoginName.ToLower();
string email = user.Email.ToLower();
string userParams = "FullName=" + fullName + ",LoginName=" + loginName + ",Email=" + email;
if (string.IsNullOrEmpty(ParentHeight))
{
ParentHeight = "100"; //Defaults to 100 since otherwise nothing will be visible.
}
if (string.IsNullOrEmpty(ParentWidth))
{
ParentWidth = "100"; //Defaults to 100 since otherwise nothing will be visible.
}
StringBuilder styleScriptText = new StringBuilder();
styleScriptText.Append("<style type=\"text/css\">" +
"html, body" +
" {" +
" height: 100%;" +
" overflow: auto;" +
" }" +
"body" +
" {" +
" padding: 0;" +
" margin: 0;" +
" }" +
"</style>" +
"<script type=\"text/javascript\">" +
" function onSilverlightError(sender, args) {" +
" var appSource = \"\";" +
" if (sender != null && sender != 0) {" +
" appSource = sender.getHost().Source;" +
" }" +
" var errorType = args.ErrorType;" +
" var iErrorCode = args.ErrorCode;" +
" if (errorType == \"ImageError\" || errorType == \"MediaError\") {" +
" return;" +
" }" +
" var errMsg = \"Unhandled Error in Silverlight Application \" + appSource + \"\n\";" +
" errMsg += \"Code: \" + iErrorCode + \" \n\";" +
" errMsg += \"Category: \" + errorType + \" \n\";" +
" errMsg += \"Message: \" + args.ErrorMessage + \" \n\";" +
" if (errorType == \"ParserError\") {" +
" errMsg += \"File: \" + args.xamlFile + \" \n\";" +
" errMsg += \"Line: \" + args.lineNumber + \" \n\";" +
" errMsg += \"Position: \" + args.charPosition + \" \n\";" +
" }" +
" else if (errorType == \"RuntimeError\") {" +
" if (args.lineNumber != 0) {" +
" errMsg += \"Line: \" + args.lineNumber + \" \n\";" +
" errMsg += \"Position: \" + args.charPosition + \" \n\";" +
" }" +
" errMsg += \"MethodName: \" + args.methodName + \" \n\";" +
" }" +
" throw new Error(errMsg);" +
" }" +
"</script>");
StringBuilder objectText = new StringBuilder();
objectText.Append(styleScriptText.ToString() +
"<div id=\"silverlightControlHost\" style=\"width:" + ParentWidth + "; height:" + ParentHeight + ";");
if (ParentBorderEnabled)
{
objectText.Append(" border: silver 1px solid; ");
}
objectText.Append("\"><object data=\"data:application/x-silverlight,\" type=\"application/x-silverlight-2\"" +
" width=\"100%\" height=\"100%\">" +
"<param name=\"source\" value=\"" + XapPath + "\" />" +
"<param name=\"onerror\" value=\"onSilverlightError\" />" +
"<param name=\"background\" value=\"white\" />" +
"<param name=\"minRuntimeVersion\" value=\"3.0.40624.0\" />" +
"<param name=\"autoUpgrade\" value=\"true\" />" +
"<param name=\"initParams\" value=\"" + userParams + "\" />" +
"<a href=\"http://go.microsoft.com/fwlink/?LinkID=149156&v=3.0.40624.0\" style=\"text-decoration: none;\">" +
"<img src=\"http://go.microsoft.com/fwlink/?LinkId=108181\" alt=\"Get Microsoft Silverlight\" style=\"border-style: none\" />" +
"</a>" +
"</object>" +
"<iframe id='_sl_historyFrame' style='visibility: hidden; height: 0; width: 0; border: 0px'>" +
"</iframe>" +
"</div>");
Literal literal = new Literal()
{
Text = objectText.ToString()
};
this.Controls.Clear();
this.Controls.Add(literal);
}
}
catch (Exception ex)
{
//Handle error…


            }
}
}
}


App.xaml.cs Application_Startup method



private void Application_Startup(object sender, StartupEventArgs e)
{
if (e.InitParams != null)
{
foreach (var item in e.InitParams)
{

if (item.Key == "FullName")
{
Resources.Add(item.Key, item.Value.Replace("_", ", ")); //Silverlight expected InitParams to be a comma delimited value, so this was changed to an underscore in the Web Part code. See above.
}
else
{
Resources.Add(item.Key, item.Value);
}
}
}
this.RootVisual = new UserNameControl();
}


Accessing App resource properties within a User Control



In my case, I have created a TextBox that toggles between a prompt to sign and their full name.



public class UserNameTextBox : TextBox
{
protected override void OnMouseLeftButtonDown(MouseButtonEventArgs e)
{
if (Text == "Click To Sign!")
{
if (App.Current.Resources.Contains("FullName"))
{
Text = App.Current.Resources["FullName"].ToString();
Tag = App.Current.Resources["LoginName"].ToString();
}
}
else
{
Text = "Click To Sign!";
}
}
}


XAML



Include something like the following in your UserControl tag.



xmlns:local="clr-namespace:Acme.SilverlightControls"




Add your control XAML like this.




<local:UserNameTextBox Name="UserName" Text="Click To Sign!" Background="Yellow" Width="200" Height="100"></local:UserNameTextBox>



Screenshots



For simplicity, I have added the XAP file and a Web Part Page to the same Document Library. Your path will need to be a relative or absolute URI otherwise. When you add the web part to the page, you have four properties to pick from. I included a property for Parent Border Enabled so that you can have a visual way to tweak the width and height.



image



When the user clicks, their full name appears.



image