（十） 数据库与WPF实战

1.连接数据库

选择左侧工具栏，server explorer。右键点击数据连接“data connections”，选择“add connection”添加新数据链接。或者，点击这个带有绿色小加号的服务器图标，也能打开同样的”新数据链接“界面。

在这个界面中，选择microsoft sql server，继续。如果是第一次使用visual studio的数据库链接功能，那么可能会弹出对话框。这是因为visual studio缺少了某些数据库链接组件，必须把这些组件安装好才能使用。（安装后要重启）

接下来，我们来填写数据库信息，首先需要填写数据库服务名称，server name，安装好了有一个界面，上面有一个连接字符串，这个链接字符串上就有我们需要的一切与数据库链接相关的信息，比如说数据库名称、用户名、甚至是数据库密码。不过因为我们刚刚在安装数据库的时候没有设置密码，所以我们在做数据库链接的时候也就不需要输入密码了。

我们把server name复制过去。然后，可以选择启动一个默认数据库，数据库名称也在链接字符串里，就是这个master，也复制过去。

配置完毕，不过别着急点ok，先点击test，测试一试是否数据库能链接成功。好了，数据库链接成功，现在我们再点击ok。

2. dataset与table设置

找到identiy sepcification，把它设置为true。我们会发现identity increment也会自动设置为true，identity seed也会自动设置为1。这些数据代表每当我们有新的数据保存的时候，我们的主键id都将会随着新数据的添加而自动增加 1。

点击这里的update按钮，我们提交数据库修改吧。检查一下有没有错误，继续点击update。

接下来，回到server explorer中，刷新一下，现在，这个新的customers数据表就创建好了。而它的数据结构只需要展开这个三角形下拉箭头就能看到了。

添加数据

• 右键点击customers数据表，选择“show table data”
• 随便添加一些数据
  

3.显示列表型数据：客户列表

3.1 WPF项目创建

1. 创建新项目，WPF的项目有两个平台，一个是传统的.net平台，另一个是比较新的.net core平台，虽然 WPF不能跨平台，但是新的 .net core平台却是未来的发展趋势。
2. 项目名称 WPF CMS，,net 平台版本，选择5或者6都可以。然后，点击创建。

3.2 链接数据库

• 打开wpf的xmal所对应的cs文件。我们需要通过数据库链接字符串来访问数据库。

• 为了能够使用数据库，我们需要先给项目安装一个数据库工具。打开nuget管理界面，在broswer页面中搜索system.Data.SqlClient, 选择安装这个工具。
  

• 定义一个字符串类型的本地变量，string connectionString, 使用ConfigurationManager，调用ConnectionString，然后在方括号中填入数据库链接字符串。

• 如何获得数据库链接字符串呢？打开server explorer，左键点击数据库，然后在右侧菜单栏底部的属性窗口中，我们就可以找到数据库的链接字符串，复制一下，粘贴到代码中。(反斜杠要做转义字符处理)

• 创建一个数据库链接的私有成员变量

• 给这个SqlConnection创建链接数据库的实例。

• 访问数据库的过程我们可以包装在一个私有方法中， private。 方法暂时不需要返回数据，使用void。方法名称叫做，showCustomers，在方法的内部我们就需要使用数据库适配器了。

• 创建一个数据库适配器的实例，

数据库适配器的作用就是访问数据库获得数据，而获得数据的过程需要执行sql语句。

• 数据库适配器通过SqlConnection来链接数据库，执行sql select查询，并且把返回的查询结果以c#对象的形式保存在内存中。接下来，我们就可以通过对象的形式来使用这些数据了。

• 数据库链接需要占用IO资源，一般来说，一次完成的数据库访问需要执行打开数据库、读取数据库、关闭数据库这三个操作，很多萌新程序员有时候会在读取完数据后忘记关闭数据库链接，产生内存泄露从而造成资源浪费。

• 不过，数据库适配器已经非常完美的帮我们解决了这个麻烦了，使用数据库适配器我们只需要关注业务层面的操作，比如数据库查询。而打开数据库和关闭数据库的操作都会由数据库适配器自动完成。只不过，在使用数据库适配器的时候，我们需要使用一个比较特殊的语法，就是using，使用了using来包裹的代码都将会被c#的自动回收系统所托管。

• 在using内部，我们将会把读取的数据保存在DataTable对象中，这个DataTable 定义在system.data命名空间中。

