EF Code First增删改查
1、创建项目及下载Entity Framework
创建项目如下:
项目中安装Entity Framework:
2、数据库连接、实体类及映射文件配置
数据库连接配置文件:App.config
<?xml version="1.0" encoding="utf-8"?> <configuration> <configSections> <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --> <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /> </configSections> <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"> <parameters> <parameter value="Data Source=(local); User ID=sa; Password=1; MultipleActiveResultSets=True" /> </parameters> </defaultConnectionFactory> </entityFramework> </configuration>
实体类文件:Province.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Northwind.App.Entities { public class Province { /// <summary> /// 省份ID /// </summary> public Guid ProvinceID { get; set; } /// <summary> /// 省份编号 /// </summary> public string ProvinceNo { get; set; } /// <summary> /// 省份名称 /// </summary> public string ProvinceName { get; set; } } }
实体类映射文件:ProvinceMap.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Entity.ModelConfiguration; using System.ComponentModel.DataAnnotations.Schema; using Northwind.App.Entities; namespace Northwind.App.Mapping { public class ProvinceMap : EntityTypeConfiguration<Province> { public ProvinceMap() { // Primary Key this.HasKey(t => t.ProvinceID); // Properties this.Property(t => t.ProvinceNo).HasMaxLength(10); this.Property(t => t.ProvinceName).HasMaxLength(50); // Table & Column Mappings this.ToTable("Province"); this.Property(t => t.ProvinceID).HasColumnName("ProvinceID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.None); this.Property(t => t.ProvinceNo).HasColumnName("ProvinceNo"); this.Property(t => t.ProvinceName).HasColumnName("ProvinceName"); } } }
数据库操作上下文:NorthwindContext.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Entity; using Northwind.App.Entities; using Northwind.App.Mapping; namespace Northwind.App { public class NorthwindContext : DbContext { public DbSet<Province> Provinces { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new ProvinceMap()); } } }
3、新增
代码:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Northwind.App.Entities; namespace Northwind.App { class Program { static void Main(string[] args) { Province province = new Province { ProvinceID = Guid.NewGuid(), ProvinceNo = "100000", ProvinceName = "北京" }; using (NorthwindContext db = new NorthwindContext()) { db.Provinces.Add(province); db.SaveChanges(); } } } }
执行的Sql语句:
exec sp_executesql N'insert [dbo].[Province]([ProvinceID], [ProvinceNo], [ProvinceName]) values (@0, @1, @2) ',N'@0 uniqueidentifier,@1 nvarchar(10),@2 nvarchar(50)',@0='F0427980-6CB6-4D5B-91F5-ADE3E676DEA1',@1=N'100000',@2=N'北京'
4、修改
代码:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using Northwind.App.Entities; namespace Northwind.App { class Program { static void Main(string[] args) { Province province = new Province { ProvinceID = new Guid("F0427980-6CB6-4D5B-91F5-ADE3E676DEA1"), ProvinceNo = "100000", ProvinceName = "上海" }; using (NorthwindContext db = new NorthwindContext()) { db.Entry(province).State = EntityState.Modified; db.SaveChanges(); } } } }
执行的Sql语句:
exec sp_executesql N'update [dbo].[Province] set [ProvinceNo] = @0, [ProvinceName] = @1 where ([ProvinceID] = @2) ',N'@0 nvarchar(10),@1 nvarchar(50),@2 uniqueidentifier',@0=N'100000',@1=N'上海',@2='F0427980-6CB6-4D5B-91F5-ADE3E676DEA1'
5、删除
代码1:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using Northwind.App.Entities; namespace Northwind.App { class Program { static void Main(string[] args) { Province province = new Province { ProvinceID = new Guid("F0427980-6CB6-4D5B-91F5-ADE3E676DEA1") }; using (NorthwindContext db = new NorthwindContext()) { db.Provinces.Attach(province); db.Provinces.Remove(province); db.SaveChanges(); } } } }
代码2:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using Northwind.App.Entities; namespace Northwind.App { class Program { static void Main(string[] args) { Province province = new Province { ProvinceID = new Guid("F0427980-6CB6-4D5B-91F5-ADE3E676DEA1") }; using (NorthwindContext db = new NorthwindContext()) { db.Entry(province).State = EntityState.Deleted; db.SaveChanges(); } } } }
执行的Sql语句:
exec sp_executesql N'delete [dbo].[Province] where ([ProvinceID] = @0)',N'@0 uniqueidentifier',@0='F0427980-6CB6-4D5B-91F5-ADE3E676DEA1'
6、查询
代码:
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Northwind.App { class Program { static void Main(string[] args) { using (NorthwindContext db = new NorthwindContext()) { var expr = from p in db.Provinces select p; foreach (var item in expr) { Console.WriteLine("{0}-{1}-{2}", item.ProvinceID, item.ProvinceNo, item.ProvinceName); } } } } }
执行的Sql语句:
SELECT [Extent1].[ProvinceID] AS [ProvinceID], [Extent1].[ProvinceNo] AS [ProvinceNo], [Extent1].[ProvinceName] AS [ProvinceName] FROM [dbo].[Province] AS [Extent1]
以上全部代码为Entity Framework Code First的基本增删改查的操作,仅为第一步使用Entity Framework Code First操作Sql Server数据库。