Business Connectivity Services (BCS) were formerly known as Business Data Catalog (BDC) in SharePoint 2007. BDC is a one of the most powerful features of MOSS 2007 to integrate various Line of business (LOB) systems like SAP, Siebel, SQL Server and web services. Now BCS is extended to support .NET assemblies and WCF services as data source(s). Apart from read, update, delete and search, BCS data could be available for offline access.
Now am here Creating External Content Type Using WCF services.
Here am using SQL Server 2008 R2 database and i have one table with the name "Employee" having Four Columns EmpNo(int),EmpName(varchar),Salary(int) and DeptNo(int).
Note: we can use any type of databse
Now am trying to get the data from this table and display in SharePoint external list.
I. Creating WCF Service:-
1. Create new WCF Service Application project using WCF Template in Visual Studio.
2. By Default it provide one service and one interface with the name "Service1.svc" and "IService1.cs" respectively. But am not interested to use with these two so am removing these two file from the project.
3. Now am adding new WCF service to the project with the name "UdayWCFService.svc".
a. Right click on project --> add --> New Item
b. Select "web" from Installed Templates and select "WCF service" and name it as "UdayWCFService.svc".
c. After adding wcf service file, now the solution explorer looks like
4. Now open IUdayWCFService.cs, add operation contracts i.e method signatures and am creating datamember with four properties which are same as my table columns.
5. Now open UdayWCFService.cs file which is under UdayWCFService.svc
This .cs file implements IUdayWCFService.cs so, need to write implementation for all methods which are in the interface.
a. Add using Sytem.data.SqlClient name space to the file.
b. Creating constructor in that am creating SQLConnection object .
c. and implementing InserEmployee method as follows
SqlConnection con;
SqlCommand cmd;
public UdayWCFService()
{
con=new SqlConnection("Data Source=UDAYKUMARREDDY;Initial Catalog=Company;uid=sa;pwd=urpasswordhere");
}
//Creating new Item
public int InsertEmployee(Employee emp)
{
int i=-1;
try
{
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "insert into Employee values(@EmpNo,@EmpName,@Salary,@DeptNo)";
cmd.Parameters.AddWithValue("@EmpNo", emp.EmpNo);
cmd.Parameters.AddWithValue("@EmpName", emp.EmpName);
cmd.Parameters.AddWithValue("@Salary", emp.Salary);
cmd.Parameters.AddWithValue("@DeptNo", emp.DeptNo);
i=cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
return i;
}
d. Now implementing GetEmployees method as follows
//Getting list of items
public List<Employee> GetEmployees()
{
List<Employee> listemp = new List<Employee>();
try
{
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "Select * from Employee";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
listemp.Add(new Employee { EmpNo = Convert.ToInt32(reader[0]), EmpName = reader[1].ToString(),
Salary = Convert.ToInt32(reader[2]), DeptNo = Convert.ToInt32(reader[3]) });
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
return listemp;
}
e. Now implementing GetEmployeeByEmpNo as follows
//Getting specific item
public Employee GetEmployeeByEmpNo(int empno)
{
Employee emp = new Employee();
try
{
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "Select * from Employee where EmpNo=@EmpNo";
cmd.Parameters.AddWithValue("@EmpNo", empno);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
emp.EmpNo = empno;
emp.EmpName = reader[1].ToString();
emp.Salary = Convert.ToInt32(reader[2]);
emp.DeptNo = Convert.ToInt32(reader[3]);
}
reader.Close();
}
catch
{
}
finally
{
con.Close();
}
return emp;
}
f. Now implementing UpdateEmployee as follows
//Updating item
public int UpdateEmployee(Employee emp)
{
int i = -1;
try
{
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "update Employee set EmpName=@EmpName,Salary=@Salary,DeptNo=@DeptNo where EmpNo=@EmpNo";
cmd.Parameters.AddWithValue("@EmpNo", emp.EmpNo);
cmd.Parameters.AddWithValue("@EmpName", emp.EmpName);
cmd.Parameters.AddWithValue("@Salary", emp.Salary);
cmd.Parameters.AddWithValue("@DeptNo", emp.DeptNo);
i = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
return i;
}
g. Now implementing deleteEmployee as follows
//Deleting item
public int DeleteEmployee(int empno)
{
int i = -1;
try
{
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "delete from Employee where EmpNo=@EmpNo";
cmd.Parameters.AddWithValue("@EmpNo", empno);
i = cmd.ExecuteNonQuery();
}
catch
{
}
finally
{
con.Close();
}
return i;
}
finally UdayWCFService.cs file looks like this.
6. Now open web.config remove all unnecessary tags and make it looks like this
<?xml version="1.0"?>
<configuration>
<system.web>
<compilation debug="true"></compilation>
<identity impersonate="false" />
</system.web>
<system.serviceModel>
<services>
<service behaviorConfiguration="UKReddyWCF_ExternalList.UdayWCFServiceBehavior"
name="UKReddyWCF_ExternalList.UdayWCFService">
<endpoint address="" binding="wsHttpBinding" contract="UKReddyWCF_ExternalList.IUdayWCFService">
<identity>
<dns value="localhost" />
</identity>
</endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
</service>
</services>
<behaviors>
<serviceBehaviors>
<behavior name="UKReddyWCF_ExternalList.UdayWCFServiceBehavior">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
</behavior>
</serviceBehaviors>
</behaviors>
</system.serviceModel>
</configuration>
Endpoint address will be left as empty, will be update after deploying service on IIS.
7. Now save all your project file. we need to deploy this wcf service on IIS
Deploy WCF Service on IIS:-
a. run inetmgr command on run window(windows+R)
b. Right Click on web sites and select add web site.
c. Specify Site name: "", Physical path: "", Port: ""(port number should be unused). then click on "OK".
d. After adding web site it looks like this.
Then we can say WCF service is deployed on IIS.
8. now go to the project solution and open web.config.
update endpoint address as http://<servername>:<portno>/<wcfservicenameinproject>.svc
9. Now we can check our WCF service in two ways using browser and WCFTESTCLIENT
if you got the following error "Failed to add a service. Service metadata may not be accessible. Make sure your service is running and exposing metadata." please refers this pose . if not leave it.
another way is using wcf test client
open visual studio tools command prompt and run "wcftestclient"
then right click on my service project and add endpoint address
HOW TO CREATE EXTERNAL LIST USING WCF SERVICE:-
Till now we have done with how to create and deploy WCF service and checking wcf service working or not.
Now trying to show how to create external content type and create external list using SPD2010.
1. Open SPD2010 --> open web site --> Click on External Content Types which is on left hand side under Site Objects.
Now Select New External Content Type and specify Name (but here am going with default name i.e new external content type (2)) Then Click on "Click here to discover external data sources and define operations" which is adjacent to External system. Have a look on screenshot.
2. Click on Add Connection and select Data Source Type As "WCF Service" and Click "OK".
3. After selecting External Data Source Type Selection it will ask for WCF Connection Properties
Like Service Metadata URL: http://<servername>:<portno>/<wcfservicename>.svc?wsdl
it is like <endpoint address>?wsdl
Specify Name for the WCF connection and check "Define custom proxy namespace for programmatic access".
4. After successfully adding WCF connection it looks like this and it shows set of all methods in that service.
5. Now we need to create operation for this external content type.
First we need to create operation for Read Item Operation then Read list operation.
a. Right click on GetEmployeeByNo and select "new Read Item Operation".
b. We can change Operation Properties.(but am going with default properties in this step).
Click On "Next".
c. Now we need to specify input parameter for this operation like empno(which is primary key in Employee Table). So select EmpNo in DataSource Elements and Check "Map to Identifier" in Properties. Then Click on "Next".
d. This operation will return one record of Employee Type. so in this also select EmpNo in Data Source Elements and chekck "Map to Identifier" and click on "finish".
Till now I created Read Item Operation.
e. Now am trying to Create Operation for Getting List,
Right click on GetEmployees and select "New Read List Operation".
f. Specify Operation Properties(am going with default properties in this step). Click on "Next".
g. To get List items no need of input parameters so Click on "next".
h. This method will return set of records of type "employee". While showing this data in list which column should be act as primary key column. here EmpNo is the primary key so am select that key and checking "Map to Identifier" and also checking "Show in picker" properties.Then Click on "Finish".
i. now creating operation for add new item in list.
Right click on InsertEmployeee Method and Select "New Create Operation".
j. Specify Operation Properties and click on "next". (am going with default properties in this step)
k. To insert new item we need values for columns and in this we need to add one column as primary key and that column value should be required field in add new item. For this Select "EmpNo" and Check "Map to Identifier". Click on "Next".
l. In return Parameter configuration Select "InsertEmployee" and Check "Map to Identifier" then click on "Finish".
m. Now am creating operation for update item by right click on "UpdateEmployee" and select "New Update Operation".
n. Specify Operation Properties and click on "Next".
o. while updating a record on SQL we specify where condition that where condition specify unique column example "update Employee set empname="xxx" where EmpNo=1"; where EmpNo is the unique column and will update only that recored only where EmpNo isequal to "1".
So for that Select "EmpNo" in Data Source Elements and Check "Map to Identifier".
Click on "Finish".
p. Now creating operation for Delete item from list. For this Right click on "DelelteEmployee" and select "New Delete Operarion".
q. Specify Operation peroperties and click on "Next".
r. To delete one record in table we need to specify unique column for that record i.e "delete from Employee where EmpNo=1". Here EmpNo is the unique column in table. So we need to pass Input parameter for this method and map to identifier.
So select "EmpNo" in data source elements and check "Map to Identifier" then click on "Finish".
Till now we have created all operation which we can perform on a list.
Creating External List using External Content Type:-
6. After adding all operation External content type(UdayWCF). Now am creating External using this content Type. For this select UdayWCF external content type and Click on "Create Lists & Form" which is in ribbon.
7. Specify External List Name and Description and Click on "OK". but dont close SPD2010.
8. Now go to your site where you created External Content type and External list.Then you can find the newly created external list at bottom of List.
9. Click on external list then it will show On message "Access Denied by Business Data Connectivity".
10. Now go back to SPD2010 and select your External Content type and click on "Create Profile Page" .
11. Open Central Administration and select Manage Service Application-->Business Connectivity Service.
It will show all external content types and Check your external content type and select "Set Object Permissions" on Ribbon.
12. Add Administrator and check all operation and click on "OK".
13. Now go back to your browser and refresh.It will display all data in list which actually are present in external database.
we can see all operations on a item like view, update, and delete item.
14. Now am trying to add new item from ribbon.
adding new item info
after adding new item it looks like this.
now am trying to delete one item by selecting one item and clicking delete item from ribbon
it is successfully deleted from list.
Now am trying to edit item. Select item and click edit item.
Modify item value and save item.
Now item successfully updated with updated values these values are also updated in database.
Ahh.. Thats it.
Successfully create external list using WCF and performed all operation on that list.
Any questions or suggestion please let me know.
Thanks all..
Thank you this post is really helpful
ReplyDeleteYou are welcome yasser zaid.
DeleteThanks Reddy...
ReplyDelete