一、单表操作
数据表的创建,创建名为UserData的数据表,包含以下属性(UserId、UserName、Password)
![](https://img-blog.csdnimg.cn/img_convert/da9ddeb0d85bf6ad1fcf896f005907ac.png)
在对表操作前向主程序program.cs中插入方法的调用
using (var db = new Learn.Db.Db.FirstDbContext())
数据的查询
var blogs = db.UserData
.Where(b =>b.UserId == 1)
.ToList();
Console.WriteLine(blogs.ToString());
blogs.ToString();
SQL查询
var data = db.UserData.FromSqlInterpolated($"SELECT * FROM LearnDb.dbo.UserData WHERE UserId = {1}").ToList();
var blogs = db.UserData.FromSqlInterpolated($"SELECT * FROM dbo.Blogs");
添加数据
UserData UserData = new UserData();
UserData.UserId = 3;
UserData.UserName = "ChenZhe";
UserData.Password = "111111";
db.UserData.Add(UserData);
int count = db.SaveChanges();
修改数据
var UserData = db.UserData.Single(UserData => UserData.UserName=="Make");//查询原数据
UserData.UserName ="WuTao";//修改原数据
await db.SaveChangesAsync();//把修改的数据保存关系数据库
删除数据
var UserData = db.UserData.Single(UserData => UserData.UserName == "ZhangLe");
db.Remove(UserData);
await db.SaveChangesAsync();//把删除的数据保存关系数据库
二、多表查询
1.数据准备
建立以下两张表,用来做联表查询(通过Id将两张表连接起来,查询Pet所对应的Owner):
![](https://img-blog.csdnimg.cn/img_convert/e206518d628d2a6618bfccc84a873998.png)
![](https://img-blog.csdnimg.cn/img_convert/a1bc7af9589f7f10b689603a4aa9daf2.png)
2. 获取属性
public class Entity
{
//Persons表
[Table("Persons")]
public class Persons
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
}
//Pets表
[Table("Pets")]
public class Pets
{
[Key]
public int Id { get; set; }
[Column("Name", TypeName = "string")]
public string PetName { get; set; }
public int Owner { get; set; }
}
}
3. 建立模型
public DbSet<Entity.Persons> Persons { get; set; }
public DbSet<Entity.Pets> Pets { get; set; }
public DbSet<Result> Results { get; set; }
Result用来保存查询到的结果。
4. 定义接口
创建一个接口类用来定义接口
![](https://img-blog.csdnimg.cn/img_convert/42b914c828ffc3d1e7dcebae2abeb189.png)
public interface IActionResult
{
List<Result> TestQuery();
}
5. 定义方法
创建一个类用来定义方法
![](https://img-blog.csdnimg.cn/img_convert/8d32e1c8c85b8097a8cf12303d583a77.png)
public class ActionResult : IActionResult
{
public List<Result> TestQuery()
{
try
{
using (var db = new Learn.Db.Db.FirstDbContext())
{
var sql = "select a.Id,a.Name,b.Name PetName from Persons a left join Pets b on a.Id = b.Id";
var result = db.Results.FromSqlRaw(sql).ToList();
return result;
}
}
catch (Exception)
{
throw;
}
}
6. 调用方法,运行程序
IActionResult actionResult = new ActionResult();//调用方法
using (var db = new Learn.Db.Db.FirstDbContext())
{
var data = actionResult.TestQuery();
}
catch(Exception e)
{
}
程序运行结果如下:
![](https://img-blog.csdnimg.cn/img_convert/d59f1710c610f583c78e676ed6ec60d6.png)