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