static void StatusTrackings()
{
StudentManageDBEntities db = new StudentManageDBEntities();
//禁用自动跟踪
db.Configuration.AutoDetectChangesEnabled = false;
}
//状态跟踪类型测试
static void Status()
{
StudentManageDBEntities db = new StudentManageDBEntities();
//创建一个对象：当前对象的状态为detached
Students student = new Students
{
StudentName = "龚均强",
Gender = "男",
Birthday = Convert.ToDateTime("1991-04-22"),
StudentIdNo = 520722199104221978,
CardNo = "2958141",
Age = 28,
PhoneNumber = "18408230331",
StudentAddress = "西华大学",
ClassId = 1,
};
Console.WriteLine(db.Entry(student).State.ToString());
//添加到缓存对象中：状态为Added
db.Students.Add(student);
Console.WriteLine(db.Entry(student).State.ToString());
//保存后：UnChanged
db.SaveChanges();
Console.WriteLine(db.Entry(student).State.ToString());
}
//导航属性与join查询
static void MultQuery()
{
StudentManageDBEntities db = new StudentManageDBEntities();
DateTime dt = Convert.ToDateTime("1800-01-11");
var query1 = from o in db.ScoreList
where o.Students.Gender == "女" && o.Students.StudentName.StartsWith("龚")
select new
{
o.Students.StudentName, o.Students.Gender, o.Students.StudentClass.ClassName, o.CSharp,
o.SQLServerDB
};
foreach (var student in query1)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", student.StudentName, student.Gender, student.ClassName, student.CSharp);
}
Console.WriteLine("-----------------------");
var query2 = from o in db.ScoreList
join s in db.Students on o.StudentId equals s.StudentId
join c in db.StudentClass on s.ClassId equals c.ClassId
where s.Gender == "女" && s.StudentName.StartsWith("龚")
select new { s.StudentName, s.Gender, c.ClassName, o.CSharp, o.SQLServerDB };
foreach (var student in query2)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", student.StudentName, student.Gender, student.ClassName, student.CSharp);
}
}
static void AddStudents()
{
Students student = new Students
{
StudentName = "龚均强",
Gender = "男",
Birthday = Convert.ToDateTime("1991-04-22"),
StudentIdNo = 510722199104221978,
CardNo = "1958141",
Age = 28,
PhoneNumber = "18408230331",
StudentAddress = "西华大学",
ClassId = 1,
};
//第一种写法：
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
db.Entry(student).State = EntityState.Added;
db.SaveChanges();
}
//第二种写法：
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
//db.Students.Attach(student);//可以省略，将student实体添加到上下文中
db.Students.Add(student);
db.SaveChanges();
}
}
static void UpdateStudents()
{
Students student = new Students
{
StudentName = "龚均强",
Gender = "男",
Birthday = Convert.ToDateTime("1991-04-22"),
StudentIdNo = 510722199104221978,
CardNo = "1958141",
Age = 28,
PhoneNumber = "18408230331",
StudentAddress = "西华大学",
ClassId = 1,
};
//第一种写法：构建全部属性对象修改
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
//db.Students.Attach(student);//可以省略，将student实体添加到上下文中
db.Entry(student).State = EntityState.Modified;
db.SaveChanges();
}
//第二种写法：先查询再修改
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
//查询对象
var stu = db.Students.FirstOrDefault(o => o.StudentId == 100012);
//修改对象属性
stu.StudentAddress = "红哇小区";
//提交修改
db.SaveChanges();//跟新到数据库（自动生成SQL语句）
}
}
//数据投影到对象集合
static void DataTolist()
{
StudentManageDBEntities db = new StudentManageDBEntities();
DateTime dt = Convert.ToDateTime("1800-01-11");
var query1 = from o in db.Students
where o.StudentName.StartsWith("龚") && o.Gender == "女" && o.Birthday > dt
orderby o.Age descending
select new { o.StudentId, o.StudentName, o.Birthday, o.Gender, o.Age };
List <Students> students = new List <Students>();
foreach (var item in query1)
{
students.Add(new Students
{
StudentId = item.StudentId,
StudentName = item.StudentName,
Gender = item.Gender,
Age = item.Age
});
}
foreach (var student in students)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", student.StudentId, student.StudentName, student.Gender, student.Age);
}
}
/// <summary>
/// 新增对象
/// </summary>
static void AddModel()
{
//【1】数据验证
//【2】对象封装
Students student = new Students
{
StudentName = "龚均强",
Gender = "男",
Birthday = Convert.ToDateTime("1991-04-22"),
StudentIdNo = 510722199104221978,
CardNo = "1958141",
Age = 28,
PhoneNumber = "18408230331",
StudentAddress = "西华大学",
ClassId = 1,
};
//【3】创建数据库上下文对象
StudentManageDBEntities db = new StudentManageDBEntities();
db.Students.Add(student); //将对象添加到缓存中
int result = db.SaveChanges(); //跟新到数据库（自动生成SQL语句）
Console.WriteLine(result);
Console.WriteLine("学号：" + student.StudentId);
}
static void DeleteStudents()
{
//根据主键构建一个对象
Students student = new Students
{
StudentId = 100000
};
//第一种写法：通过设置状态
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
db.Set <Students>().Attach(student);
db.Entry(student).State = EntityState.Deleted;
db.SaveChanges();
}
//第二种写法：通过remove方法
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
db.Set <Students>().Attach(student);
db.Students.Remove(student);
db.SaveChanges();//跟新到数据库（自动生成SQL语句）
}
//第三种写法：先查寻，再删除
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
var stu = db.Students.Where(o => o.StudentId == 100000).FirstOrDefault();
db.Students.Remove(stu);
db.SaveChanges();//跟新到数据库（自动生成SQL语句）
}
}
/// <summary>
/// 编辑：修改
/// </summary>
static void Modify()
{
//【1】数据验证
//【2】对象封装
Students student = new Students
{
StudentId = 100012,
StudentName = "龚均强",
Gender = "男",
Birthday = Convert.ToDateTime("1991-04-22"),
StudentIdNo = 510722199104221978,
CardNo = "0000000",
Age = 28,
PhoneNumber = "18408230331",
StudentAddress = "西华大学",
ClassId = 2,
};
//【3】创建数据库上下文对象
StudentManageDBEntities db = new StudentManageDBEntities();
db.Students.Attach(student);
db.Entry(student).State = EntityState.Modified; //设置对象的状态为修改
int result = db.SaveChanges(); //跟新到数据库（自动生成SQL语句）
Console.WriteLine(result);
Console.WriteLine("学号：" + student.StudentId);
}
static void Query()
{
StudentManageDBEntities db = new StudentManageDBEntities();
//var list = db.Students.Where(o => o.StudentId > 100000);
////var list = db.Students.Select(o=>o.StudentId);
var list = from a in db.Students where a.StudentId > 100000 select a;
Console.WriteLine(list);
foreach (var item in list)
{
Console.WriteLine(item.StudentName + "\t" + item.Birthday + "\t" + item.StudentAddress);
}
}
/// <summary>
/// 线查找对象再删除对象
/// </summary>
static void Delete()
{
StudentManageDBEntities db = new StudentManageDBEntities();
//查询对象
var student = db.Students.FirstOrDefault(o => o.StudentId == 100012);
//从缓存中移除对象删除
db.Students.Remove(student);
//提交
int result = db.SaveChanges();//跟新到数据库（自动生成SQL语句）
Console.WriteLine("影响行数：" + result);
}
//状态跟踪与无状态跟踪
static void StatusTracking()
{
StudentManageDBEntities db = new StudentManageDBEntities();
//状态跟踪查询：UnChanged
var stu1 = (from o in db.Students select o).FirstOrDefault();
Console.WriteLine(db.Entry(stu1).State.ToString());
//无状态查询：detached 查询不被跟踪，好处：提升性能，返回实体不缓存，适合纯粹查询
var stu2 = db.Students.AsNoTracking().Select(o => o).FirstOrDefault();
Console.WriteLine(db.Entry(stu2).State.ToString());
}
/// <summary>
/// 部分字段的修改
/// </summary>
static void Modify1()
{
StudentManageDBEntities db = new StudentManageDBEntities();
//查询对象
var stu = db.Students.FirstOrDefault(o => o.StudentId == 100012);
//修改对象属性
stu.StudentAddress = "红哇小区";
//提交修改
int result = db.SaveChanges();//跟新到数据库（自动生成SQL语句）
Console.WriteLine("影响行数：" + result);
}
static void SelectStudents()
{
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
var stulist = from o in db.Students select o;
foreach (var student in stulist)
{
Console.WriteLine(student.StudentName + "\t" + student.StudentId);
}
//查询学总数
Console.WriteLine("学生总数：" + db.Students.Count());
//使用缓存查询总数
Console.WriteLine("学生总数：" + db.Students.Local.Count());
}
}
static void Delete2()
{
StudentManageDBEntities db = new StudentManageDBEntities();
Students student = new Students
{
StudentId = 100013,
};
db.Set <Students>().Attach(student);
db.Entry(student).State = EntityState.Deleted;
//提交
int result = db.SaveChanges();//跟新到数据库（自动生成SQL语句）
Console.WriteLine("影响行数：" + result);
}
//子查询
static void SuQuery()
{
StudentManageDBEntities db = new StudentManageDBEntities();
var query2 = from s in (from o in db.Students
where o.ClassId == 1 || o.ClassId == 2
select new { o.StudentClass.ClassName, o.StudentName, o.StudentId })
where s.StudentId > 100000
select s;
Console.WriteLine(query2);
foreach (var student in query2)
{
Console.WriteLine("{0}\t{1}\t{2}", student.ClassName, student.StudentName, student.StudentId);
}
}
//EF执行原声SQL语句：增删改操作，使用DBContext对象的DataBase属性，该属性支持一系列数据库操作
static void ExecSql()
{
string sql1 = "update Students set StudentName='张某某' where StudentId=100002";
string sql2 = "update Students set StudentName=@StudentName where StudentId=@StudentId";
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@StudentName", "龚老师"),
new SqlParameter("@StudentId", 100000),
};
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
var result1 = db.Database.ExecuteSqlCommand(sql1);
var result2 = db.Database.ExecuteSqlCommand(sql2, paras);
Console.WriteLine("执行结果：" + result1 + "\t" + result2);
}
}
//数据投影与匿名对象
static void DataProjection()
{
StudentManageDBEntities db = new StudentManageDBEntities();
DateTime dt = Convert.ToDateTime("1800-01-11");
var query1 = from o in db.Students
where o.StudentName.StartsWith("龚") && o.Gender == "女" && o.Birthday > dt
orderby o.Age descending
select new { o.StudentId, o.StudentName, o.Birthday, o.Gender, o.Age };
foreach (var student in query1)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t", student.StudentId, student.StudentName, student.Gender, student.Age);
}
Console.WriteLine(query1);
Console.WriteLine("查询总数：{0}", query1.Count());
}
//select
static void ExecSelect()
{
var sql = "select count(1) from Students";
var sql1 = "select * from Students where ClassId=@ClassId";
SqlParameter[] paras1 = new SqlParameter[]
{
new SqlParameter("@ClassId", 1),
};
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
var result1 = db.Database.SqlQuery <int>(sql).ToList()[0];
var result2 = db.Database.SqlQuery <Students>(sql1, paras1);
foreach (var stu in result2)
{
Console.WriteLine(stu.StudentName);
}
Console.WriteLine("总数：" + result1);
}
}
//在select子句中使用子查询
static void SlelectQuery()
{
StudentManageDBEntities db = new StudentManageDBEntities();
var query2 = from s in db.Students
select new
{
name = s.StudentName,
subject = from o in s.ScoreList select new { sql = o.CSharp, DB = o.SQLServerDB }
};
Console.WriteLine(query2);
foreach (var student in query2)
{
Console.WriteLine(student.name);
foreach (var s in student.subject)
{
Console.WriteLine("{0}\t{1}\n", s.DB, s.sql);
}
}
}
static void LinkToEntities()
{
StudentManageDBEntities db = new StudentManageDBEntities();
//Convert不能被转变换成对应的sql语句；会报错
// var query1 = from o in db.Students
// where o.StudentName.StartsWith("龚") && o.Gender=="男" && o.Birthday>Convert.ToDateTime("1900-01-11")
// orderby o.Age descending
// select o;
//解决办法：
//可以先将不能转换成对应sql语句的方法单独拿出来转换后在使用
DateTime dt = Convert.ToDateTime("1800-01-11");
var query1 = from o in db.Students
where o.StudentName.StartsWith("龚") && o.Gender == "女" && o.Birthday > dt
orderby o.Age descending
select o;
Console.WriteLine(query1);
Console.WriteLine("查询总数：{0}", query1.Count());
}
//调用存储过程
static void ExecProcedure()
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@StudentName", "龚老师"),
new SqlParameter("@StudentId", 100001),
};
SqlParameter[] paras1 = new SqlParameter[]
{
new SqlParameter("@ClassId", 1),
};
using (StudentManageDBEntities db = new StudentManageDBEntities())
{
var result1 = db.Database.ExecuteSqlCommand("execute usp_updateStu @StudentName,@StudentId", paras);
var result2 = db.Database.SqlQuery <Students>("execute usp_selectStu @ClassId", paras1);
foreach (var stu in result2)
{
Console.WriteLine(stu.StudentName);
}
Console.WriteLine("第一个受影响行数：" + result1);
}
}