IMPLEMENTING THE BUSINESS LOGIC |
Chapter 21 |
509 |
|
sqlDataAdapter1.Fill(dataSet11, “FltDetails”); |
|
|
|
|
|
|
|
sqlConnection1.Close(); |
|
|
|
bool exists=false; |
|
|
|
foreach (DataRow myRow in dataSet11.Tables[“FltDetails”].Rows) |
|
|
|
{ |
|
|
|
if (myRow[0].ToString().Trim().ToLower()==txtFltNo.Text.ToLower())
{
exists=true; txtOrg.Text=myRow[1].ToString(); txtDest.Text=myRow[2].ToString();
txtDepTime.Text=myRow[3].ToString().Substring(myRow[3].ToString(). Length-11).Trim();
if(lstClass.SelectedIndex==0) txtFare.Text=myRow[8].ToString();
else txtFare.Text=myRow[9].ToString();
}
}
if (exists==false)
{
lblMessage.Text=”Incorrect flight number. Please try again”; return;
}
txtTNo.Text=”Auto generated”; txtFltNo.Enabled=false; lstClass.Enabled=false; Cal1.Enabled=false;
sqlDataAdapter2.SelectCommand.Parameters[0].Value=txtFltNo.Text.Trim(); sqlDataAdapter2.SelectCommand.Parameters[1].Value=Cal1.SelectedDate.
ToShortDateString(); sqlDataAdapter2.SelectCommand.Parameters[2].Value=lstClass. SelectedItem.Text; sqlConnection1.Open();
sqlDataAdapter2.Fill(dataSet11, “FltStatus”); if (dataSet11.Tables[“FltStatus”].Rows.Count==0)
{
txtStatus.Text=”Available”;
}
510 Project 4 CREATING AN AIRLINE RESERVATION PORTAL
else
{
int status=Convert.ToInt32(dataSet11.Tables[“FltStatus”].Rows[0][3]); if (status<=0)
{
txtStatus.Text=”Waitlisted (“ + Convert.ToString((status-1)) + “)”;
}
else
{
txtStatus.Text=”Available”;
}
}
}
After the customer agrees to proceed with the reservation, the details of the flight are retrieved from the dtFltDetails and the dtFltStatus tables. The status of the flight is retrieved to ensure that the flight status has not changed between the time when the request for reservation was first made to the actual processing of the process. This functionality is achieved by the following code snippet:
if (txtName.Text==”” || txtName.Text==null)
{
lblMessage.Text=”Invalid user name”; return;
}
string TicketNo, DateOfRes, DateOfJourney, FltNo, ClassOfRes, Name, EMail; int TicketConf, Status, Fare;
try
{
FltNo=txtFltNo.Text.Trim(); ClassOfRes=lstClass.SelectedItem.Text; Name=txtName.Text; DateOfRes=DateTime.Today.Date.ToShortDateString(); DateOfJourney=Cal1.SelectedDate.ToShortDateString(); TicketConf=0; Fare=Convert.ToInt32(txtFare.Text.Trim()); dataSet11.Clear();
sqlConnection1.Open();
IMPLEMENTING THE BUSINESS LOGIC |
Chapter 21 |
511 |
|
|
|
|
|
sqlDataAdapter2.SelectCommand.Parameters[0].Value=txtFltNo.Text.Trim(); sqlDataAdapter2.SelectCommand.Parameters[1].Value= Cal1.SelectedDate
.ToShortDateString();
sqlDataAdapter2.SelectCommand.Parameters[2].Value= lstClass.SelectedItem.Text; sqlDataAdapter2.Fill(dataSet11, “FltStatus”);
if (dataSet11.Tables[“FltStatus”].Rows.Count==0)
{
//fill in the flight details sqlDataAdapter1.Fill(dataSet11, “FltDetails”); string strTotSeats;
int intTotSeats;
foreach (DataRow myRow in dataSet11.Tables[“FltDetails”].Rows)
{
if (myRow[0].ToString().Trim().ToLower()==txtFltNo.Text.ToLower())
{
if(lstClass.SelectedIndex==0)
{
strTotSeats=myRow[6].ToString();
}
else
{
strTotSeats=myRow[7].ToString();
}
intTotSeats=Convert.ToInt32(strTotSeats); sqlDataAdapter2.InsertCommand.Parameters[0].Value= txtFltNo.Text
.Trim();
sqlDataAdapter2.InsertCommand.Parameters[1].Value= Cal1.SelectedDate
.ToShortDateString(); sqlDataAdapter2.InsertCommand.Parameters[2].Value= lstClass
.SelectedItem.Text; sqlDataAdapter2.InsertCommand.Parameters[3].Value=intTotSeats-1; sqlDataAdapter2.InsertCommand.ExecuteNonQuery();
}
}
//set status as available Status=1;
}
512 Project 4 CREATING AN AIRLINE RESERVATION PORTAL
else
{
int val=Convert.ToInt32(dataSet11.Tables[“FltStatus”].Rows[0][3]); if (val<=0)
{
Status=val-1;
}
else
{
Status=1;
}
sqlDataAdapter2.UpdateCommand.Parameters[0].Value=txtFltNo.Text.Trim(); sqlDataAdapter2.UpdateCommand.Parameters[1].Value= Cal1.SelectedDate
.ToShortDateString(); sqlDataAdapter2.UpdateCommand.Parameters[2].Value= lstClass.SelectedItem
.Text; sqlDataAdapter2.UpdateCommand.ExecuteNonQuery();
}
The information that is retrieved from the database tables is updated into the dtReservations table. To update information into the dtReservations table, the following code snippet is used:
sqlDataAdapter3.Fill(dataSet11, “TicketNos”); int count, maxno, ticketno;
if (dataSet11.Tables[“TicketNos”].Rows.Count>0)
{
maxno=Convert.ToInt32(dataSet11.Tables[“TicketNos”].Rows[0][0].ToString()); for (count=1; count < dataSet11.Tables[“TicketNos”].Rows.Count; count++)
{
if (maxno < Convert.ToInt32(dataSet11.Tables[“TicketNos”].Rows[count][0]
.ToString()))
maxno=Convert.ToInt32(dataSet11.Tables[“TicketNos”].Rows[count][0].ToString());
}
}
else
{
maxno=0;
}
IMPLEMENTING THE BUSINESS LOGIC |
Chapter 21 |
513 |
|
|
|
|
|
ticketno=maxno+1; TicketNo=Convert.ToString(ticketno); EMail=txtEMail.Text;
if (EMail==null || EMail==””)
{
EMail=”NotSpecified”;
}
else
{
sqlDataAdapter4.SelectCommand.Parameters[0].Value=EMail; sqlDataAdapter4.Fill(dataSet11,”FreqFl”);
if (dataSet11.Tables[“FreqFl”].Rows.Count==0)
{
//do nothing to the fare
}
else
{
int discount; discount=Convert.ToInt32(dataSet11.Tables[“FltStatus”].Rows[0][0]);
discount=(100-discount)/100; Fare=Fare-discount;
}
}
sqlDataAdapter3.InsertCommand.Parameters[0].Value=TicketNo; sqlDataAdapter3.InsertCommand.Parameters[1].Value=FltNo; sqlDataAdapter3.InsertCommand.Parameters[2].Value=DateOfJourney; sqlDataAdapter3.InsertCommand.Parameters[3].Value=ClassOfRes; sqlDataAdapter3.InsertCommand.Parameters[4].Value=Name; sqlDataAdapter3.InsertCommand.Parameters[5].Value=EMail; sqlDataAdapter3.InsertCommand.Parameters[6].Value=Fare; sqlDataAdapter3.InsertCommand.Parameters[7].Value=Status; sqlDataAdapter3.InsertCommand.Parameters[8].Value=Session[“usrName”].ToString(); sqlDataAdapter3.InsertCommand.Parameters[9].Value=DateOfRes; sqlDataAdapter3.InsertCommand.Parameters[10].Value=TicketConf; sqlDataAdapter3.InsertCommand.ExecuteNonQuery();
sqlConnection1.Close();
lblMessage.Text=”Reservation complete. Fare is US$ “+ Fare.ToString(); txtFltNo.Text=””;