C#学习(九)——数据库与WPF

一、创建WPF项目链接数据库

首先创建WPF项目，在nuget安装包中下载System.Data.SqlClient

配置数据库的连接字符串来连接数据库
创建示例数据库，拥有两个table：

CREATE TABLE [dbo].[Customers] (
    [Id]       INT         IDENTITY (1, 1) NOT NULL,
    [Name]     NCHAR (50)  NOT NULL,
    [IdNumber] NCHAR (18)  NOT NULL,
    [Address]  NCHAR (100) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].Appointments
(
	[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Time] DATETIME NOT NULL, 
    [CustomerId] INT NOT NULL, 
    CONSTRAINT [FK_Appointments_Customers] FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
)

实现UI界面对于数据库数据的显示
MainWindow.xaml.cs

public partial class MainWindow : Window
{
   
    private SqlConnection _sqlConnection;
    public MainWindow()
    {
   
        InitializeComponent();
        string connectionString = "数据库连接字符串";//填入自己的数据库连接字符串

        _sqlConnection = new SqlConnection(connectionString);

        ShowCustomers();
    }

    //访问数据库
    private void ShowCustomers()
    {
   
        //防止数据库死机等意外导致以下操作全部失败,系统崩溃
        try
        {
   
            SqlDataAdapter adapter = new SqlDataAdapter("select * from Customers", _sqlConnection);
            //数据库适配器可以帮我们解决数据库的打开关闭
            using (adapter)
            {
   
                DataTable customerTable = new DataTable();
                adapter.Fill(customerTable);

                customerList.DisplayMemberPath = "Name";
                //绑定数据
                customerList.SelectedValuePath = "Id";
                //绑定数据源
                customerList.ItemsSource = customerTable.DefaultView;
            }
        }catch (Exception ex)
        {
   
            MessageBox.Show(ex.ToString());
        }        
    }

	private void customerList_SelectionChanged(object sender, SelectionChangedEventArgs e)
	{
   
	    try
	    {
   
	        //查询Appointments.CustomerId = Customers.Id且为参数传入的Id，@为参数引导
	        string query = "select * from Appointments join Customers on Appointments.CustomerId = Customers.Id where Customers.Id = @CustomerId";
	
	        var customerId = customerList.SelectedValue;
	        //用于删除预约信息时，会先删除customerId，造成customerId为空情况
	        if (customerId == null)
	        {
   
	            appointmentList.ItemsSource = null;
	            return;
	        }        
	
	        SqlCommand sqlCommand = new SqlCommand(query, _sqlConnection);
	
	        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
	        //键值对数据在此聚合
	        sqlCommand.Parameters.AddWithValue("@CustomerId", customerId);
		
	        using (sqlDataAdapter)
	        {
   
	            DataTable appointmentTable = new