Examples of C# .NET CLR procedures

Once the basics of procedures, also called stored procedures, and the essentials of .NET common language runtime routines are understood, you can start using CLR procedures in your applications.

Before you begin

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

About this task

This topic contains examples of CLR procedures implemented in C# that illustrate the supported parameter styles, passing parameters, including the dbinfo structure, how to return a result set and more. For examples of CLR UDFs in C#:

Examples of C# .NET CLR functions

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

Procedure

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

The C# external code file
Example 1: C# parameter style GENERAL procedure
Example 2: C# parameter style GENERAL WITH NULLS procedure
Example 3: C# parameter style SQL procedure
Example 4: C# procedure returning a result set
Example 5: C# procedure accessing the dbinfo structure
Example 6: C# procedure in PROGRAM TYPE MAIN style

Example

The C# external code file

The examples show a variety of C# procedure implementations. Each example consists of two parts: the CREATE PROCEDURE statement and the external C# code implementation of the procedure from which the associated assembly can be built.

The C# source file that contains the procedure implementations of the following examples is named gwenProc.cs and has the following format:

  using System;
  using System.IO;
  using IBM.Data.DB2;
 
  namespace bizLogic
  {
     class empOps
     {          ... 
       // C# procedures 
                ...
     }
  }

The file inclusions are indicated at the top of the file. The IBM.Data.DB2 inclusion is required if any of the procedures in the file contain SQL. There is a namespace declaration in this file and a class empOps that contains the procedures. 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.

It is important to note the name of the file, the namespace, and the name of the class, that contains a given procedure implementation. These names are important, because the EXTERNAL clause of the CREATE PROCEDURE statement for each procedure must specify this information so that the database manager can locate the assembly and class of the CLR procedure.

Example 1: C# parameter style GENERAL procedure

This example shows the following:

CREATE PROCEDURE statement for a parameter style GENERAL procedure
C# code for a parameter style GENERAL procedure

This procedure takes an employee ID and a current bonus amount as input. It retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus is calculated, based on the employee's salary, and returned along with the employee's full name. If the employee is not found, an empty string is returned.

  CREATE PROCEDURE setEmpBonusGEN(IN empID CHAR(6), 
                                  INOUT bonus Decimal(9,2), 
                                  OUT empName VARCHAR(60))
  SPECIFIC SetEmpBonusGEN
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  MODIFIES SQL DATA
  EXECUTION CONTROL SAFE
  FENCED
  THREADSAFE
  DYNAMIC RESULT SETS 0
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!SetEmpBonusGEN' ;

   public static void SetEmpBonusGEN(    String empID, 
                                     ref Decimal bonus, 
                                     out String empName)
   {
      // Declare local variables
      Decimal salary = 0;

      DB2Command myCommand = DB2Context.GetCommand();
      myCommand.CommandText = 
                       "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY "
                     + "FROM EMPLOYEE "
                     + "WHERE EMPNO = '" + empID + '";

      DB2DataReader reader = myCommand.ExecuteReader();

      if (reader.Read())  // If employee record is found
      {
         // Get the employee's full name and salary
         empName = reader.GetString(0) + " " +
                   reader.GetString(1) + ". " +
                   reader.GetString(2);

         salary = reader.GetDecimal(3);

         if (bonus == 0)
         {
            if (salary > 75000)
            {
               bonus = salary * (Decimal)0.025;
            }
            else
            {
               bonus = salary * (Decimal)0.05;
            }
         }
      }
      else  // Employee not found
      {
         empName = "";  // Set output parameter
      }
      
      reader.Close();
   }

Example 2: C# parameter style GENERAL WITH NULLS procedure

This example shows the following:

CREATE PROCEDURE statement for a parameter style GENERAL WITH NULLS procedure
C# code for a parameter style GENERAL WITH NULLS procedure

