Examples of C# .NET CLR functions

Once you understand the basics of user-defined functions (UDFs), and the essentials of CLR routines, you can start exploiting CLR UDFs in your applications and database environment. This topic contains some examples of CLR UDFs to get you started.

Before you begin

Attention: With the release of Db2 11.5.9, support for Microsoft .Net common language runtime (CLR) routines is deprecated and might be removed in the future. If you are currently running routines that have a dependency on .NET CLR, rewrite the routine logic in a supported language and then recreate the routines.

Before working with the CLR UDF examples you might want to read the following concept topics:

The following examples make use of a table named EMPLOYEE that is contained in the SAMPLE database.

About this task

For examples of CLR procedures in C#:

Examples of C# .NET CLR procedures

Procedure

Use the following examples as references when making your own C# CLR UDFs:

The C# external code file
Example 1: C# parameter style SQL table function
Example 2: C# parameter style SQL scalar function

Example

The C# external code file

The following examples show a variety of C# UDF implementations. The CREATE FUNCTION statement is provided for each UDF with the corresponding C# source code from which the associated assembly can be built. The C# source file that contains the functions declarations used in the following examples is named gwenUDF.cs and has the following format:

  using System;
  using System.IO;
  using IBM.Data.DB2;
 
  namespace bizLogic
  {
     ...
     // Class definitions that contain UDF declarations
     // and any supporting class definitions
     ...
  }

The function declarations must be contained in a class within a C# file. The use of namespaces is optional. If a namespace is used, the namespace must appear in the assembly path name provided in the EXTERNAL clause of the CREATE PROCEDURE statement. The IBM.Data.DB2. inclusion is required if the function contains SQL.

Example 1: C# parameter style SQL table function

This example shows the following:

CREATE FUNCTION statement for a parameter style SQL table function
C# code for a parameter style SQL table function

This table function returns a table containing rows of employee data that was created from a data array. There are two classes associated with this example. Class person represents the employees, and the class empOps contains the routine table UDF that uses class person. The employee salary information is updated based on the value of an input parameter. The data array in this example is created within the table function itself on the first call of the table function. Such an array could have also been created by reading in data from a text file on the file system. The array data values are written to a scratchpad so that the data can be accessed in subsequent calls of the table function.

On each call of the table function, one record is read from the array and one row is generated in the table that is returned by the function. The row is generated in the table, by setting the output parameters of the table function to the desired row values. After the final call of the table function occurs, the table of generated rows is returned.

  CREATE FUNCTION tableUDF(double)
  RETURNS TABLE (name varchar(20), 
                 job varchar(20), 
                 salary double)
  EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!tableUDF'
  LANGUAGE CLR
  PARAMETER STYLE SQL
  NOT DETERMINISTIC
  FENCED
  THREADSAFE
  SCRATCHPAD 10
  FINAL CALL
  EXECUTION CONTROL SAFE
  DISALLOW PARALLEL
  NO DBINFO

  // The class Person is a supporting class for  
  // the table function UDF, tableUDF, below.
  class Person
  {
       private String name;
       private String position;
       private Int32 salary;

       public Person(String newName, String newPosition, Int32
       newSalary)
       {
          this.name = newName;
          this.position = newPosition;
          this.salary = newSalary;
       }

       public String getName()
       {
          return this.name;
       }

       public String getPosition()
       {
          return this.position;
       }

       public Int32 getSalary()
       {
          return this.salary;
       }
  }

  class empOps
  {
    
     public static void TableUDF( Double factor, out String name,
                       out String position, out Double salary,
                       Int16 factorNullInd, out Int16 nameNullInd,
                       out Int16 positionNullInd, out Int16 salaryNullInd,
                       ref String sqlState, String funcName,
                       String specName, ref String sqlMessageText,
                       Byte[] scratchPad, Int32 callType)
     {

        Int16 intRow = 0;

        // Create an array of Person type information
        Person[] Staff = new
        Person[3];
        Staff[0] = new Person("Gwen", "Developer", 10000);
        Staff[1] = new Person("Andrew", "Developer", 20000);
        Staff[2] = new Person("Liu", "Team Leader", 30000); 

        salary = 0;
        name = position = "";       
        nameNullInd = positionNullInd = salaryNullInd = -1;

        switch(callType)
        {
           case (-2):  // Case SQLUDF_TF_FIRST:
             break;

           case (-1):  // Case SQLUDF_TF_OPEN:
             intRow = 1;
             scratchPad[0] = (Byte)intRow;  // Write to scratchpad
             break;
           case (0):   // Case SQLUDF_TF_FETCH:
             intRow = (Int16)scratchPad[0];
             if (intRow > Staff.Length)
             {
                sqlState = "02000";  // Return an error SQLSTATE
             }
             else
             {
                // Generate a row in the output table 
                // based on the Staff array data.
                name =
                Staff[intRow-1].getName();
                position = Staff[intRow-1].getPosition();
                salary = (Staff[intRow-1].getSalary()) * factor;
                nameNullInd = 0;
                positionNullInd = 0;
                salaryNullInd = 0;
             }
             intRow++;
             scratchPad[0] = (Byte)intRow;  // Write scratchpad
             break;

           case (1):   // Case SQLUDF_TF_CLOSE:
             break;
     
           case (2):   // Case SQLUDF_TF_FINAL:
             break;
        }
     }
  }

