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

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!
   

C# Subfile - Display an iSeries table in a Grid

We're all used to subfiles on the iSeries. Who can fondly recall many a debate over page by page vs. load all subfile?   How do you create the equivalent of a subfile in .Net?

Easy.

Download the visual studio project from here.

Here's a basic example to get started. The Visual Studio IDE can actually do a lot of the work for you. You can even create a grid (read subfile) with just one or two lines of code. (Post to come)  However, it's more prudent to begin with code you can understand rather than wading through what looks like binary spaghetti.

This example also introduces data sets and data tables. Data sets are just like data structures but without any definition and data tables are just like the field definitions of data structures -not to be confused with tables in databases. We use Data sets and data tables to disconnect from the data source. Connect to the data source, get the data, fill the data set with the data, disconnect from the data source and use the data set in lieu of the actual data. Another way to think of data sets is to think of them as a cache, bucket, container, plastic bag. See my earlier post on data sets for more info.

 

Steps

1. Define your grid (i.e. subfile)
2. Attach the grid to your form (VS creates one for you auto-)
3. Create a data set to hold your data
4. Create a data table to define the fields in the data set
5. Add the data table to the data set
6. Connect  to your iSeries
7. Execute an SQL statement to read from a table
8. Read each row and add to the data set
9. Disconnect from the iSeries
10. Attach the data set to the grid
11. Display the grid

It sounds like a lot of work just to output data to a grid - and it is. Why bother with the data set and the table - can't I just write directly out the grid? Yes you can - but this example is here to show you not only how to display data in a grid from the iSeries but how to best manage that data as well. A Data Set will help you do that. 
It is true that there are much simpler approaches on the iSeries but that comes at a price. Once you get out of the db2 and green screen box things get quite tricky on the As/400.  .Net is more complicated yes but its complexity comes from flexibility.

iSeries prerequisites:

The table on the iSeries in this example is called customers. Create it in library QGPL
create the table in DDS or go into SQL by typing 'strsql' in the iSeries command prompt and create the table as follows:

CREATE TABLE QGPL/CUSTOMERS (NAME CHAR (30 ) NOT NULL WITH DEFAULT,
BALANCE DEC (5 ) NOT NULL WITH DEFAULT)

Add records using the INSERT sql command, DBU or your favorite data editor on the iSeries

C# Code:

This is the code for the form. The 'Program.cs' in solution explorer is unchanged. Simply create a windows project, double click on the form that appears and replace all the code with the code below. Insert your iSeries IP address and make sure you have created the iSeries table as describe above or replace with your own ensuring that you correctly specify the columns.  

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using IBM.Data.DB2.iSeries; // Make sure you add this under 'References' in Solution Explorer
// You need the above reference as a dll which is part of iSeries client access.

namespace iSeries_Grid
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Define the grid size
            DataGrid subfile = new DataGrid();
            subfile.Location = new Point(0, 0);
            subfile.Size = new Size(400, 500);

            //Attach it to the form
            Controls.AddRange(new Control[] { subfile });


            // Create a DataSet to hold data from iSeries Table
            DataSet dataStructure = new DataSet();

            //Create a table to hold the iSeries data
            DataTable dt = new DataTable("Customers");
            dt.Columns.Add("Name");
            dt.Columns.Add("Balance");


            //Add the datatable to the data set
            dataStructure.Tables.Add(dt);

            // Open connection to the iSeries
            iDB2Connection conn = new iDB2Connection();
           conn.ConnectionString = "DataSource=192.168.0.1";

 // You can put "UserID=myuserid;Password=mypass" 
// if you don't want to be prompted

   //  Create a command to select records from the customer table
            iDB2Command command = new iDB2Command();
          command.CommandText = "Select * from qgpl.customers";
         command.Connection = conn; 
 // ties the command to the connection to the iSeries

         conn.Open();

            // Execute the sql statement. Get a Data Reader object 
            iDB2DataReader readFile = command.ExecuteReader();

            // Read each row from the table and output the results into the data set

            while (readFile.Read())
            {
                // Create a row to hold data
                DataRow datarow = dataStructure.Tables["customers"].NewRow();

                datarow["Name"] = readFile.GetString(0);
                datarow["Balance"] = readFile.GetiDB2Integer(1);
                
                // add the row to the data table customer
                dataStructure.Tables["customers"].Rows.Add(datarow);


                }       

            // Clean up - Close connections
            readFile.Close();
            command.Dispose();
            conn.Close();

         //   Attach the data set to the data grid
            subfile.DataSource = dataStructure;
            subfile.DataMember = dataStructure.Tables[0].TableName;



            // Display the subfile
            subfile.Show();
      

        } // End of Method


    } //End of Class

} // End of Namespace

 

This code is based on an example in the IBM .Net Redbook modified

for this post.

Reading an iSeries table in C# (with video)

Here's another simple example of reading a table in C# from the iSeries.
  See the video for this here. 

   Download the Visual Studio Project here

