接上篇:个人 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="data source=localhost;initial catalog=AdventureWorks2016;persist security info=True;user id=sa;MultipleActiveResultSets=True;App=EntityFramework"" 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();
}
}
}
运行结果:
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.)
觉得挺奇怪的,本机 是安装了 VS2017 + SQL Server 2016 的,为什么会出现这个错误;
同样,如果这个时候把 Bin目录 拷贝到其他PC运行,同样也会出现这个错误;(如果是SQL Server 2012 就不会有这个错误。)
具体原理确实不是很清楚。。。
个人通过查询参考相关帖子,然后用了以下解决方法:
在Model类库中对 数据库上下文类的构造函数中加入强制定义:(在本机上就可以直接执行,如果要发布到其他PC运行,则需要拷贝SQL Server 2016 对应的Microsoft.SqlServer.Types.dll 到应用程序根目录)
public DBEntities() : base("name=DBEntities")
{
SqlProviderServices.SqlServerTypesAssemblyName = "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
}
注:这样做也有个小问题,好像每次如果数据库更新要刷新这个Model的时候,这行代码可能会自动消失,还要手动再拷贝进来;
这样就可以正确取出数据:
Step3