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

C# Subfile - iSeries data in a C# Grid -2 no coding (with Video)

Well almost no coding.

If you look at my previous post where I showed how to code a grid in C# using data from the iSeries we manually coded the grid, the data set and the connection. This example accomplishes the same result except you'll be done in under 5 minutes!  It uses the IDE to build the grid, data set, adapter, connection and SQL command.

The video also shows how to add in the iSeries .net data components to your Visual Studio toolbox.
This post easier shown than discussed - so check out the following video.

Steps

1. Create a windows form application project
2. Add in the IBM dll to your references
3. Add in the IBM data tools to your toolbox using Tools, Choose Toolbox items and filter 'idb2'
4. Add a basic grid to your form
5. Drag the iDB2Connection, IDB2Command, iDB2DataAdapter and a data set to your form
6. Configure each by right clicking and selecting properties
7. Double click outside the grid to bring up the code for the Load method of the form.
8. Add in the following code
   iDB2DataAdapter1.Fill(dataSet1);
   dataGrid1.DataMember = dataSet1.Tables[0].TableName;
9. Run the program!
   

RPG Variables vs C# Variables

In RPG we define variables in our 'D or C Specs.  We don't have to  uniquely declare character or numeric-  just the inclusion of a value in the decimal places column is enough. Leave it out and you just defined a character variable.
For the most part this is all you need to write 90% of business applications. There are other variable types such Binary, Graphic etc but these are encountered less often.
 

DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords++

D NameofDog       S             24              
D Counter         S              2  0  
D  Price          S              5  2           
D Datefield       S               D             

Here's the equivalent in C#

String NameofDog;
int Counter;
decimal Price;
DateTime datefield;

The first thing that you will notice is that there is no length defined for the fields in C#. That's a great improvement over RPG where you usually explicitly declare length and end up in lots of trouble when a value gets chopped off when moved to a smaller field.

In C# there are way more value types than needed such as byte (for numbers 0-255), sbyte, short, long, float etc.





Common Value Types in C#


Int - integer - whole numbers (no decimals). Useful for counting.


string - holds alphanumeric values


bool - true or false (similar to indicators)


double any decimal up to 15 significant digits


decimal - any decimal up to 28 sig. digits

The variable declarations in RPG did not quite translate to C#. There is no Date value type in C#.  The statement ' DateTime datefield;' declares an object reference called datefield. How do I know this? Only because I know there is not value type of date in C#.  The fact that I am declaring an object called datefield just as I would a variable value brings up all sorts of interesting things about C# - mainly that objects are just another kind of variable. More on that later.

What about Operations on variables?


Let's add 1 to Counter in RPG and C#

RPG

Counter = Counter + 1;

C#

Counter = Counter + 1;

The code is the exact same in both!  There are other differences but we'll get operations in another post.




Lot's of free stuff inside


When you declare a variable in C# whether it is a value type like an integer or a 'reference' type like the DateTime object I created, along with the variable comes packaged  actions you might need on it.  In RPG if I want to convert my counter to a string variable, I would do the following.





RPG


Character = %char(Numeric); 



In C#, there is a method or function included in the variable when you declare it. The brackets indicate that ToString is a method.

Character = Numeric.ToString();

This is a fundamental and important aspect of C# - The actions you perform on variables and objects are often methods of that variable or object. If you want to trim the end of a string in C# you just say Character.Trim(); . Because you declared Character as a type String and String has all these methods now Character has them. There is no 'trim' opcode or expression. It's a method of string and when you declare a variable as a type string it gets all the methods belonging to string.

What's great about this is that you don't have to wait 10 years for IBM to come up with another op-code - you just write one yourself!  But, you may be asking, how do I know what method to use? There must be thousands. There are. However the Visual Studio IDE makes it simple and has a feature called intellisense - it fills out the statement as you type showing a drop down of all the methods and properties of an object or variable. Don't worry if this doesn't make a huge amount of sense right now. You'll get the hang of it with practice but it is one of those kind of mind-flips that is a big switch from Procedural RPG and Object Oriented C#.  It also makes sense. While RPG is an awesome language it has hit a stone wall. Creating lots of more opcodes or expressions is just going to make the language more complex. C# doesn't need lots of 'op-codes' - in fact there are about 77 in C# 2.0 and many you will never use. Most actions come from methods in classes.

RPG Subroutine = C# Method?

Someone asked me the other day if RPG subroutines are the same as C# methods.
The answer is yes - like C# static methods but RPG Procedures are even more similar to C# static methods.
The reason being is that while C# methods are discrete named blocks of code you can call from within a program (C# Class) - just like an RPG Subroutine; C# methods allow parameters in the call - just like RPG procedures do. Methods have lots of other features that I’ll get in to but first lets draw comparisons.
Here’s an example:
Both applications read a table called ‘People’ and print out the name of each person on the console.
They both call a subroutine/method called Get_People that takes no parameters from the main block of code.

RPG
Fpeople IF E K DISK
/FREE
exsr Get_People ;
*inlr = *on;

BegSr Get_People;
read People ;
dow not %eof ;
Dsply name ;
Read People
Enddo;
Endsr;


C#

using System;
using System.IO;
namespace ConsoleApplication1
{
class ProgramClass
{
static void Main() // Main block of code 'Main' must be in the class
{
get_People();
}
static public void get_People( )
{
using (FileStream fileStream = new FileStream(@"C:\csharp\people.txt",
FileMode.Open,
FileAccess.Read,
FileShare.None))
{
using (StreamReader streamReader = new StreamReader(fileStream))
{
string text = streamReader.ReadLine();
while (text != null)
{
Console.WriteLine(text);
text = streamReader.ReadLine();
}
}
}
Console.ReadLine(); // Pause the screen
}
}
}

