Visual Basic 2010 数据库开发之酒店管理系统04预定管理

Imports System.Data.SqlClient
Public Class frmYuDing
    Dim flag As String = ""

    Private Sub SetControlDisable()
        GroupBox1.Enabled = False
    End Sub
    Private Sub SetControlEnable()
        GroupBox1.Enabled = True
    End Sub
    Private Sub SetControlEmpty()
        txtName.Text = ""
        cmbSex.Text = ""
        txtPhone.Text = ""
        cmbLeiXing.Text = ""
        cmbBianHao.Text = ""
        txtDays.Text = ""
        txtCount.Text = ""
    End Sub
    Private Sub SetControlDebug()
        txtName.Text = "李雷"
        cmbSex.Text = "男"
        txtPhone.Text = "13688888888"
        cmbLeiXing.Text = "套房"
        cmbBianHao.Text = "2001"
        txtDays.Text = "2"
        txtCount.Text = "1"
    End Sub

    Dim conn As SqlConnection = GetConnection()
    Dim strSql As String = "Select * From 预定信息"
    Dim myda As New SqlDataAdapter(strSql, conn)
    Dim mydt As DataTable
    Dim mydv As DataView
    Dim bmdata As CurrencyManager
    Private Sub FillDataAndView()
        mydt = New DataTable
        myda.Fill(mydt)
        mydv = New DataView(mydt)
        bmdata = Me.BindingContext(mydv)
    End Sub

    Private Sub BindFields()
        txtName.DataBindings.Clear()
        cmbSex.DataBindings.Clear()
        txtPhone.DataBindings.Clear()
        cmbLeiXing.DataBindings.Clear()
        cmbBianHao.DataBindings.Clear()
        DateTimePicker1.DataBindings.Clear()
        DateTimePicker2.DataBindings.Clear()
        txtDays.DataBindings.Clear()
        txtCount.DataBindings.Clear()

        txtName.DataBindings.Add("text", mydv, "姓名")
        cmbSex.DataBindings.Add("text", mydv, "性别")
        txtPhone.DataBindings.Add("text", mydv, "电话号码")
        cmbLeiXing.DataBindings.Add("text", mydv, "房间类型")
        cmbBianHao.DataBindings.Add("text", mydv, "房间编号")
        DateTimePicker1.DataBindings.Add("value", mydv, "预定日期")
        DateTimePicker2.DataBindings.Add("value", mydv, "抵店日期")
        txtDays.DataBindings.Add("text", mydv, "预定天数")
        txtCount.DataBindings.Add("text", mydv, "预定人数")
    End Sub
    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim intIndex As Integer = e.RowIndex
        Try
            txtName.Text = DataGridView1.Rows(intIndex).Cells(0).Value
            cmbSex.Text = DataGridView1.Rows(intIndex).Cells(2).Value
            txtPhone.Text = DataGridView1.Rows(intIndex).Cells(3).Value
            cmbLeiXing.Text = DataGridView1.Rows(intIndex).Cells(4).Value
            cmbBianHao.Text = DataGridView1.Rows(intIndex).Cells(5).Value
            DateTimePicker1.Value = DataGridView1.Rows(intIndex).Cells(6).Value
            DateTimePicker2.Value = DataGridView1.Rows(intIndex).Cells(7).Value
            txtDays.Text = DataGridView1.Rows(intIndex).Cells(8).Value
            txtCount.Text = DataGridView1.Rows(intIndex).Cells(9).Value
        Catch ex As Exception

        End Try
    End Sub

    Private Sub frmYuDing_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Disposed
        Me.Close()
        frmMain.Show()
    End Sub

    Private Sub frmYuDing_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SetControlDisable()

        FillDataAndView()
        BindFields()

        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        DataGridView1.DataSource = mydt
    End Sub

 
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        flag = "Add"
        SetControlEnable()
        'SetControlEmpty()
        SetControlDebug()
    End Sub

    Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModify.Click
        flag = "modify"
        SetControlEnable
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim conn As SqlConnection = GetConnection()
        conn.Open()
        If flag = "Add" Then
            Dim strSql As String = _
                "Insert into 预定信息 " _
              & "Values(@姓名,@性别,@电话号码,@房间类型,@房间编号,@预定日期,@抵店日期," _
              & "@预定天数,@预定人数)"
            Dim comm As New SqlCommand(strSql, conn)
            comm.CommandType = CommandType.Text
            comm.Parameters.AddWithValue("@姓名", txtName.Text)
            comm.Parameters.AddWithValue("@性别", cmbSex.Text)
            comm.Parameters.AddWithValue("@电话号码", txtPhone.Text)
            comm.Parameters.AddWithValue("@房间类型", cmbLeiXing.Text)
            comm.Parameters.AddWithValue("@房间编号", cmbBianHao.Text)
            comm.Parameters.AddWithValue("@预定日期", DateTimePicker1.Value)
            comm.Parameters.AddWithValue("@抵店日期", DateTimePicker2.Value)
            comm.Parameters.AddWithValue("@预定天数", txtDays.Text)
            comm.Parameters.AddWithValue("@预定人数", txtCount.Text)
            comm.ExecuteNonQuery()

        ElseIf flag = "modify" Then
            Dim strSql As String = _
                "Update 预定信息 " _
              & "Set 姓名=@姓名,性别=@性别,电话号码=@电话号码,房间类型=@房间类型," _
              & "房间编号=@房间编号,预定日期=@预定日期,抵店日期=@抵店日期," _
              & "预定天数=@预定天数,预定人数=@预定人数 " _
              & "Where 姓名='{0}'"
            strSql = String.Format(strSql, txtName.Text)
            Dim comm As New SqlCommand(strSql, conn)
            comm.CommandType = CommandType.Text
            comm.Parameters.AddWithValue("@姓名", txtName.Text)
            comm.Parameters.AddWithValue("@性别", cmbSex.Text)
            comm.Parameters.AddWithValue("@电话号码", txtPhone.Text)
            comm.Parameters.AddWithValue("@房间类型", cmbLeiXing.Text)
            comm.Parameters.AddWithValue("@房间编号", cmbBianHao.Text)
            comm.Parameters.AddWithValue("@预定日期", DateTimePicker1.Value)
            comm.Parameters.AddWithValue("@抵店日期", DateTimePicker2.Value)
            comm.Parameters.AddWithValue("@预定天数", txtDays.Text)
            comm.Parameters.AddWithValue("@预定人数", txtCount.Text)
            comm.ExecuteNonQuery()

        Else
            Exit Sub
        End If
        conn.Close()
        SetControlDisable()

        FillDataAndView()
        BindFields()

        DataGridView1.DataSource = mydt
    End Sub

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        Dim intPosition As Integer
        intPosition = Me.BindingContext(mydv).Position - 1

        If MessageBox.Show("确定删除吗？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Stop) = DialogResult.Yes Then

            If intPosition < 0 Then
                intPosition = 0
            End If

            Dim conn As SqlConnection = GetConnection()
            conn.Open()
            Dim strSql As String = _
                "DELETE FROM 预定信息 " _
              & "WHERE 姓名=@姓名"
            Dim comm As SqlCommand = New SqlCommand(strSql, conn)
            comm.CommandType = CommandType.Text
            comm.Parameters.AddWithValue _
                ("@姓名", BindingContext(mydv).Current("姓名").ToString)
            comm.ExecuteNonQuery()

            conn.Close()

        End If
        Me.BindingContext(mydv).Position = intPosition

        FillDataAndView()
        BindFields()

        DataGridView1.DataSource = mydt
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
        frmMain.Show()
    End Sub
End Class