个人 WPF+EF(DBFirst) 简单应用开发习惯及EF学习测试(备忘) -- 2

时间:2023-12-10 22:02:26

接上篇:个人 WPF+EF(DBFirst) 简单应用开发习惯及EF学习测试(备忘) -- 1

Step1 在主程序中设置连接数据库

从Model类库的 App.Config 把数据库字符串拷贝出来,放到主程序 App.Config 中:

<connectionStrings>
<add name="DBEntities" connectionString="metadata=res://*/EFDBFirstModel.csdl|res://*/EFDBFirstModel.ssdl|res://*/EFDBFirstModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost;initial catalog=AdventureWorks2016;persist security info=True;user id=sa;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

注:此前从数据库中建立模型时选择的是:在数据库连接字符串里把密码隐藏,以后在应用中指定密码;所以此字符串中密码是不显示的;

在 主程序 App中设置这个完整的带密码的字符串,作为后续连接时使用:(后面直接用 App.DBConnectionString 这个静态字符串属性即可)

using System;
using System.Configuration;
using System.Data.Common;
using System.Data.Entity.Core.EntityClient;
using System.Windows; namespace WPF_EF_DBFirst
{
/// <summary>
/// Interaction logic for App.xaml
/// </summary>
public partial class App : Application
{
private static string _db_ConnectionString; /// <summary>
/// DB Connection String With Password
/// </summary>
public static string DBConnectionString
{
get
{
if (String.IsNullOrEmpty(_db_ConnectionString))
{
var originalConnectionString = ConfigurationManager.ConnectionStrings["DBEntities"].ConnectionString;
var entityBuilder = new EntityConnectionStringBuilder(originalConnectionString);
var factory = DbProviderFactories.GetFactory(entityBuilder.Provider);
var providerBuilder = factory.CreateConnectionStringBuilder();
providerBuilder.ConnectionString = entityBuilder.ProviderConnectionString;
providerBuilder.Add("Password", "Your Password");
_db_ConnectionString = providerBuilder.ToString();
}
return _db_ConnectionString;
}
}
}
}

Step2 做第一个Datagrid 显示数据的测试

在 MainWindow 窗口上放第一个测试按钮:

<Button x:Name="ListView" Content="列表显示" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Click="ListView_Click"/>

在 ListView_Click 事件中,打开准备要新建的 Win_ListView 测试窗口:

        private void ListView_Click(object sender, RoutedEventArgs e)
{
Win_ListView win_ListView = new Win_ListView();
win_ListView.Owner = this;
win_ListView.ShowDialog();
}

新建一个 Win_ListView 测试显示窗口: (就放一个 TabControl 、一个DataGrid 和一个退回 按钮) (先准备测试显示 Person表里的人名数据)

<Window x:Class="WPF_EF_DBFirst.Win_ListView"
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_EF_DBFirst"
mc:Ignorable="d"
Title="Win_ListView" Height="326.341" Width="455.122" Loaded="Window_Loaded" WindowState="Maximized">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="257*"/>
<RowDefinition Height=""/>
</Grid.RowDefinitions>
<TabControl Margin="6,3,3,3">
<TabItem Header="Person">
<Grid Background="#FFE5E5E5">
<DataGrid Margin="3,3,3,3" x:Name="dg_Person" AutoGenerateColumns="False" IsReadOnly="True">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding FirstName}" Header="FirstName" ElementStyle="{StaticResource DataGridTextCenter}"/>
<DataGridTextColumn Binding="{Binding MiddleName}" Header="MiddleName" ElementStyle="{StaticResource DataGridTextCenter}"/>
<DataGridTextColumn Binding="{Binding LastName}" Header="LastName" ElementStyle="{StaticResource DataGridTextCenter}"/>
</DataGrid.Columns>
</DataGrid>
</Grid>
</TabItem>
<TabItem Header="TabItem">
<Grid Background="#FFE5E5E5"/>
</TabItem>
</TabControl>
<Button x:Name="btBack" Content="Back" HorizontalAlignment="Right" Margin="0,3,3,0" Grid.Row="" VerticalAlignment="Top" Width="" Click="btBack_Click"/>
</Grid>
</Window>

Win_ListView中增加实体定义(简单测试就直接在本窗口内进行数据连接了)以及增加 2个事件处理:(一个是Windows Load 一个是 退出按钮);

using System.Linq;
using System.Windows;
using WPF_EF_DBFirst.Model; namespace WPF_EF_DBFirst
{
/// <summary>
/// Interaction logic for Win_ListView.xaml
/// </summary>
public partial class Win_ListView : Window
{
private DBEntities DB;
public Win_ListView()
{
InitializeComponent();
} private void Window_Loaded(object sender, RoutedEventArgs e)
{
DB = new DBEntities();
DB.Database.Connection.ConnectionString = App.DBConnectionString;
dg_Person.ItemsSource = DB.Person.ToList();
} private void btBack_Click(object sender, RoutedEventArgs e)
{
Close();
}
}
}

运行结果:
个人 WPF+EF(DBFirst) 简单应用开发习惯及EF学习测试(备忘) -- 2

Step3 做一个出现异常的数据读取测试

在主窗口随便加个测试按钮,然后为按钮事件加上以下代码:

        private void ListReadTest_Click(object sender, RoutedEventArgs e)
{
try
{
DBEntities DB = new DBEntities();
DB.Database.Connection.ConnectionString = App.DBConnectionString;
MessageBox.Show(DB.Address.First().AddressLine1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

非常简单的代码,就是直接读取 Address 表的第一条记录的 AddressLine1 列的数据;
直接运行,点击这个按钮后会弹出以下错误:

(Spatial types and functions are not available for this provider because the assmbly 'Microsoft.SqlServer.Types' version 10 or higher could not be found.)

个人 WPF+EF(DBFirst) 简单应用开发习惯及EF学习测试(备忘) -- 2

觉得挺奇怪的,本机 是安装了 VS2017 + SQL Server 2016 的,为什么会出现这个错误;

同样,如果这个时候把 Bin目录 拷贝到其他PC运行,同样也会出现这个错误;(如果是SQL Server 2012 就不会有这个错误。)

具体原理确实不是很清楚。。。

个人通过查询参考相关帖子,然后用了以下解决方法:

在Model类库中对 数据库上下文类的构造函数中加入强制定义:(在本机上就可以直接执行,如果要发布到其他PC运行,则需要拷贝SQL Server 2016 对应的Microsoft.SqlServer.Types.dll 到应用程序根目录)

个人 WPF+EF(DBFirst) 简单应用开发习惯及EF学习测试(备忘) -- 2

      public DBEntities() : base("name=DBEntities")
{
SqlProviderServices.SqlServerTypesAssemblyName = "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
}

注:这样做也有个小问题,好像每次如果数据库更新要刷新这个Model的时候,这行代码可能会自动消失,还要手动再拷贝进来;

这样就可以正确取出数据:

个人 WPF+EF(DBFirst) 简单应用开发习惯及EF学习测试(备忘) -- 2

Step3