Relationships in Sharepoint Lists

Ok, this is a technique that I use when I want to get over one of the limitations of Sharepoint lists. It's the elephant in the room, relationships! Yes that's right, Sharepoint lists does not "do" relationships. What! I hear you cry! do not fear there are ways around this, one of the ways which I use is import each sharepoint list into a datatable, create a datarelation, add the datatables to a Dataset and then join the tables with the datarelation. I then output the result to final datatable for output. Sounds more complicated than it is allow me to demonstrate:

private DataTable getLimitedData()
{
DataSet ds = new DataSet();

DataTable finalTable = new DataTable();
finalTable.Columns.Add("ExampleIndexRefTitle", Type.GetType("System.String"));
finalTable.Columns.Add("ExampleIndexRef", Type.GetType("System.String"));
finalTable.Columns.Add("Code", Type.GetType("System.String"));
finalTable.Columns.Add("Type", Type.GetType("System.String"));

DataTable exampleIndexTable = Utility.List.GetList("exampleIndex", sp);
DataTable documentTable = Utility.List.GetList("ExampleDocLibrary", sp);

ds.Tables.Add(exampleIndexTable);
ds.Tables.Add(documentTable);

DataColumn parentColumn = ds.Tables[0].Columns["Reference"];
DataColumn childColumn = ds.Tables[1].Columns["exampleIndex"];
DataRelation relation = new DataRelation("exampleIndexREL", parentColumn, childColumn, false);

ds.Relations.Add(relation);

foreach (DataRow dr in ds.Tables[0].Rows)
{
foreach (DataRow childRow in dr.GetChildRows("exampleIndexREL"))
{
DataRow newRow = finalTable.NewRow();
newRow["ExampleIndexRefTitle"] = dr["Title"].ToString();
newRow["ExampleIndexRef"] = dr["Reference"].ToString();
newRow["Code"] = childRow["Code"].ToString();
newRow["Type"] = childRow["ContentType"].ToString();

finalTable.Rows.Add(newRow);
}
}

return finalTable;
}

Please note: that the Utility.List.GetList() method that I mention is merely part of a helper class that I wrote to return a DataTable object from a Sharepoint List which is as follows:

public static DataTable GetList(string List, SPSite Site)
{
SPWeb Website = Site.OpenWeb();
SPList DocLib = Website.Lists[List];
DataTable tempTable;
SPQuery SelectQuery = new SPQuery();
SelectQuery.Query = "";

SPListItemCollection SelectedDoc = DocLib.GetItems(SelectQuery);
tempTable = DocLib.GetItems(SelectQuery).GetDataTable();

return tempTable;
}

This should just about do it and the two Sharepoint lists have now the equivalent of an inner join relationship.

All good!

Intermittent 403 - Forbidden Error

we've been hitting this 403 error on a custom page that we have developed with custom .NET web parts.

Adding the <>\Users read, read and execute and list folder contents permissions to the bin folder of the SPS basically solved it. Check out the following link for a more detailed explanation: http://blogs.msdn.com/johnwpowell/archive/2008/05/23/sharepoint-intermittent-403-forbidden-errors.aspx

Content placeholders

PlaceHolderBodyAreaClass - Additional body styles in the page header
PlaceHolderBodyLeftBorder - Border element for the main page body
PlaceHolderBodyRightMargin - Right margin of the main page body
PlaceHolderCalendarNavigator - Shows a date-picker for navigating in a calendar when a calendar is visible on the page
PlaceHolderFormDigest - The "form digest" security control
PlaceHolderGlobalNavigation - The global navigation breadcrumb
PlaceHolderHorizontalNav - Top navigation menu for the page
PlaceHolderLeftActions - Bottom of the left navigation area
PlaceHolderLeftNavBar - Left navigation area
PlaceHolderLeftNavBarBorder - Border element on the left navigation bar
PlaceHolderLeftNavBarDataSource - Data source for the left navigation menu
PlaceHolderLeftNavBarTop - Top of the left navigation area
PlaceHolderMain - Main content of the page
PlaceHolderMiniConsole - A place to show page-level commands, for example, WIKI commands such as Edit Page, History, and Incoming Links
PlaceHolderNavSpacer - The width of the left navigation area
PlaceHolderPageDescription - Description of the page contents
PlaceHolderPageImage - Page icon in the upper-left area of the page
PlaceHolderSearchArea - Search box area
PlaceHolderSiteName - Site name
PlaceHolderTitleAreaClass - Additional styles in the page header
PlaceHolderTitleAreaSeparator - Shows shadows for the title area
PlaceHolderTitleBreadcrumb - Main content breadcrumb area
PlaceHolderPageTitleInTitleArea - Page title shown immediately below the breadcrumbs
PlaceHolderTitleLeftBorder - Left border of the title area
PlaceHolderTitleRightMargin - Right margin of the title area
PlaceHolderTopNavBar - Top navigation area
PlaceHolderUtilityContent - Extra content at the bottom of the page
SPNavigation - Empty by default in Windows SharePoint Services 3.0. Can be used for additional page editing controls.
WSSDesignConsole - The page editing controls when the page is in Edit Page mode (in the browser, click Site Actions, and then click Edit Page)

Sourced from http://office.microsoft.com/en-us/sharepointdesigner/HA101651201033.aspx

Debugging a webpart

How to debug a webpart? Seems almost impossible at first but with a little VS magic anything is possible.

Open up you webpart project and set your breakpoints. You then need to attach the debugger to the ASP.NET (w3wp.exe) process.

