WPF【10_2】数据库与WPF实战-示例

客户预约关联示例图

MainWindow.xaml 代码
<Window x:Class="WPF_CMS.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:local="clr-namespace:WPF_CMS"
    mc:Ignorable="d"
    Title="MainWindow" Height="450" Width="800">
<Grid>
    <Label Content="客户列表" HorizontalAlignment="Left" Margin="32,22,0,0" VerticalAlignment="Top"/>
    <ListBox Name="customerList" HorizontalAlignment="Left" Height="229" Margin="32,61,0,0" VerticalAlignment="Top" Width="249" SelectionChanged="customerList_SelectionChanged"/>
    <Label Content="预约记录" HorizontalAlignment="Left" Margin="444,22,0,0" VerticalAlignment="Top"/>
    <ListBox Name="appointmentList" HorizontalAlignment="Left" Height="229" Margin="444,61,0,0" VerticalAlignment="Top" Width="249"/>
    <Button Content="删除客户" HorizontalAlignment="Left" Margin="32,306,0,0" VerticalAlignment="Top" Width="249" Click="DeleteCustomer_Click"/>
    <Button Content="取消预约" HorizontalAlignment="Left" Margin="444,306,0,0" VerticalAlignment="Top" Width="249" Click="DeleteAppointment_Click"/>
    <TextBox Name="NameTextBox" HorizontalAlignment="Left" Margin="32,359,0,0" Text="TextBox" TextWrapping="Wrap" VerticalAlignment="Top" Width="120"/>
    <TextBox Name="IdTextBox" HorizontalAlignment="Left" Margin="322,359,0,0" Text="TextBox" TextWrapping="Wrap" VerticalAlignment="Top" Width="120"/>
    <TextBox Name="AddressTextBox" HorizontalAlignment="Left" Margin="175,359,0,0" Text="TextBox" TextWrapping="Wrap" VerticalAlignment="Top" Width="120"/>
    <Label Content="姓名" HorizontalAlignment="Left" Margin="32,331,0,0" VerticalAlignment="Top"/>
    <Label Content="身份证" HorizontalAlignment="Left" Margin="175,333,0,0" VerticalAlignment="Top"/>
    <Label Content="住址" HorizontalAlignment="Left" Margin="322,331,0,0" VerticalAlignment="Top"/>
    <Button Content="添加客户" HorizontalAlignment="Left" Margin="32,382,0,0" VerticalAlignment="Top" Click="AddCustomer_Click"/>
    <DatePicker Name="AppointmentDatePicker" HorizontalAlignment="Left" Margin="467,356,0,0" VerticalAlignment="Top"/>
    <Button Content="预约" HorizontalAlignment="Left" Margin="589,359,0,0" VerticalAlignment="Top" Click="AddAppointment_Click"/>
    <Button Content="更新客户资料" HorizontalAlignment="Left" Margin="112,387,0,0" VerticalAlignment="Top" Click="UpdateCustomer_Click"/>
</Grid>
</Window>

MainWindow.xaml.cs 代码
public partial class MainWindow : Window
{
    private SqlConnection _sqlConnection;
    public MainWindow()
    {
        InitializeComponent();
        string connectionString = "Data Source=localhost;Initial Catalog=course565;Persist Security Info=True;User ID=sa;Password=PaSSword12!;Pooling=False";

        _sqlConnection = new SqlConnection(connectionString);

        ShowCustomers();
    }

    private void ShowCustomers()
    {
        try
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select * from Customers", _sqlConnection);

            using (sqlDataAdapter)
            {
                DataTable customerTable = new DataTable();
                sqlDataAdapter.Fill(customerTable);

                customerList.DisplayMemberPath = "Name";
                customerList.SelectedValuePath = "Id";
                customerList.ItemsSource = customerTable.DefaultView;
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        }
    }

