Connecting IBM DB2 Database from ASP NET C# application

Connecting to IBM DB@  can be done by OleDbConnection

Using System.Data.OleDb

Public Collection<TestObject> GetTestData(sampleInput)

{

  string testQuery = GetTestQuery(sampleInput);

  DataSet testDataSet = new DataSet();

  string connectionString = GetConnStringFromConfig();

  using(OleDbConnection connection = new OleDbConnection(connectionString))

{

     try

{

      OleDbParameters[] parameters = null;

      List<OleDbParameters> parametersList = GetParameterList(sampleInput);

      if(parametersList != null && parametersList.Count >0)

 { 

            parameters = parametersList.ToArray();

            DB2Helper.FillDataSet(connectionString,CommandType.Text,testQuery,testDataSet,null,parameters);

 }

        else

        {

         DB2Helper.FillDataSet(connectionString,CommandType.Text,testQuery,testDataSet,null,null);

        }

        Datatable testDataTable = testDataSet.Table[0];

}

     catch(OleDbException ex)

     {

       EnterprizeLogging.Logger.Logerror(ex,ModuleName);

     }

   }

}

public string GetTestQuery(SampleInputType sampleInput)

{

  StringBuilder testQuery = new StringBuilder();

  testQuery.Append(" Select Id,Name,City From sampleTableName ");

  int usedParameterCount = 0;

  if(sampleInput.Id != 0)

  {

    if(usedParameterCount == 0)

    {

     testQuery.Append(" Where Id = ? ");

    }

    else

    {

      testQuery.Append(" and Id = ? ");

    }

    usedParameterCount ;

  }

  if(!string.IsNullOrEmpty(sampleInput.Name))

  {

    if(usedParameterCount == 0)

    {

     testQuery.Append(" Where Name = ? ");

    }

    else

    {

      testQuery.Append(" and Name = ? ");

    }

    usedParameterCount ;

  }

  if(!string.IsNullOrEmpty(sampleInput.City))

  {

    if(usedParameterCount == 0)

    {

     testQuery.Append(" Where City = ? ");

    }

    else

    {

      testQuery.Append(" and City = ? ");

    }

    usedParameterCount ;

  }

public List<OleDbParameters> GetParameterList(SampleInputType sampleInput)

{

  List<OleDbParameters> parameterList = new List<OleDbParameters>();

  if(sampleInput.Id != 0)

  {

    parameterList.Add(DataHelper.CreateParm("@Id",DbType.Int32,ParameterDirection.Input,sampleInput.Id));

  }

  if(!string.IsNullOrEmpty(sampleInput.Name))

  {

    parameterList.Add(DataHelper.CreateParm("@Name",DbType.String,ParameterDirection.Input,sampleInput.Name));

  }

  if(!string.IsNullOrEmpty(sampleInput.City))

  {

    parameterList.Add(DataHelper.CreateParm("@City",DbType.String,ParameterDirection.Input,sampleInput.City));

  }

  return parameterList;

}

Write This function in DataHelper Class :

internal static OleDbParameter CreateParm(string parmName,OleDbType type ,ParameterDirection direction, Object value)

{

  OleDbParameter idParm = new OleDbParameter();

  idParm.ParameterName = parmName;

  idParm.Direction = direction;

  idParm.Type = type;

  if(value == null)

  idParm.Value = DBNull.Value

   else

  idParm.Value = value

  return idParm;

}