In this blog I have tried to connect Sybase as external data External the SharePoint 2010 BCS (Business Connectivity Service Useful Link : http://msdn.microsoft.com/en-us/magazine/ee819133.aspx). Simple concept is we will make a one (windows communication foundation) wcf web service which play a role as middle ware between External system and our SharePoint Farm, Then We will create a external Content type through SharePoint designer 2010 and associate with a external list.
First I will create a simple Table and procedure in the Sybase, which fetch customer data.
Second I will create a WCF Service which connects External System (Sybase) with our SharePoint Farm.
Third we will create an external content type in SharePoint designer make sure the connectivity between external system and SharePoint 2010 farm.
First Part (Sybase Connectivity, Tables and Procedure)
Check the server setting through dsedit, command on run, then set the IP and port of the server where you need to connect.
Using SQL Advantage for tables and procedure.
Table (Sybase)
Create Table NorthWindCustomer
(
NorthWindId int Identity ,
NorthWindCustomerName nVarchar(90) Not Null,
NorthWindCity nVarchar(90) Not Null,
NorthWindCountry nVarchar(90) Not Null,
)
go
GRANT SELECT ON dbo.NorthWindCustomer TO commonUser
go
IF OBJECT_ID(‘dbo.NorthWindCustomer) IS NOT NULL
PRINT ‘<<< CREATED TABLE dbo.NorthWindCustomer >>>’
ELSE
PRINT ‘<<< FAILED CREATING TABLE dbo.NorthWindCustomer >>>’
go
Procedure
IF OBJECT_ID(‘dbo.NorthWindGetCsutomer’) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.NorthWindGetCsutomer
IF OBJECT_ID(‘dbo.NorthWindGetCsutomer’) IS NOT NULL
PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.NorthWindGetCsutomer >>>’
ELSE
PRINT ‘<<< DROPPED PROCEDURE dbo.NorthWindGetCsutomer >>>’
END
go
create procedure NorthWindGetCsutomer
as
Select * from NorthWindCustomer
IF OBJECT_ID(‘dbo.NorthWindGetCsutomer’) IS NOT NULL
PRINT ‘<<< CREATED PROCEDURE dbo.NorthWindGetCsutomer >>>’
ELSE
PRINT ‘<<< FAILED CREATING PROCEDURE dbo.NorthWindGetCsutomer >>>’
go
–GRANT EXECUTE ON dbo.NorthWindGetCsutomer TO commonuser
go
— WCF Service which create channel between external system and the SharePoint Farm. I will create a WCF Service From Scratch, which makes the concept more clear.
(1) Start a Class Library Project Name as northWindBCS Service.
(2) Add the Intrface as Service Contract
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
namespace EmployeeInfoSrv
{
[ServiceContract]
interface IEmployee
{
[OperationContract]
List GetEmployeeName(int name);
[OperationContract]
List GetEmployeeInformation();
[OperationContract]
List GetAllCustomer();
}
—- Model Calss as Data Cotnract
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.Runtime.Serialization;
namespace NorthWindLibraryService
{
[DataContract]
public class EmployeeInformation
{
public int ID { get; set; }
public string NAME { get; set; }
public string FAHTERNAME { get; set; }
public string CITY { get; set; }
public string Country { get; set; }
public string Address { get; set; }
public string Emal { get; set; }
}
————- Service as Database connectivity and Interface implementation
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Odbc;
namespace EmployeeInfoSrv
{
public class EmployeeService : IEmployee
{
public List GetEmployeeName(int Email)
{
try
{
SqlConnection objConnection = new SqlConnection();
DataSet ObjDataset = new DataSet();
DataTable Ta = new DataTable();
SqlDataAdapter objAdapater = new SqlDataAdapter();
SqlCommand objCommand = new SqlCommand(“asp_GetEmployee”);
objConnection.ConnectionString = “***************”;// System.Configuration.ConfigurationSettings.AppSettings[“ConnStr”].ToString();
objConnection.Open();
objCommand.Connection = objConnection;
objCommand.Parameters.AddWithValue(“@Email”, Email);
objAdapater.SelectCommand = objCommand;
objCommand.CommandType = CommandType.StoredProcedure;
objAdapater.Fill(Ta);
List _Employee = new System.Collections.Generic.List();
for (int i = 0; i < Ta.Rows.Count; i++)
{
EmployeeInformation _Obj = new EmployeeInformation();
_Obj.ID = Convert.ToInt32(Ta.Rows[i][0].ToString());
_Obj.NAME = Ta.Rows[i][1].ToString();
_Obj.Country = Ta.Rows[i][2].ToString();
_Obj.Emal = Ta.Rows[i][3].ToString();
_Obj.FAHTERNAME = Ta.Rows[i][4].ToString();
_Employee.Add(_Obj);
}
objConnection.Close();
return _Employee;
}
catch (Exception ex)
{
System.IO.StreamWriter d = new System.IO.StreamWriter(“C:\\axix.txt”);
d.WriteLine(ex.ToString());
return null;
}
}
public List GetEmployeeInformation()
{
try
{
SqlConnection objConnection = new SqlConnection();
DataSet ObjDataset = new DataSet();
DataTable Ta = new DataTable();
SqlDataAdapter objAdapater = new SqlDataAdapter();
SqlCommand objCommand = new SqlCommand(“getcustomer”);
objConnection.ConnectionString = “***************”;// System.Configuration.ConfigurationSettings.AppSettings[“ConnStr”].ToString();
objConnection.Open();
objCommand.Connection = objConnection;
objAdapater.SelectCommand = objCommand;
objCommand.CommandType = CommandType.StoredProcedure;
objAdapater.Fill(Ta);
List _Employee = new System.Collections.Generic.List();
for (int i = 0; i < Ta.Rows.Count; i++)
{
EmployeeInformation _Obj = new EmployeeInformation();
_Obj.ID=Convert.ToInt32(Ta.Rows[i][0].ToString());
_Obj.NAME = Ta.Rows[i][1].ToString();
_Obj.Country = Ta.Rows[i][2].ToString();
_Obj.Emal = Ta.Rows[i][3].ToString();
_Obj.FAHTERNAME = Ta.Rows[i][4].ToString();
_Employee.Add(_Obj);
}
objConnection.Close();
return _Employee;
}
catch (Exception ex)
{
System.IO.StreamWriter d = new System.IO.StreamWriter(“C:\\axix.txt”);
d.WriteLine(ex.ToString());
return null;
}
}
public List GetAllCustomer()
{
try
{
OdbcDataAdapter _da = new OdbcDataAdapter(); //using ODBC Adapter
DataTable Ta = new DataTable();
string _Command = string.Empty;
string _Param_value = string.Empty;
string _conectionString = string.Empty;
_conectionString = “DSN=MYODBCDriverName;uid=sharepoint;pwd=2010;”;//
string _CmdText = “exec NorthWindGetCsutomer'”;
_da = new OdbcDataAdapter(_CmdText, _conectionString);
Ta = new DataTable();
_da.Fill(Ta);
List _ActualList = new List(); //Moduel Class List
List _Employee = new System.Collections.Generic.List();
for (int i = 0; i < Ta.Rows.Count; i++)
{
EmployeeInformation _Obj = new EmployeeInformation();
_Obj.ID = Convert.ToInt32(Ta.Rows[i][0].ToString());
_Obj.NAME = Ta.Rows[i][1].ToString();
_Obj.Country = Ta.Rows[i][2].ToString();
_Obj.Emal = Ta.Rows[i][3].ToString();
_Obj.FAHTERNAME = Ta.Rows[i][4].ToString();
_Employee.Add(_Obj);
}
return _ActualList;
}
catch (Exception ex)
{
return null;
}
}
}
}
We build the class library,with the interface and the implementation.
After this we will add a new asp.net Empty Website which host our WCF Service on IIS,
Change the port and disable the dynamic port for the website.
Now we will add a new text file and name as .svc which is our wcf Service information.
open the .svc file and paste the following.
<%@ServiceHost Service=”EmployeeInfoSrv.EmployeeService”%>
EmployeeInfoSrv -> is the Namspace of my classlibrary
EmployeeService-> Is the class which implement the interface
Add the following content to the webconfig.
<system.web>
<compilation debug=”true” targetFramework=”4.0″ />
</system.web>
<system.webServer>
<modules runAllManagedModulesForAllRequests=”true” />
<directoryBrowse enabled=”true” />
</system.webServer>
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name=”MyServiceTypeBehaviors”>
<serviceMetadata httpGetEnabled=”true” />
<serviceDebug includeExceptionDetailInFaults=”false” />
</behavior>
</serviceBehaviors>
</behaviors>
<services>
<service name=”EmployeeInfoSrv.EmployeeService” behaviorConfiguration=”MyServiceTypeBehaviors”>
<endpoint address=”” binding=”wsHttpBinding” contract=”EmployeeInfoSrv.IEmployee” />
<endpoint contract=”IMetadataExchange” binding=”mexHttpBinding” address=”mex” />
</service>
</services>
</system.serviceModel>
EmployeeInfoSrv -> is the Namspace of my classlibrary
IEmployee -> is the interfce
EmployeeService-> Is the class which implement the interface
Now Host the Website on IIS, Now our WCF Service Will bring the Sybase data to our sp2010 list from external system and show in the external list.
Now open the Sharepoint Designer 2010, and Create a new external Content type and type the url of our WCF Service.
Define the WCF service url with the Endpoing, In the below screen shot I browsed the service with SharePoint Designer 2010.
Create the ReadList Operation. Use the below link to define or create BCS (Sharepoint2010) for External Content type.
How to: Create an External Content Type Based on a WCF Endpoint (http://msdn.microsoft.com/en-us/library/ee556831%28office.14%29.aspx)
this is the most and simple way to connect external system with Sharepoint 2010. there are also two more way with BCS to connect with external system, i.e. asmx service and .netassembly.
http://msdn.microsoft.com/en-us/library/ee556431%28office.14%29.aspx
Abdulazizfarooqi's Blog 