public T QuerySingleValue <T>(iDB2Connection connection, string queryString)
{
iDB2Command selectCommand = GenerateCommand(connection, queryString);
object value = selectCommand.ExecuteScalar();
return((T)Convert.ChangeType(value, typeof(T)));
}
public static void Main()
{
Console.WriteLine($"Profiler attached: {Samples.SampleHelpers.IsProfilerAttached()}");
var command = new iDB2Command("MyCommand");
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
}
try
{
command.ExecuteScalar();
}
catch (Exception)
{
}
try
{
command.ExecuteReader();
}
catch (Exception)
{
}
Console.WriteLine("Done");
}
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
/// <returns>DataTable</returns>
public Object GetValue(string sqlStatement, Action <iDB2ParameterCollection> parameters)
{
Object obj;
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null)
{
parameters(cmd.Parameters);
}
try
{
obj = cmd.ExecuteScalar();
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return(obj);
}
public T QuerySingleValue <T>(iDB2Connection connection, string queryString, params KeyValuePair <string, KeyValuePair <iDB2DbType, object> >[] queryParams)
{
iDB2Command selectCommand = GenerateCommand(connection, queryString, queryParams);
object value = selectCommand.ExecuteScalar();
return((T)Convert.ChangeType(value, typeof(T)));
}
public T QuerySingleValue <T>(string queryString, params KeyValuePair <string, object>[] queryParams)
{
using (iDB2Connection connection = GetConnection())
{
try
{
iDB2Command selectCommand = GenerateCommand(connection, queryString, queryParams);
object value = selectCommand.ExecuteScalar();
try
{
return((T)Convert.ChangeType(value, typeof(T)));
}
catch
{
return(default(T));
}
}
catch (Exception ex)
{
WriteLog(ex, queryString, queryParams);
throw;
}
finally
{
ConnectionClose(connection);
}
}
}
private void CompareValueWithScalarQuery(Dictionary <String, String> parameters)
{
if (parameters.ContainsKey("Value") && parameters.ContainsKey("SelectQuery"))
{
using (iDB2Command command = new iDB2Command(parameters["SelectQuery"], _dB2connection))
{
if (parameters.ContainsKey("ParameterVariables"))
{
foreach (String str in parameters["ParameterVariables"].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
if (EngineState.VariableDictionary.ContainsKey(str))
{
command.Parameters.AddWithValue(str, EngineState.VariableDictionary[str]);
}
}
}
try
{
EngineState.ConditionalStack.Push(parameters["Value"].Equals(command.ExecuteScalar().ToString()));
}
catch (Exception ex)
{
}
}
}
}
/// <summary>
/// Execute an iDB2Command that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new iDB2Parameter(":prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of iDB2Paramters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(iDB2Connection connectionString, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters)
{
iDB2Command cmd = new iDB2Command();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(val);
}
public Decimal EjecutarEscalar()
{
int escalar = 0;
try
{
escalar = (int)_Comando.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
return(escalar);
}
public static Object DB2Select1()
{
iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString);
string sql = "SELECT VALUE FROM SEQ_TABLE WHERE KEY=@KEY FOR UPDATE";
iDB2Command cmd = new iDB2Command();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new iDB2Parameter("@KEY", "USER"));
object val = cmd.ExecuteScalar();
return(val);
}
public static object ExecuteDb2Scalar(CommandType commandType, string instructionSQL, Object parameters = null)
{
object rpta;
try
{
var strEsquema = ConfigurationManager.AppSettings["Esquema"];
Ambito.Esquema = strEsquema;
Contexto contexto = new Contexto(Ambito.Esquema);
iDB2Connection conexion = new iDB2Connection();
conexion = contexto.getConexionRNSLIB;
if (conexion.State == ConnectionState.Open)
{
conexion.Close();
}
conexion.Open();
iDB2Command dbCmd = new iDB2Command();
dbCmd.Connection = conexion;
dbCmd.CommandType = commandType;
dbCmd.CommandText = instructionSQL;
iDB2Parameter[] db2Parameters = GetParameters(parameters);
if (db2Parameters.Length > 0)
{
dbCmd.Parameters.AddRange(db2Parameters);
}
rpta = dbCmd.ExecuteScalar();
conexion.Close();
}
catch (Exception ex)
{
rpta = ex.Message;
return(rpta);
}
return(rpta);
}
/// <summary>
/// Execute a iDB2Command (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">An array of iDB2Paramters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(iDB2Transaction transaction, CommandType commandType, string commandText, params iDB2Parameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
// Create a command and prepare it for execution
iDB2Command cmd = new iDB2Command();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return(retval);
}
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
/// <returns>DataTable</returns>
public Object GetValue(string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
Object obj;
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null) { parameters(cmd.Parameters); }
try
{
obj = cmd.ExecuteScalar();
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return obj;
}