Example 2: C# parameter style SQL scalar function

This example shows the following:

CREATE FUNCTION statement for a parameter style SQL scalar function
C# code for a parameter style SQL scalar function

This scalar function returns a single count value for each input value that it operates on. For an input value in the nth position of the set of input values, the output scalar value is the value n. On each call of the scalar function, where one call is associated with each row or value in the input set of rows or values, the count is increased by one and the current value of the count is returned. The count is then saved in the scratchpad memory buffer to maintain the count value between each call of the scalar function.

This scalar function can be easily invoked if for example we have a table defined as follows:

  CREATE TABLE T (i1 INTEGER);
  INSERT INTO T VALUES 12, 45, 16, 99;

A simple query such as the following can be used to invoke the scalar function:

    SELECT countUp(i1) as count, i1 FROM T;

The output of such a query would be:

  COUNT           I1
  -----------     ----------
  1               12
  2               45
  3               16
  4               99

This scalar UDF is quite simple. Instead of returning just the count of the rows, you could use a scalar function to format data in an existing column. For example you might append a string to each value in an address column or you might build up a complex string from a series of input strings or you might do a complex mathematical evaluation over a set of data where you must store an intermediate result.

  CREATE FUNCTION countUp(INTEGER) 
  RETURNS INTEGER
  LANGUAGE CLR
  PARAMETER STYLE SQL
  SCRATCHPAD 10
  FINAL CALL
  NO SQL
  FENCED
  THREADSAFE
  NOT DETERMINISTIC
  EXECUTION CONTROL SAFE
  EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!CountUp' ;


  class empOps
  {
     public static void CountUp(     Int32 input,     
                                 out Int32 outCounter,
                                     Int16 inputNullInd, 
                                 out Int16 outCounterNullInd,
                                 ref String sqlState,
                                     String funcName,
                                     String specName,
                                 ref String sqlMessageText,
                                     Byte[] scratchPad,
                                     Int32 callType)
     {
        Int32 counter = 1;        

        switch(callType)
        {
           case -1: // case SQLUDF_FIRST_CALL
             scratchPad[0] = (Byte)counter;
             outCounter = counter;
             outCounterNullInd = 0;
             break;
           case 0:  // case SQLUDF_NORMAL_CALL:
             counter = (Int32)scratchPad[0];
             counter = counter + 1;
             outCounter = counter;
             outCounterNullInd = 0;
             scratchPad[0] =
             (Byte)counter;
             break;
           case 1:  // case SQLUDF_FINAL_CALL:
             counter =
             (Int32)scratchPad[0];
             outCounter = counter;
             outCounterNullInd = 0;
             break;
           default: // Should never enter here
                    // * Required so that at compile time 
                    //   out parameter outCounter is always set *
             outCounter = (Int32)(0);
             outCounterNullInd = -1;
             sqlState="ABCDE";
             sqlMessageText = "Should not get here: Default
             case!";
             break;
        }            
     } 
  }