To Attach to the ASP.NET Process:

1. Create a Web Part Page on the default SharePoint site.
2. Add the Web Part to the page.
3. Attach the debugger to the W3wp process. To do this, follow these steps:
4. On the Debug menu in Visual Studio .NET, click Processes.
   1. Verify that the Show system processes check box is selected.
   2. Verify that the Show processes in all sessions check box is selected.
   3. Under Available Processes, click W3wp.exe in the Process list, and then click Attach.
   4. Under Choose the program types that you want to debug, select Common Language Runtime, and then click OK.
   5. Click Close.

Debugging

After attaching to the W3wp process, you can debug the code. To do this, use Internet Explorer to open the test page that includes the new Web Part. As the page renders, the control switches to the Visual Studio debugger (depending on the location of the break point). You can now use the debugging mode to check the Web Part functionality.

Most of the above quoted from http://msdn2.microsoft.com/en-us/library/ms916837.aspx
for indepth analysis.

Context sensitive menus in Sharepoint lists

Ok, following on from the custom data lists and menu item below. I have just been trying to come up with a way so you can have context sensitive menu's appear. I.e. a certain menu will appear if certain data is present.

At first, I found it tricky to just see the data being populated in the cell of the SPGridView. In a traditional GridView control you can simply do the following within the RowDataBound event:

void grid_RowDataBound(object sender, GridViewRowEventArgs e)
{

string s = e.Row.Cells[0].Text;

}

Using the SPGridView, this will not work. A Label is used for text or a Microsoft.Sharepoint.WebControls.Menu for a menu (weird huh ;-))

So in other words to see the data you need to cast, the control which is held within the Controls collection of the cell.

Label Example

void grid_RowDataBound(object sender, GridViewRowEventArgs e)
{

string s = ((Label)e.Row.Cells[0].Controls[0]).Text;

}

Ok, now we have some background on how to "see" the data before it is bound and rendered we can move onto the custom context sensitive menu's.

Context Sensitive Menu's

We can now "see" the data that will drive our context sensitive menu's. The below code snippet is doing exactly the same as above apart from the column is rendered as a Menu and therefore, we need to cast it as a menu to see it's contents. So basically we are going to check to see if a particular column in the grid has value of "Admin" if it does we will show a custom menu if not it will default to what ever menu has all ready been defined previously:

void grid_RowDataBound(object sender, GridViewRowEventArgs e)
{

if ((Microsoft.Sharepoint.WebControls.Menu)e.Row.Cells[0].Controls[0]).Text == "Admin")
{

MenuTemplate testListMenu = new MenuTemplate();
testListMenu.ID = "testListMenu";

MenuItemTemplate raiseMenu = new MenuItemTemplate("Give them a raise");
testListMenu.Controls.Add(raiseMenu);

MenuItemTemplate praiseMenu = new MenuItemTemplate("Praise them");
testListMenu.Controls.Add(praiseMenu);

this.Controls.Add(testListMenu);

(Microsoft.Sharepoint.WebControls.Menu)e.Row.Cells[0].Controls[0]).TemplateId = "testListMenu";

}
}

Ta Da! Custom List context sensitive menus in Sharepoint!

Lists for non-Sharepoint data - SPGridView

Now we all pull data in from everywhere, right? Of course we do! Not everything is in a Sharepoint list, most of our data is in databases right? So how do we present this data to the user through Sharepoint?

Most things we will see on the net involves you having to manually crank out a web part building the table yourself, etc. Unfortunately, this data will be presented differently to your Sharepoint lists, i.e. no sorting, no filtering, drop down menu's, etc. Unless you want to painstakingly try to replicate the style and functionality used by Sharepoint lists, which might take a while ;-).

The answer to your problems is SPGridView. Check out this great post on how you can use your own datasets from non-Sharepoint sources and display them in the same list control as Sharepoint Lists use. You can also add cool item specific drop down menu's, filtering, sorting, paging, etc. Very cool.

Please note that I found in the example supplied that when tryingto read the DefaultView of a table in a DataSet, that the code example supplied would not work. To get around the problem, I used SPBoundField instead of BoundField. Happy Days!

Deployment and Code Access Security

I must admit when I was first shown how to deploy and secure a webpart in SP2007 I was taken aback. It seemed like such a difficult process, i.e:

1. Setting the assembly info to UsePartiallyTrustedCallers
2. Signing the assembly
   
3. Editing the section of the web.config of the SP site
4. Then changing the Trust Level to WSS_Medium
   
5. or creating/changing a custom security policy
   

My first thoughts where that our sys admin chaps, who will be deploying our web parts, are going to put their foot down and say, if not all, but most of the following:

• Sorry guys, no way are we following these steps.
• Why are you changing the web.config?
• Where is the MSI??
• Why don't we just put it in the GAC?

It's not that our sys admins are averse to complex procedures, far from it, but it could possibly introduce errors which wastes the time of the developers, the sys admins and the client.

So after my initial gut reaction to just deploy to the GAC (we don't want to deploy to the GAC as it gives the webpart full access) we came across away to deploy the webpart and for it to automagicaly modify the web.config and create a custom security policy. Sound good? Damn straight!

The power lies in using a WSP to deploy your webpart. Check out this link as to how to create a WSP. This excellent post by Brett Lonsdale goes into depth of how you can craft a manifest.xml file that will solve most of our deployment problems.

Check it out, it solved our deployment headache, we can now easily pass over the WSP to our sys admin who then easy to deploy the webpart with stsadm.exe without compromising security.