客户预约关联示例图
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();
}
}
}