// Name: ProfileRemove - Delete the specified profile // // Description: // Deletes the profile identified by the email. // // Inputs: // profileDataSet Data set. // profileAddr Identifying profile address. // // Outputs: // profileDataSet Updated result set. // // Returns: // rowsDeleted Number of rows affected by the query. // // History: // 02-Oct-2006 ([email protected]) // Created. // 11-Aug-2009 ([email protected]) // Added Transaction before Prepare is called on the // delete command. public int ProfileRemove(DataSet profileDataSet, string profileAddr) { int rowsDeleted = 0; IngresParameter email = new IngresParameter("up_email", IngresType.NChar); try { IngresTransaction delete; email.Value = profileAddr; ingresDataAdapter6.DeleteCommand.Connection.Open(); delete = ingresDataAdapter6.DeleteCommand.Connection.BeginTransaction(); ingresDataAdapter6.DeleteCommand.Transaction = delete; ingresDataAdapter6.DeleteCommand.Prepare(); ingresDataAdapter6.DeleteCommand.Parameters.Clear(); ingresDataAdapter6.DeleteCommand.Parameters.Add(email); rowsDeleted = ingresDataAdapter6.DeleteCommand.ExecuteNonQuery(); DialogResult result = MessageBox.Show("Profile: " + profileAddr, "Remove profile", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.No) { rowsDeleted = 0; delete.Rollback(); } else if (result == DialogResult.Yes) { delete.Commit(); } } catch (IngresException ex) { DialogResult result = DisplayError(ex); if (result != DialogResult.OK) { throw; } } finally { if (ingresDataAdapter6 != null && ingresDataAdapter6.DeleteCommand != null && ingresDataAdapter6.DeleteCommand.Connection != null) ingresDataAdapter6.DeleteCommand.Connection.Close(); } return (rowsDeleted); }
// Name: LoadAirportName - Populate s data set with airport names // // Description: // Execute a query to return the airport codes with their names. // // Inputs: // direction Specifies which store country code to use. // area Specifies the place value to use in the // WHERE clause. // airportNameTable Area to store the result set. // // Outputs: // airportNameTable Updated with result set. // // Returns: // None. // // History: // 02-Oct-2006 ([email protected]) // Created. public void LoadAirportName(int direction, String area, System.Data.DataTable airportNameTable) { IngresParameter country = new Ingres.Client.IngresParameter("ap_ccode", IngresType.NChar); IngresParameter region = new Ingres.Client.IngresParameter("ap_place", IngresType.NVarChar); region.Value = area; airportNameTable.Clear(); try { ingresDataAdapter4.SelectCommand.Connection.Open(); ingresDataAdapter4.SelectCommand.Prepare(); ingresDataAdapter4.SelectCommand.Parameters.Clear(); switch (direction) { case DEPART: country.Value = departing.country; break; case ARRIVE: country.Value = arriving.country; break; } ingresDataAdapter4.SelectCommand.Parameters.Add(country); ingresDataAdapter4.SelectCommand.Parameters.Add(region); ingresDataAdapter4.Fill(airportNameTable); } finally { if (ingresDataAdapter4 != null && ingresDataAdapter4.SelectCommand != null && ingresDataAdapter4.SelectCommand.Connection != null) ingresDataAdapter4.SelectCommand.Connection.Close(); } }
//
// Profiles
// --------
//
// Name: LoadProfile - Returns the values for a specfied profile
//
// Description:
// Executes the query to return the values identified by the
// profile email address.
//
// Inputs:
// profileDataTable Area to store query results.
// email Profile identifying email address.
//
// Outputs:
// profileDataTable Updated result table.
//
// Returns:
// None.
//
// History:
// 02-Oct-2006 ([email protected])
// Created.
public void LoadProfile(DataTable profileDataTable, String email)
{
IngresParameter up_email =
new Ingres.Client.IngresParameter("up_email", IngresType.NChar);
try
{
up_email.Value = email;
profileDataTable.Clear();
ingresDataAdapter6.SelectCommand.Connection.Open();
ingresDataAdapter6.SelectCommand.Prepare();
ingresDataAdapter6.SelectCommand.Parameters.Clear();
ingresDataAdapter6.SelectCommand.Parameters.Add(up_email);
ingresDataAdapter6.Fill(profileDataTable);
}
catch (IngresException ex)
{
DialogResult result = DisplayError(ex);
}
finally
{
if (ingresDataAdapter6 != null &&
ingresDataAdapter6.SelectCommand != null &&
ingresDataAdapter6.SelectCommand.Connection != null)
ingresDataAdapter6.SelectCommand.Connection.Close();
}
}
// Name: CheckVersion - Test the stored schema version
//
// Description:
// Compares the specified version number with the stored version
// If the verson matches or exceeds the required version the
// function returns true.
//
// Inputs:
// Major Major version number.
// Minor Minor version number.
// Release Release version number.
//
// Outputs:
// None.
//
// Returns:
// true Version matches or exceeds the required version.
// false Incorrect version level.
//
// History
// 02-Oct-2006 ([email protected])
// Created.
public bool CheckVersion(int Major, int Minor, int Release)
{
bool retcode = false;
IngresCommand ingresVersionSelect = new IngresCommand();
IngresDataAdapter ingresVersionAdapter;
// Create query parameters and store the method parameter values
// into each one.
IngresParameter majorVersion = new IngresParameter("ver_major", IngresType.Int);
IngresParameter minorVersion = new IngresParameter("ver_minor", IngresType.Int); ;
IngresParameter releaseVersion = new IngresParameter("ver_release", IngresType.Int);
majorVersion.Value = Major;
minorVersion.Value = Minor;
releaseVersion.Value = Release;
ingresVersionSelect = new IngresCommand(
"SELECT FIRST 1 ver_major, ver_minor, ver_release, ver_date, ver_install" +
" FROM version" +
" WHERE ver_major >= ? AND ver_minor >= ? AND ver_release >=?" +
" ORDER BY ver_id DESC",
ingresConnection1);
ingresVersionSelect.Connection = ingresConnection1;
ingresVersionAdapter = new IngresDataAdapter(ingresVersionSelect);
try
{
ingresVersionSelect.Connection.Open();
ingresVersionSelect.Prepare();
ingresVersionSelect.Parameters.Clear();
ingresVersionSelect.Parameters.Add(majorVersion);
ingresVersionSelect.Parameters.Add(minorVersion);
ingresVersionSelect.Parameters.Add(releaseVersion);
versionDataSet.Clear();
ingresVersionAdapter.Fill(versionDataSet);
if (versionDataSet.Tables[0].Rows.Count > 0)
{
retcode = true;
}
}
catch (Ingres.Client.IngresException ex)
{
if (DisplayError(ex) == DialogResult.OK)
{
throw;
}
else
{
Application.Exit();
}
}
finally
{
if (ingresVersionSelect != null &&
ingresVersionSelect.Connection != null)
ingresVersionSelect.Connection.Close();
}
return (retcode);
}
// Name: LoadAirport - populate a dataset with retrieved data
//
// Description:
// Perform a call to a row producing procedure to populate a data
// set with results from the database.
//
// Inputs:
// direction Sets the parameters according to direction.
// area
// airportDataSet
//
// Outputs:
// airpotDataSet
//
// Returns:
// None.
//
// History:
// 02-Oct-2006 ([email protected])
// Created.
// 13-Oct-2006 ([email protected])
// Add start row to the result set.
public void LoadAirport(int direction, String area, System.Data.DataTable airportTable)
{
DataRow dataRow;
IngresParameter country = new Ingres.Client.IngresParameter(
"ap_ccode", IngresType.NChar);
IngresParameter region = new Ingres.Client.IngresParameter(
"ap_place", IngresType.NVarChar);
region.Value = area;
airportTable.Clear();
dataRow = airportTable.NewRow();
dataRow["ap_iatacode"] = rm.GetString("StartAirportCode"); ;
airportTable.Rows.InsertAt(dataRow, 0);
try
{
ingresDataAdapter3.SelectCommand.Connection.Open();
ingresDataAdapter3.SelectCommand.Prepare();
ingresDataAdapter3.SelectCommand.Parameters.Clear();
switch (direction)
{
case DEPART:
country.Value = departing.country;
departing.region = region.Value.ToString();
break;
case ARRIVE:
country.Value = arriving.country;
arriving.region = region.Value.ToString();
break;
}
ingresDataAdapter3.SelectCommand.Parameters.Add(country);
ingresDataAdapter3.SelectCommand.Parameters.Add(region);
ingresDataAdapter3.Fill(airportTable);
}
finally
{
if (ingresDataAdapter3 != null &&
ingresDataAdapter3.SelectCommand != null &&
ingresDataAdapter3.SelectCommand.Connection != null)
ingresDataAdapter3.SelectCommand.Connection.Close();
}
}
//
// Routes
// ------
//
// Name: RouteSearch
//
// Description:
// Executes a SELECT query to retrieve routes.
// Query command text is varied according to the round trip
// parameter as is the WHERE clause.
//
// Inputs:
// routeDataSet Area to store query results.
// departingAirport Departing airport code.
// arrivingAirport Arriving airport code.
// flightDays Prefered days mask.
// roundTrip Flag to include return flights
//
// Outputs:
// routeDataSet Updated with result set.
//
// Returns:
// None.
//
// History:
// 02-Oct-2006 ([email protected])
// Created.
public void RouteSearch(DataSet routeDataSet, String departingAirport,
String arrivingAirport, String flightDays, CheckState roundtrip)
{
IngresParameter depart =
new Ingres.Client.IngresParameter("rt_depart_from",
IngresType.NChar);
IngresParameter arrive =
new Ingres.Client.IngresParameter("rt_arrive_to",
IngresType.NChar);
IngresParameter returnTripDepartingAirport =
new Ingres.Client.IngresParameter("rt_depart_from",
IngresType.NChar);
IngresParameter returnTripArrivingAirport =
new Ingres.Client.IngresParameter("rt_arrive_to",
IngresType.NChar);
IngresParameter onDays =
new Ingres.Client.IngresParameter("rt_flight_day",
IngresType.NChar);
depart.Value = departingAirport;
arrive.Value = arrivingAirport;
returnTripDepartingAirport.Value = arrivingAirport;
returnTripArrivingAirport.Value = departingAirport;
onDays.Value = flightDays;
routeDataSet.Clear();
// Clear the query parameter list, parameters need to be added in
// the order they appear in the query.
ingresDataAdapter5.SelectCommand.Parameters.Clear();
// Add route query parameters
ingresDataAdapter5.SelectCommand.Parameters.Add(depart);
ingresDataAdapter5.SelectCommand.Parameters.Add(arrive);
if (roundtrip == CheckState.Checked)
{
// Set the query command text with additional WHERE clause
ingresDataAdapter5.SelectCommand.CommandText =
String.Format(rm.GetString("RouteQuery"),
rm.GetString("RouteQueryRoundTripWhereClause"));
// Add return trip query paramters
ingresDataAdapter5.SelectCommand.Parameters.Add(returnTripDepartingAirport);
ingresDataAdapter5.SelectCommand.Parameters.Add(returnTripArrivingAirport);
}
else
{
// Set the query command text
ingresDataAdapter5.SelectCommand.CommandText =
String.Format(rm.GetString("RouteQuery"), "");
}
ingresDataAdapter5.SelectCommand.Parameters.Add(onDays);
try
{
ingresDataAdapter5.SelectCommand.Connection.Open();
ingresDataAdapter5.SelectCommand.Prepare();
ingresDataAdapter5.Fill(routeDataSet);
}
finally
{
if (ingresDataAdapter5 != null &&
ingresDataAdapter5.SelectCommand != null &&
ingresDataAdapter5.SelectCommand.Connection != null)
ingresDataAdapter5.SelectCommand.Connection.Close();
}
}
// Name: LoadRegion - populate a dataset with retrieved data
//
// Description:
// Perform a SELECT query to populate a data set with results
// from the database.
//
// Inputs:
// direction Saves the country name according to direction.
// regionDataSet
//
// Outputs:
// regionDataSet
//
// Returns:
// None.
//
// History:
// 02-Oct-2006 ([email protected])
// Created.
// 13-Oct-2006 ([email protected])
// Add start row to the result set.
private void LoadRegion(int direction, String nation,
System.Data.DataSet placeDataSet)
{
DataRow dr;
IngresParameter country = new
Ingres.Client.IngresParameter("ap_ccode", IngresType.NChar);
country.Value = nation;
placeDataSet.Clear();
dr = placeDataSet.Tables["airport"].NewRow();
dr["ap_place"] = rm.GetString("StartRegionPlace");
placeDataSet.Tables["airport"].Rows.InsertAt(dr, 0);
try
{
ingresDataAdapter2.SelectCommand.Parameters.Clear();
ingresDataAdapter2.SelectCommand.Connection.Open();
ingresDataAdapter2.SelectCommand.Prepare();
switch (direction)
{
case DEPART:
departing.country = country.Value.ToString();
break;
case ARRIVE:
arriving.country = country.Value.ToString();
break;
}
ingresDataAdapter2.SelectCommand.Parameters.Add(country);
ingresDataAdapter2.Fill(placeDataSet);
}
finally
{
if (ingresDataAdapter2 != null &&
ingresDataAdapter2.SelectCommand != null &&
ingresDataAdapter2.SelectCommand.Connection != null)
ingresDataAdapter2.SelectCommand.Connection.Close();
}
}
/// <summary>
/// Populate the IngresCommand.Parameters collection with data type
/// metadata about the parameters of the specified database procedure.
/// </summary>
/// <param name="command"></param>
public static void DeriveParameters(IngresCommand command)
{
if (command == null)
throw new ArgumentNullException(
"DeriveParameters parameter 'command' must not be null.");
IngresConnection conn = command.Connection;
if (conn == null || conn.State != ConnectionState.Open)
throw new InvalidOperationException(
"The IngresCommand.Connection must be specified and open.");
if (command.CommandText == null ||
command.CommandText.Length == 0)
throw new InvalidOperationException(
"The IngresCommand.CommandText must be specify a procedure name.");
if (command.CommandType != CommandType.StoredProcedure)
throw new InvalidOperationException(
"Only CommandType.StoredProcedure is supported.");
ArrayList tokens = Ingres.ProviderInternals.MetaData.ScanSqlStatement(
command.CommandText);
String[] restriction = new String[3];
if (tokens.Count == 1) // procname
{
restriction[2] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
}
else
if (tokens.Count == 3 && // schemaname.procname
(String)tokens[1] == ".")
{
restriction[1] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
restriction[2] = UnquoteIdent((String)tokens[2], conn, "\"", "\"");
}
else
if (tokens.Count == 5 && // catalogname.schemaname.procname
(String)tokens[1] == "." &&
(String)tokens[3] == ".")
{
restriction[0] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
restriction[1] = UnquoteIdent((String)tokens[2], conn, "\"", "\"");
restriction[2] = UnquoteIdent((String)tokens[4], conn, "\"", "\"");
}
else
throw new InvalidOperationException(
"Invalid procedure name.");
DataTable datatable = conn.GetSchema("ProcedureParameters", restriction);
command.Parameters.Clear();
foreach (DataRow row in datatable.Rows)
{
string name = (String)row["COLUMN_NAME"];
IngresType ingresType = (IngresType)row["INGRESTYPE"];
IngresParameter parm = new IngresParameter(name, ingresType);
command.Parameters.Add(parm);
} // end foreach (DataRow row in datatable)
}
// Name: RouteDelete
//
// Description:
// Executes an DELET query to remove a route.
//
// Inputs:
// dataTable Table containing new values.
//
// Outputs:
// None.
//
// Returns:
// None.
//
// History:
// 02-Oct-2006 ([email protected])
// Created.
public int RouteDelete(DataGridViewRow row)
{
int deleted = 0;
IngresParameter airline = new IngresParameter("rt_airline", IngresType.NChar);
IngresParameter flight = new IngresParameter("rt_flight_num", IngresType.Int);
IngresParameter flight_day = new IngresParameter("rt_flight_day", IngresType.NChar);
IngresCommand delCommand = ingresDataAdapter5.DeleteCommand;
try
{
IngresTransaction delTxn;
airline.Value = row.Cells[0].Value;
flight.Value = row.Cells[2].Value;
flight_day.Value = row.Cells[8].Value;
delCommand.Connection.Open();
delTxn = delCommand.Connection.BeginTransaction();
delCommand.Transaction = delTxn;
delCommand.Prepare();
delCommand.Parameters.Clear();
delCommand.Parameters.Add(airline);
delCommand.Parameters.Add(flight);
delCommand.Parameters.Add(flight_day);
deleted = delCommand.ExecuteNonQuery();
DialogResult result = MessageBox.Show(
String.Format(
rm.GetString("RouteDeleteConfirm"),
airline.Value.ToString(),
flight.Value.ToString(),
row.Cells[3].Value,
row.Cells[4].Value,
row.Cells[8].Value),
rm.GetString("RouteDeleteCaption"),
MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.No)
{
deleted = 0;
delTxn.Rollback();
}
else if (result == DialogResult.Yes)
{
delTxn.Commit();
}
}
catch (IngresException ex)
{
DialogResult result = DisplayError(ex);
throw;
}
finally
{
if (delCommand != null &&
delCommand.Connection != null)
delCommand.Connection.Close();
}
return (deleted);
}
/// <summary>
/// Add a parameter object to the parameter collection.
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public IngresParameter Add(IngresParameter value)
{
_CheckType(value);
IngresParameter parm = (IngresParameter)value;
_CheckParameterAlreadyContained(parm);
_CheckParameterName(parm);
_parmCollection.Add(parm);
return parm;
}
private void _CheckParameterName(IngresParameter parm)
{
if (parm.ParameterName == null || parm.ParameterName.Length == 0)
{
string ParameterN;
while(true)
{
_parameterNameConstructedCount++;
ParameterN = String.Format( // build "Parameter1", etc.
"Parameter{0}", _parameterNameConstructedCount);
if (!this.Contains(ParameterN)) // if no duplicate then break
break;
}
parm.ParameterName = ParameterN;
}
}
private void _CheckParameterAlreadyContained(IngresParameter parm)
{
if (!this.Contains(parm)) // if not in the Collection then return OK
return;
string parmName = parm.ParameterName;
if (parmName == null)
parmName = "";
//Already in the collection! User forgot to cmd.Parameters.Clear()
string msg =
"The ParameterCollection already contains " +
"the Parameter object (ParameterName = '{0}'). " +
"Consider a Clear of the ParameterCollection " +
"such as cmd.Parameters.Clear() before adding the Parameter.";
throw new ArgumentException(String.Format(msg, parmName));
}
/// <summary>
/// Copy the IngresParameter objects from the collection into an
/// IngresParameter[] array beginning at an index.
/// </summary>
/// <param name="array"></param>
/// <param name="index"></param>
public void CopyTo(IngresParameter[] array, int index)
{
((ICollection)this).CopyTo(array, index);
}
/// <summary>
/// Adds an array of IngresParameter to the end of
/// the IngresParameterCollection.
/// </summary>
/// <param name="values"></param>
public void AddRange(IngresParameter[] values)
{
this.AddRange((Array)values);
}
