EF框架配置使用各种数据库

Content

准备工作

环境: Visual Studio Community 2017,Windows 10.0.15063 pro

  • 先新建一个ASP.NET MVC项目DbConnectPrac

pic00

pic0

  • 程序包管理器控制台安装EF框架
1
Install-Package EntityFramework

PS. 如果解决方里有多个项目,默认项目注意选择DbConnectPrac

  • Models中新建一个类

    pic1

    Program.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
using System.ComponentModel.DataAnnotations;
namespace DbConnectPrac.Models
{
public class User
{
[Key]
public int Uid { get; set; }
[Required]
[StringLength(16, MinimumLength = 3)]
public string NickName { get; set; }
[Required]
[StringLength(16, MinimumLength = 6)]
public string Password { get; set; }
[Required]
[RegularExpression(@"^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$")]
public string Email { get; set; }
public int Privilege { get; set; }
}
public class Video
{
[Key]
public int Vid { get; set; }
[Required]
[StringLength(30)]
public string Vname { get; set; }
[Required]
public string Vurl { get; set; }
[Required]
public string Thumbnail { get; set; }
public int ViewedNum { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Required]
public string UploadTime { get; set; }
[Required]
public string Vtype { get; set; }
public int Uid { get; set; }
[StringLength(200)]
public string Vinfo { get; set; }
}
public class Comment
{
[Key]
public int Cid { get; set; }
[Required]
public int Uid { get; set; }
[Required]
public int Vid { get; set; }
[Required]
public string Content { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Required]
public string CommentTime { get; set; }
}
public class History
{
[Key]
public int Hid { get; set; }
[Required]
public int Uid { get; set; }
[Required]
public int Vid { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Required]
public string HistoryTime { get; set; }
}
}
  • 生成解决方案

快捷键 Ctrl+Shift+b

  • Controllers中新建控制器

pic2

pic3

点击添加,将生成以下文件

pic5

分别把另外几个类也创建好带视图的控制器

LocalDB

LocalDB是VS自带的简化版SQL Server

  • 查看项目根目录的Web.config,发现已添加了LocalDB的相关内容
1
2
3
4
5
6
7
8
9
10
11
12
13
<connectionStrings>
<add name="DbConnectPracContext" connectionString="Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=DbConnectPracContext-20170603111224; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|DbConnectPracContext-20170603111224.mdf" providerName="System.Data.SqlClient" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="mssqllocaldb" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
  • 运行项目进入对应的Controller你可以方便的实现增删改查(CRUD)操作。

pic6

SQL Server

版本 2016 Developer edition

  • 在根目录的Web.config中添加SQL Server的连接字段
1
2
3
4
5
6
<connectionStrings>
<!-- 按实际情况更改 Data Source, User ID, Password -->
<add name="MSSQLConnectContext"
connectionString="Data Source=localhost; Initial Catalog=TESTDB; Persist Security Info=True; User ID=sa; Password=123"
providerName="System.Data.SqlClient"/>
</connectionStrings>
  • 打开Models/DbConnectPracContext.cs
1
2
3
4
5
6
7
public class DbConnectPracContext : DbContext
{
// 修改指定使用的数据库连接
//public DbConnectPracContext() : base("name=DbConnectPracContext"){} // LocalDB
public DbConnectPracContext() : base("name=MSSQLConnectContext") {} // SQL Server
// .....省略......
}
  • 程序包管理器控制台输入
1
Enable-Migrations -EnableAutomaticMigrations
  • 运行项目进入对应的Controller你可以方便的实现增删改查(CRUD)操作。

MySQL

版本 5.7.18

  • 程序包管理器控制台安装EF框架
1
Install-Package MySql.Data.Entity
  • 查看项目根目录的Web.config

    更新了以下有关MySQL的内容

1
2
3
4
5
6
7
8
9
10
11
<entityFramework>
<providers>
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider>
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
  • connectionStrings中添加MySQL的连接字段
1
2
3
4
5
<connectionStrings>
<add name="MySQLConnectContext"
connectionString="server=localhost; port=3306; database=TESTDB; uid=root; password=123"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
  • 打开Models/DbConnectPracContext.cs完成两处修改
1
2
3
4
5
6
7
8
9
// 在Context指定mySql的配置文件
[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
public class DbConnectPracContext : DbContext
{
// 修改指定使用的数据库连接
//public DbConnectPracContext() : base("name=DbConnectPracContext"){} // LocalDB
public DbConnectPracContext() : base("name=MySQLConnectContext"){} // MySQL
// .....省略......
}
  • 程序包管理器控制台输入
1
Enable-Migrations -EnableAutomaticMigrations
  • 运行项目进入对应的Controller你可以方便的实现增删改查(CRUD)操作。

连接MySQL查看生成的testdb表

pic7

Oracle

版本 12.2.0.1.0

  • 建议先创建一个新用户,否则使用默认用户可能有意想不到的问题
1
2
3
4
5
6
-- 用SQLPLUS登录后,连接管理员 (system_password 是你的管理员密码)
CONNECT system/system_password@ORCL
-- 该版本Oracle上创建新用户名必须C##或c##开头,否则不合法
CREATE USER C##TESTUSER IDENTIFIED BY 123;
-- 创建测试用户并授权,要给DBA权限
GRANT CONNECT, RESOURCE, DBA, CREATE VIEW TO C##TESTUSER ;
  • 程序包管理器控制台安装EF框架
1
Install-Package Oracle.ManagedDataAccess.EntityFramework
  • 查看根目录的Web.config

    更新了以下有关Oracle的内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<configSections>
<section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</configSections>
<connectionStrings>
<add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=oracle_user;Password=oracle_user_password;Data Source=oracle" />
</connectionStrings>
<entityFramework>
<providers>
<provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="Oracle.ManagedDataAccess.Client" />
<add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>
<oracle.manageddataaccess.client>
<version number="*">
<dataSources>
<dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) " />
</dataSources>
</version>
</oracle.manageddataaccess.client>
  • connectionStrings中修改Oracle的连接字段的参数
1
2
3
4
5
6
<connectionStrings>
<!-- 因为生成的<dataSources>中服务名是ORCL,与我的设置相同,所以这里直接用别名SampleDataSource-->
<add name="OracleDbContext"
providerName="Oracle.ManagedDataAccess.Client"
connectionString="User Id=C##TESTUSER;Password=123;Data Source=SampleDataSource" />
</connectionStrings>
  • 修改Models/DbConnectPracContext.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
namespace DbConnectPrac.Models
{
public class DbConnectPracContext : DbContext
{
// 修改指定使用的数据库连接
//public DbConnectPracContext() : base("name=DbConnectPracContext") { } // LocalDB
public DbConnectPracContext() : base("name=OracleDbContext") { } // Oracle
// 默认的模式名是dbo,但Oracle中不存在模式名为dbo,需要指定默认模式名
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("C##TESTUSER"); // 默认模式名就是把你用户名大写
}
// .....省略......
}
}

Oracle中不存在模式名为dbo,dbo是SQL Server数据库的,如图例。

pic9

  • 程序包管理器控制台输入
1
Enable-Migrations -EnableAutomaticMigrations
  • 运行项目进入对应的Controller你可以方便的实现增删改查(CRUD)操作。

连接Oracle查看生成的表

pic10

最后一次测试时,发现一个BUG,如果把刚生成的表全删除,试图通过重新运行项目重新建表,会失败。这个情况只在Oracle上发生,大概官方还没注意到这个BUG。“一次性”用户真是尴尬……