EF(Entity Framework)有三种使用场景,1. 从数据库生成Class,2.由实体类生成数据库表结构,3. 通过数据库可视化设计器设计数据库,同时生成实体类。本文介绍从数据库生成Class模式。
本人工作中对数据库的应用并不大,仅用于设备日常维保记录,起初接触数据库使用字符串连接,通过命令语句实现增删改查,使用EF后省去了命令语句的编写。文中将进行对比介绍:
首先设计数据库,建立home库,建立members表,表结构与内容如图:
- ![](https://img2022.cnblogs.com/blog/1046538/202211/1046538-20221115172030497-1651365474.png)
通过命令语句的方式实现家庭成员的增删改查:
声明变量:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
string conStr = "Database=home;Data Source=localhost;User Id=root;Password=123456";
MySqlCommand cmd;
MySqlDataReader result;
View Code
查询成员:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using (MySqlConnection comm = new MySqlConnection(conStr))
{
comm.Open();
string str = "select column_name from information_schema.columns where table_schema = '" + "home" + "' and table_name='" + "members" + "'";
cmd = new MySqlCommand(str, comm);
result = cmd.ExecuteReader();
if (result.HasRows)
{
while (result.Read())
{
ret += result.GetString("column_name") + "\t";
}
ret += "\r\n";
}
comm.Close();
comm.Open();
cmd = new MySqlCommand("select * from members", comm);
result = cmd.ExecuteReader();
if (result.HasRows)
{
while (result.Read())
{
ret += result[0].ToString() + "\t";
ret += result[1].ToString() + "\t";
ret += result[2].ToString() + "\t";
ret += result[3].ToString() + "\t";
ret += result[4].ToString() + "\t";
ret += result[5].ToString() + "\t";
ret += result[6].ToString() + "\t";
ret += result[7].ToString() + "\r\n";
}
}
comm.Close();
}
View Code
增加或修改成员:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using (MySqlConnection comm = new MySqlConnection(conStr))
{
comm.Open();
cmd = new MySqlCommand("select * from members where Role='" + role + "'", comm);
result = cmd.ExecuteReader();
if (result.HasRows)
{
string update = "update members set Age='" + m.age + "' TName=" + m.name + "' Sex='" + m.sex + "' BirthDay='" + m.birth
+ "' Job='" + m.job + "' TNumber='" + m.num + "' where Role='" + m.role + "'";
comm.Close();
comm.Open();
cmd = new MySqlCommand(update, comm);
cmd.ExecuteNonQuery();
comm.Close();
}
else
{
string update = "insert into members(TName,Sex,Age,Job,BirthDay,TNumber,Role) values('" + m.name + "','" + m.sex + "','" + m.age + "','" + m.job + "','" + m.birth + "','" + m.num + "','" + m.role + "')";
comm.Close();
comm.Open();
cmd = new MySqlCommand(update, comm);
cmd.ExecuteNonQuery();
comm.Close();
}
}
View Code
删除成员:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using (MySqlConnection comm = new MySqlConnection(conStr))
{
comm.Open();
cmd = new MySqlCommand("delete from members where Role='" + role + "'", comm);
cmd.ExecuteNonQuery();
comm.Close();
}
View Code
下面使用EF框架来实现一遍以上功能:
首先添加实体数据模型:
-> ![](https://img2022.cnblogs.com/blog/1046538/202211/1046538-20221115172907839-1578058178.png)
点击新建连接,然后输入本地名称,账户密码,选择数据库,再进行测试连接,成功后点击确定,程序就生成了homeEntities类。
![](https://img2022.cnblogs.com/blog/1046538/202211/1046538-20221115173128320-1975479083.png)
查询功能:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using (homeEntities db = new homeEntities())
{
foreach (var v in db.members)
{
ret += v.ID + "\t";
ret += v.TName + "\t";
ret += v.Sex + "\t";
ret += v.Age + "\t";
ret += v.Job + "\t";
ret += v.BirthDay + "\t";
ret += v.TNumber + "\t";
ret += v.Role + "\t\r\n";
}
}
View Code
增加或修改功能:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using (homeEntities db = new homeEntities())
{
members mem = db.members.Where(d=>d.Role==role).FirstOrDefault();
if (mem == null)
{
members newitem = new members()
{
TName = m.name,
TNumber = m.num,
Sex = m.sex,
Role = m.role,
BirthDay = m.birth,
Job = m.job,
Age = Convert.ToInt16(m.age),
};
db.members.Add(newitem);
}
else
{
mem.TName = m.name;
mem.TNumber = m.num;
mem.Sex = m.sex;
mem.Role = m.role;
mem.Job = m.job;
mem.BirthDay = m.birth;
mem.Age = Convert.ToInt16(m.age);
db.Entry(mem).State = EntityState.Modified;
}
db.SaveChanges();
}
View Code
删除功能:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using (homeEntities db = new homeEntities())
{
members mem = db.members.Where(d => d.Role == role).FirstOrDefault();
if(mem!=null)
db.members.Remove(mem);
db.SaveChanges();
}
View Code
界面演示:
![](https://img2022.cnblogs.com/blog/1046538/202211/1046538-20221115173820903-571108559.png)
由上可见使用EF后,可像操作类一样对数据库进行操作,无需进行命令语句编写。
当然EF的功能和使用范围远不止这些,本文仅对最基础的操作进行记录,可满足简单使用数据库的产品的应用。
|