In our last blog of this series Walkthrough of creating a SharePoint 2010 external list using Visual Studio 2010 Beta, we introduced how to create a simple “Hello world” external list in SharePoint 2010 Beta using Business Data Connectivity Designer in Visual Studio 2010 Beta.
In this blog, we will show you how to pull data from an external database into an external list and enable Create, Read, Update and Delete (CRUD) functions to the external list.
First of all, you need to have SharePoint 2010 Public Beta and Visual Studio 2010 installed on your machine in order to complete this walkthrough. We’ll use “Northwind” database as external data source, so if you do not have an existing “Northwind” database available, we’ll walk you through to create a local database using SQL Server Express first (SQL Server Express comes with Visual Studio installation by default, in case you don’t yet have it, download it here).
At the end of this post, we will complete with a BDC model project which has a “Customer” entity connects to “Customer” table in “Northwind” database, and have CRUD operations enabled. The finished project can also be downloaded from here.
Prepare the data source
If you already have a “Northwind” database, you can skip this section. Otherwise, please download SharePoint2010_BDCSamples.zip from here and extract the SQL script file CreateSampleNorthwindDB.sql.
Open Visual Studio. Go to View->Server Explorer. Right click Data Connections in Server Explorer, and select Create New SQL Server Database.
1. In the prompt dialog, type “localhost\sqlexpress” in Server Name text box, and give the new database name “SampleNorthwind”.
* If you’re using the SQL Express that comes with SharePoint Server, please replace “localhost\sqlexpress" with "localhost\sharepoint”.
2. Start a Command Prompt. Go to Start->Run, type “Cmd” in the text box and click OK.
3. In the Command Prompt, type in following command and press enter:
sqlcmd -S localhost\sqlexpress -d samplenorthwind -i <Path of CreateSampleNorthwindDB.sql file>
Create BDC Project
Create a new C# BDC Model project and rename it “BdcSampleCSharp”. VB code snippets will also be provided, so you can create VB BDC Model project if you want. In this walkthrough, we will use C# project as an example. (Check this blog for how to create a BDC project)
Connect to external data source
To use the SampleNorthWind database, we add a LINQ to SQL model to the project:
1. On the Project menu, click Add New Item, in the prompt Add New Item dialog select Data in the Installed Templates pane, in the Templates pane select LINQ to SQL Classes, in the Name box, type “Customer”, and then click Add.
2. In the Server Explorer, go to Data Connections->[hostname]\sqlexpress.SampleNorthWind.dbo->Tables->Customers, drag the Customers table and drop it on the Customer.dbml design surface.
3. Add a new class file and rename it “CustomerDataContext.cs”. Replace the code of the class with the following code snippet.
Note: We made the connection string a constant in the code only for demo purpose, if you’re using your own database, modify the connection string as needed. In our future post we will introduce how to set the connection string in a custom property on LobSystemInstance inside the BDC model and read the value through IContextProperty interface at runtime.
C#:
public partial class CustomerDataContext
{
private const string ConnectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=SampleNorthwind;Integrated Security=True;Pooling=False";
public CustomerDataContext() :
base(ConnectionString, mappingSource)
{
OnCreated();
}
}
VB:
Partial Public Class CustomerDataContext
Private Const ConnectionString As String = "Data Source=localhost\SQLEXPRESS;Initial Catalog=SampleNorthwind;Integrated Security=True;Pooling=False"
Public Sub New()
MyBase.New(ConnectionString, mappingSource)
OnCreated()
End Sub
End Class
Design BDC Model
1. On the design surface, delete entity Entity1 which is created by default. On the View menu, click on Toolbox if it is not shown. Create a new entity by drag and drop the Entity icon from Toolbox onto design surface (see the screenshot below). In the Properties Browser, change the value of Entity’s Name property to “Customer”.
Create a Specific Finder method for the entity. To do so, on the design surface, select entity Customer, you could find a <Add a Method> command in the Method Details Window. If the Method Details Window is not opened, you can find it in menu View->Other Windows->BDC Method Details. From the <Add a Method> drop-down list, select Create Specific Finder Method:
TypeDescriptors for the return parameter Customer. The edit need to be done in BDC Explorer. You can find it by going to View->Other Windows->BDC Explorer.
a) In the Method Details Window, click <Edit> command in the drop down control from TypeDescriptor Customer as depicted below. After the click the BDC Explorer will get focused on the TypeDescriptor Customer.
LINQ to SQL model. In this example, all the TypeDesriptors have a type of System.String which is the default one so we do not need to change them. After this step, we get the following TypeDescriptors in BDC Explorer:
this blog to see how to create an external list). After the list is created, you will see the following page appears when you click on the list name:
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog1_thumb.png” width=”644″ height=”198″ />
The first thing you’ll notice is that we’ve moved some of the navigation and menus around. The stuff right in the middle (Site Name > Document Library > All Documents) is your current location. You can click on the Site Name to get back home, and you can click on the view name (All Documents) to switch to another view. Above the title, you see there’s a black row – this is where you go to access all your commands. I’ll talk more about that later. Below the title, you see the list view items. Right now there are no items, so let’s fix that.
To add an item to this library, you can click on the Add document button in the view. That button will always be available at the end of the current page, if you want to quickly add documents to this library. When you click it, you’ll notice that instead of navigating the entire page, we just put up a dialog asking you where you want to upload. This makes it faster to load and also easier to understand what’s going on. For this post, I actually want to upload multiple files – so go ahead and click on Upload Multiple Files. You’ll see something like this:
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog2_thumb.png” width=”644″ height=”440″ />
You can drag files onto the blue rectangle to add them to your upload list, or you can click on Browse for files instead to find the files in a Windows dialog. Once you’ve picked them, click Ok and they will start uploading. When the page refreshes, you’ll see lot of items in the view. If you hover over one, you’ll notice that the row gets highlighted:
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog3_thumb.png” width=”644″ height=”160″ />
You can click on the dropdown arrow in the middle of the row to see a menu of commands for this document. However, you can also click anywhere else on the row (well, anywhere that’s not a link) to select the document. This will automatically open a menu of all the available commands for the selected document:
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog4_thumb.png” width=”644″ height=”158″ />
This menu is where you can find all the commands in SharePoint. Sometimes, it will open automatically (like when you select an item). But, if you want to open it manually, you can always click on one of the tab names. Once it is open, you can look through the various available tabs by clicking on their names. The names generally describe the thing the command affects, so if you click on Library you are looking at commands you can perform on the Document Library. Commands on the Documents tab are relevant to the currently selected document or documents.
When the ribbon opened, you might have noticed that it covered up the page title. We did that to save space, but if you ever want to get back to the page title, you can just click on Browse:
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog5_thumb.png” width=”644″ height=”166″ />
Changing ribbon tabs won’t change what items you have selected, so it’s always safe to click around looking for the command you want.
Now, go ahead and click back on the Documents tab and look through the available commands. You’ll notice that you can do all the familiar SharePoint actions from here. Go ahead and click on Edit Properties, and you’ll see the form pop up in another dialog. You can make whatever changes you want, click Save and we’ll drop you right back on the same page again, quick and easy.
In addition to just selecting one item, you can select multiple items. To do that, just click on the check box that appears on the left of each row when you hover over it:
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog6_thumb.png” width=”644″ height=”179″ />
Not all commands work on multiple items, and you’ll notice that the ribbon has greyed out commands that can’t be used. This multiple selection makes it easy to delete, check in and check out many files at once.
Once you’ve added enough items, the view will start to page. This means that you’ll need to click on the next page arrow at the bottom of the view to see more of what’s in the list:
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog7_thumb.png” width=”644″ height=”114″ />
You can control the number of items shown on each page from the Modify View page, which can be accessed from the Library tab (just to the right of the Documents tab we were looking at earlier):
WindowsLiveWriter/SharePoint2010ListViewBlogSeriesPart2Usi_C1D4/lvblog8_thumb.png” width=”644″ height=”85″ />
In addition to modifying the current view, the Library tab also allows you to switch views and use the datasheet. You can also create new columns, connect the lists to client applications – like Excel, Windows Explorer or SharePoint Workspaces – and customize the list’s forms, workflows or other settings.
By looking through those commands, there’s obviously a lot more that can be done with SharePoint lists. But, with what I’ve shown above, you should be able to get around your lists and libraries with relative ease. Good luck!
Tags: Deployment, List
