Uncategorized

Connect SharePoint 2010 with Sybase Database, Using ODBC Drivers in SharePoint 2010, Connect Sybase with Sharepoint2010


In my recent task i connect the SharePoint with the Sybase database, to work further with workflow on that data. Basically I have to create work flows on data, which can help us to boost the business process. To highly available data I created a one web part which can take data and create list. I Google about the business data connectivity service in SharePoint but I found that on msdn External systems that Microsoft Business Connectivity Services can connect to include SQL Server databases, SAP applications, Web services (including Windows Communication Foundation Web services), custom applications, and Web sites based on SharePoint. By using Microsoft Business Connectivity Services, you can design and build solutions that extend SharePoint collaboration capabilities and the Office user experience to include external business data and the processes that are associated with that data.  http://technet.microsoft.com/en-us/library/ee661740.aspx, I also found some business connectivity connector. I tried it with some generic process which can update list according to the data get populated in the tables. I connect to the Sybase database using ODBC connection string.  Organization where we have Sybase installed on the servers has two main thing which help us to get detail about the server and their connection.

(1)    On run type dsedit, it describe about the servers and their IP and configuration. 

dsedit sybase

dsedit sybase

(1)    We can connect to syabse through ODBC DSN. Same as I did in this web part.

Sybsae Server Configuration

Sybsae Server Configuration

First I created a ODBC DSN which can help ODBC driver to connect database and fetch data to SharePoint 2010 List.

As we already know that SharePoint Server is based on x64 bit so we need to install the update sybase client (15 x64) bit which provides the x64 bit ODBC drivers. In 64 bit windows server we have two location for ODBC Drivers, (1)  c:\Windows\System32\odbcad32.exe which is 32bit odbc driver and (2) C:\Windows\SysWOW64\odbcad32.exe which having x64 bit ODBC drivers. So Kindly whenever you going to connect with sybase in sP2010, use the second option in your customer solutions.

Let move towards the SharePoint, remember that we have a DSN name (SybaseDSN) this help us to make connection string to Sybase. With the help to Dsedit we can check the ip and name of the Sybase Server and give it to DSN properties.

In this lab, visual web part will help us to create a new connection to Sybase and fetch data, create list and update the data to the list as required. I took the visual web part due to make it more generic. In the visual side, I asked the DSN name, userId of database, then I took the fields on which dynamic list will be created as required, that  fields will be the part of newly created list that will take data and update itself in the site collection.

In the Webpart[name].cs file add the following namespace to use .net built in classes.

using System.Data;
using System.Data.Odbc;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
Paste below code above the page_load in webpart file
public DataTable _Table;
public string _status;
Now add below code on page_load.
for make it easier we create our connection like that, by using odbc connection.

string constr = “DSN=Sybbase_DSN_Name;uid=sa;pwd=sa;”;
OdbcConnection DbConnection = new OdbcConnection(constr);
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = “SELECT top 100 *,Table order by Table_ColumnName desc “;

OdbcDataAdapter Odb_Adapter = new OdbcDataAdapter();
Odb_Adapter.SelectCommand = DbCommand;

_Table = new DataTable();
Odb_Adapter.Fill(ta1b);

// Take on lable which can show the filed on webpart page.

for (int i = 0; i < ta1b.Rows.Count; i++)
{
for (int j = 0; j < ta1b.Columns.Count; j++)
{
Lbl_Status.Text += ta1b.Rows[i][j].ToString() + “-“;
}

}
Up to this point we have connect to our Sybase database using ODBC connection, in SharPoint2010.
Drag a button on visual webpart, which create our list if it is not exist in the site collection with following code.
SPWebApplication webApp = SPWebApplication.Lookup(new Uri(SPContext.Current.Web.Url.ToString()));

foreach (SPSite site in webApp.Sites)
{
try
{
using (SPWeb web = site.RootWeb)
{
// PrintWebAndListsRecursive(web, 0);
try
{
string _ListName = “DP Accounts”;
//represent the list in sharepoint website//TryGetList Return the splistcollection
if (CheckList(web, _ListName) == “0”)
{
CreateList(web, ta1b);
_status = “Dp Account List Has Created!!!”;
lblError.Text = _status;
}
else
{

_status = “Dp Account Is Already there”;
lblError.Text = _status;
return;
}

//calling the method to creaet list

}
catch (Exception ex)
{
}
}
}

finally { site.Dispose(); }
}

//add this method to check the list name , requested by the user in this webpart.
static string CheckList(SPWeb web, string ListName)
{
string _Value = “0”;
foreach (SPList list in web.Lists)
{
if (ListName == list.Title)
{
_Value = “1”;
}
}
return _Value;
}

Now add the below code for creating list and update the list with the Sybase data.

protected void CreateList(SPWeb subweb, DataTable _Data)
{
//Creating the List

#region CreateList//add list to the SpWEb which we created on button click

Guid jobdefguid = subweb.Lists.Add(“DP Accounts”, “DP Account From Phoenix”, SPListTemplateType.GenericList);//this will return the unuiqe guid of list.//now adding column to the list we created.

SPList JobDef = subweb.Lists[jobdefguid];
JobDef.Fields.Add(“ListFieldName”, SPFieldType.Text, false);
JobDef.Fields.Add(“ListFieldName “, SPFieldType.Text, true);
JobDef.Fields.Add(“ListFieldName “, SPFieldType.Text, false);
JobDef.Fields.Add(“ListFieldName “, SPFieldType.Text, false);
JobDef.Fields.Add(“ListFieldName “, SPFieldType.Text, false);
JobDef.Fields.Add(“ListFieldName “, SPFieldType.Text, false);
JobDef.Fields.Add(“ListFieldName “, SPFieldType.Text, false);
JobDef.OnQuickLaunch = true;
JobDef.Update();
#endregion
#region Reading From DataTable and Insert in to List

for (int i = 0; i < _Data.Rows.Count; i++)
{
SPListItem newDef;
newDef = JobDef.Items.Add();
newDef[“ListFieldName “] = _Data.Rows[i][“columnname”].ToString();
newDef[“ListFieldName “] = _Data.Rows[i][“acct_ columnname “].ToString();
newDef[“ListFieldName “] = _Data.Rows[i][” columnname _dt”].ToString();
newDef[“ListFieldName “] = _Data.Rows[i][” columnname “].ToString();
newDef[“ListFieldName “] = _Data.Rows[i][” columnname “].ToString();
newDef[“ListFieldName “] = _Data.Rows[i][” columnname “].ToString();
newDef[“ListFieldName “] = _Data.Rows[i][” columnname “].ToString();
newDef.Update();
}
#endregion
}

Above code helping to connect with Sybase Database Server, fetch data ,create list and update the data.

Standard

6 thoughts on “Connect SharePoint 2010 with Sybase Database, Using ODBC Drivers in SharePoint 2010, Connect Sybase with Sharepoint2010

  1. Great says:

    An fascinating discussion may be worth comment. I feel you should write on this topic, it may definitely be a taboo subject but usually folks are not enough to dicuss on such topics. To a higher. Cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s