• 而数据的绑定过程就比较简单了，使用sqlDataAdapter，调用fill方法，向customerTable 中填充数据。数据填充完成以后，数据就会保存在customerTable 中，并且以对象化的形式保存起来了。

3.3 xaml代码

• 为了测试可以先拖拽
  
• listbox设置个名字

3.4 c#using代码块内

• 使用listbox的名称，customerList，显示绑定数据调用。
• displayMemberPath，字段路径名称就是数据库表中的字段名称，客户的名称，Name。
• 然后，还要把客户的id，也就是主键绑定过去，方便在接下来选择客户的时候识别客户信息。绑定数据调用，selectedValuePath = “Id”；
• 最后绑定数据源，也就是
  itemSource = customerTable.defaultView;

3.5 try catch

不过，我们还差最后一步，请打开代码，找到showCustomer中访问数据库的这部分代码。之前讲过，数据库操作实际上就是一个IO读写操作，读写操作涉及到外部环境、外部系统的访问，外部系统我们没办法通过程序来控制，所以存在极大的风险，比如说在访问数据库的时候我们是没办法提前知道数据库状态的，如果数据库死机了，我们的这整一坨访问数据库的代码都会失败，系统也会卡死在这里。

所以，对于访问外部资源的IO读写操作，同学们一定要小心，最好用一个try catch来包裹整个showCustomer　代码，这样就算数据库出问题了，也不会造成系统崩溃。

错误会被catch语句接住，错误信息就保存在参数exception中。通过异常处理，我们也可以在报错的时候通过弹窗告诉用户当前我们遇到的问题。

4.数据关系与关联表

4.1 建表

每个用户都有不同的预约时间。这些预约信息属于客户的关系型数据，应该保存在一张与客户表相关联的表中，客户和预约时间的关联关系就是通过这张表建立起来的。

• Appointments，预约表。主键Id已经预先定义好了，我们简单设置一下。在属性窗口中，把identify sepcification设置为true，把主键的托管给数据库，让数据库来控制id的自动增长。
• 外键 ( foreign key ) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。
• 在我们的预约表中，他的外键就是客户的主键，定义一下这个字断吧。这个字断的名称就叫做CustomerId，类型为整数，int。这里的CustomerId所对应的数据就是客户表的主键id。右键点击添加新外键，这时候需要首先填写外键的名称。
• 外键名称一般都会遵循fk，也就是foreign key的缩写，下划线，当前表名称，下划线，链接表名称。所以，我们的外键名称就是FK_Appointments_Customers，回车确认。
  
• 点击update。

4.2 测试查询

连表查询需要使用关键词 join， join 那张表呢？很明显是客户表嘛，所以 join Customers。而需要被链接的字段则是Appointments表的外键CustomerId 以及 Customers 表的主键 Id。

select * from Appointments join Customers on Appointments.CustomerId = Customers.Id
如果我们只希望看到某个特定的客户的预约记录该怎办呢？那么我们就需要使用sql的另一个关键词 where 了。通过where，我们可以给查询加上限制条件，比如说，如果我们想获得id为1的客户的预约，那么他的限制条件就是 where Customers.Id = 1。

5.显示关联型数据库：客户预约记录

1. 因为不再是查询客户，而是查询客户预约记录，所以我们的 sql 查询语句肯定得更新。最后，查询的限制条件，where where Customers.Id = 参数传入的id，也就是当前选择的客户Id， @CustomerId。string中的参数使用@符号进行引导。
2. 而这个客户id从哪里来呢？其实就是从customerlist中获得，
3. 因为sql语句字符串中使用了@符号、嵌入了参数，所以，不能直接把query字符串传递给sqlDataAdapter。我们需要一个中间机制来帮先预处理处理的一下sql语句，这个中间机制就是 SqlCommand。
   var sqlCommand = new SqlCommand(query, sqlConnections);
4. 然后使用这个sqlCommand 来代替 sqlDataAdapter 中所有的参数
5. 现在，我们就可以通过 SqlCommand 来动态为sql语句添加参数了。访问属性 Parameters， 这是一个key to value 键值对类型的列表数据，所有的sql参数都在这里；
6. 然后调用 AddWithValue 来添加参数，我们需要添加的参数名称，也就是键的名称，key，就叫做@符号所引导的CustomerId，注意拼写，一定要前后一致，然后第二个参数就是他所对应的值，从customerlist中获得的customerId。

