SharePoint 2013 adds many new features to Lists. In this article, we’ll take a look at how to import Data from a SQL table into a .bdcm file using SharePoint Designer 2013. As you know, the Business Data Connectivity (BDC) Service enables SharePoint to display business data from back-end server applications, Web services, and databases. This walkthrough shows you how to create a model for the BDC service that returns information about contacts in a sample database.
We have the following two parts to configure:
1. In the first part of this post, I will demonstrate how to get Data from a SQL table through SharePoint Designer 2013.
2. In the second part, we’ll look at how to import a Database into a .bdcm file using SharePoint Designer 2013.
I. How to get Data from a SQL table through SharePoint Designer 2013
- Step 1: Make sure that you’ve created a Northwind database in SQL Server:
- Step 2: Go to SharePoint Designer 2013 and click on the Open Site icon:
- An Open Site dialog box is shown. You must input the site URL into the Site Name textbox to create the External List.
- Click the Open button:
- The Windows Security dialog is shown, requesting your User Name and Password:
- Step 3: Go to SharePoint Designer to connect the database with SQL Server as follows:
- Click on the External Content Types menu on the left pane.
- Then, select the External Content Types icon at the top.
- In External Content Type Information, type its Name and click on the External System hyperlink to connect to SQL Server.
You will see a Connection window appear:
- Click the Add Connection button:
- You’ll see a dialog appear, prompting you to select Data Source Type as pictured below. Select SQL Server:
- After selecting the Data Source Type, fill in the SQL information fully, including Database Server, Database Name, and Secure store Application ID as shown below:
- Step 4: After connecting successfully, you’ll see Northwind database shown on Data Source Explorer as shown below. We’ll select a Table to create in here right-clicking Table Name and selecting Create All Operations:
- A Parameter Configuration is shown after that. Select as many columns as you need, and click the Finish button to complete:
- Finally, don’t forget to click the Save button after you’re done. This is pretty important, for obvious reasons:
- Now, we have a Northwind database in place, as expected:
II. How to import a Database into a .bdcm file using SharePoint Designer 2013
- Go to External Content Type and click on the Export BDC Model Manage icon in the Ribbon:
- Type a File Name and select the location to which you want to save the file:
- You will see the .bdcm file after clicking the Save button:
- Open the .bdcm file. You will see the structure of the Data Table which you created before: