Archive for January, 2010

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Article 3 in the small BCS-series:

1. SP 2010: Getting started with the Business Connectivity Services (BCS)
2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

In my previous article in the series, I talked about how easy it is to fetch information using the standard SharePoint server API-approach. In this article I will talk about how you can access data in your BCS data source, by utilizing the Client Object Model.

Client Object Model code to read from the external list

Just like I described in my previous article, you now have the awesome ability to work with data in your External Lists (connected to a data source using BCS) – namely, you can use the standard SharePoint APIs.

Since I’ve shown you how you can do this using the Server Object Model, I thought I could take another spin at it and show you the code for doing basically the same with the Client Object Model.

The underlying data

As with my previous article, I’m still using the same data source as I set up in my first article – the "ProductList" table in my SQL Server database called "Zimmergren_DB"

As seen in the SQL Server Management Studio:
image

Let’s fetch the data using a Windows Forms application that utilizes the Client Object Model!

I’ve designed a Windows Forms application to utilize the .NET Client Object model (in contrary to using the Silverlight client object model or JavaScript client object model).

It looks like this:
 image

When you click the fancy button called "Get External Data", it will use the Client Object Model to fetch the records from the external list (from the SQL server) and display them in a DataGridView. Nothing fancy.

The code!

With no further delays or chit-chat, here’s the simple code!

    // Define the Client Context (as defined in your textbox)
    SP.ClientContext context = new SP.ClientContext(tbSite.Text);
    SP.Web site = context.Web; 

    var ProductList = site.Lists.GetByTitle(tbList.Text); 

    SP.CamlQuery camlQueryAwesomeness = new SP.CamlQuery();  

    IQueryable<SP.ListItem> productItems =
                                      ProductList.GetItems(camlQueryAwesomeness); 

    IEnumerable<SP.ListItem> externalList =
                                      context.LoadQuery(productItems);

    // This is where we actually execute the request against the server!
    context.ExecuteQuery(); 

    // Retrieve the products from the product list using some fancy LINQ
    var productListData = from product in externalList
      select new
      {
          // We’re never pointing to the field at the 0-index
          // because it’s used by the BDC Identity itself. Hence our elements start at 1.
          ProductID = product.FieldValues.ElementAt(1).Value.ToString(),
          ProductName = product.FieldValues.ElementAt(2).Value.ToString(),
          ProductDescription = product.FieldValues.ElementAt(3).Value.ToString()
      }; 

    // Simply clear the rows and columns of the GridView
    gvProducts.Rows.Clear();
    gvProducts.Columns.Clear(); 

    // Add the columns we need (ProductID, Name, Description)
    gvProducts.Columns.Add("ProductID", "ProductID");
    gvProducts.Columns.Add("Name", "Product Name");
    gvProducts.Columns.Add("Description", "Product Description");
    foreach (var product in productListData)
    {
        // For each product in the list, add a new row to the GridView
        gvProducts.Rows.Add(
                                 product.ProductID,
                                 product.ProductName,
                                 product.ProductDescription
                                 );
    }

References and recommended reading

  1. Getting Started with the Client Object Model in SharePoint 2010
  2. Getting Started with Business Connectivity Services in SharePoint 2010

Summary & Download

At this point, I’ve showed you three short articles in which I describe how you can set up a Business Connectivity Services data source – then utilize the Server OM or Client OM to fetch information from that external data storage.

Worth to note is that this is still a Beta product, which of course means that it may differ in functionality and performance in comparison with the final (RTM) version of SharePoint 2010.

Download the complete Visual Studio 2010 project here: [ Zimmergren.SP2010.ClientOM.BCS.zip ]

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Article 2 in the small BCS-series:

1. SP 2010: Getting started with the Business Connectivity Services (BCS)
2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

In my previous article I talked about how you can set up a simple BCS configuration to fetch and work with external data in SharePoint 2010. In this article I will talk about how you can utilize the SharePoint 2010 object model to work with that external data, directly from the SharePoint API. It’s all really simple!

