I was given the requirement to pull information from a Sybase Database to cross reference and ID. I’ve had a similar requirement in pulling data from a SQL stored procedure, so this was pretty similar.
First I used the Sybase.Data.AseClient.dll and the sybdrvado11.dll provided by Sybase as part of their client install. Both of these .dll’s need to be installed into your ..\Program Files\Microsoft BizTalk Server 2006\ folder.
I created a VB.NET class that contains the function that brings in the ID I needed to cross reference and the connection string to the Sybase database.
The connection string is in the format Data Source=<IP Address>;Port=<Port Number>;Database=<Database Name>;UID=<UserName>;PWD=<Password>;Max Pool Size=50;Min Pool Size=5″
Here is the VB.NET class with accompanying comments.
Imports System.Data
Imports Sybase.Data.AseClient
Imports System.Xml
Public Class DatabaseConnectionClass
Public Function ReturnNEWID(ByVal OLD_ID As String, ByVal ConnectionString As String) As String
‘Create Connection
Dim conn As New AseConnection(ConnectionString)
‘Open a Connection to the Database
conn.Open()
‘Create the SQL Statement
Dim sql As String = “SELECT Field from TABLE WHERE ID= ‘” + OLD_ID+ “‘”
‘Retrieve the Data
Dim cmd As New AseCommand(sql, conn)
‘Return the Data Reader Object
Dim reader As AseDataReader = cmd.ExecuteReader()
‘Populate the Return Value
While reader.Read()
Return reader.GetString(0)
End While
‘Close the DataReader and Connection objects
reader.Close()
conn.Close()
End Function
End Class
I signed the class with a strongly named key, built and GAC the class, and referenced it as an external assembly.
When I test the map it correctly pulls from the Sybase database.