c#中EntityFramework连接MySql数据示例支持Update部分字段

2022年12月7日 483点热度 0人点赞 0条评论

c#程序如果要连接数据库，以前使用的是ADO.Net，通过DataSet、DataTable、ExecuteNoQuery等方法完成，现在微软提供EntityFramework（其实已经提供很久了）组件实现。今天的主题就是对这个组件的示例。我们使用EntityFramework连接MySql数据库，完成对数据的增删改查功能

1、准备工作

假设程序的Net类库是4.5及以上，用EntityFramework连接MySql数据库的话，需要做下面这些操作。必备的DLL文件（文末提供下载）：MySql.Data.Dll、MySql.Data.Entity.EF6.dll、EntityFramework.dll、EntityFramework.Extended.dll（最后这个是扩展类，推荐使用）
引用完以后，创建一个TestDbContextConfiguration类，代码如下

public class TestDbContextConfiguration : DbConfiguration
{
    public TestDbContextConfiguration()
    {
        EntityFramework.Locator.Current.Register<EntityFramework.Batch.IBatchRunner>(() => new EntityFramework.Batch.MySqlBatchRunner());
    }
}

然后在DbContext类中定义好特性，代码如下

[DbConfigurationType(typeof(TestDbContextConfiguration))]
public class TestDbContext : DbContext
{
    public TestDbContext()
    {
        //此处重新设置Db连接字符串
        string key = "TestDbContext";
        string dbConnString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
        this.Database.Connection.ConnectionString = dbConnString;
    }


    public DbSet<ba_mac> ba_mac { get; set; }

    public DbSet<ba_user> ba_user { get; set; }


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new ba_macMap());
        modelBuilder.Configurations.Add(new ba_userMap());
    }

}

OK，准备工作完成

2、使用示例

2.1、获取多条数据

//1,获取多条数据
var listMac1 = dbContext.ba_mac.Where(c => c.UserCode == "wanggs").ToList();
System.Console.WriteLine("Mac表数据1个数：" + listMac1.Count);

2.2、分页获取多条数据

//1.1,分页获取多条数据
//注：用Skip和Take之前，一定要OrderBy
int pageSize = 5;
int pageIndex = 2;
var listMac11 = dbContext.ba_mac.OrderBy(c => c.MacAddress).Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();
var count11 = listMac11.Count;

2.3、获取部分字段

//2,获取部分字段的多条数据
var listMac2 = dbContext.ba_mac.Where(c => c.UserCode == "wanggs")
    .Select(c => new
    {
        c.MacAddress,
        c.UserCode,
        c.LogonTime,
        c.ProductName,
        c.ProductVersion
    })
    .ToList();

2.4、多表关联查询

//3,多表关联查询
var queryMac = dbContext.ba_mac.AsQueryable();
var listModel = from ta in queryMac
                join tb in dbContext.ba_user on ta.UserCode equals tb.UserCode into temp2
                from tb in temp2.DefaultIfEmpty()
                where tb.UserCode == "wanggs"
                select new bamacBillModel()
                {
                    MacAddress = ta.MacAddress,
                    UserCode = ta.UserCode,
                    UserName = tb.UserName,
                };
var jsonModel = JsonHelper.GetJsonByObject(listModel);

2.5、新增数据

//4,新增数据
ba_mac entityAdd = new ba_mac()
{
    MacAddress = "123456",
    UserCode = "userCode1",
    LogonResult = "OK",
    LogonTime = DateTime.Now,
};
dbContext.ba_mac.Add(entityAdd);
dbContext.SaveChanges();
trans.Commit();

2.6、修改数据

//5,修改数据
dbContext.ba_mac
    .Where(c => c.MacAddress == "123456")
    .Update(c => new ba_mac()
    {
        UserCode = "userCode11"
    });
dbContext.SaveChanges();
trans.Commit();

2.7、删除数据

//6,删除数据
var queryDel = dbContext.ba_mac.Where(c => c.MacAddress == "123456");
queryDel.Delete();
dbContext.SaveChanges();
trans.Commit();

还有个技术点，如果涉及多表更新的时候，就要用到事务。要引入下面的对象就可以

using (TestDbContext dbContext = new TestDbContext())
using (var trans = dbContext.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
}

用到的DLL文件

MySql+EntityFramework.dll