Working with data in an External List using the SharePoint Object Model

The code in this sample doesn’t really differ from the way you fetch information from any other list in SharePoint (2007 or 2010). This – of course – is very welcomed news, as we do not need to learn any new frameworks or tools to work with the data in our external lists. It simply works as any other SPList, basically.

Retrieving external data, made simple:

When fetching items from an external list, you can simply do that by utilizing the good-old SPList object. We do not need to work with any other types of namespaces or frameworks in order to do this.

In my SQL Server I’ve got a table called “ProductList“.
This list is filled with the following data:

image

Fetching some items from the external list, and displaying them in a console app:

// Product List is my external list, that is working with data in the SQL Server!
SPList list = web.Lists["Product List"];

SPQuery q = new SPQuery();
q.Query =
“<Where><IsNotNull><FieldRef Name=’ProductID’ /></IsNotNull></Where>”;
q.RowLimit = 100;

SPListItemCollection col = list.GetItems(q);

foreach (SPListItem item in col)
Console.WriteLine(item["Name"].ToString());

This will render the following result (fetched from the database):

image

The things you see in the console windows is fetched straight from the SQL Server (using a BCS connection through the External List).

Writing data to the External List (hence, writing to the SQL Server)

Seriously, this is way too easy as well…

// Get the external list
SPList list = web.Lists["Product List"];

// Use the traditional approach to create SPListItems and hook it up with the list
SPListItem item = list.Items.Add();
item["Name"] = “Sample Product Wohoo“;
item["Description"] = “Sample Description Wohoo“;
item.Update();

Upon running this code in your SharePoint application, it will create the SPListItem object and add a Name and Description. When you hit .Update() it will push this data through the data source connection, to your SQL server.

Here’s what the updated data looks like:
image

We’re running a Beta-product!

As you can imagine, there’s a ton of new cool things to work with in SharePoint 2010 – where the BCS is one. This article discuss the very basics of how you can retrieve information from these lists using the normal API-approach.

At the time of this writing (during Public Beta) there isn’t any measures on performance and what impact it has on the server in comparison to alternative ways to fetch and work with the data.

As time goes on, there will be probably be some new information on this – I’ll keep you posted when I know more.

Summary

As you can see, working with external data from the SharePoint API isn’t very hard to do. What you need to make sure is to have an external list set up somewhere (see this article for how you can do that) and then you can simply use the normal SPList object from the SharePoint object model to work with the external list and it’s external data from the SQL server (in my case).

So if you haven’t already: Get on the SharePoint 2010 wagon and enjoy the ride!

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Article 1 in the small BCS-series:

1. SP 2010: Getting started with the Business Connectivity Services (BCS)
2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

BCS in SharePoint 2010 is an awesome refinement of the Business Data Catalog from MOSS 2007. With BCS – or Business Connectivity Services – you get the possibility to connect external data and work with it from SharePoint.

In this article I will not cover the basics of what BCS is all about (MSDN/TechNet does this very well) – I will rather give you a walkthrough of how you can setup a BCS connection to an external database, and then work with this information directly from a SharePoint list – without the user actually knowing anything about the connection to the database.

BCS Poster: Business Connectivity Services Poster
BCS Team Blog: http://blogs.msdn.com/bcs/

A sample SQL database

I’ll just show you how my sample database is set up – simply create a new database in your SQL Server and have it filled with some example data. In my case, this is the data in my SQL database, called Zimmergren_DB:
image

In this sample database, I’ve added a table called ProductList which in theory will represent some products in this database, like this:
image

I’m filling the database with some sample data, so we will be familiar with this data when we later watch this information from SharePoint:
image

Alright – we have some sample data in our SQL Server. Nothing fancy, just some very simple data. Great, let’s get going with the fun stuff!

Creating an external content type