This procedure takes an employee ID and a current bonus amount as input. If the input parameter is not null, it retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus based on salary is calculated and returned along with the employee's full name. If the employee data is not found, a NULL string and integer is returned.

  CREATE PROCEDURE SetEmpbonusGENNULL(IN empID CHAR(6), 
                                      INOUT bonus Decimal(9,2), 
                                      OUT empName VARCHAR(60))
  SPECIFIC SetEmpbonusGENNULL
  LANGUAGE CLR
  PARAMETER STYLE GENERAL WITH NULLS
  DYNAMIC RESULT SETS 0
  MODIFIES SQL DATA
  EXECUTION CONTROL SAFE
  FENCED
  THREADSAFE
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!SetEmpBonusGENNULL'
  ;

   public static void SetEmpBonusGENNULL(    String empID, 
                                         ref Decimal bonus, 
                                         out String empName, 
                                             Int16[] NullInds)
   {
      Decimal salary = 0;
      if (NullInds[0] == -1) // Check if the input is null
      {
        NullInds[1] = -1;    // Return a NULL bonus value
        empName = "";        // Set output value
        NullInds[2] = -1;    // Return a NULL empName value
      }
      else
      {
         DB2Command myCommand = DB2Context.GetCommand();
    	     myCommand.CommandText = 
                         "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY "
                       + "FROM EMPLOYEE "
                       + "WHERE EMPNO = '" + empID + "'";
         DB2DataReader reader = myCommand.ExecuteReader();
 
         if (reader.Read())  // If employee record is found
         {
            // Get the employee's full name and salary
            empName = reader.GetString(0) + " "
            +
                      reader.GetString(1) + ". " +
                      reader.GetString(2);
            salary = reader.GetDecimal(3);

            if (bonus == 0)
            {
              if (salary > 75000)
              {
                 bonus = salary * (Decimal)0.025;
                 NullInds[1] = 0; // Return a non-NULL value
              }
              else
              {
                 bonus = salary * (Decimal)0.05;
                 NullInds[1] = 0; // Return a non-NULL value
              }
            }
         }
         else  // Employee not found
         {
            empName = "*sdq;;         // Set output parameter
            NullInds[2] = -1;     // Return a NULL value
         }
	       
         reader.Close();
      }
   }

Example 3: C# parameter style SQL procedure

This example shows the following:

CREATE PROCEDURE statement for a parameter style SQL procedure
C# code for a parameter style SQL procedure

This procedure takes an employee ID and a current bonus amount as input. It retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus based on salary is calculated and returned along with the employee's full name. If the employee is not found, an empty string is returned.

  CREATE PROCEDURE SetEmpbonusSQL(IN empID CHAR(6), 
                                  INOUT bonus Decimal(9,2), 
                                  OUT empName VARCHAR(60))
  SPECIFIC SetEmpbonusSQL
  LANGUAGE CLR
  PARAMETER STYLE SQL
  DYNAMIC RESULT SETS 0
  MODIFIES SQL DATA
  FENCED
  THREADSAFE
  EXECUTION CONTROL SAFE
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!SetEmpBonusSQL' ;

   public static void SetEmpBonusSQL(    String empID, 
                                     ref Decimal bonus, 
                                     out String empName, 
                                         Int16  empIDNullInd, 
                                     ref Int16  bonusNullInd, 
                                     out Int16  empNameNullInd, 
                                     ref string sqlStateate, 
                                         string funcName, 
                                         string specName, 
                                     ref string sqlMessageText)
   {
      // Declare local host variables
      Decimal salary eq; 0;

      if (empIDNullInd == -1) // Check if the input is null
      {
         bonusNullInd = -1;   // Return a NULL bonus value
         empName = "";
         empNameNullInd = -1; // Return a NULL empName value
      }
      else
      {
         DB2Command myCommand = DB2Context.GetCommand();
         myCommand.CommandText = 
                          "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY
                          "
                        + "FROM EMPLOYEE "
                        + "WHERE EMPNO = '" + empID + "'";

         DB2DataReader reader = myCommand.ExecuteReader();
		 
         if (reader.Read())  // If employee record is found
         {
            // Get the employee's full name and salary
            empName = reader.GetString(0) + " "
            +
            reader.GetString(1) + ". " +
            reader.GetString(2);
            empNameNullInd = 0;
            salary = reader.GetDecimal(3);
			
            if (bonus == 0)
            {
               if (salary > 75000)
               {
                  bonus = salary * (Decimal)0.025;
                  bonusNullInd = 0;  // Return a non-NULL value
               }
               else
               {
                  bonus = salary * (Decimal)0.05;
                  bonusNullInd = 0;  // Return a non-NULL value
               }
            }
         }
         else  // Employee not found
         {
            empName = "";         // Set output parameter
            empNameNullInd = -1;  // Return a NULL value
         }

         reader.Close();
      }
   }

Example 4: C# parameter style GENERAL procedure returning a result set

This example shows the following:

CREATE PROCEDURE statement for an external C# procedure returning a result set
C# code for a parameter style GENERAL procedure that returns a result set

This procedure accepts the name of a table as a parameter. It returns a result set containing all the rows of the table specified by the input parameter. This is done by leaving a DB2DataReader for a given query result set open when the procedure returns. Specifically, if reader.Close() is not executed, the result set will be returned.

  CREATE PROCEDURE ReturnResultSet(IN tableName
                                   VARCHAR(20))
  SPECIFIC ReturnResultSet
  DYNAMIC RESULT SETS 1
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!ReturnResultSet' ;

   public static void ReturnResultSet(string tableName)
   {
      DB2Command myCommand = DB2Context.GetCommand();
         
      // Set the SQL statement to be executed and execute it.
      myCommand.CommandText = "SELECT * FROM " + tableName;
      DB2DataReader reader = myCommand.ExecuteReader();

      // The DB2DataReader contains the result of the query.
      // This result set can be returned with the procedure,
      // by simply NOT closing the DB2DataReader.
      // Specifically, do NOT execute reader.Close();
   }