6.删除数据

6.1取消预约业务逻辑

• 删除使用 delete 关键词，从Appointments 表中删除数据，所以是 delete from Appointments ，需要限定于删除当前所选定的记录，所以还要加上where Id = @AppointmentId。

string query = "delete from Appointments where Id = @AppointmentId";
var appointmentId = appointmentList.SelectedValue;

• 而这个AppointmentId 则来appointmentList.SelectedValue.
• 然后实例化一个 SqlCommand，把sql字符串和私有成员数据库链接放进去。
• 最重要的是，不要忘记给@AppointmentId赋值。调用sqlCommand.Parameters.AddWithValue，第一个参数是sql语句的占位符@AppointmentId，第二个参数则是当前选定的预约id，appointmentId
• 接下来，为了执行这个SqlCommand，我们必须先对数据库进行连接，_sqlConnection.Open()。
• 当数据库链接打开以后，我门就能执行删除数据的操作了，使用sqlCommand，调用ExecuteScalar来执行sql语句。
• 最后一步，在数据库操作完成以后，同学们一定要记得调用_sqlConnection.Close来关闭数据库链接。
• 用finally来执行无论有没有错误都必须要执行的逻辑。那么，关闭数据库链接和刷新预约列表，我们就可以放进finally来处理了。

6.2 删除客户

客户和预约记录是有外键关系的，如果我们直接删除客户而继续保留预约数据，那么数据库将会报错。
所谓的约束"FK_Appointments_Customers"指的就是Customers表和Appointments表的外键关系。如果我们删除了Customers表某条记录，那么与这个客户对应的预约记录就悬空了，这条数据就变成了冗余数据永远无法访问，也永远无法删除了。

所以，外键的重要性也就体现在这里了，他可以从最底层的数据库来防止错误的数据操作。所以，我们在删除客户之前应该通过外键关系先删除他所有的预约，然后再删除客户。

```c#
private void DeleteCustomer_Click(object sender, RoutedEventArgs e)
{
    try
    {
        string queryDeleteAppointments = "delete from Appointments where CustomerId = @CustomerId";
        string queryDeleteCustomer = "delete from Customers where Id = @CustomerId";
        var customerId = customerList.SelectedValue;

        SqlCommand sqlCommandDeleteAppointments = new SqlCommand(queryDeleteAppointments, _sqlConnection);
        sqlCommandDeleteAppointments.Parameters.AddWithValue("@CustomerId", customerId);

        SqlCommand sqlCommandDeleteCustomer = new SqlCommand(queryDeleteCustomer, _sqlConnection);
        sqlCommandDeleteCustomer.Parameters.AddWithValue("@CustomerId", customerId);

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

7.添加和更新

7.1 添加

添加客户

  var sql = "insert into  Customers values(@name,@id,@address)";

  SqlCommand cmd1 = new SqlCommand(sql, _sqlConnection);

  cmd1.Parameters.AddWithValue("@name", CustomerName.Text);
  cmd1.Parameters.AddWithValue("@id", CustomerId.Text);
  cmd1.Parameters.AddWithValue("@address", CustomerAddress.Text);

添加预约

 var sql = "insert into  Appointments values(@date,@Customerid)";

 SqlCommand cmd1 = new SqlCommand(sql, _sqlConnection);

 cmd1.Parameters.AddWithValue("@date", AppointmentDatePicker.Text);
 cmd1.Parameters.AddWithValue("@Customerid", customerList.SelectedValue);

7.2 更新

在customerList_SelectionChanged的方法中

  //选中的一项资料显示在下面
  DataRowView selectedItem=customerList.SelectedItem as DataRowView;
  CustomerName.Text = selectedItem["Name"] as string;
  CustomerId.Text = selectedItem["IdNumber"] as string;

Trim去掉空格

 var sql = "update Customers set  Name=@name,IdNumber=@idNumber,Address=@address where Id=@customerId";

 SqlCommand cmd1 = new SqlCommand(sql, _sqlConnection);

 cmd1.Parameters.AddWithValue("@name", CustomerName.Text.Trim());
 cmd1.Parameters.AddWithValue("@idNumber", CustomerId.Text.Trim());
 cmd1.Parameters.AddWithValue("@address", CustomerAddress.Text.Trim());
 cmd1.Parameters.AddWithValue("@customerId", customerList.SelectedValue);