The most effective and easy way to set up a simple BCS connection, is to use SharePoint Designer 2010. You heard me, we can now get up and running with BCS by using SPD instead of modeling complex ADF files and things like that.

In order to do this, we need to create a new External Content Type!

Here’s how do create our External Content Type and hook it up with our database, step by step:

  1. Open the site you want to work with using SharePoint Designer 2010
  2. Select "External Content Types" in the left hand navigation:
    image
    Loading this page might take some time, be patient!
  3. Click to create a new External Content Type like this:
    image
  4. Click the link that reads: "Click here to discover external data sources and define operations":
    image
  5. Click "Add Connection"
    image
  6. Select "SQL Server" as your Data Source Type:
    image
  7. Enter the details about your connection to your SQL Server:
    image
  8. When the connection is made, your Data Source Explorer will be filled with the database you have specified. Now choose the table you want to work with, and right-click and select "Create All Operations":
    image 

    You’ll be presented with a wizard-like dialog where you can specify the operations, elements and other properties for your BCS connection.

  9. Click "Next" to get to the Parameters page
  10. Select the field that you want to act as an Identifier. In my case I’ve selected my ProductID just to get on with it:
    image
  11. Click "Finish"
  12. You’ll be presented with a list of operations that your External Content Type can do, like this:
    image

That’s it. A few points, a few clicks – and you’re done. Let’s create an external list (using the Browser to show how simple it is..) and hook up our external content type with it!

Creating an external list

There’s a few ways to create an external list in SharePoint 2010. We will create it using the Browser UI to show you how simple it can be.

  1. Open your site and choose Site Actions - More Options…
    image
  2. Select the External List template, and click Create
    image
  3. Enter a name for your list, e.g. Product List
  4. You’ll see a field in this list called External Content Type, click the browse-button beside it:
    image

    What is really awesome here, is that you’re now presented with a dialog where you simply can choose the data source for this list. That means, you’ll select the data source you’ve created (mine is called Zimmergren_DB). Then your list will automatically work against the SQL database, but still have the look and feel of a SharePoint 2010 list.

  5. Select your data source and click OK:
    image
  6. Now simply click the button called Create:
    image

Would you look at that! You’re now working with external data, from your (what looks to be) normal SharePoint list! This is brilliant!

You now have the ability to create new items, update existing items, delete items and do all your normal CRUD-operations (CRUD = Create, Read, Update, Delete) straight from the SharePoint 2010 list.

Proof of concept – Adding a new product

Let’s just for the fun of it add a new product called "Awesome Product 1.0" like the following screenshot:
image

Now go to your SQL Server and see the changes take effect immediately. The data is NOT stored in SharePoint, it’s stored in your SQL Database.

This is what my table now looks like in the SQL Server, after adding a new item in the SharePoint list:
image

Summary

With a few points, followed by a few clicks – you’ve set up your external data connection. Basically it’s that simple.

Of course there’s a lot of things to consider when doing these configurations – and you might not want to auto-generate the CRUD-operations, but rather create them one by one and specify more fine-grained permissions etc.

This is merely a sample to show you how easy it is to actually get up and running with the SharePoint 2010 Business Connectivity Services (BCS) and work with external data!

Enjoy

SP 2010: How To – Relational lists in SharePoint 2010

January 5th, 2010 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

One of the new cool things in SharePoint 2010 is the fact that we now have relational lists. In my previous article about List Joins, I talked about how you programmatically can fetch and join information from more than one list with the improved SPQuery object.

In this article I will give you an overview of what capabilities you get out of the box when installing SharePoint 2010 – in terms of relational data in lists.

Relational Lists in SharePoint 2010 – Overview

By utilizing Lookup fields in SharePoint 2010, we can enforce a relationship behavior that we previously would have to work very hard to achieve.

Microsoft have now provided us with a few new options when working with Lookup Fields:

  1. Joins between lists
  2. Projected Fields
  3. Relational integrity

Joins between lists

