|This post discusses about how to connect .NET Connectivity Assembly for Business Connectivity services using Visual Studio 2010. Business Data Connection Model allows you to connect to the external data sources and can edit them directly from SharePoint site list.|
The table that we are going read from the SQL Server are
Now Create a New Project in Visual Studio 2010 and select the Business Data Connectivity Model template from the dialogue box.
Choose deploy as a farm solution while creating the project
Visual Studio creates an entity for you as shown below
Rename the entity to TraningEvent and add some properties as shown below
By default it visual studio creates two properties Identifier1 and Message. We can override those properties with custom properties.
Switch back to BDC model designer as shown below, Rename the entity to TraningEvent Entity
You can edit the parameters of ReadItem input parameter and return parameter as shown below
Change the Identifier1 parameter name to TrainingEventId and data type to Int32. ReadItem is a specific finder where I can retrieve item from the database based on individual id Trainingevent.
Change the name of return parameter name from entity1 to TrainingEvent so that specific finder find an event to specific entity. Now setup the type descriptors for this event Add any other type descriptors to this event type like login name and title.
Do the same for Readlist method which finds multiple events and having the return parameter of type list item. You can also add create method using the dropdown list as shown below
Type descriptors will automatically setup for the method in BDC explorer just change the type names for the parameters
Similarly you can add delete method and update a method and click save changes.
Visual Studio Creates a sample code when you create methods where you can override that code with the custom logic by reading the items from the database
Similarly write logic for create, update and delete methods.
Now you can deploy the solution to SharePoint but before deploying check you are having proper administrative permissions in SharePoint Central administration and click manage service applications option
Click on the business connectivity services option
You can set the appropriate permissions for this BDC model
Now create an external list from SharePoint site as shown below
Select the external content type from the data source picker for the list as shown below
Click on create to save the list.
Now add some events to the list as shown below
Now you can notice the saved items in SQL server table
You can perform edit and delete operation on list and can check back the database for the same. More about the BDC can be read here