using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using System.Data.SqlClient;namespace FrmHangBanUser{public partial class FrmUser : Form{//1.連接字符串string connString = "Data Source = .;Initial Catalog=Ticket;User ID = sa; Pwd = sa";//3.創建DataSet對象DataSet set = new DataSet();public FrmUser(){InitializeComponent();}#region 窗體加載事件/// <summary>/// 窗體加載事件！！/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void FrmUser_Load(object sender, EventArgs e){//FlightInfo();AirwaysInfo();CityInfo();}#endregion#region 出發地/// <summary>/// 出發地/// </summary>public void AirwaysInfo(){try{//2.創建Connection對象SqlConnection conn = new SqlConnection(connString);//4.創建DataAdapter對象StringBuilder _sb = new StringBuilder();_sb.AppendLine(@"SELECT Id,CityName FROM CityInfo");SqlDataAdapter adapter = new SqlDataAdapter(_sb.ToString(), conn);//5.填充數據集adapter.Fill(set, "CityUser");//6.綁定數據源到ComboBox控件中this.cboAirways.DataSource = set.Tables["CityUser"];this.cboAirways.ValueMember = "Id";this.cboAirways.DisplayMember = "CityName";//7.添加行對象DataRow row = set.Tables["CityUser"].NewRow();row["Id"] = -1;row["CityName"] = "請選擇";set.Tables["CityUser"].Rows.InsertAt(row, 0);//8.默認選中一行this.cboAirways.SelectedIndex = 0;}catch (Exception ex){MessageBox.Show(ex.Message);}}#endregion#region 非空驗證/// <summary>/// 非空驗證/// </summary>public void Check(){if(this.cboAirways.SelectedIndex == 0){MessageBox.Show("請輸入你要出發的城市?。。?quot;,"操作提示",MessageBoxButtons.OKCancel,MessageBoxIcon.Question);}else if (this.cboMudidi.SelectedIndex == 0){MessageBox.Show("請輸入你的目的地啊啊?。。?quot;, "操作提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);}}#endregion#region 目的地/// <summary>/// 目的地/// </summary>public void CityInfo(){try{//2.創建Connection對象SqlConnection conn = new SqlConnection(connString);//4.創建DataAdapter對象StringBuilder _sb = new StringBuilder();_sb.AppendLine(@"SELECT Id,CityName FROM CityInfo");SqlDataAdapter adapter = new SqlDataAdapter(_sb.ToString(), conn);//5.填充數據集adapter.Fill(set, "City");//6.綁定數據源到ComboBox控件中this.cboMudidi.DataSource = set.Tables["City"];this.cboMudidi.ValueMember = "Id";this.cboMudidi.DisplayMember = "CityName";//7.添加行對象DataRow row = set.Tables["City"].NewRow();row["Id"] = -1;row["CityName"] = "請選擇";set.Tables["City"].Rows.InsertAt(row, 0);//8.默認選中一行this.cboMudidi.SelectedIndex = 0;}catch (Exception ex){MessageBox.Show(ex.Message);}}#endregion#region 航班信息/// <summary>/// 航班信息/// </summary>public void FlightInfo(){try{//2.創建Connection對象SqlConnection conn = new SqlConnection(connString);//4.創建DataAdapter對象StringBuilder _sb = new StringBuilder();_sb.AppendLine(@"SELECT F.FlightNO,A.Airways,F.LeaveTime,F.LandTime,F.PriceFROM FlightInfo AS F INNER JOIN AirwaysInfo AS A ON F.AirwaysId = A.Id");SqlDataAdapter adapter = new SqlDataAdapter(_sb.ToString(), conn);//5.填充數據集if (set.Tables["Airways"]!=null){set.Tables["Airways"].Clear();}adapter.Fill(set, "Airways");//6.綁定數據源this.dvgUserInfo.DataSource = set.Tables["Airways"];}catch (Exception ex){MessageBox.Show(ex.Message);}}#endregion#region 查詢按鈕功能/// <summary>/// 查詢按鈕功能/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void btnChaXun_Click(object sender, EventArgs e){Check();Filter();}#endregion#region 按選擇的條件篩選/// <summary>/// 按選擇的條件篩選/// </summary>public void Filter(){try{SqlConnection conn=new SqlConnection(connString);//篩選條件DataSet ds = new DataSet();int city = Convert.ToInt32(cboAirways.SelectedValue);int Destination = Convert.ToInt32(cboMudidi.SelectedValue);StringBuilder sb = new StringBuilder();if(city!=-1 && Destination!=-1){sb.AppendLine(@"SELECT F.FlightNO,A.Airways,F.LandTime,F.LeaveTime,F.PriceFROM FlightInfo AS F INNER JOIN AirwaysInfo AS A ON F.AirwaysId = A.Id");sb.AppendFormat(@"WHERE LeaveCity = {0} AND Destination = {1}", city, Destination);}SqlDataAdapter adapter = new SqlDataAdapter(sb.ToString(),conn);adapter.Fill(ds,"User");this.dvgUserInfo.DataSource = ds.Tables["User"];}catch (Exception ex){MessageBox.Show(ex.Message);}}#endregion#region 關閉按鈕功能/// <summary>/// 關閉按鈕功能/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void btnGuanbi_Click(object sender, EventArgs e){DialogResult result = MessageBox.Show("你確定要退出程序嘛~","退出提示",MessageBoxButtons.OKCancel,MessageBoxIcon.Stop);if(result == DialogResult.OK){Application.Exit(); //退出程序了。。 - - |||}}#endregion#region 實現航班選擇功能/// <summary>/// 實現航班選擇功能/// </summary>private void dvgUserInfo_MouseClick(object sender, MouseEventArgs e){txtHNo.Text = dvgUserInfo.SelectedRows[0].Cells["FlightNO"].Value.ToString();txtGongSi.Text = dvgUserInfo.SelectedRows[0].Cells["Airways"].Value.ToString();this.txtChuFa.Text = this.cboAirways.Text;this.txtMuDi.Text = this.cboMudidi.Text;txtShijian.Text = dvgUserInfo.SelectedRows[0].Cells["LeaveTime"].Value.ToString();txtDaoDa.Text = dvgUserInfo.SelectedRows[0].Cells["LandTime"].Value.ToString();txtPiaoJia.Text = dvgUserInfo.SelectedRows[0].Cells["Price"].Value.ToString();}#endregion#region 航班預定功能/// <summary>/// 航班預定功能/// </summary>/// <returns></returns>public bool Insert(){Random _dom = new Random();int no = _dom.Next(100000, 1000000);SqlConnection conn = null;string No = this.txtHNo.Text;DateTime LeaveDate = this.dateTimePicker1.Value;string Number = this.nuShang.Value.ToString();try{conn = new SqlConnection(connString);//構建插入學生記錄的SQL的語句StringBuilder _sbu = new StringBuilder();_sbu.AppendLine("INSERT INTO OrderInfo(OrderId,FlightNo,LeaveDate,Number)");_sbu.AppendFormat("VALUES('{0}','{1}','{2}','{3}')", no, No, LeaveDate, Number);_sbu.AppendFormat("SELECT @@IDENTITY");//創建Command對象SqlCommand command = new SqlCommand(_sbu.ToString(), conn);//打開連接conn.Open();//調用方法int result = command.ExecuteNonQuery();//對返回值進行處理if (result > 0){MessageBox.Show("恭喜你！預定成功！訂單編號為"+no);return true;}else{MessageBox.Show("預定失敗！請重試！");return false;}}catch (Exception ex){MessageBox.Show(ex.Message);return false;}finally{//關閉連接conn.Close();}}#endregion#region 預定按鈕/// <summary>/// 預定按鈕！/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void btnYuDing_Click(object sender, EventArgs e){Insert();}#endregion}}