JG Vimalan's Blog

It is all about coding!

Connect to DB2 database using Enterprise Library 5.0 – Data Access Application Block

In this post, I will show you how to connect to DB2 database using Enterprise Library 5.0 – Data Access Application Block. The code snippets are developed using C#, ASP.NET and Visual Studio 2010.

Step 1:

Install Enterprise Library 5.0 and IBM Db2 Client (I am using 9.7 fix 6) in your development system.

Step 2:

Add reference to Microsoft.Practices.EnterpriseLibrary.Data in your project.

Step 3:

In the web.config file, add the connection string for target DB2 database,

<connectionStrings>
<add name=”DB2Conn” connectionString=”Dsn=CustomerDb2;uid=abcdefg;pwd=tempassword”
providerName=”System.Data.Odbc” />

Step 4:

In the app settings, add the Db2 schema name as shown below,

<add key=”DB2SchemaName” value=”DevSerDb2″ />

In my case, the schema name is DevSerDb2.

Step 5:

In my Utility class, I added properties for accessing the values from Web.config file as shown below,

public class Utility
{

internal static string GetDB2SchemaName
{
get { return ConfigurationManager.AppSettings[“DB2SchemaName”]; }
}

internal static string GetDB2ConnectionName
{
get { return string.Format(“DB2Conn”); }
}

Step 6:

In your data access class, add reference as shown below,

using Microsoft.Practices.EnterpriseLibrary.Data;

Step 7:

I have a stored procedure in Db2 which accepts 2 parameters and returns multiple rows of records. The code snippet for connecting to it and retrieving the values is as follows,

public DataSet Search(string userId, string firstName)
{
DataSet resultSet = null;
StringBuilder sql = new StringBuilder(“Call “).Append(Utility.GetDB2SchemaName).Append(“.SP_CUSTOMER_LIST(?,?)”);
Database database = DatabaseFactory.CreateDatabase(Utility.GetDB2ConnectionName);
using (DbCommand dbCommand = database.GetStoredProcCommand(sql.ToString()))
{
OdbcParameter userIdParam = new OdbcParameter(“@userId”, OdbcType.VarChar, 24);
userIdParam.Direction = ParameterDirection.Input;
userIdParam.Value = userId;
dbCommand.Parameters.Add(userIdParam);

OdbcParameter firstNameParam = new OdbcParameter(“@firstName”, OdbcType.VarChar, 24);
firstNameParam.Direction = ParameterDirection.Input;
firstNameParam.Value = firstName;
dbCommand.Parameters.Add(firstNameParam);

resultSet = database.ExecuteDataSet(dbCommand).Tables[0];
}
return resultSet;

}

Advertisements

August 28, 2012 - Posted by | C#.NET, DB2, Enterprise Library 5.0

No comments yet.

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

%d bloggers like this: