Pulling data from DB2

[ctsmith84]

Hello, I'm hoping that someone with experience with programming against DB2 can help a new programmer down the correct path.

I've been given a project that requires me to pull data from tables in the companies DB2 database, and when all said and done have that data placed into an excel workbook. I don't have the DB2. NET Data Provider on my machine, and it doesn't appear that I can get it. Due to the nature of the company (financial institution), they would rather the application not use LINQ or anything like that. They prefer for us to write a query (using a .qry text file) and have the application read in that file, which would be located somewhere on the LAN.

Now, I understand (and can get help from co-workers) how to write the SQL part, and read it in. I am just sort of lost on how to A.) connect to and get the data out of DB2, and B.) systematically put it into an Excel workbook.

There have been some similar applications written against the DB2 that I can look at, but unfortunately they were written as Macros using VBA in Excel. They have brought me in as an intern because I am studying C# right now at school, and they want to start moving towards having programs being developed in .NET.

I have experience using the Microsoft Office Interop for Excel to automate Excel, so if I can learn how to make the extracted DB2 data into an object that can be "pasted" into Excel some way, that would work just fine.

Anyone out there that has experience programming against any sort of DBMS could probably point me in the right direction (either by explaining, or showing me where some tutorials are) as from what I understand, they are all pretty similar in this sense...and ANY help would be greatly appreciated!

[ctsmith84]

To clarify, I do have access to .NET Data providers (OLE DB and ODBC), just not the DB2 specific data provider.

I'm looking for help on the syntax, etc. to connect to our database using one of the data providers I do have (OLE DB or ODBC). And I need to be able to read the SQL commands in via a text or .qry file, and put the resulting records into an Excel file.

[cudahead]

I have no experience with DB2 from .NET, but I found some promising infos:

1) from http://www.codeproject.com/KB/database/DotnetDb2.aspx
a quite realistic looking connection string:

OleDbConnection cn = new OleDbConnection(
  "Provider=IBMDA400.1;Data Source=TEST;User ID=db2user;" + 
  "Password=db2user;Default Collection =SAMPLEDB");

2) from http://csharp.net-informations.com/dataadapter/selectcommand-oledb.htm
a snippet how to execute simple sql commands with a OleDbConnection object:

string connetionString = null;
   OleDbConnection connection ;
   OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
   DataSet ds = new DataSet();
   int i = 0;
   connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;";
   connection = new OleDbConnection(connetionString);
   try
   {
      connection.Open();
      oledbAdapter.SelectCommand = new OleDbCommand("Your SQL Statement Here", connection);
      oledbAdapter.Fill(ds);
      oledbAdapter.Dispose();
      connection.Close();
      for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
      {
         MessageBox.Show (ds.Tables[0].Rows[i].ItemArray[0].ToString());
      }
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.ToString());
   }

[ctsmith84]

Thanks cudahead. I'm about to give both of these a run right now, and see what comes of it.