As mentioned, we have the capability to create relationships between lists in SharePoint 2010. This is quite easy to do using the browser UI, which I will soon demonstrate step-by-step.

Projected Fields

With projected fields we have the capability to pull information from a parent list into the view of the child list.

This basically mean that you can reference and display information from a parent list, in your child list. The fields are Read-Only but enables you to get a much nicer joined view.

Relational integrity

With SharePoint 2010 and relations in lists, you would of course wonder how it handles the relational integrity. E.g. what happens if I delete or try to delete something in the parent list?

Well, there’s generally two relational integrity options:

  1. Restricted delete
    Basically the restricted delete option enables you to enforce a behavior that means that you can’t delete any items that have relations from the list. E.g. if the item you’re trying to delete have a bunch of child-items, you cannot delete them.
  2. Cascade delete
    Cascade delete on the other hand, means that when you’re trying to delete an item which has relations – it’ll delete the related items as well.

Delete and Recover related items – Recycle Bin

A question I got the other day was:

"If I delete an item in my parent list and have cascading delete so all my child items are deleted, how do I restore them if I made a mistake?"

Quite simple my dear Watson, you utilize the recycle bin. When you delete an item using Cascading Delete, the item and it’s related items are placed in the Recycle Bin. From there you can obviously easily recover the items as well. This is what an item with relations looks like in the recycle bin:

[PIC]

Step by step – Do it yourself

Alright – so we’ve covered some of the basics of relational lists, nothing fancy. But now we want to create some lists and have relationships between them – so let’s get on with it!

1. Create a parent list

  1. Create a new Custom List named "ParentList"
  2. Create a new Coumn in that list as per the following settings:
    image 
  3. Add some sample items in the list like this:
    image

2. Create a child list

  1. Create a new Custom List named "ChildList"
  2. Create a new Column in that list as per the following settings:
    image
    Please note that I checked the City checkbox. This will create a Projected Field against the lookup automatically so you can view that information which exist in the parent list – directly in the child list.

3. Test out the projected fields functionality

Add some new items in the ChildList to see that when you add an item and choose a company from the ParentList it will automatically show the projected field ("City") as a read-only field in the child-list:
image

4. What about enforcing relational behavior?

I’m glad you asked. When you create (or change settings for) a lookup field (like the "ParentLink" field), you have the ability to change settings for the relational behavior.

Go to your "ParentLink" column, or when you create a new lookup field – and see the following dialog:
image

From this dialog as you can see, you have the ability to make the necessary settings for your fields.

Restricted Delete

If you choose the "Restricted Delete" option, you will see the following behavior when trying to delete an item that has related items:
image 

Cascade Delete

If you choose the "Cascade Delete" option for your lookup field instead, you’ll be sending the items directly to the Recycle Bin instead. Then it’ll look like this in the recycle bin:
image

Note, that you have the icon that looks like a relational diagram in your recycle bin – this means that you’ve deleted an item that may have deleted linked items. If you restore this item (in our case, TOZIT) it will automatically restore all of the items that were originally deleted.

So, we have the kind of enforced relationships we’ve longed for since the dawn of days!

Summary

Voila. Easy as 1-2-3, you have created two very simple lists and created a relationship between them – and optionally you can enforce this relationship using the "Enforce relationship behavior" settings for Restrict Delete or Cascade Delete.

Enjoy!

SP 2010: How To – Event Receivers and Custom Error Pages

January 5th, 2010 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

SharePoint 2007

Many of you are aware that in SharePoint 2007, you can create Event Receivers (aka. Event Handlers) to take care of things for you when specific things happen. For example, you can subscribe to the "ItemAdded" event for a specific list, and have your custom code execute when the event fires.

Also, you are in SP 2007 able to cancel an event if you have the need for it, hence providing the user with an error message in the browser.

With SharePoint 2007 you had no way to customize this error message, and could only display the out-of-the-box message.

I’ve been getting the question about 1 million times – but never had any really good answer. Until now, where this specific functionality is built into SP 2010.

