In the first part of the series we saw how users information which comes from SQL Server aspnetdb database can be retrieved by separate pages instead of retrieving all users and implement pagination in memory. Such approach gives us advantage in performance which will be crucial when amount of users is very big. In this part we will continue improving performance and will consider how we may attach additional information for each user returned from SQL Server from Sharepoint’s User information list (e.g. such data as Title, Created and Modified).
Let’s return to the code sample from the first part. The key part is those which retrieves appropriate page of information from the database:
1: // get paged users from sql database
2: int pageNumber = args.StartRowIndex/args.MaximumRows;
3: int totalRecords;
4: var membershipUsers = Membership.GetAllUsers(pageNumber, args.MaximumRows,
5: out totalRecords);
6: if (membershipUsers.Count == 0)
7: {
8: return null;
9: }
10:
11: var users = new DataTable();
12: users.Columns.Add("Name");
13: users.Columns.Add("Email");
14: users.Columns.Add("Active");
15:
16: foreach (MembershipUser membershipUser in membershipUsers)
17: {
18: var row = users.NewRow();
19: row["Name"] = membershipUser.UserName;
20: row["Email"] = membershipUser.Email;
21: row["Active"] = membershipUser.IsApproved ? "Yes" : "No";
22: users.Rows.Add(row);
23: }
In result we have DataTable object with Name, Email and Active columns and current page of users information. Now for each user in the current page we need to add additional information from Sharepoint: Title, Modified, Created. How to do it? The simples way is to perform CAML query for each user and add this data to the resulting DataTable. But it will lead to the classic n + 1 problem, when n users will cause n database queries plus optional 1 query for retrieving all users first. In turn it causes performance impact and all our efforts from the first part with retrieving paged information from SQL Server won’t have a lot of sense. Is there a better way to get data? Yes it is: instead of making many CAML queries for each user we may make single CAML query which will return data for all users returned from the database. This query is more complex: it should be built dynamically for each page of the users. E.g. if we have 3 users in the DataTable with names testuser1, testuser2 and testuser3 we need to built CAML query which will return data by the following conditions:
item[“Name”] == “testuser1” || item[“Name”] == “testuser2” || item[“Name”] == “testuser2”
which will be presented in CAML like this:
1: <Where>
2: <And>
3: <And>
4: <Eq>
5: <FieldRef Name="Name" />
6: <Value Type="Text">testuser1</Value>
7: </Eq>
8: <Eq>
9: <FieldRef Name="Name" />
10: <Value Type="Text">testuser2</Value>
11: </Eq>
12: </And>
13: <Eq>
14: <FieldRef Name="Name" />
15: <Value Type="Text">testuser3</Value>
16: </Eq>
17: </And>
18: </Where>
Also note that page size is not hardcoded parameter and may be also be different and that in the final page number of users may be less than page size. The bigger number of users will be retrieved from database, the bigger CAML tree will be.
In order to built CAML query we will use Camlex – open source library for building dynamic CAML queries. It is ideal for the mentioned requirements, i.e. when CAML query should be built in runtime base on some parameters. Before to use it we need to notice one thing: Name column in User information list in Sharepoint contains user name in form {membership provider name}:{user name}. Membership provider name is specified in web.config and can be retrieved by using SPIisSettings.MembershipProvider property. Let’s first see how CAML query is built:
1: var site = SPContext.Current.Site;
2: var settings = CodeFiles.Utils.GetFBAIisSettings(site);
3: if (settings == null)
4: return null;
5:
6: // ... get paged data from SQL Server
7:
8: var users = new DataTable();
9: users.Columns.Add("Name");
10: users.Columns.Add("Email");
11: users.Columns.Add("Active");
12: users.Columns.Add("Title");
13: users.Columns.Add("Modified");
14: users.Columns.Add("Created");
15:
16: // ... fill DataTable with users info from the database
17:
18: // get appropriate users from Sharepoint
19: string prefix = settings.MembershipProvider.ToLower() + ":";
20: var orExpressions = new List<Expression<Func<SPListItem, bool>>>();
21: foreach (MembershipUser membershipUser in membershipUsers)
22: {
23: var mu = membershipUser;
24: orExpressions.Add(x => (string)x["Name"] == prefix + mu.UserName);
25: }
26: var orExpression = ExpressionsHelper.CombineOr(orExpressions);
27: var andExpressions = new List<Expression<Func<SPListItem, bool>>>();
28: andExpressions.Add(x => (string)x["ContentType"] == "Person");
29: andExpressions.Add(orExpression);
30:
31: var query = new SPQuery();
32: query.Query = Camlex.Query().WhereAll(andExpressions).ToString();
33: query.ViewFields = Camlex.Query().ViewFields(x => new[] { x["Name"],
34: x["LinkTitle"], x["Email"], x["Modified"], x["Created"] });
35: DataTable spUsers = null;
36: try
37: {
38: spUsers = site.RootWeb.SiteUserInfoList.GetItems(query).GetDataTable();
39: }
40: catch (Exception ex)
41: {
42: return null;
43: }
44:
45: if (spUsers != null)
46: {
47: foreach (DataRow userRow in users.Rows)
48: {
49: foreach (DataRow spRow in spUsers.Rows)
50: {
51: string spUserName = ((string) spRow["Name"]).Replace(prefix, "");
52: if (((string) userRow["Name"]).ToLower() == spUserName.ToLower())
53: {
54: userRow["Title"] = spRow["Title"];
55: userRow["Modified"] = spRow["Modified"];
56: userRow["Created"] = spRow["Created"];
57: break;
58: }
59: }
60: }
61: }
At first we build CAML query (lines 19-35) with Camlex. E.g. if DataTable contains 20 users returned from SQL Server, it will build the following query:
1: <Where>
2: <And>
3: <Eq>
4: <FieldRef Name="ContentType" />
5: <Value Type="Text">Person</Value>
6: </Eq>
7: <Or>
8: <Or>
9: <Or>
10: <Or>
11: <Or>
12: <Or>
13: <Or>
14: <Or>
15: <Or>
16: <Eq>
17: <FieldRef Name="Name" />
18: <Value Type="Text">fba:testuser1</Value>
19: </Eq>
20: <Eq>
21: <FieldRef Name="Name" />
22: <Value Type="Text">fba:testuser2</Value>
23: </Eq>
24: </Or>
25: <Eq>
26: <FieldRef Name="Name" />
27: <Value Type="Text">fba:testuser3</Value>
28: </Eq>
29: </Or>
30: <Eq>
31: <FieldRef Name="Name" />
32: <Value Type="Text">fba:testuser4</Value>
33: </Eq>
34: </Or>
35: <Eq>
36: <FieldRef Name="Name" />
37: <Value Type="Text">fba:testuser5</Value>
38: </Eq>
39: </Or>
40: <Eq>
41: <FieldRef Name="Name" />
42: <Value Type="Text">fba:testuser6</Value>
43: </Eq>
44: </Or>
45: <Eq>
46: <FieldRef Name="Name" />
47: <Value Type="Text">fba:testuser7</Value>
48: </Eq>
49: </Or>
50: <Eq>
51: <FieldRef Name="Name" />
52: <Value Type="Text">fba:testuser8</Value>
53: </Eq>
54: </Or>
55: <Eq>
56: <FieldRef Name="Name" />
57: <Value Type="Text">fba:testuser9</Value>
58: </Eq>
59: </Or>
60: <Eq>
61: <FieldRef Name="Name" />
62: <Value Type="Text">fba:testuser10</Value>
63: </Eq>
64: </Or>
65: </And>
66: </Where>
After that we add data from Sharepoint to resulting DataTable (lines 46-62) by mapping rows by Name column and after that it contains data both form SQL Server and from Sharepoint. SPIisSettings object used in the example above can be retrieved with the following helper function (it is given from CKS.FBA open source project):
1: public static SPIisSettings GetFBAIisSettings(SPSite site)
2: {
3: SPIisSettings settings = null;
4:
5: // try and get FBA IIS settings from current site zone
6: try
7: {
8: settings = site.WebApplication.IisSettings[site.Zone];
9: if (settings.AuthenticationMode == AuthenticationMode.Forms)
10: return settings;
11: }
12: catch
13: {
14: // expecting errors here so do nothing
15: }
16:
17: // check each zone type for an FBA enabled IIS site
18: foreach (SPUrlZone zone in Enum.GetValues(typeof(SPUrlZone)))
19: {
20: try
21: {
22: settings = site.WebApplication.IisSettings[(SPUrlZone)zone];
23: if (settings.AuthenticationMode == AuthenticationMode.Forms)
24: return settings;
25: }
26: catch
27: {
28: // expecting errors here so do nothing
29: }
30: }
31:
32: // return null if FBA not enabled
33: return null;
34: }
As you can see using of Camlex allowed us to solve the problem of retrieving data from Sharepoint quite gracefully. In our solution for optimizing performance we get only necessary page of users information from SQL Server and then perform single CAML query to the Sharepoint, which returns data for only selected users, and then merge information and show it in the listing. Without Camlex alternative way would be to use Linq 2 Sharepoint, but it would require more setup from us. From my point of view this is very nice example which shows how effective Camlex may be. I hope that this information will help you in your work and you will use Camlex in scenarios when need to build CAML queries dynamically.
No comments:
Post a Comment