    private void customerList_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        try
        {
            string query = "select * from Appointments join Customers on Appointments.CustomerId = Customers.Id where Customers.Id = @CustomerId";

            var customerId = customerList.SelectedValue;
            if (customerId==null)
            {
                appointmentList.ItemsSource = null;
                return;
            }

            DataRowView selectedItem = customerList.SelectedItem as DataRowView;
            NameTextBox.Text = selectedItem["Name"] as string;
            IdTextBox.Text = selectedItem["IdNnumber"] as string;
            AddressTextBox.Text = selectedItem["Address"] as string;

            SqlCommand sqlCommand = new SqlCommand(query, _sqlConnection);

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

            sqlCommand.Parameters.AddWithValue("@CustomerId", customerId);

            using (sqlDataAdapter)
            {
                DataTable appointmentTable = new DataTable();
                sqlDataAdapter.Fill(appointmentTable);

                appointmentList.DisplayMemberPath = "Time";
                appointmentList.SelectedValuePath = "Id";
                appointmentList.ItemsSource = appointmentTable.DefaultView;
            }
        }
        catch (Exception error)
        {
            MessageBox.Show(error.ToString());
        }
    }

    private void DeleteAppointment_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            var sql = "delete from Appointments where Id = @AppointmentId";

            var appointmentId = appointmentList.SelectedValue;

            SqlCommand sqlCommand = new SqlCommand(sql, _sqlConnection);
            sqlCommand.Parameters.AddWithValue("@AppointmentId", appointmentId);

            _sqlConnection.Open();
            sqlCommand.ExecuteScalar();
            
        }
        catch (Exception error)
        {
            MessageBox.Show(error.ToString());
        }
        finally
        {
            _sqlConnection.Close();
            customerList_SelectionChanged(null, null);
        }
    }

    private void DeleteCustomer_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            string sqlDeleteAppointment = "delete from Appointments where CustomerId=@CustomerId";
            string sqlDeleteCustomer = "delete from Customers where id=@CustomerId";

            var customerId = customerList.SelectedValue;

            SqlCommand cmd1 = new SqlCommand(sqlDeleteAppointment, _sqlConnection);
            SqlCommand cmd2 = new SqlCommand(sqlDeleteCustomer, _sqlConnection);

            cmd1.Parameters.AddWithValue("@CustomerId", customerId);
            cmd2.Parameters.AddWithValue("@CustomerId", customerId);

            _sqlConnection.Open();
            cmd1.ExecuteScalar();
            cmd2.ExecuteScalar();
        }
        catch (Exception error)
        {
            MessageBox.Show(error.ToString());
        }
        finally
        {
            _sqlConnection.Close();
            ShowCustomers();
            customerList_SelectionChanged(null, null);
        }
    }

    private void AddCustomer_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            var sql = "insert into Customers values (@name, @id, @address)";

            SqlCommand sqlCommand = new SqlCommand(sql, _sqlConnection);

            sqlCommand.Parameters.AddWithValue("@name", NameTextBox.Text);
            sqlCommand.Parameters.AddWithValue("@id", IdTextBox.Text);
            sqlCommand.Parameters.AddWithValue("@address", AddressTextBox.Text);

            _sqlConnection.Open();
            sqlCommand.ExecuteScalar();

        }
        catch (Exception error)
        {
            MessageBox.Show(error.ToString());
        }
        finally
        {
            _sqlConnection.Close();
            ShowCustomers();
        }
    }

    private void AddAppointment_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            var sql = "insert into Appointments values (@date, @customerId)";

            SqlCommand sqlCommand = new SqlCommand(sql, _sqlConnection);

            sqlCommand.Parameters.AddWithValue("@date", AppointmentDatePicker.Text);
            sqlCommand.Parameters.AddWithValue("@customerId", customerList.SelectedValue);

            _sqlConnection.Open();
            sqlCommand.ExecuteScalar();
        }
        catch (Exception error)
        {
            MessageBox.Show(error.ToString());
        }
        finally
        {
            _sqlConnection.Close();
            customerList_SelectionChanged(null, null);
        }
    }

    private void UpdateCustomer_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            var sql = "update Customers set Name=@name, IdNnumber=@idNumber, Address=@address where Id=@customerId";

            SqlCommand sqlCommand = new SqlCommand(sql, _sqlConnection);
            sqlCommand.Parameters.AddWithValue("@name", NameTextBox.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@idNumber", IdTextBox.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@address", AddressTextBox.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@customerId", customerList.SelectedValue);

            _sqlConnection.Open();
            sqlCommand.ExecuteScalar();

        }
        catch (Exception error)
        {
            MessageBox.Show(error.ToString());
        }
        finally
        {
            _sqlConnection.Close();
            ShowCustomers();
        }
    }
}