SharePoint 2010

The object model has been extended in various places, and this is one of the most welcome changes for me and a lot of my fellow peers doing daily SharePoint development.

So, in this article I will walk you through the news with Event Receivers in SP 2010 in regards to creating custom Error Pages for your users.

Creating an Event Receiver in SharePoint 2010 with a Custom Error Page

Using Visual Studio 2010, choose the new template called "Event Receiver" like so:
image

Choose if you want this to be a Farm Solution or a Sandboxed Solution. Then click next:
image

Finally, choose the Type of receiver you want to create, what event you would like to hook up and to what type. I chose "List Item Events", "Announcements" and "An item is being added":
image

Click next and let Visual Studio 2010 work it’s magic.

You’re presented with the following project structure that is created for us:
image

I will not dig deep on how and why the structure of the project looks the way it does now – it will be covered in another article.

1. Adding some basic logic for your Event Receiver

In the EventReceiver1.cs file that you’re presented with, you can quite easily add any code you want – and some code has already been added so you don’t have to!

The code looks like this out of the box:

image

Now, what I want to do in order to make sure my event receiver works – is to simply add some dummy-code and have it tested!

Add the following code to your ItemAdding-method:
 image

All we do here is check our item that is being added makes a condition to see if the DueDate property is set. The breakpoint is simply added because you easily should see that your code executes and works as expected.

2. Press the Holy Button (F5)

By pressing F5, Visual Studio will take care of the build, packaging and deployment of your Event Receiver. For more details on the actual F5-experience, I encourage you to read MSDN, SDK and all the blogs out there.

You are presented with a web page (VS 2010 launches IE for you as well), where you now can easily test your Event Receiver.

In our case, when adding a new Announcement, we should automatically check the DueDate property. Currently we don’t cancel the event or do anything else – let’s leave that to your imagination.

Cancelling an Event in SharePoint 2010

So – now that we’ve got a very basic Event Receiver in SharePoint 2010, we should add that Custom Error Page we talked about.

  • Start by adding a New Item to your project of type Application Page:
    image
  • Visual Studio 2010 will now automatically add a mapped folder called "Layouts" which is mapping to the _layouts folder in the SharePointRoot (14-folder):
    image
  • Edit the ErrorDueDate.aspx file to add some rich HTML:
    image
    Note that I am referring to an image as well. I just popped that into the same Layouts-folder that was created for my Event Receiver project. 
     
  • Edit your EventReceiver1.cs file and add the current logic:
    image
    This will now cancel the event if the DueDate field is empty, and show the user a custom error page that you designed yourself.
  • Hit F5 and let Visual Studio 2010 work it’s magic again
  • Try it out by adding a new Announcement without adding a DueDate:
    image
  • You should now be presented with the following dialog:
     image

As you can see, your custom HTML now appears. Apparently I didn’t do any fancy design on my Application Page, but you can add more images and whatever else you want to make it more easy for the users to understand what actually went wrong – and how to make it on from there.

Summary

As easy as 1-2-3, you’ve created a new event receiver in SharePoint 2010 – and created a Custom Error Page upon which your users will land when they’re presented with the error – something that wasn’t really possible in 2007.

Enjoy!

SP 2010: List Joins & SPQuery enchancements!

January 4th, 2010 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

As per request by some of my readers who wanted information about how you can query SharePoint lists using joins in SharePoint 2010, here’s an introduction!

With the introduction of relational lists in SharePoint 2010, they also introduced a new set of investments in how we can interact with our lists.

We now have Projected fields, joins and relational integrity between parent and child lists.

Projected Fields & Relational Integrity will be covered in an upcoming blog series.

Joins

With our new relational lists in SharePoint 2010, we also have a better support for joins. Generally there’s the following ways of joining lists to pull out aggregated data:

  1. SharePoint API (SPQuery object)
  2. LINQ to SharePoint
  3. CAML
  4. SharePoint Designer 2010

