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
GO
SET
GO
SET
GO
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 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.
Step 11: Select newly created ECT. And click on create.
Your new External List using stored procedure is ready.
Enjoy.
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.
Step 11: Select newly created ECT. And click on create.
Your new External List using stored procedure is ready.
Enjoy.
END
GO
AS
BEGIN
ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[SP_BCS_ReadItem_BCS_Customer]
