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:
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:
So 