Example 5: C# parameter style SQL procedure accessing the dbinfo structure

This example shows the following:

CREATE PROCEDURE statement for a procedure accessing the dbinfo structure
C# code for a parameter style SQL procedure that accesses the dbinfo structure

To access the dbinfo structure, the DBINFO clause must be specified in the CREATE PROCEDURE statement. No parameter is required for the dbinfo structure in the CREATE PROCEDURE statement however a parameter must be created for it, in the external routine code. This procedure returns only the value of the current database name from the dbname field in the dbinfo structure.

  CREATE PROCEDURE ReturnDbName(OUT dbName VARCHAR(20))
  SPECIFIC ReturnDbName
  DYNAMIC RESULT SETS 0
  LANGUAGE CLR
  PARAMETER STYLE SQL
  FENCED
  THREADSAFE
  EXECUTION CONTROL SAFE
  DBINFO
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!ReturnDbName'
  ;

   public static void ReturnDbName(out string dbName, 
                                   out Int16  dbNameNullInd, 
                                   ref string sqlState, 
                                       string funcName, 
                                       string specName, 
                                   ref string sqlMessageText, 
                                       sqludf_dbinfo dbinfo)
   {
      // Retrieve the current database name from the
      // dbinfo structure and return it.
      // ** Note! ** dbinfo field names are case sensitive
      dbName = dbinfo.dbname;
      dbNameNullInd = 0;  // Return a non-null value;

      // If you want to return a user-defined error in
      // the SQLCA you can specify a 5 digit user-defined
      // sqlStateate and an error message string text.
      // For example:
      //
      //   sqlStateate = "ABCDE";  
      //   sqlMessageText = "A user-defined error has occurred"
      //
      //  the database manager returns the above values to the client in the
      //  SQLCA structure.  The values are used to generate a
      //  standard sqlStateate error.
   }

Example 6: C# procedure with PROGRAM TYPE MAIN style

This example shows the following:

CREATE PROCEDURE statement for a procedure using a main program style
C# parameter style GENERAL WITH NULLS code in using a MAIN program style

To implement a routine in a main program style, the PROGRAM TYPE clause must be specified in the CREATE PROCEDURE statement with the value MAIN. Parameters are specified in the CREATE PROCEDURE statement however in the code implementation, parameters are passed into the routine in an argc integer parameter and an argv array of parameters.

  CREATE PROCEDURE MainStyle( IN empID CHAR(6), 
                              INOUT bonus Decimal(9,2), 
                              OUT empName VARCHAR(60))
  SPECIFIC MainStyle						   
  DYNAMIC RESULT SETS 0
  LANGUAGE CLR
  PARAMETER STYLE GENERAL WITH NULLS
  MODIFIES SQL DATA
  FENCED
  THREADSAFE
  EXECUTION CONTROL SAFE
  PROGRAM TYPE MAIN
  EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!main' ;

   public static void main(Int32 argc, Object[]
   argv)
   {
     String empID = (String)argv[0];   // argv[0] has nullInd:argv[3]
     Decimal bonus = (Decimal)argv[1]; // argv[1] has nullInd:argv[4]
                                       // argv[2] has nullInd:argv[5]
     Decimal salary = 0;     
     Int16[] NullInds = (Int16[])argv[3];

     if ((NullInds[0]) == (Int16)(-1)) // Check if empID is null
     {
        NullInds[1] = (Int16)(-1);  // Return a NULL bonus value
        argv[1] = (String)"";       // Set output parameter empName
        NullInds[2] = (Int16)(-1);  // Return a NULL empName value
        Return;
     }
     else
     {
        DB2Command myCommand = DB2Context.GetCommand();
        myCommand.CommandText = 
                         "SELECT FIRSTNME, MIDINIT, LASTNAME, salary "
                       + "FROM EMPLOYEE "
                       + "WHERE EMPNO = '" + empID + "'";
        
        DB2DataReader reader = myCommand.ExecuteReader();
 
        if (reader.Read())  // If employee record is found
        {
          // Get the employee's full name and salary
          argv[2] = (String) (reader.GetString(0) + " " +
                              reader.GetString(1) + ".
                              " +
                              reader.GetString(2));
          NullInds[2] = (Int16)0;
          salary = reader.GetDecimal(3);

          if (bonus == 0)
          {
             if (salary > 75000)
             {
                argv[1] = (Decimal)(salary * (Decimal)0.025); 
                NullInds[1] = (Int16)(0);  // Return a non-NULL value
             }
             else
             {
                argv[1] = (Decimal)(salary * (Decimal)0.05);
                NullInds[1] = (Int16)(0);  // Return a non-NULL value
             }
          }
        }
        else  // Employee not found
        {
           argv[2] = (String)("");         // Set output parameter
           NullInds[2] = (Int16)(-1);      // Return a NULL value
        }

        reader.Close();
      }
   }