Entity FrameworkCore(EFCore)使用SqlServer、Mysql和Sqlite
一、新建一个.net7的控制台顶级语句应用程序,引入如下NuGet包:
.NET CLI命令如下:
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
将Microsoft.EntityFrameworkCore.Sqlite换成相应的包名称即可
二、新建实体类
public class Blog
{
public int BlogId { get; set; }
public string? Url { get; set; }
public int Rating { get; set; }
public List<Post> Posts { get; set; } = new List<Post>();
}
public class Post
{
public int PostId { get; set; }
public string? Title { get; set; }
public string? Content { get; set; }
public int BlogId { get; set; }
public Blog? Blog { get; set; }
}
三、使用工厂方法模式创建抽象工厂DBContext类和具体BDContext类
public abstract class BloggingDataBaseContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
}
具体的SqlServer ContextDB类
public class SqlServerBloggingContext : BloggingDataBaseContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
@"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True");
}
}
具体Mysql ContextDB类
public class MysqlBloggingContext : BloggingDataBaseContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseMySql(
@"server=localhost;port=3306;uid=EFCoredb;pwd=pwd;database=efcoredb", new MySqlServerVersion(new Version(5,7,40)));
}
}
具体Sqlite ContextDB类
public class SqliteBloggingContext : BloggingDataBaseContext
{
public string DbPath { get; }
public SqliteBloggingContext()
{
var folder = Environment.SpecialFolder.LocalApplicationData;
var path = Environment.GetFolderPath(folder);
DbPath = System.IO.Path.Join(path, "blogging.db");
}
// 以下配置EF以在您的平台的特殊“本地”文件夹中创建Sqlite数据库文件。
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlite($"Data Source={DbPath}");
}
四、迁移,该方法是使用CodeFirst创建数据库(或表)
我测试的时候mysql数据库没生成成功,手动创建的数据库
在vs里生成一下项目(EFCore或许不是必需的,我是采用先生成的方法)。
然后,在项目下创建文件夹:Migrations,并在Migrations文件夹下创建三个子文件夹:
SqlServer
Mysql
Sqlite
如图:
在项目根目录下,使用PowerShell控制台下运行以下命令(如图)
dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
由于有多ContextDb类,所以必须要指定具体的ContextDb类,不然会报错
创建SqlServer迁移
dotnet ef migrations add BloggingDataBaseContext -c SqlServerBloggingContext -o Migrations/SqlServer
dotnet ef database update --context SqlServerBloggingContext
创建Mysql迁移
dotnet ef migrations add BlogDataBaseMigraMysql -c MysqlBloggingContext -o Migrations/Mysql
dotnet ef database update --context MysqlBloggingContext
创建Sqlite迁移
dotnet ef migrations add BlogDataBaseMigraSqlite -c SqliteBloggingContext -o Migrations/sqlite
dotnet ef database update --context SqliteBloggingContext
五、对博客进行创建、查询、删除、修改的静态方法
static async Task CreatesBlogs(BloggingDataBaseContext db)
{
Console.WriteLine("插入新博客");
List<Blog> blogs = new List<Blog>();
for (int j = 0; j < 100; j++)
{
var blog1 = new Blog { Url = "http://blogs.msdn.com/adonet" + j.ToString() };
List<Post> posts = new List<Post>();
for (int i = 0; i < 10; i++)
{
posts.Add(new Post { Content = j.ToString() + i.ToString(), Title = j.ToString() + i.ToString() });
Console.WriteLine($"第{i}条Post生成成功");
}
blog1.Posts.AddRange(posts);
// blogs.Add(blog1);
Console.WriteLine($"第{j}条Blog生成成功");
db.Add(blog1);
db.SaveChanges();
await Console.Out.WriteLineAsync($"第{j}条Blog添加数据库成功");
}
//db.AddRange(blogs);
//await db.SaveChangesAsync();
}
static void DelAllBlogs(BloggingDataBaseContext db)
{
Console.WriteLine("删除博客");
var blogs = db.Blogs.Where(b => b.BlogId > 0);
db.RemoveRange(blogs);
db.SaveChanges();
Console.WriteLine("删除成功!");
}
static void SelectBlog(BloggingDataBaseContext db)
{
// Read
Console.WriteLine("查询博客");
var blog = db.Blogs
.OrderBy(b => b.BlogId)
.First();
}
static async Task UpdateBlog(BloggingDataBaseContext db, Blog blog)
{
// Update
Console.WriteLine("更新博客并添加帖子");
blog.Url = "https://devblogs.microsoft.com/dotnet";
await db.SaveChangesAsync();
}
六、调用
using BloggingDataBaseContext db = new SqliteBloggingContext();
await CreatesBlogs(db);
//DelAllBlogs(db);
//SelectBlog(db);
//await UpdateBlog(db);
可以将连接字符串写入配置文件,还可以使用反射,在配置文件配置不同的数据库,这里不再阐述。
独学而无友,则孤陋而寡闻。