As you can see, RPG is easier to read and less verbose than its C# counterpart. To be fair DB2 is part of the iSeries operating system so all the file handling is already part of the DB.

Remember these are Static methods shown in C#. A static method in C# is where there is only one instance of the method. Believe it or not you can multiple instances of a method in C#. These are called instance methods and are used more often than static methods. I'll get into the difference but the purpose of this post is to show some similarity between RPG and C# first.

C# for iSeries RPG programmers

There are many iSeries RPG programmers out there who are keen to get started on another programming language that matches the flexibility and scope of RPG with the added web, windows and forms simplicity available in modern IDE's.
Which language will you invest your valuable time in? Java, PHP, Visual Basic, Delphi, Ruby, C#?


Java is a good, mature candidate since it also runs on the iSeries. Ruby is open and emerging as easy to learn, productive and object oriented. Delphi (Object Pascal) has been around for donkey's. PHP is mainly used for scripting and web pages so only server partial needs. Visual Basic and C# are just variations of Microsoft's powerful .NET platform but C# programmers get paid more. For someone still on the iSeries you will want a language that has excellent connectivity to the black box. That leaves Java and C#. C# has Linq! and is far easier than Java at creating windows desktop applications but Java does run on the iSeries. So it's a toss up but my friends, Linq pushes the argument to C#.

Let's look at C#.
C# (like Java) can be hard to read, is full of abstruse concepts such as polymorphism, inheritance and encapsulation. Five lines of RPG could be one line in c# and one line in RPG could be five in c#.
It's Geeky. At first glance code will appear to operate your vacum cleaner but will just read a file and print its contents. It gives you lots of ways to accomplish the same thing. Lots.
It's not simple. It's not easy. It's a pain in the ass.
It's a great language.

I recommend that RPG developers dive in to c# despite the challenges. The best way to deal with challenges is, of course, to ignore them. Yes, run away. Start writing code c# just as you would in RPG. In other words I am advocating to begin your next language which is built specifically for object oriented (OO) development to start using it as a functional procedural language. Write short little programs to display iSeries data in grids for the web, for windows, for handhelds. Think of c# as RPG for the desktop.
You may well be persecuted by OO idealogues who are (bitterly) passionate about the 'right ' way to code. They like 'coding'. RPG programmers like 'going home at 5'. They really couldn't give a shite. RPG developers are for the most part business oriented types who create software to solve real life business situations.

As time passes you'll find that you'll write more complicated code and will need to start using object oriented principles.Keep your eyes on this blog for step by step instructions for writing c# programs for use against the iSeries data and legacy applications and together we will work up to object oriented development beginning with procedural C# - let's call it RPG#.

Using .NET Linq with the DB2 - release date?

Though IBM has pledged to provide Linq to access DB2 programmatically within C# - it doesn't exist yet. In fact, Linq to SQL only works for Microsft SQL. You can do a backdoor version by using an OLEDB linked server in SQL Server for example. IBM won't tell us when Linq can access DB2 so keep an eye out at their Developer Works site at http://www-128.ibm.com/developerworks/wikis/display/DB2/DB2+and+.NET+FAQ

Magic Chewing Gum

OK I'm going to tell you something really cool. If nothing else this should be the reason to switch to c#... LINQ or Language Integrated query.
Technically LINQ is magic chewing gum created by Celtic druids 5,000 years ago out of the residue at the bottom of guiness barrels to bring all the warring tribes of Ireland together. Fionn McCool tried it and became king. At the annual spitting contest he spat a wad so far that it exploded through a worm hole ending up on Anders Hejlsberg's desk in Redmond as a USB stick of Juicy fruit. Once Anders plugged the magic chewing gum into his PC he was able to incorporate LINQ into .Net and this virtual gum is now able to stick together disparate pieces of data from any source whatsoever.

With LINQ C# is now a juicy data oriented language.
The problem we face today is a multitude of different types of data such as Microsoft excel spreadsheets, the e-mail messages, xml, databases, queues, registries, blah blah blah. With each of these comes their own awful object model. You have to learn all these terrible APIs and access methodologies like a dope which is another big turnoff with these multiplatform development systems like .net. With LINQ there is now a single way to access any type of data. You can query iSeries data, arrays, xml all in the same way without sacrificing what you can do with all these types of data. Another plus is that when you compile your code with LINQ statements they are checked for the correct type of field and objects. You know this in RPG when you try to compile a program without defining the field that you are using.
I know you're probably thinking that ODBC does the same thing because you can use SQL statements to access different types of databases but it doesn't check the types at compile time and it only works for relational databases. LINQ has turned c# into a real language. It makes it more of a data language. It makes it cool.

Here's a simple example:

Linq Where selection

You can only have a beer if you're over 21. The query expression creates a new sequence of numbers that satisfy the selection criteria. The for each section iterates over each element in this new sequence and prints its value.

public void AllowedToDrink() {
int[] numbers = { 25, 1, 17, 44, 20, 9, 81, 26, 37, 12, 0 };

var lowNums =
from n in numbers
where n > 20
select n;

Console.WriteLine("These People Can Have a Beer:");
foreach (var b in lowNums) {
Console.WriteLine(b);
}
}

Result

Numbers > 21 :
4
1
3
2
0

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;
   }
   
   }
   
   
   }
   }
   }