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