As you can see (line 13)  all you need in the connection string is the IP address of your machine. If you don't include the log on parameters -UserID=myuser; Password=mypass, then you will be prompted for them by the iSeries.
You need to include the IBM .net provider in your 'References' in your Visual Studio project. It's located in the Client Access directory. If you don't have client access, then download the 'technology preview' from IBM for free.

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Text;

   4:  using IBM.Data.DB2.iSeries;

   5:   

   6:  namespace Prez_Rank

   7:  {

   8:      class Program

   9:      {

  10:          static void Main(string[] args)

  11:          {

  12:              iDB2Connection conn = new iDB2Connection();

  13:              conn.ConnectionString = "DataSource=192.168.0.1";

  14:              conn.Open();

  15:   

  16:              iDB2Command cmd = new iDB2Command();

  17:              cmd.CommandText = "Select * from colm.customers";

  18:              cmd.Connection = conn;

  19:   

  20:   

  21:   

  22:              iDB2DataReader dr= cmd.ExecuteReader();

  23:   

  24:              while (dr.Read())

  25:              {

  26:                  Console.WriteLine(dr.GetString(0));

  27:   

  28:              }

  29:              Console.ReadLine();

  30:   

  31:          }

  32:      }

  33:  }

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.

Comparing RPG code to C# : The For Loop

There are similarities between free format RPGIV and C#. In the example below I show a small program that accomplishes the same task in both languages. The task is to find the first blank character in a string from the end to the beginning. I'm well aware there is a single method available for this on String in C# but the purpose here is to show the similarities.
RPG is below:


















Now C#:

You can see some basic similarities:

• Statements end with a ';' ,


• the For loop is similar.


• 'Leave' gets you out of an RPG loop while 'Break' exits a C# loop.


• Assigning values is the same in both.


• Declaring variables in RPGIV requires both type and how many characters (you can declare varying length variables but do define an initial length). To define a variable you must put values in fixed positions in a special kind of statement block called 'Definition Specifications' or D-specs. In C# you simply declare the type. The type of the index 'i' is denoted by a zero in the decimal placess position.


• C# requires a 'Main' method. RPG just executes from the first executable line.


• C# variables are local by default. I can't access that 'i' variable outside of that For loop - the program wouldn't even compile. RPG variables are global unless used in procedures.



• C# requires a class definition, namespace and you define what other namespaces in .Net you want to refer to in your program with the 'Using' statements. The using just means you don't have to refer to the full name like 'System.Console.Readline()'.



• C# doesn't need 'EndFor' or 'EndIf'. Coding is accomplished in blocks between curly braces '{' and '}'.



• The biggest difference is that C# strings and objects have a multitude of methods and properties on the string itself - not part of the language. RPGIV is pretty good at string manipulation too but can never approach the flexibility of C# as the RPG designers would have to create a new keyword when it needs to perform a function on a string that it doesn't already have. In C# there are only a handful of keywords - most actions are accmplished by methods on the objects themselves. For instance in the above example you access the length of a string by examining its property. e.g. 'field.length' returns an integer value whereas in RPG I chose to use the built-in-function %len to do the same thing.
  To find the first non-blank in a string starting from the end you can use field.IndexofLast(' ') . RPGers note how the methods and properties are on the string 'field' which I defined. As soon as you define a variable it creates a copy of the string object so you have full access to a huge amount of manipulation techniques. Adding new methods and properties to strings and other types isn't a major change in the language.

RPG Output below

C# Output:

Datasets are what?

Datasets are a layer between the actual database and your application. When you need to connect to different tables and databases, datasets can help by separating all the connecting to your different data stores and working on the actual data. The analogy with RPG is that datasets are data structures. If you read a table in an RPG program and the put that data into a data structure, updated the fields in that data structure then moved the data structure back to your table then the dataset is the data structure and the bit that moves data between the table is the data adapter.
You don't need all this of course. It's part of .NET, specifically ADO.NET - that part of the framework that's supposed to make it easier to build complex data access application. The stuff that manages and brings it in and out of your application . Kind of like the way the RPG cycle (remember that?) reads in a table automatically. You can just read and write from any database without using datasets and adapters. It's just much easier and less error prone when you use data sets and adapters to manage your data. The IDE automatically creates these when you drop a table onto a form.

When you look at the code to fill a dataset from an adapter you can see there's not much too it. The key term here is Dataset. Datasets are completely separated from the database. Think of them as a data structure where you load data from your table to. The data structure knows nothing of the table where it got the data. It's just used to hold the data in memory. All queries, updates etc. are done to the Dataset not to the data store. We let the dataadapter take care of fetching data to and from the actual table.

// build a command object and prepare an SQL statement so that you can look at your table 
SqlCommand buildData = conn.CreateCommand();

cmd.CommandText = "Select * from Orders where OrdQty >100";
// Build the Data Adapater - this fills a data set 
SqlDataAdapter dataAdapter = new SqlDataAdapter(buildData);

// Now create the data set. The data structure which contains the data retrieved by the data adapter 
DataSet orderDataSet = new DataSet();

// Populate the dataset with the Data Adapter
dataAdapter.Fill(dataSet);

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