/// <summary>
/// 创建数据库上下文
/// </summary>
public static void CreateDBContext()
{
//DbContext
using (var db = new MasDBContext())
{
//db.Database.CreateIfNotExists();//如果数据库不存在时则创建
db.Donators.Add(new Donator()
{
Name = "mas", Amount = 10000, DonateDate = DateTime.Now
});
db.SaveChanges();//自动创建数据库
//这是通过检测上下文中所有的对象的状态来完成的。所有的对象都驻留在上下文类
//Deleted，Added，Modified和Unchanged
}
//自定义连接字符串
using (var db = new MasDBContext("Server=.;Database=CodeFirstAppx;Integrated Security=SSPI;MultipleActiveResultSets=true;"))
{
db.Donators.FirstOrDefault();
}
//将DbContext转换成ObjectContext
using (var db = new MasDBContext())
{
var objectContext = (db as System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext;
}
}
/// <summary>
/// 删除记录——Delete
/// </summary>
static void Delete()
{
#region 先查询后删除
using (MasDBContext db = new MasDBContext())
{
#region 4.0 删除记录
var toBeDeletedDonator = db.Donators.FirstOrDefault(d => d.Name == "待打赏");//根据Name找到要删除的测试数据
if (toBeDeletedDonator != null)
{
db.Donators.Remove(toBeDeletedDonator); //如果满足条件，就将该对象使用Remove方法标记为Deleted
db.SaveChanges(); //最后持久化到数据库
//db.Donators.RemoveRange(db.Donators.Where(g => g.Name == "待打赏"));//批量删除
}
#endregion
}
#endregion
#region 把对象附加到上下文中，然后删除
using (MasDBContext db = new MasDBContext())
{
Donator entity = new Donator();
entity.DonatorId = 1;
//也是要跟踪当前的实体对象
db.Donators.Attach(entity);
//将状态设置为Deleted
db.Donators.Remove(entity);
db.SaveChanges();
}
#endregion
#region 将DBEntityEntry状态设置为Deleted
using (MasDBContext db = new MasDBContext())
{
Donator entity = new Donator();
entity.DonatorId = 1;
//也是要跟踪当前的实体对象
db.Donators.Attach(entity);
//将DBEntityEntry状态设置为Deleted
db.Entry(entity).State = EntityState.Deleted;
db.SaveChanges();
}
#endregion
Console.Write("DB has Deleted!");//提示DB创建成功
Console.Read();
}
/// <summary>
/// 事务操作
/// </summary>
static void Transaction()
{
#region 显示事务
//隐形事务
using (MasDBContext db = new MasDBContext())
{
//db.Database.Connection.Open();//打开数据库连接
using (DbContextTransaction tran = db.Database.BeginTransaction())
{
try
{
Donator article = new Donator()
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
};
db.Donators.Add(article);
db.SaveChanges();
article = new Donator()
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
};
db.Donators.Add(article);
db.SaveChanges();
//提交事务
tran.Commit();
}
catch (Exception ex)
{
//回滚事务
tran.Rollback();
throw ex;
}
}
}
#endregion
#region 分布式事务 (显示事务)
//分布式事务 (显示事务)
using (TransactionScope tran = new TransactionScope())
{
#region 新增一
using (MasDBContext db = new MasDBContext())
{
try
{
Donator article = new Donator()
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
};
db.Donators.Add(article);
db.SaveChanges();
article = new Donator()
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
};
db.Donators.Add(article);
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
//其他DBContext
#region 新增二
using (MasDBContext db = new MasDBContext())
{
try
{
Donator article = new Donator()
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
};
db.Donators.Add(article);
db.SaveChanges();
article = new Donator()
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
};
db.Donators.Add(article);
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
//提交事务
//tran.Complete();
}
#endregion
}
/// <summary>
/// 更新记录——Update
/// </summary>
static void Update()
{
goto GG;
#region 先查询，后修改
using (MasDBContext db = new MasDBContext())
{
#region 3.0 更新记录 单个值修改
//先查询出要修改的数据，然后再修改新的值
var toBeUpdatedDonator = db.Donators.FirstOrDefault(d => d.DonatorId == 1);
if (toBeUpdatedDonator != null)
{
toBeUpdatedDonator.Name = "醉千秋";
toBeUpdatedDonator.DonateDate = DateTime.Now;
//db.Entry(toBeUpdatedDonator).State = EntityState.Modified; //全部字段更新
db.SaveChanges();
}
//sql语句
/*
*
* 更新了部分字段
* exec sp_executesql N'UPDATE [dbo].[Donator]
* SET [DonatorName] = @0, [DonateDate] = @1
* WHERE ([Id] = @2)
* ',N'@0 nvarchar(10),@1 datetime2(7),@2 int',@0=N'醉千秋',@1='2017-08-28 10:44:02.7261187',@2=3
*
*/
#endregion
}
#endregion
#region 更新记录 单个值修改
using (MasDBContext db = new MasDBContext())
{
Donator entity = new Donator()
{
DonatorId = 1, Name = "Mas", DonateDate = DateTime.Now
};
//将当前实体用EF进行跟踪
db.Donators.Attach(entity);
entity.Name = "Mass";
db.SaveChanges();
//sql语句
/*
* 更新了部分字段
*
* exec sp_executesql N'UPDATE [dbo].[Donator]
* SET [DonatorName] = @0
* WHERE ([Id] = @1)
* ',N'@0 nvarchar(10),@1 int',@0=N'Mass',@1=1
*
*
*/
}
#endregion
#region 更新记录 全部值修改
using (MasDBContext db = new MasDBContext())
{
//
Donator entity = new Donator()
{
DonatorId = 1
};
//将状态设置为Modified
DbEntityEntry entry = db.Entry(entity);
entry.State = EntityState.Modified;
db.SaveChanges();
//sql语句
/*
* 是更新了全部字段
*
* exec sp_executesql N'UPDATE [dbo].[Donator]
* SET [DonatorName] = @0, [Amount] = @1, [DonateDate] = @2
* WHERE ([Id] = @3)
* ',N'@0 nvarchar(10),@1 decimal(18,2),@2 datetime2(7),@3 int',@0=N'Mas',@1=0,@2='2017-08-28 11:07:59.6731249',@3=1
*
*
*/
}
#endregion
#region 更新记录 部分值修改
using (MasDBContext db = new MasDBContext())
{
Donator article = new Donator()
{
DonatorId = 1, Name = "Mas", DonateDate = DateTime.Now
};
//将当前实体用EF进行跟踪
db.Donators.Attach(article);
DbEntityEntry entry = db.Entry(article);
//部分更新 需要的字段设为为IsModified=true
entry.Property("Name").IsModified = true;
entry.Property("DonateDate").IsModified = true;
db.SaveChanges();
//sql
/*
*
* 是更新了全部字段
*
* exec sp_executesql N'UPDATE [dbo].[Donator]
* SET [DonatorName] = @0, [DonateDate] = @1
* WHERE ([Id] = @2)
* ',N'@0 nvarchar(10),@1 datetime2(7),@2 int',@0=N'Mas',@1='2017-08-28 11:26:37.6201252',@2=1
*
*
*/
}
#endregion
GG:
using (var db = new MasDBContext())
{
Donator originalEmployee = new Donator()
{
DonatorId = 1
};
db.Donators.Attach(originalEmployee);
Donator newEmployee = new Donator()
{
DonatorId = 1, Name = "Mas", DonateDate = DateTime.Now, Amount = 100
};
db.Entry(originalEmployee).CurrentValues.SetValues(newEmployee);
db.SaveChanges();
/*
*
* Donator originalEmployee = new Donator() { DonatorId = 1 };
* Donator newEmployee = new Donator() { DonatorId = 1, Name = "Mas", DonateDate = DateTime.Now, Amount = 100 };
*
*
* exec sp_executesql N'UPDATE [dbo].[Donator]
* SET [DonatorName] = @0, [Amount] = @1, [DonateDate] = @2
* WHERE ([Id] = @3)
* ',N'@0 nvarchar(10),@1 decimal(18,2),@2 datetime2(7),@3 int',@0=N'Mas',@1=100.00,@2='2017-08-28 17:50:36.4737045',@3=1
*
*
*
*
* Donator originalEmployee = new Donator() { DonatorId = 1, Name = "Mas", };
* Donator newEmployee = new Donator() { DonatorId = 1, Name = "Mas", DonateDate = DateTime.Now, Amount = 100 };
*
*
*
* exec sp_executesql N'UPDATE [dbo].[Donator]
* SET [Amount] = @0, [DonateDate] = @1
* WHERE ([Id] = @2)
* ',N'@0 decimal(18,2),@1 datetime2(7),@2 int',@0=100.00,@1='2017-08-28 18:34:34.5027045',@2=1
*
*/
}
Console.Write("DB has Updated!");//提示DB创建成功
Console.Read();
}
/// <summary>
/// 创建记录——Create
/// </summary>
static void Insert()
{
#region 批量新增
using (var db = new MasDBContext())
{
var donators = new List <Donator>
{
new Donator
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
},
new Donator
{
Name = "海风",
Amount = 5,
DonateDate = new DateTime(2016, 4, 8)
},
new Donator
{
Name = "醉千秋",
Amount = 18.8m,
DonateDate = new DateTime(2016, 4, 15) //一条语句有问题，所有语句都回滚
}
};
db.Donators.AddRange(donators); //批量数据插入
db.SaveChanges(); //隐式事务，自动提交事务
//方法将更改提交到数据库，
//该方法是以单事务执行的。最终，所有的数据库更改都会以单个工作单元持久化。
//既然是事务的，那么这就允许将批量相关的更改作为单个操作提交，这样就保证了事务一致性和数据完整性。
}
#endregion
#region 批量新增 DbEntityEntry设为Added
using (var db = new MasDBContext())
{
var donators = new List <Donator>
{
new Donator
{
Name = "陈志康",
Amount = 50,
DonateDate = new DateTime(2016, 4, 7)
},
new Donator
{
Name = "海风",
Amount = 5,
DonateDate = new DateTime(2016, 4, 8)
},
new Donator
{
Name = "醉千秋",
Amount = 18.8m,
DonateDate = new DateTime(2016, 4, 15) //一条语句有问题，所有语句都回滚
}
};
//将donators添加到DbEntityEntry中，并将State状态设置为 Added
foreach (var item in donators)
{
db.Entry(item).State = EntityState.Added;
}
db.SaveChanges();
}
#endregion
Console.Write("DB has Created!");//提示DB创建成功
Console.Read();
}