using IBM.Data.DB2.iSeries;
using System;
using System.Data;
namespace iSeriesTestProcedure
{
class Program
{
static void Main(string[] args) => TestiSeries();
private static void Run(IDbConnection c, string sql) {
var cmd = c.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
private static object RunRead(IDbConnection c, string sql)
{
var cmd = c.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
object result = null;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result = reader.GetValue(0);
}
}
return result;
}
private static void TestiSeries()
{
var connectionString = "DataSource=***;UserID=***;Password=***;DataCompression=False;DEFAULT COLLECTION=***;Pooling=false;";
using (var con = new iDB2Connection(connectionString))
{
con.Open();
try
{
var transation = con.BeginTransaction();
var cmd = con.CreateCommand();
Run(con, "CREATE OR REPLACE VARIABLE P1 CHAR(435)");
Run(con, "SET P1 = '147900701066'");
var sql = string.Join(Environment.NewLine, new string[] {
"BEGIN",
" DECLARE P2 CHAR(435);",
" SET P2 = P1;",
" CALL \"@TESTALL\".\"ADR006C\"(P2);",
" VALUES (P2) INTO P1;",
"END"
});
Run(con, sql);
var data = RunRead(con, "VALUES (P1)");
Console.WriteLine($"{data}");
}
catch (Exception ex)
{
var error = ex.ToString();
Console.WriteLine(error);
}
}
}
}
}
Optimized generic function:
public T1 ExecuteCallWithSingleInputAndOutputParam<T1>(string callName, string callParamsAsString)
{
var connection = _connection;
var paramLength = callParamsAsString.Length;
var callnamePrepared = PrepareCallName(callName);
RunStatement(connection, $"CREATE OR REPLACE VARIABLE P1 CHAR({callParamsAsString.Length})");
RunStatement(connection, $"SET P1 = '{callParamsAsString}'");
RunStatement(connection, string.Join(Environment.NewLine, new string[] {
"BEGIN",
$" DECLARE P2 CHAR({paramLength});",
" SET P2 = P1;",
$" CALL {callnamePrepared}(P2);",
" VALUES (P2) INTO P1;",
"END"
}));
return RunRead<T1>(connection, "VALUES (P1)");
}
private string PrepareCallName(string callName)
{
var splitted = callName.Split('.');
if (splitted.Length != 2)
throw new Exception("invalid callname");
var a = splitted[0];
var b = splitted[1];
return $"\"{a}\".\"{b}\"";
}
private static void RunStatement(DataConnection c, string sql)
{
var cmd = CreateCommand(c, sql);
cmd.ExecuteNonQuery();
}
private static T1 RunRead<T1>(DataConnection c, string sql)
{
var cmd = CreateCommand(c, sql);
var result = cmd.ExecuteScalar();
return (T1)result;
}
private static IDbCommand CreateCommand(DataConnection c, string sql)
{
var cmd = c.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
447100cookie-checkiSeries call procedure with INOUT parameter