/// <summary> /// DateGridView的绑定 /// </summary> public void BindResult() { //创建数据库连接 DBHelper dbhelper = new DBHelper(); try { // 查询年级的sql语句 StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT * From RoomDetailDB"); // 填充DataSet SqlDataAdapter adapter = new SqlDataAdapter(sql.ToString(), dbhelper.Connection); //绑定数据源 if (ds.Tables != null) { ds.Tables.Clear(); this.dgvRoomDetail.DataSource = null; this.dgvRoomDetail.Rows.Clear(); } adapter.Fill(ds); this.dgvRoomDetail.DataSource = ds.Tables[0]; } catch (Exception ex) { MessageBox.Show("系统发生错误!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public void BindRoom() { //创建数据库连接 DBHelper dbhelper = new DBHelper(); try { // 查询科目的sql语句 string sql = "SELECT [RoomID] FROM [RoomDetailDB] where [RoomStatus] = 'empty'"; // 打开数据库连接 dbhelper.OpenConnection(); //填充DataSet SqlDataAdapter adapter = new SqlDataAdapter(sql, dbhelper.Connection); ds = new DataSet(); adapter.Fill(ds); //绑定数据源 this.cmbRoom.DataSource = ds.Tables[0]; this.cmbRoom.ValueMember = "RoomID"; this.cmbRoom.DisplayMember = "RoomID"; } catch (Exception ex) { MessageBox.Show("系统发生错误!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { //关闭连接 dbhelper.CloseConnection(); } }
public void ShowManager() { //创建数据库连接 DBHelper dbhelper = new DBHelper(); DataSet ds = new DataSet(); try { // 查询年级的sql语句 StringBuilder sql = new StringBuilder("SELECT ID,Phone FROM ManagerDB"); //填充DataSet //SqlDataAdapter adapter = new SqlDataAdapter(sql.ToString(), dbhelper.Connection); SqlDataAdapter adapterStudent = new SqlDataAdapter(); SqlCommand command = new SqlCommand(sql.ToString(), dbhelper.Connection); adapterStudent.SelectCommand = command; // 填充前,先清空原有的数据 if (ds.Tables["ManagerDB"] != null) { ds.Tables["ManagerDB"].Clear(); } adapterStudent.Fill(ds, "ManagerDB"); //绑定数据源 this.dataGridView1.DataSource = ds.Tables["ManagerDB"]; } catch (Exception ex) { MessageBox.Show(ex.Message, "输入提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
private void btSave_Click(object sender, EventArgs e) { if (txtNewRecPhone.Text == string.Empty || txtNewRecPwd.Text == string.Empty || txtNewRecAccount.Text == string.Empty) { MessageBox.Show("信息输入不完全!请重新输入"); return; } else { DBHelper dbhelper = new DBHelper(); string CAPTION = "输入提示"; StringBuilder sb = new StringBuilder();//查询用的SQL语句 sb.AppendLine("insert into ReceptionistDB values ('"+txtNewRecAccount.Text+"','"+txtNewRecPwd.Text+"','"+txtNewRecPhone.Text+"')"); SqlCommand command = new SqlCommand(sb.ToString(), dbhelper.Connection); // 打开数据库连接 dbhelper.OpenConnection(); // 执行命令 int result = command.ExecuteNonQuery(); // 根据操作结果给出提示信息 if (result < 1) { MessageBox.Show("增加失败,请重试!", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { MessageBox.Show("增加成功!", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information); } dbhelper.CloseConnection(); } this.Close(); }
public void Update(string s,string ID) { DBHelper dbhelper = new DBHelper(); SqlCommand command; dbhelper.OpenConnection(); sb.AppendLine("update ReceptionistDB set Pwd = '" + s + "' where ID = '"+ID+"'"); command = new SqlCommand(sb.ToString(), dbhelper.Connection); command.ExecuteNonQuery(); dbhelper.CloseConnection(); }
private void btQuery_Click(object sender, EventArgs e) { if (txtQueryManagerPhone.Text == string.Empty) { MessageBox.Show("请输入经理姓名"); return; } else { DBHelper dbhelper = new DBHelper(); DataSet ds = new DataSet(); try { // 查询年级的sql语句 StringBuilder sql = new StringBuilder("SELECT ID,Phone FROM ManagerDB where ID = '" + txtQueryManagerPhone.Text + "'"); //填充DataSet //SqlDataAdapter adapter = new SqlDataAdapter(sql.ToString(), dbhelper.Connection); SqlDataAdapter adapterStudent = new SqlDataAdapter(); SqlCommand command = new SqlCommand(sql.ToString(), dbhelper.Connection); adapterStudent.SelectCommand = command; dbhelper.OpenConnection(); int count = command.ExecuteNonQuery(); if (count <= 0) { MessageBox.Show("未找到该经理"); return; } else { // 填充前,先清空原有的数据 if (ds.Tables["ManagerDB"] != null) { ds.Tables["ManagerDB"].Clear(); } adapterStudent.Fill(ds, "ManagerDB"); //绑定数据源 this.dataGridView1.DataSource = ds.Tables["ManagerDB"]; } } catch (Exception ex) { MessageBox.Show(ex.Message, "输入提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { dbhelper.CloseConnection(); } } }
private void button1_Click(object sender, EventArgs e) { DBHelper dbhelper = new DBHelper(); string CAPTION = "输入提示"; StringBuilder sb = new StringBuilder();//查询用的SQL语句 sb.AppendLine("Select * from viplistDB where ID = '"+txtVipID.Text+"'"); SqlCommand command1 = new SqlCommand(sb.ToString(), dbhelper.Connection); //打开链接 dbhelper.OpenConnection(); //执行查询语句 int count = Convert.ToInt32( command1.ExecuteScalar()); if (count > 0) { MessageBox.Show("该用户已经是VIP"); return; } dbhelper.CloseConnection(); try { StringBuilder sql = new StringBuilder(); sql.AppendLine("INSERT INTO VIPlistDB Values ('"+this.txtVipID.Text+"','"+this.txtVipName.Text+"')"); // 创建command对象 SqlCommand command = new SqlCommand(sql.ToString(), dbhelper.Connection); // 打开数据库连接 dbhelper.OpenConnection(); // 执行命令 int result = command.ExecuteNonQuery(); // 根据操作结果给出提示信息 if (result < 1) { MessageBox.Show("增加失败,请重试!", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { MessageBox.Show("增加成功!", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show("系统出现错误!", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { //关闭数据库连接 dbhelper.CloseConnection(); } }
public void Update() { DBHelper dbhelper = new DBHelper(); SqlCommand command; dbhelper.OpenConnection(); sb.AppendLine("update RoomDetailDB set RoomStatus = 'full',GuestName='" + guest.GuestName + "',GuestID='" + guest.GuestID + "',"); sb.AppendLine("VIP='" + guest.Vip + "',CheckInDate='" + hotelroom.CheckInDate + "',"); sb.AppendLine("CheckOutDate='" + hotelroom.ChechOutDate + "'"); sb.AppendLine("where roomid = '" + hotelroom.HotelRoomNo + "'"); command = new SqlCommand(sb.ToString(), dbhelper.Connection); command.ExecuteNonQuery(); dbhelper.CloseConnection(); }
private void btTurnoverQuery_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); float sum = 0; DBHelper dbhelper = new DBHelper(); string sql = "select * from IncomeDB where date between'"+dtpBeginDate.Value.ToShortDateString()+"'and '"+dtpEndDate.Value.ToShortDateString()+"'"; dbhelper.OpenConnection(); SqlCommand command = new SqlCommand(sql,dbhelper.Connection); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { sum=sum+Convert .ToSingle( dr["Income"]); } MessageBox.Show("这一段时间累计收入"+sum.ToString ()+"元"); dbhelper.CloseConnection(); }
/// <summary> /// 检索用户名、密码是否存在 /// </summary> /// <param name="message">表示查询状态</param> /// <returns>true的时候表示能在数据库中查找到对应的一项,false的时候表示不能在数据库中查找出来</returns> public bool CheckUser(ref string message) { bool isVaildUser = false; string id =txtReceptionistID. Text.Trim(); string pwd =this.txtReceptionistPwd .Text.Trim(); StringBuilder sb = new StringBuilder();//查询用的SQL语句 sb.AppendFormat("SELECT COUNT(*) FROM [ReceptionistDB] WHERE [ID]='{0}' AND [Pwd]='{1}'",id,pwd); //执行查询 int count = 0; DBHelper dbhelper = new DBHelper(); try { //创建command语句 SqlCommand command = new SqlCommand(sb.ToString(), dbhelper.Connection); //打开链接 dbhelper.OpenConnection(); //执行查询语句 count = (int)command.ExecuteScalar(); //如果结果大于0,则表示通过账号密码验证 if (count > 0) { isVaildUser = true; } else { message = "用户名或密码不存在"; isVaildUser = false; } } catch (Exception ex) { message = "数据库出错,请改正后再试"; isVaildUser = false; } finally { //关闭数据库 dbhelper.CloseConnection(); } return isVaildUser;//当isVaildUser为false的时候,表示验证失败,为true的时候表示验证成功 }
private void btConfirmation_Click(object sender, EventArgs e) { try { DBHelper db = new DBHelper(); string sql = "Select * from RoomDetailDB where RoomID = '" + txtRoomID.Text + "'"; db.OpenConnection(); SqlCommand command = new SqlCommand(sql, db.Connection); SqlDataReader dr = command.ExecuteReader(); dr.Read(); txtRoomPrice.Text = dr["RoomPrice"].ToString(); if (dr["RoomStatus"].ToString() == "empty") { rbempty.Checked = true; rbbusy.Checked = false; } else { rbempty.Checked = false; rbbusy.Checked = true; } txtGuestName.Text = dr["GuestName"].ToString(); txtGuestID.Text = dr["GuestID"].ToString(); if (dr["VIP"].ToString() == "yes") { rbVIP.Checked = true; rbNotVIP.Checked = false; } else { rbVIP.Checked = false; rbNotVIP.Checked = true; } dtpcheckintime.Value = Convert.ToDateTime(dr["CheckInDate"]); dtpCheckOut.Value = Convert.ToDateTime(dr["CheckOutDate"]); db.CloseConnection(); } catch (Exception ex) { MessageBox.Show("无此房间或房间为空!"); } }
public void CheckOut() { //结账部分 DBHelper dbhelper = new DBHelper(); string CAPTION = "输入提示"; StringBuilder sb = new StringBuilder();//查询用的SQL语句 sb.AppendLine("Select * from IncomeDB where Date = '" + dtpCheckOut.Value.ToShortDateString() + "'"); SqlCommand command1 = new SqlCommand(sb.ToString(), dbhelper.Connection); //打开链接 dbhelper.OpenConnection(); //执行查询语句 int count = Convert.ToInt32(command1.ExecuteScalar()); if (count > 0) { SqlDataReader dr = command1.ExecuteReader(); dr.Read(); float sum = Convert.ToSingle(dr["Income"]); TimeSpan ts = dtpCheckOut.Value - dtpcheckintime.Value; int day = ts.Days; //MessageBox.Show(day.ToString()); float price = Convert.ToSingle(txtRoomPrice.Text); float k = 1; if (rbVIP.Checked == true) k = 0.9f; sum = sum + price * day * k; MessageBox.Show("需要支付"+(price * day * k).ToString()+"元"); dr.Close(); string sql = "update IncomeDB set Income ='" + sum + "' where date='" + dtpCheckOut.Value.ToShortDateString() + "'"; SqlCommand command2 = new SqlCommand(sql, dbhelper.Connection); command2.ExecuteNonQuery(); } else { float sum = 0; TimeSpan ts = dtpCheckOut.Value - dtpcheckintime.Value; int day = ts.Days; //MessageBox.Show(day.ToString()); float price = Convert.ToSingle(txtRoomPrice.Text); float k = 1; if (rbVIP.Checked == true) k = 0.9f; sum = sum + price * day * k; MessageBox.Show("需要支付" + (price * day * k).ToString() + "元"); string sql = "insert into IncomeDB values ('" + sum + "' ,'" + dtpCheckOut.Value.ToShortDateString() + "')"; SqlCommand command2 = new SqlCommand(sql, dbhelper.Connection); command2.ExecuteNonQuery(); } //房间状态更新部分 DBHelper dbhelper1 = new DBHelper(); StringBuilder sb1 = new StringBuilder();//查询用的SQL语句 sb.AppendLine("update RoomDetailDB set RoomStatus = 'empty',GuestName= null,GuestID=null,"); sb.AppendLine("VIP=null,CheckInDate=null,"); sb.AppendLine("CheckOutDate=null "); sb.AppendLine("where roomid = '"+txtRoomID.Text+"'"); SqlCommand command3 = new SqlCommand(sb.ToString(), dbhelper.Connection); command3.ExecuteNonQuery(); rm.BindResult(); this.Close(); }
private void btGuestIn_Click(object sender, EventArgs e) { hotelroom.HotelRoomNo = cmbRoom.SelectedValue.ToString(); hotelroom.CheckInDate = dtpGuestIn.Value; hotelroom.Duration = Convert.ToInt32( txtDuration.Text); hotelroom.ChechOutDate = dtpGuestIn.Value.AddDays(hotelroom.Duration); guest.GuestID = txtGuestID.Text; guest.GuestName = txtGuestName.Text; //sb.AppendFormat("Select * from VIPlistDB where ID = '" + txtGuestID.Text + "'"); string sql = "Select * from VIPlistDB where ID = '"+txtGuestID.Text+"'"; int count = 0; // 数据库查询的结果 DBHelper dbhelper = new DBHelper(); try { // 创建Command命令 SqlCommand command = new SqlCommand(sql, dbhelper.Connection); //SqlCommand command = new SqlCommand(sb.ToString(), dbhelper.Connection); // 打开连接 dbhelper.OpenConnection(); // 执行查询语句 count = Convert.ToInt32(command.ExecuteScalar()); if (count > 0) { guest.Vip = IsVip.yes; } else { guest.Vip = IsVip.no; } // // sql = "update RoomDetailDB set RoomStatus = 'full',GuestName='" + guest.GuestName + "',GuestID='" + guest.GuestID + "',VIP='" + guest.Vip + "',CheckInDate='"+hotelroom.CheckInDate+"',CheckOutDate='"+hotelroom.ChechOutDate+"' where roomid = '"+hotelroom.HotelRoomNo+"'"; } catch (Exception ex) { MessageBox.Show( "系统发生错误,请稍后再试!"); } finally { //关闭数据库连接 dbhelper.CloseConnection(); } Update(); mfm.dgvRoomDetail.DataSource = null; mfm.dgvRoomDetail.Rows.Clear(); mfm.BindResult(); this.Close(); }