How to video: Access data on iSeries using ADO.NET

Here is a super simplified video example of a C# application reading from a table on an iSeries. It uses the IBM .net managed provider (See Different ways to access iSeries data and this) . You have this  if you have IBM Client Access. If not, download the technology preview here. Sound problem? jiggle the player control if the sound goes out (I'm trying out demo utility software let me know what one you use.)
.

To view the video click here.

To download the project used in the video, click here.

Subscribe to DotNetFluke to receive weekly useful tips for integrating .NET with the iSeries.
If you have any iSeries/.NET integrations questions or suggestions for articles email me at cbyrne+blog@AranRock.com

SQL Crib Sheet for iSeries and .NET

A friend was asking me for some SQL samples the other day so I dug up this old crib sheet from some time ago. These can be used with slight modification in both iSeries and .NET queries. Enjoy!

Select Statements

Return all records all columns in a table:

    select * from MyTableq3

Return all records but only cqcus and cqcush in a table:

    select cqcus, cqcush from MyTableq3

Return cqcus for all records in a table with a specific value for cqcush:

    select cqcus from MyTableq3 where cqcush=123

Return all records in a table where cqcus is one of three possible values:

    select * from MyTableq3 where cqcus in (value1,value2,value3)

Return the number of records in a table:

    select count(*) from TheTable

Return the number of records in a table with a specific value for cqcush:

    select count(*) from MyTableq3 where cqcush=123

Simple join:

    select * from MyTableq3, MyTable
    where MyTableq3.cqcus=MyTable .cqcusA

or

    select MyTableq3.cqcus, MyTable .cqcusA from MyTableq3, MyTable
    where MyTableq3.cqcush=MyTable .cqcusB

Select all unique values in cqcus from a table:

    select distinct(cqcus) from MyTableq3

or

    select distinct cqcus from MyTableq3

Select all unique values for cqcus from a table together with the number of records with that unique value:

    select cqcus, count(*) from MyTableq3
    group by cqcus

Select all unique values for combinations of cqcus and cqcush from a table together with the number of records with that combination:

    select cqcus, cqcush, count(*) from MyTableq3
    group by cqcus, cqcush

Select the number of unique values:

    select count(distinct cqcus) from MyTableq3

Select all duplicate records in a table, where two (or more) records are considered duplicates if they share a common value for a single cqcus:

    select cqcus, count(cqcus) from MyTableq3
    group by cqcus
    having count(*) > 1

Select all duplicate records in a table, where two (or more) records are considered duplicates if they share common values for a pair of cqcuss:

    select cqcus, cqcush, count(*) from MyTableq3
    group by cqcus, cqcush
    having count(*) > 1

Select similar records, i.e. all records which have duplicate cqcus and cqcush in a table but with different cqcus3 (i.e. specifying which cqcuss must be the same and which different):

    select * from table as A, table as B
    where A.cqcus=B.cqcus
    and A.cqcush=B.cqcush
    and A.cqcus3<>B.cqcus3;

Note:

    * It is important to specify at least one cqcus which is different between the two records otherwise this query will list a record as being the same as itself.
    * This query will not find duplicate records, i.e. records with every cqcus the same.

Select all records from a table which do not share a common ID with records from a second table:

    select * from MyTableq3
    where cqcus not in (select cqcush from MyTable )

Note:

    * Sub-queries are quite slow.
    .

An alternative using a join (which can be much faster):

    select MyTableq3.* from MyTableq3
    left join MyTable  on (MyTableq3.cqcus = MyTable .cqcush)
    where MyTable .cqcush is null;

The following method (which has been suggested by Michael Miller) is to use EXISTS. It is much faster on SQL Server than the above (but Michael says it is comparable with the left join technique on Oracle):

    select * from MyTableq3
    where not exists (select cqcush from MyTable  where MyTable .cqcush = MyTableq3.cqcus)

To perform a two way join:

    select * from
    MyTableq3 left join MyTable  on (MyTableq3.cqcus = MyTable .cqcus),
    MyTableq3 left join table3 on (MyTableq3.cqcush = table3.cqcus3)

this has been tested on SQL Server, but not on Oracle or MySql. It does not work with MS-Access.

To combine the results of two queries (be aware that the number and types of cqcuss in both queries must agree):

    select * from MyTableq3
    union select * from MyTable

To return a value based on the contents of a cqcus. This can be done using either Iif, Decode or Case, depending on the database.

The following works with MSAccess:

    select Iif(cqcus = 1, 'one', 'not one')
    from MyTableq3

