private void DavidForm_Load(object sender, EventArgs e)
{
try
{
lBoxData.Items.Add("Data Load was Successful");
//Connection string
connectionString = "Data Source = deathstar.gtc.edu; User ID = itpa452; Initial Catalog = S101FF5C";
//SQL Statement
sql = "SELECT * FROM EMPLOYEES";
connection = new iDB2Connection(connectionString); //Assign connection string to the idb2 connection object
dataAdapter = new iDB2DataAdapter(sql, connection); //Assign idb2 connection and sql statement to the data adapter
dataset = new DataSet(); //New DataSet
dataAdapter.Fill(dataset); //Fill data adapter with data set
foreach (DataRow data in dataset.Tables[0].Rows)
{
lBoxData.Items.Add("Name: " + data[1] + data[2] + " | Employee Number: " + data[0]);
}
}
catch (Exception ee)
{
Console.WriteLine("Error Code");
}
}
public static DataSet GetDataSet(string commandText, CommandType commandType, iDB2Parameter[] parameters = null)
{
try
{
using (iDB2Connection connection = new iDB2Connection(ConfigurationManager.ConnectionStrings["DB2"].ConnectionString))
{
connection.Open();
using (iDB2Command command = new iDB2Command(commandText, commandType, connection))
{
command.CommandTimeout = 300;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
var dataset = new DataSet();
iDB2DataAdapter dataAdaper = new iDB2DataAdapter(command);
dataAdaper.Fill(dataset);
connection.Close();
return(dataset);
}
}
}
catch (Exception ex)
{
throw;
}
}
private void JoshuaForm_Load(object sender, EventArgs e)
{
string sql;
string connectionString;
try
{
connectionString = "Data Source = deathstar.gtc.edu; User ID = ITPA485; Initial Catalog = S101FF5C";
sql = "SELECT * FROM EMPLOYEE";
connection = new iDB2Connection(connectionString);
dataAdapter = new iDB2DataAdapter(sql, connection);
EMPLOYEE = new DataSet();
dataAdapter.Fill(EMPLOYEE);
foreach (DataRow pRow in EMPLOYEE.Tables[0].Rows)
{
listBox1.Items.Add(pRow[1] + " " + pRow[2] + " " + pRow[17]);
}
connection.Close();
}
catch (Exception ex)
{
listBox1.Items.Add(ex.Message);
}
}
/// <summary>
/// Executes a stored procedure that returns a result set.
/// </summary>
/// <param name="connString">The conn string.</param>
/// <param name="sqlStatement">The SQL statement.</param>
/// <param name="parameters">The parameters.</param>
public DataTable ExecuteStoredProcedureWithResultSet(string sqlStatement, Action <iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
parameters(cmd.Parameters);
}
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return(dt);
}
/// <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 DataTable GetData(string sqlStatement, Action <iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null)
{
parameters(cmd.Parameters);
}
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return(dt);
}
private DataTable ExecuteSelectQuery(string strStatement)
{
try
{
lstResponse.Add(SetResponse(strStatement, string.Empty, ResponseStatusType.LOG));
DataTable dtSelect = new DataTable();
if (objSQLCon.State == ConnectionState.Closed)
{
objSQLCon.Open();
}
iDB2DataAdapter daSelect = new iDB2DataAdapter(strStatement, objSQLCon);
daSelect.Fill(dtSelect);
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
return(dtSelect);
}
catch (iDB2Exception ex)
{
lstResponse.Add(SetResponse(strStatement, ex.Message, ResponseStatusType.CRITICAL));
}
catch (Exception ex)
{
lstResponse.Add(SetResponse(strStatement, ex.Message, ResponseStatusType.CRITICAL));
}
finally
{
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
}
return(null);
}
public void LlenarDataSet(DataSet DS, String NombreTabla)
{
iDB2DataAdapter Adaptador;
Adaptador = new iDB2DataAdapter();
Adaptador.SelectCommand = _Comando;
Adaptador.Fill(DS, NombreTabla);
}
/// <summary>
/// Executes the command object using DB2DataAdapter to fill the dataset and return
/// </summary>
/// <param name="commandToExecute">The command to execute.</param>
/// <returns>DataSet</returns>
private DataSet ExecuteDataSet(iDB2Command commandToExecute)
{
DataSet outputDataSet = null;
using (iDB2DataAdapter db2DataAdapterExecuter = new iDB2DataAdapter(commandToExecute))
{
outputDataSet = new DataSet();
db2DataAdapterExecuter.Fill(outputDataSet);
}
return(outputDataSet);
}
public DataSet GetDataSetFromProcedure(string strSQLWithoutCALLString, iDB2Parameter[] sqlParameters)
{
var objDataAd = default(iDB2DataAdapter);
var objDs = default(DataSet);
var objCmd = default(iDB2Command);
var objConn = default(iDB2Connection);
try
{
objConn = new iDB2Connection(strConn);
objDataAd = new iDB2DataAdapter();
objCmd = new iDB2Command("{CALL " + strSQLWithoutCALLString + "}", objConn);
objDataAd.SelectCommand = objCmd;
foreach (var objPar in sqlParameters)
{
objCmd.Parameters.Add(objPar);
}
objDs = new DataSet();
objDataAd.Fill(objDs);
}
catch (Exception ex)
{
if (objCmd is object)
{
objCmd.Dispose();
}
if (objDs is object)
{
objDs.Dispose();
}
objDs = default;
objException = ex;
}
finally
{
if (objDataAd is object)
{
objDataAd.Dispose();
}
if (objConn is object)
{
objConn.Close();
objConn.Dispose();
}
}
return(objDs);
}
public static DataTable ExecuteDb2Query(CommandType commandType, string instructionSQL, Object parameters = null)
{
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);
}
iDB2DataAdapter da = new iDB2DataAdapter();
da.SelectCommand = dbCmd;
DataTable dtResult = new DataTable();
da.Fill(dtResult);
conexion.Close();
return(dtResult);
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet EjecutarProcedimientoAlmacenado(String dataSetName)
{
iDB2DataAdapter Adaptador;
DataSet DS;
if (dataSetName == null)
{
DS = new DataSet();
}
else
{
DS = new DataSet(dataSetName);
}
Adaptador = new iDB2DataAdapter();
Adaptador.SelectCommand = _Comando;
Adaptador.Fill(DS);
return(DS);
}
public DataTable GetDataTable(string strSQL)
{
var objConn = default(iDB2Connection);
var objDataAd = default(iDB2DataAdapter);
DataTable objDt;
try
{
if (blnTrans == false)
{
objConn = new iDB2Connection(strConn);
objDataAd = new iDB2DataAdapter(strSQL, objConn);
}
else
{
objDataAd = new iDB2DataAdapter();
objDataAd.SelectCommand = new iDB2Command(strSQL, objConnection, objTransaction);
}
strLastSQLExecuted = strSQL;
objDt = new DataTable();
objDataAd.Fill(objDt);
}
catch (Exception ex)
{
throw new Exception(strSQL);
}
finally
{
if (objDataAd is object)
{
objDataAd.SelectCommand.Dispose();
objDataAd.Dispose();
}
if (objConn is object)
{
objConn.Close();
objConn.Dispose();
}
}
return(objDt);
}
public DataSet GetDataSet(string strSQL, ref string Err)
{
var objDs = default(DataSet);
var objDataAd = default(iDB2DataAdapter);
var dsLocal = new DataSet();
var objConn = default(iDB2Connection);
try
{
objConn = new iDB2Connection(strConn);
objConn.Open();
objDs = new DataSet();
objDataAd = new iDB2DataAdapter(strSQL, objConn);
objDataAd.Fill(objDs);
}
catch (Exception ex)
{
Err = ex.Message;
if (dsLocal is object)
{
dsLocal.Dispose();
}
dsLocal = default;
objException = ex;
}
finally
{
if (objConn is object)
{
objConn.Close();
objConn.Dispose();
}
if (objDataAd is object)
{
objDataAd.Dispose();
}
}
return(objDs);
}
public static DataTable GetDataTable(string commandText, CommandType commandType, iDB2Parameter[] parameters = null)
{
var dataset = new DataSet();
try
{
LayoutModel layoutModel = new LayoutModel();
layoutModel = (LayoutModel)HttpContext.Current.Session["LayoutDetails"];
if (layoutModel.ClientDatabase != null)
{
string Conn = "DataSource = as400.abc.abchldg.com; userid = aspamo; password = a$pamo99; Default Collection =" + layoutModel.ClientDatabase + ";";
using (iDB2Connection connection = new iDB2Connection(Conn))
{
connection.Open();
using (iDB2Command command = new iDB2Command(commandText, commandType, connection))
{
command.CommandTimeout = 1000;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
iDB2DataAdapter dataAdaper = new iDB2DataAdapter(command);
dataAdaper.Fill(dataset);
connection.Close();
return(dataset.Tables[0]);
}
}
}
return(dataset.Tables[0]);
}
catch (Exception ex)
{
throw;
}
}
public bool ExecuteSql(string sql, out DataTable oDataTable, out string oMessage)
{
bool res = false;
oMessage = string.Empty;
oDataTable = new DataTable();
try
{
_conn = new iDB2Connection(ConnectionString);
_conn.Open();
_command = new iDB2Command(sql, _conn)
{
CommandTimeout = DbTimeOut
};
_adapter = new iDB2DataAdapter
{
SelectCommand = _command
};
_adapter.Fill(oDataTable);
res = true;
}
catch (Exception ex)
{
oMessage = ex.Message;
Logging.WriteLog(ex.Message);
}
finally
{
if (_conn != null)
{
if (_conn.State == ConnectionState.Open)
{
_conn.Close();
}
}
}
return(res);
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string listInd = lBoxData.SelectedIndex.ToString();
int listIndInt = Convert.ToInt32(listInd);
try
{
//Connection string
connectionString = "Data Source = deathstar.gtc.edu; User ID = itpa452; Initial Catalog = S101FF5C";
//SQL Statement
sql = "SELECT * FROM EMPLOYEES";
connection = new iDB2Connection(connectionString); //Assign connection string to the idb2 connection object
dataAdapter = new iDB2DataAdapter(sql, connection); //Assign idb2 connection and sql statement to the data adapter
dataset = new DataSet(); //New DataSet
dataAdapter.Fill(dataset); //Fill data adapter with data set
DataRow data = dataset.Tables[0].Rows[listIndInt];
tBoxState.Text = data[5].ToString();
}
catch (Exception ee) { Console.WriteLine("How'd dis Happen, like you loaded the page just fine?"); }
}
public void ActualizaDatos(String tabla, DataTable datos)
{
if (_Conexion == null || datos == null)
{
return;
}
DataSet tablaDataSet = new DataSet();
String qry = "select * from " + tabla + " where 1 = 0 ";
_DataAdapter = new iDB2DataAdapter();
_DataAdapter.SelectCommand = new iDB2Command(qry, _Conexion);
_DataAdapter.Fill(tablaDataSet);
iDB2CommandBuilder Cmb = new iDB2CommandBuilder(_DataAdapter);
_DataAdapter.InsertCommand = Cmb.GetInsertCommand();
foreach (DataRow fila in datos.Rows)
{
fila.SetAdded();
}
_DataAdapter.Update(datos);
}
public DataSet QueryDataSet(string queryString, params KeyValuePair <string, object>[] queryParams)
{
using (iDB2Connection connection = GetConnection())
{
try
{
iDB2Command selectCommand = GenerateCommand(connection, queryString, queryParams);
iDB2DataAdapter adapter = new iDB2DataAdapter(selectCommand);
DataSet ds = new DataSet();
adapter.Fill(ds, "table");
return(ds);
}
catch (Exception ex)
{
WriteLog(ex, queryString, queryParams);
throw;
}
finally
{
ConnectionClose(connection);
}
}
}
/// <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 DataTable GetData(string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null) { parameters(cmd.Parameters); }
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return dt;
}
private void btnInsert_Click(object sender, EventArgs e)
{
try
{
DataRow newRow = deathstarDataSet.WORKORDER.NewRow();
newRow["orderID"] = txtWrkOrdID.Text;
newRow["planeNo"] = cbxPlaneNo.Text;
newRow["taskID"] = cbxTaskID.Text;
newRow["strDat"] = dteStart.Text;
newRow["endDat"] = dteEnd.Text;
newRow["woStat"] = txtStatus.Text;
newRow["partsLbr"] = numCost.Text;
newRow["lbrHours"] = numLabor.Text;
deathstarDataSet.WORKORDER.AddWORKORDERRow((DeathstarDataSet.WORKORDERRow)newRow);
deathstarDataSet.WORKORDER.AcceptChanges();
iDB2DataAdapter dataAdapter = new iDB2DataAdapter();
dataAdapter.Update(deathstarDataSet.WORKORDER);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public override void executeDynamicSqlWithReturnValue( String sqlString, ref DataSet resultDataSet )
{
try
{
iDB2Command command = _connection.CreateCommand();
command.CommandText = sqlString;
command.CommandType = System.Data.CommandType.Text;
command.Transaction = (null != _transaction) ? _transaction : null;
resultDataSet = new DataSet();
iDB2DataAdapter dataAdapter = new iDB2DataAdapter( command );
dataAdapter.Fill( resultDataSet );
}
catch( System.Exception e )
{
throw new Persistence.Exceptions.DynamicSqlException( e.Message );
}
}
private static void UpdateFoundNotPart(string guid)
{
using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
string query = GetPartNotFoundUpdateMasterString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(query, conn);
cmd.Connection.Open();
objDataAdapter.UpdateCommand = cmd;
objDataAdapter.UpdateCommand.CommandTimeout = 0;
cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char);
cmd.Parameters["@guid"].Value = guid;
cmd.Parameters.Add("@retrn", iDB2DbType.iDB2Char);
cmd.Parameters["@retrn"].Value = 'R';
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
private static void UpdateFoundPart(string guid, GetMasterResult getResult)
{
using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
string query = GetPartFoundUpdateMasterString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(query, conn);
cmd.Connection.Open();
objDataAdapter.UpdateCommand = cmd;
objDataAdapter.UpdateCommand.CommandTimeout = 0;
cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char);
cmd.Parameters["@guid"].Value = guid;
cmd.Parameters.Add("@usrid", iDB2DbType.iDB2Char);
cmd.Parameters["@usrid"].Value = (getResult.UserID.Trim().Length > 0) ? getResult.UserID : " ";
cmd.Parameters.Add("@branded", iDB2DbType.iDB2Char);
cmd.Parameters["@branded"].Value = getResult.Branded;
cmd.Parameters.Add("@comcode", iDB2DbType.iDB2Char);
cmd.Parameters["@comcode"].Value = (getResult.Commodity_Code.Trim().Length > 0)
? getResult.Commodity_Code
: " ";
cmd.Parameters.Add("@level", iDB2DbType.iDB2Integer);
cmd.Parameters["@level"].Value = getResult.Level;
cmd.Parameters.Add("@status", iDB2DbType.iDB2Char);
cmd.Parameters["@status"].Value = (getResult.Status.Trim().Length > 0) ? getResult.Status : " ";
cmd.Parameters.Add("@rtnble", iDB2DbType.iDB2Char);
cmd.Parameters["@rtnble"].Value = getResult.Returnable;
cmd.Parameters.Add("@tariffcd", iDB2DbType.iDB2Char);
cmd.Parameters["@tariffcd"].Value = (getResult.Tariff_Code.Trim().Length > 0)
? getResult.Tariff_Code
: " ";
cmd.Parameters.Add("@amsc", iDB2DbType.iDB2Char);
cmd.Parameters["@amsc"].Value = (getResult.AMSC.Trim().Length > 0) ? getResult.AMSC : " ";
cmd.Parameters.Add("@tqty", iDB2DbType.iDB2Integer);
cmd.Parameters["@tqty"].Value = getResult.Technical_Qty;
cmd.Parameters.Add("@svclife", iDB2DbType.iDB2Integer);
cmd.Parameters["@svclife"].Value = getResult.Service_Life;
cmd.Parameters.Add("@pkgcode", iDB2DbType.iDB2Char);
cmd.Parameters["@pkgcode"].Value = (getResult.Package_Code.Trim().Length > 0)
? getResult.Package_Code
: " ";
cmd.Parameters.Add("@info", iDB2DbType.iDB2Char);
cmd.Parameters["@info"].Value = (getResult.Information.Trim().Length > 0)
? getResult.Information
: " ";
cmd.Parameters.Add("@retrn", iDB2DbType.iDB2Char);
cmd.Parameters["@retrn"].Value = 'R';
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
private static void InsertCrossParts(string guid, GetMasterResult getResult)
{
foreach (CrossPart s in getResult.CrossPartList)
{
using (
var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
string query = GetCrossPartInsertString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(query, conn);
cmd.Connection.Open();
objDataAdapter.InsertCommand = cmd;
objDataAdapter.InsertCommand.CommandTimeout = 0;
cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char);
cmd.Parameters["@guid"].Value = guid;
cmd.Parameters.Add("@item", iDB2DbType.iDB2Char);
cmd.Parameters["@item"].Value = s.PartNumber;
cmd.Parameters.Add("@brand", iDB2DbType.iDB2Char);
cmd.Parameters["@brand"].Value = s.Brand;
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
private void GetAepmMaster()
{
var dt = new DataTable();
var dset = new DataSet();
//retrieve any records needing updating
try
{
using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
// string sql = @"SELECT G_GUID, G_ITEM, G_RETRN FROM CGJLEGAC.GETMSTRL1 WHERE G_RETRN != 'R' and G_RETRN != 'S'";
string sql = GetUnprocessMasterRecsString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(sql, conn);
objDataAdapter.SelectCommand = cmd;
objDataAdapter.SelectCommand.CommandTimeout = 0;
dt.Clear();
dset.Clear();
objDataAdapter.Fill(dt);
objDataAdapter.Fill(dset, "currentSelections");
var cb = new iDB2CommandBuilder(objDataAdapter);
AddParameters(cb);
//update records to 'S' for submitted
for (int i = 0; i < dset.Tables["currentSelections"].Rows.Count; i++)
{
dset.Tables["currentSelections"].Rows[i]["G_RETRN"] = 'S';
}
objDataAdapter.Update(dset, "currentSelections");
var taskList = new Task[dt.Rows.Count];
//submit records asynch
for (int i = 0; i < dt.Rows.Count; i++)
{
taskList[i] = TestAsync(dt, i);
}
Task.WaitAll(taskList);
}
}
catch (Exception ex)
{
WriteToEventLog(ex);
}
}
public void ConnectionAs400()
{
try
{
GetConnection();
var query = "select nrocta, ayn, ndosol, cotit, ndocso, estad from vivilib.maevivi";
using (iDB2Command cmd = new iDB2Command(query, conAS400))
{
cmd.CommandType = CommandType.Text;
iDB2DataAdapter da = new iDB2DataAdapter(cmd);
DataTable dt = new DataTable();
var response = new List <TitularViewModel>();
conAS400.Open();
da.Fill(dt);
foreach (DataRow item in dt.Rows)
{
response.Add(MapToValueAs400(item));
}
conSql01.Open();
/*Delete all data */
SqlCommand cmd1 = conSql01.CreateCommand();;
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "DELETE FROM Titular";
cmd1.ExecuteNonQuery();
/*Fin delete*/
using (SqlTransaction oTransaction = conSql01.BeginTransaction())
{
using (SqlCommand oCommand = conSql01.CreateCommand())
{
oCommand.Transaction = oTransaction;
oCommand.CommandType = CommandType.Text;
oCommand.CommandText = "INSERT INTO [Titular] ([AyNTitular] ,[DniTitular] ,[AyNCoTitular] ,[DniCoTitular] ,[NumeroCuenta]) VALUES (@AyNTitular, @DniTitular, @AyNCoTitular, @DniCoTitular, @NumeroCuenta );";
oCommand.Parameters.Add(new SqlParameter("@AyNTitular", SqlDbType.NChar));
oCommand.Parameters.Add(new SqlParameter("@DniTitular", SqlDbType.Decimal));
oCommand.Parameters.Add(new SqlParameter("@AyNCoTitular", SqlDbType.NChar));
oCommand.Parameters.Add(new SqlParameter("@DniCoTitular", SqlDbType.Decimal));
oCommand.Parameters.Add(new SqlParameter("@NumeroCuenta", SqlDbType.Decimal));
try
{
foreach (var oSetting in response)
{
oCommand.Parameters[0].Value = oSetting.ApellidoNombreTitular;
oCommand.Parameters[1].Value = oSetting.DniTitular;
oCommand.Parameters[2].Value = oSetting.ApellidoNombreCoTitular;
oCommand.Parameters[3].Value = oSetting.DniCoTitular;
oCommand.Parameters[4].Value = oSetting.NumeroCuenta;
if (oCommand.ExecuteNonQuery() != 1)
{
//'handled as needed,
//' but this snippet will throw an exception to force a rollback
throw new InvalidProgramException();
}
}
oTransaction.Commit();
}
catch (Exception)
{
oTransaction.Rollback();
throw;
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Executes a stored procedure that returns a result set.
/// </summary>
/// <param name="connString">The conn string.</param>
/// <param name="sqlStatement">The SQL statement.</param>
/// <param name="parameters">The parameters.</param>
public DataTable ExecuteStoredProcedureWithResultSet(string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null) { parameters(cmd.Parameters); }
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return dt;
}
private void searchButton_Click(object sender, EventArgs e)
{
staffListBox.Items.Clear();
try
{
//Connection string
connectionString = "Data Source = deathstar.gtc.edu; User ID = itpa472; Initial Catalog = S101FF5C";
//SQL Statement
sql = "SELECT * FROM CREW";
connection = new iDB2Connection(connectionString); //Assign connection string to the idb2 connection object
dataAdapter = new iDB2DataAdapter(sql, connection); //Assign idb2 connection and sql statement to the data adapter
dataset = new DataSet(); //New DataSet
dataAdapter.Fill(dataset); //Fill data adapter with data set
string[] flights = new string[10]; //Array to hold valid flight numbers
string number = flightTextBox.Text.ToUpper(); //initiate a variable with the value of the input textbox and convert to upper case
int count = 0; //counter for the loop
//Loop to add valid flight numbers into the flights array
foreach (DataRow dataFields in dataset.Tables[0].Rows)
{
flights[count] = dataFields[0].ToString();
if (number == flights[count])
{
sql = "SELECT * FROM CREW WHERE FLIGHTNO = " + number;
dataAdapter = new iDB2DataAdapter(sql, connection);
staffListBox.Items.Add("Staff in Flight: " + number);
staffListBox.Items.Add("");
foreach (DataRow dataRow in dataset.Tables[0].Rows)
{
staffListBox.Items.Add("Pilots: " + dataFields[2]);
staffListBox.Items.Add("Attendants: " + dataFields[3]);
staffListBox.Items.Add("Clerk: " + dataFields[4]);
staffListBox.Items.Add("Maintenance: " + dataFields[5]);
staffListBox.Items.Add("Janitor: " + dataFields[6]);
staffListBox.Items.Add("Refueling: " + dataFields[7]);
staffListBox.Items.Add("");
break;
}
break;
}
else if (flights[count] != number && count == 9)
{
staffListBox.Items.Add("INVALID FLIGHT NUMBER!!!");
printInputStmt();
}
else
{
count++;
}
}
connection.Close(); //Close connection
}
catch (Exception ex)
{
staffListBox.Items.Add(ex.Message);
}
}
