BCS, Business Connectivity Service, using BCS we can connect to external
databases and can display data in SharePoint list. Databases are may be
SQL,MySQL, Oracle or some other. We can connect to the any external
database and fetch the data. Not only displaying we can also add new
records, update records and delete records.
Here i want try to show BCS operations using MySQL database.
I have one table i.e employee and it has 3 columns.
2. Now open visual studio 2010, Create new project.
3. In the left panel Select SharePoint --> 2010, select Business Data Connectivity Model from the right side panel.
4. Enter URL for debugging.
5. It will show BDC Model with default entity, now rename that entity name to 'Employee' from entity properties.
6. Now specify/rename Identity Column name to empid(it is primary key in mysql database's table) in the place of identifier.
7. Now change identifier data type to Integer by using identifier's properties.
8. Now rename Entity1.cs to EmployeeProperties, in this class we will specify all column names as properties.
9. Delete default methods for the entity.
10. Now open EmployeeProperties.cs file and specify columns as properties.
11. Now open EmployeeService.cs file and delete all methods and make sure that it looks like this.
12. Select entity and select Create Specific Finder Method from dropdown which in BDC Method details.
13. now go to BDC Explorer, Expand Employee i.e entity you can find two things one is identifier and Entity object.
expand identifier and right click select properties, change identifier and identifier Entity.
expand Entity and select entity name tab right click select properties,change type name to Properties class instance.
14. Now right click on entity name and select "Add Type Descriptor".
Change Type descriptor default name to "empid" and its type name to
System.Int32.
And also set identifier to Empid.
15. Now again add Add Type Descriptor and change default name to EmpName, Type Name to System.String.
16. Now again add Add Type Descriptot and Change default name to EmpAddress, Type Name to System.String.
17. now again go to BDC Method Details , this time select Create Finder Method.
18. Now Again go to BDC Method details , this time select Create Creator Method.
19. Now Again go to BDC Method details, select Deleter Method from the Dropdown.
20. Go to BDC Method details Click on Add a method and select Create Updator Method from the dropdown.
Go to BDC Explorer window expand updater method,right click on primary
column field and specify Pre-Updater Required to True , Read-only to
false.
21. Now go to Entity's Service class i.e EmployeeService.cs. Remove all
predefinded methods details and your functionality for each method which
were added previous.
Here am connecting to MySQL database for that am using mysql.data.dll and adding that dll's reference to the project.
Here the code.
Now deploy the solution.
22. After successfully deployment. Go to Central Administration and click on "Manage Service applications".
23.
Click on Business Data Connectivity Services from the list of Services.
24. Select your BDC name and click on Set Object permissions.
25.
Assign User and give permission to that user. here am taking administrator and assign all permission to single user.
26. Open your site and create external list.
27. Specify List name and Specify external Content Type(which was created recently).
28. Now it is showing data from the external database or getting data from other than sql server here am using MySql Server.
29. Click on Empid it will show item record in display form.
30. Now am try to update the item, click on edit item and modifying field values.
31. After saving modified record updates are done on database and again data getting from the database.
32. Now click on new item from ribbon and try to insert new item.
newly added record now displaying in list.
Now am trying to delete item.
That's it. Now we can connect and fetch data from any database in SharePoint using BDC Model.