This is equivalent to the following on SqlServer:

    select Case when cqcus = 1 then 'One' else 'Two' End

    from MyTableq3

Direct join to see if any records from table 1 match table 2
SELECT statement run complete.          
  select * from MyTableq3, MyTable        
    where cqcus=cfcus and  cqcush=cfcush
and cqvar = cfvar                      
and cqlv = cflv                        
and cqsizgrp = cfsizgrp                

For Oracle use the DECODE function.

To create a new table to hold the results of the select query:

    select * into MyTable  from MyTableq3

Be aware that this will fail if MyTable  exists, and that the new table will be created without any indexes.
Insert

Insert new record into a table:

    insert into MyTableq3 values (1,2,3)

Insert new record into a table explicitly naming cqcuss:

    insert into MyTableq3 (cqcus,cqcush,cqcus3) values (1,2,3)

Insert new record into a table using values from another table:

    insert into MyTableq3 (cqcus,cqcush,cqcus3)
    select cqcusA,2,cqcusC from SomeTable

Update

Update all records in a table:

    update MyTableq3 set cqcus=2

Update specific records in a table:

    update MyTableq3 set cqcus=2 where cqcus=1

To update more than one cqcus at a time:

    update MyTableq3 set cqcus=2, cqcush=3

Update a cqcus in a table using a value from another table where both records are referenced by a common key - warning, different databases support different syntax!

    This works in MS-Access and MySQL (5) but not in SQL Server:

    update TableOne
        inner join TableTwo on TableOne.commonID = TableTwo.commonID
        set TableOne.cqcus = TableTwo.cqcusX

or

    This works in MS-Access but not in SQL Server:

    update TableOne, TableTwo
        set TableOne.cqcus = TableTwo.cqcusX
        where TableOne.commonID = TableTwo.commonID

or

   

    update tableOne
    set tableOne.cqcus=tableTwo.cqcusX
    from tableOne, tableTwo
    where tableOne.commonID=tableTwo.commonID

 

 

Delete

Delete all records in a table (dangerous):

    delete from MyTableq3

Delete specific records in a table:

    delete from MyTableq3 where cqcus=value

Delete records from one table which do not have a matching cqcus in another table:

    delete from MyTableq3 where cqcus not in
    (select cqcush from TableTwo)

Keys

Be aware that there are often subtle syntax variations between different database systems. Also other key properties (for example 'clustered') will vary between database systems. Therefore please treat this part of the SQL crib sheet as a guide only.

Create a primary key on a table:

    Alter Table TheTable Add Primary Key (cqcus, cqcush)

To add an index on a cqcus:

    alter table MyTableq3 Add Index (cqcus)

To remove a primary key:

    alter table drop primary key

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!
   

Where to get the iSeries ADO.NET data provider

The iSeries .NET data provider is the native data provider that allows you to access iSeries data and programs using .NET in a managed way.

This is a .dll (IBM.Data.DB2.iSeries.dll ) that you add in as a reference when creating .Net apps to connect to the iSeries

Once added in you have access to all the classes and methods that simplify writing applications in .Net to leverage iSeries data in the db2 database

The dll is part of iSerices Access for windows which you can download for free (when you select the technology preview). Make sure you check 'Selective Setup' and include the Data and Programmer's toolkit so you get all the data access drivers, help and tools.

1. Download iSeries Access from http://www-03.ibm.com/systems/i/software/access/windows/downloads.html
   Select the technology preview version. You'll have do the register rigmarole.
   If the link doesn't work, google 'iSeries Access windows' to find the latest location.
   


2. Install iSeries Access making sure that you select 'custom install' to include the programmers toolkit (it comes in handy as it has examples etc.) . Once you have installed iSeries Access you will find the dll in C:\Program Files\IBM\Client Access\IBM.Data.DB2.iSeries.dll
   


3. In Visual Studio, open up your project. Click on the Solution Explorer, then references. Right click references and select 'Add Reference'. A dialog box appears. Click 'Browse'. Add in the dll from the location above.
   


4. Add the namespace '
   using IBM.Data.DB2.iSeries;' to your code


5. Start using the iSeries classes! The iDB2Command is what you use to run queries, IDB2Connection to create a connection string to your iSeries db, iDB2DataAdapter gives you a cache to both connect, retrieve and update a set of data.

With the native .NET provider you have support for:

• SQL (INSERT, UPDATE, DELETE, SELECT)
• Stored procedure support
• Commitment control
• Connection pooling
• SQL naming,Unicode, Threads and multiple databases (IASPs)

To call iSeries programs use a stored procedure.

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

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