In this article I will touch upon the SPQuery enhancements regarding joins and relational data, and will cover the rest in future articles.

SPQuery enhancements

Using the object model with SharePoint 2010, you now have the ability to use some new fancy properties of the SPQuery object:

  1. SPQuery.Joins
  2. SPQuery.ProjectedFields

SPQuery.Joins Property

With the new property called Joins on the SPQuery object, we can (using CAML, as usual..) define a join for our query like so:

<Joins>
    <Join Type=’LEFT ListAlias=’List1‘>
        <Eq>
            <FieldRef Name=’Field1′ RefType=’Id’ />
            <FieldRef Name=’ID’ List=’List1‘ />
        </Eq> 
    </Join> 

    <Join Type=’LEFT’ ListAlias=’List2‘>
        <Eq>
            <FieldRef List=’List1‘ Name=’SomeFieldName’ RefType=’Id’ />
            <FieldRef List=’List2‘ Name=’ID’ /> 
        </Eq>
    </Join>
</Joins>

A working sample of the SPQuery code could look like this:

SPQuery query = new SPQuery();
query.Query = "[YOUR CAML QUERY HERE]";
query.Joins = "[YOUR JOIN CAML HERE (See above for example)]";
query.ViewFields = "[SAME AS BEFORE]";

SPListItemCollection items = myList.GetItems(query);
foreach(SPListItem item in items)
    // Work with your items here like you’re used to, but now all joined up!

So the only thing you really need to do is to make sure you’ve got the join set up with the SPQuery.Joins property – and then shoot!

SPQuery.ProjectedFields Property

The SDK states the ProjectedFields property quite clearly:

…itemizes fields from joined foreign lists so that they can be referenced in the Where element and the ViewFields element

You should have a go at this link to learn more about projected fields, and how you can utilize them in your SPQuery objects:
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.projectedfields(office.14).aspx

Summary

It’s pretty easy to do joins using the SPQuery object in the SharePoint object model these days, and all you really need to do is have a quick look at the SPQuery class in order to grasp the news.

So, pretty easy huh?
- Well, even more easy is to use LINQ to SharePoint, which I will try to cover in an article later this week.

My most popular SharePoint posts of 2009

January 4th, 2010 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

As per requests, I have put together a small list of my post popular and most read blog posts of 2009.

All in all, the year of 2009 was awesome – and we’ve only got even more awesome things to look forward to in 2010.

But to get on with it, here’s my "Top 10 Most Popular SharePoint Posts" of 2009!

My Top 10 Most Popular SharePoint Posts – 2009

  1. MOSS 2007: Getting Started with the Business Data Catalog
    1. Related: Write your first BDC Web Part
  2. WSPBuilder – Walkthrough of the Visual Studio Add-in
  3. Introduction to SharePoint Online
  4. SharePoint Online – Customization
  5. SP 2010: Getting started with the Client Object Model
    Although this was very recently posted, it has gained a lot of popularity and is quickly moving up the list of most read posts.
  6. Recover/Fetch your Application Pool Password
  7. How To: SharePoint and Silverlight – Part 1
  8. Upload a document to a SharePoint Document Library programmatically (SharePoint Object Model)
  9. Debugging your code execution for anonymous users in SharePoint 
  10. 10 Free SharePoint Themes – Visual Overview

Summary

A very short list of the most viewed articles I wrote in 2009. There’s plenty more of course, but to keep it short and concise I chose to go with the Top 10 as requested :-)

There’s also another cool way to keep track of popular posts in the SharePoint community, and that’s using PostRank. Visit this URL: http://www.postrank.com/topics/sharepoint and you’ll be presented with popular blogs about SharePoint where you can easily find new content to read.

As for 2010, we’ve got some really fancy stuff to look forward to – and I’ve written a few posts about SharePoint 2010 to cover some of the new aspects for developers. Keep an eye out for them.

Enjoy this simple post. I’m still on vacation, so I’m not going to be much elaborate than this ;-)