LINQ to DB2 Beta available tomorrow

 

As you probably know the geeks at IBM have been scrambling to put together a LINQ to DB2 Entity framework ever since LINQ was announced - and by jove they've done it! 
You know what LINQ is right? It allows you to query data in C# and refer to columns in tables  by their column names directly just as you would any field in your C# program. Yes I know, this seems like an uber basic requirement for any language but at least it is done.
Initially LINQ had only SQL, XML and in-memory fields were supported but IBM, Oracle and MySQL quickly started getting in on the act to support their dbs.
We've learned here at Dot Net Fluke that IBM will announce tomorrow (May 6th) a beta of the LINQ to DB2 beta. We'll post the link when it comes available.
The bad news is that it is not yet available for the iSeries. So we'll all just have to sit and wait!

Update: Here's the announcement IBM Announces LINQ to DB2 connector

Different ways to access iSeries data

Remember, you have 4 main methods to access DB2 for iSeries from .NET programs:

1. The ADO.NET managed provider for iSeries Access for Windows, IBM.Data.DB2.iSeries. This is probably your best bet for iSeries data connectivity. Get info on it here
   and....
2. ODBC: The Microsoft-supplied ODBC bridge provider using the iSeries Access forWindows ODBC driver for underlying database connectivity. Microsoft.Data.Odbc
3. OLE DB: The Microsoft-supplied OLEDB bridge provider using one of the iSeries Accessfor Windows OLE DB providers for underlying database connectivity. System.Data.OleDb This is an unmanaged method.
4. The DB2 for Linux, UNIX, and Windows (LUW) managed provider implemented by IBM software group.

Managed vs. Unmanaged

The managed provider is superior in terms of controlling actions to the DB from within your application. Managed means managed by .net - the drivers to access the iSeries are compiled into a .Net assembly. Unmanaged means that it is outside the .Net assembly - an .exe for example.
When you use OLE DB you are using an unmanaged provider so there is an extra step that has to occur from .net to the iSeries such as a bridge between the two. You should experience better performance when using the .Net managed provider.

Here's an overview of the IBM .net managed provider (from IBM)

I highly recommend reading the IBM redbook ' Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET' from which the above is summarized. 'You can download it at http://www.redbooks.ibm.com/abstracts/sg246440.html

Calling a program on an iSeries with .net

Here's an easy way to call a program on the AS/400 with .net. What's neat about this function is that it also accepts a return argument.

Requirements:

1. Client Access
   This method uses the Client Access library so you will need to have Client Access installed on your PC. Specifically you need cwbx.dll which is in the Iseries access folder C:\Program Files\IBM\Client Access\Shared
   Add this into your Visual Studio project as a reference
2. The host server on the iSeries must be started using STRHOSTSVR SERVER(*ALL)
   Copy the code below into Visual Studio
   
   
   
   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.IO;
   using cwbx; // cwbx.dll is in the Iseries access folder C:\Program Files\IBM\Client Access\Shared
   // YOu must add it in to you project; Click on your references folder in solution explorer to add it in.
   
   namespace CallAS400pgm
   {
   class Program
   {
   
   
   static void Main(string[] args)
   {
   
   // Modify the following for your own iSeries
   
   string AS400Name = "192.168.0.1"; // Change this to the IP address of your machine
   string AS400User = "USER"; // User name to sign on to AS/400
   string AS400Password = "password"; // Password used to sign on to the AS/400
   string AS400Pgm = "DOTNET1"; //Name of program you wish to call on the AS/400
   string AS400Lib = "QGPL"; // Name of library where the program is located
   
   
   Console.WriteLine("Creating AS/400 object....");
   
   cwbx.AS400System AS400 = new cwbx.AS400SystemClass(); // creates an as/400 object
   cwbx.Program program = new cwbx.Program(); // Create a program object
   
   AS400.Define(AS400Name); // IP of AS/400
   
   program.system = AS400;
   program.system.UserID = AS400User; // Your user name
   program.system.Password = AS400Password; // Your password
   
   // define the name of the program you want to call on the iSeries
   program.LibraryName = AS400Lib; //Library where your program is located
   program.ProgramName = AS400Pgm; // Program that this app will call
   
   // NOTE: before you sign on, the host server on the iSeries must be started using STRHOSTSVR SERVER(*ALL)
   Console.WriteLine("Signing on to " + AS400Name);
   AS400.Signon();
   AS400.Connect(cwbcoServiceEnum.cwbcoServiceRemoteCmd);
   
   if (AS400.IsConnected(cwbcoServiceEnum.cwbcoServiceAll) == 0)
   {
   Console.WriteLine("Not connected");
   }
   else
   {
   ProgramParameters parms = new ProgramParameters(); // must create parameter collection
   // parms.Clear(); // if you have no parm use this statement
   
   // Define the parms you are sending and receiving from the iSeries pgm
   parms.Append("MsgToAS400", cwbrcParameterTypeEnum.cwbrcInput, 30); // // Input parm called 'MsgToAS400;
   parms.Append("ReplyFromAS400", cwbrcParameterTypeEnum.cwbrcOutput, 30); // create a parameter object name, type & length
   
   // puts a value into the parameter object
   StringConverter strcon = new StringConverterClass();
   strcon.Length = 30;
   parms["MsgToAS400"].Value = strcon.ToBytes(" This is from a dot net pgm, hi");
   
   
   try
   {
   Console.WriteLine("Sending a message to the iSeries....");
   
   Console.WriteLine("Calling program on the AS400....");
   program.Call(parms); // Runs until job is completed
   
   // Get the return value from the ISeries pgm
   
   String reply = strcon.FromBytes(parms["ReplyFromAS400"].Value);
   Console.WriteLine(reply);
   //This program dotnet1 is called on the iSeries. dotnet1 has 2 parameters
   // it takes the first and displays it to a user
   // it then sends back a message to this class in ReplyFromAS400
   
   Console.ReadLine();
   
   
   }
   catch (Exception e)
   {
   foreach (Error error in AS400.Errors)
   {
   Console.WriteLine(error.ToString());
   }
   
   throw;
   }
   
   }
   
   
   }
   }
   }