原生ADO.NET访问数据库
进程交互靠的是网络协议
ado.net 操作数据库
sqlcommand
sqlconnection
adapter
datareader
操作数据库—数据库只认识Sql语句
不爽的地方:
a:写起来比较麻烦
b:开发者得知道sql
c:不同的表写不同的sql—会有重复代码
d:开发效率就低一些-性能最好
O/RM框架
ORM是一个封装,是一个代理,可以把数据库“搬”到程序中,要操作数据库,可以直接通过操作ORM框架完成对数据库的操作;
数据库只认识sql语句;
ORM-底层—ADO.NET;进一步的封装了,可以通过映射以后,通过对实体的操作,达到数据库中数据的操作;
1.生成Sql语句—大量的反射;
2.性能不好—可以通过缓存来解决
3.Sql语句是由程序生成,相对来说比较僵化,没有我们自己写的精炼;
4.开发便捷,不需要去了解Sql,降低学习成本;
5.思想的进步—操作类(对象),以面向对象的思想去操作数据库
常用的ORM框架:
- EF6:比较重,可以支持多种数据库,可以支持数据库的迁移;和VS配合的好;已经非常成熟了;
- NHibernate:比较重;
- Dapper:轻量级—宇宙第一性能之王;
- IBatis.Net
- LinqToSql : 只支持SqlServer,现在已经不在维护了;
- Sql sugar
现在使用的大部分ORM—基本上都是可以直接支持Sql语句;
EF三种映射
DbFirst: -----数据库先行,通过数据库来映射不同的实体(对应数据库中不同的表),视图+存储过程+函数;
CodeFirst: -----代码先行,直接写业务逻辑,通过业务逻辑实体去生成数据库;
CodeFirstFromDb: -----数据库已经存在,还是代码先行,数据存在就不用生成数据库;
DBFrist
1.创建ADO.NET实体模型
2.选择数据库驱动
3.选择数据库主机名称(IP)’
4.生成edml文件
edmx:DbContex+实体对象(数据库表)
自动生成的DbContext:
public partial class SunDBSetEntities : DbContext
{
public SunDBSetEntities()
: base("name=SunDBSetEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public virtual DbSet<Company> Company { get; set; }
public virtual DbSet<SysLog> SysLog { get; set; }
public virtual DbSet<SysMenu> SysMenu { get; set; }
public virtual DbSet<SysRole> SysRole { get; set; }
public virtual DbSet<SysRoleMenuMapping> SysRoleMenuMapping { get; set; }
public virtual DbSet<SysUser> SysUser { get; set; }
public virtual DbSet<SysUserMenuMapping> SysUserMenuMapping { get; set; }
public virtual DbSet<SysUserRoleMapping> SysUserRoleMapping { get; set; }
}
调用代码如下:
using (SunDBSetEntities context=new SunDBSetEntities())
{
SysUser user = context.SysUser.Find(3);
SysUser user1 = context.SysUser.FirstOrDefault();
var userlist = context.SysUser.Where(u=>u.Id<10);
SysUser userNew = new SysUser()
{
Name = "yoyo",
Password = "12356789",
Status = 1,
Phone = "3167889953",
Mobile = "14567890432",
Address = "北京市",
Email = "56334678@qq.com",
QQ = 10304996765,
WeChat = "tyhj",
Sex = 1,
CreateTime = DateTime.Now,
CompanyId = 4,
LastLoginTime = null,
LastModifyTime = DateTime.Now
};
//EF有一个状态跟踪的东西;
//新增
context.SysUser.Add(userNew);
context.SaveChanges();
//修改
userNew.Name = "朝夕Richard老师";
context.SaveChanges();
//删除
context.SysUser.Remove(userNew);
context.SaveChanges();
CodeFirstFromDb
来自于数据库的CodeFirst,数据库已经存在;
三种映射:在写代码的时候:存在程序的实体对象名称,属性名称和数据库中的表名,字段名称不一致
- 特性映射 Table(“数据库表名称”) , [Column(“Name”)]
- modelBuilder.Entity<类名称>()
.ToTable(表名称) .Property(c => c.属性名称)
.HasColumnName(数据库字段名称); - public class SysLogMapping : EntityTypeConfiguration
{
public SysLogMapping()
{
this.ToTable(“SysLog”);
}
}
1.特性映射
[Table("SysUser")]
public partial class SysUserInfo
{
public int Id { get; set; }
[Required]
[StringLength(20)]
[Column("Name")]
public string UserName { get; set; }
[Required]
[StringLength(64)]
public string Password { get; set; }
public byte Status { get; set; }
[StringLength(20)]
public string Phone { get; set; }
[StringLength(20)]
public string Mobile { get; set; }
[StringLength(50)]
public string Address { get; set; }
[StringLength(100)]
public string Email { get; set; }
public long? QQ { get; set; }
[StringLength(50)]
public string WeChat { get; set; }
public byte? Sex { get; set; }
2.通过OnModelCreating 方法配置:链式语法配置
在OnModelCreating 方法中添加:modelBuilder.Entity< SysRoleInfo>().ToTable(“SysRole”).Property(p => p.TextInfo).HasColumnName(“Text”);
public partial class CodeFirstFromDb : DbContext
{
public CodeFirstFromDb()
: base("name=CodeFirstFromDb")
{
}
public virtual DbSet<Company> Companies { get; set; }
public virtual DbSet<SysLogInfo> SysLogs { get; set; }
public virtual DbSet<SysMenu> SysMenus { get; set; }
public virtual DbSet<SysRoleInfo> SysRoles { get; set; }
public virtual DbSet<SysRoleMenuMapping> SysRoleMenuMappings { get; set; }
public virtual DbSet<SysUserInfo> SysUsers { get; set; }
public virtual DbSet<SysUserMenuMapping> SysUserMenuMappings { get; set; }
public virtual DbSet<SysUserRoleMapping> SysUserRoleMappings { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<SysRoleInfo>().ToTable("SysRole").Property(p => p.TextInfo).HasColumnName("Text");
modelBuilder.Entity<Company>()
.HasMany(e => e.SysUsers)
.WithRequired(e => e.Company)
.WillCascadeOnDelete(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.Url)
.IsUnicode(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.Description)
.IsUnicode(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.SourcePath)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Phone)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Mobile)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Email)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.WeChat)
.IsUnicode(false);
}
}
3.定义Mapping配置类
(1)定义配置类:需要继承自EntityTypeConfiguration泛型类,泛型的具体类型是需要配置的类型;
(2)在OnModelCreating 方法中添加:modelBuilder.Configurations.Add(new SysLogInfoMapping());
/// <summary>
/// 配置类需要继承自EntityTypeConfiguration泛型类,泛型的具体类型是需要配置的类型
/// </summary>
public class SysLogInfoMapping : EntityTypeConfiguration<SysLogInfo>
{
public SysLogInfoMapping()
{
this.Property(p => p.UserNameInfo).HasColumnName("UserName");
this.ToTable("SysLog");
}
}
public partial class CodeFirstFromDb : DbContext
{
public CodeFirstFromDb()
: base("name=CodeFirstFromDb")
{
}
public virtual DbSet<Company> Companies { get; set; }
public virtual DbSet<SysLogInfo> SysLogs { get; set; }
public virtual DbSet<SysMenu> SysMenus { get; set; }
public virtual DbSet<SysRoleInfo> SysRoles { get; set; }
public virtual DbSet<SysRoleMenuMapping> SysRoleMenuMappings { get; set; }
public virtual DbSet<SysUserInfo> SysUsers { get; set; }
public virtual DbSet<SysUserMenuMapping> SysUserMenuMappings { get; set; }
public virtual DbSet<SysUserRoleMapping> SysUserRoleMappings { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new SysLogInfoMapping());
modelBuilder.Entity<Company>()
.HasMany(e => e.SysUsers)
.WithRequired(e => e.Company)
.WillCascadeOnDelete(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.Url)
.IsUnicode(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.Description)
.IsUnicode(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.SourcePath)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Phone)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Mobile)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Email)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.WeChat)
.IsUnicode(false);
}
}
CodeFirst
代码先行,先写业务逻辑,通过代码去生成数据库;
0.nuget安装EntityFramework
1.准备好实体+DbContext
2.配置好配置文件
3.直接运行代码,默认就去生成数据库
映射策略:
- new CreateDatabaseIfNotExists< CodeFirstContext>();//默认不存在就创建
- new DropCreateDatabaseAlways< CodeFirstContext>();//每次都删除重建
- new DropCreateDatabaseIfModelChanges< CodeFirstContext>();
- Database.SetInitializer< CodeFirstContext>(new DropCreateDatabaseIfModelChanges<
CodeFirstContext>());
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//默认选择第一个
new CreateDatabaseIfNotExists<CodeFirstDbContext>();//默认不存在就创建
// new DropCreateDatabaseAlways<CodeFirstDbContext>();//每次都删除重建
// new DropCreateDatabaseIfModelChanges<CodeFirstDbContext>();
//Database.SetInitializer<CodeFirstDbContext>(new DropCreateDatabaseIfModelChanges<CodeFirstDbContext>());
modelBuilder.Configurations.Add(new SysLogInfoMapping());
modelBuilder.Entity<SysRoleInfo>().ToTable("SysRole").Property(p => p.TextInfo).HasColumnName("Text");
modelBuilder.Entity<Company>()
.HasMany(e => e.SysUsers)
.WithRequired(e => e.Company)
.WillCascadeOnDelete(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.Url)
.IsUnicode(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.Description)
.IsUnicode(false);
modelBuilder.Entity<SysMenu>()
.Property(e => e.SourcePath)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Phone)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Mobile)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.Email)
.IsUnicode(false);
modelBuilder.Entity<SysUserInfo>()
.Property(e => e.WeChat)
.IsUnicode(false);