articulate SharePoint

In this part, I am going to explain how we can use stored procereures to craete external content type ECT. Almost every one know how to create External content type using BCS in sharepoint 2010 using tables.

in this part we will have readItem and readList Operation in the BCS.

I am using the AdventureWorks2008R2 sample database which can be downloaded from here

Step 1: Create a new table called Customers. Make sure you have a row_id (PK) field.
USE
GOSET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[BCS_Customer]([id] [int] IDENTITY(1,1) NOT NULL,[FName] [varchar](50) NULL,[LName] [varchar](50) NULL,[Address] [varchar](50) NULL,[Phone] [varchar](50) NULL,[Email] [varchar](50) NULL ON [PRIMARY]GO
SET
GO
ANSI_PADDING ONInsert some test records.

Step 2:Create a Stored Procedure to read Item from the table.

[AdventureWorks2008R2]
) [AdventureWorks2008R2]

USE

SET

create

@id int

SET NOCOUNT ON;

select * from dbo.BCS_CRUD_DEMO where ID = @id
Step 3: Create a Stored Procedure to read all items from the table
USE
GO
SET
GO
SET
GO
create
@id intSET NOCOUNT ON;
select * from dbo.BCS_CustomerEND
GO

Step 4:Click on External Content Type on the TOP LEFT CORNER MENU

AS
BEGIN ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[SP_BCS_ReadList_BCS_Customer] [AdventureWorks2008R2]

Step 5: Click on Add Connection Select SQL Server from drop down list and Enter SQL Server name and Database

Step 6: Expand the Adventureworks2008R2 tree and expand Routimes and right click on our read item stored procedure.

Step 7: Right click on ReadList Stored Procedure and follow these steps.

Save ECT.

Step 8: Go you your sharepoint site and click on Site Actions View All Site Content
Step 9: Click on create
Step 10: Select Data from the left pane and click on External List on the right side selection area. Click on create . Give appropriate name and click on the External Content Type Selection icon.