在EF中使用原生SQL,首先要创建上下文对象
using (var db = new Entities()) { //数据操作 }
新增
string sql = "insert into UserInfo values('zhangsan','123456')"; db.Database.ExecuteSqlCommand(sql);
参数化新增
sql = "insert into UserInfo values(@UserName,@UserPass)"; var param = new SqlParameter[] { new SqlParameter("@UserName","lisi"), new SqlParameter("@UserPass","123456") }; db.Database.ExecuteSqlCommand(sql, param);
删除
sql = "delete from UserInfo where UserName='zhangsan'"; db.Database.ExecuteSqlCommand(sql);
参数化删除
sql = "delete from UserInfo where UserName=@UserName"; db.Database.ExecuteSqlCommand(sql, new SqlParameter("@UserName", "lisi"));
修改
sql = "update UserInfo set UserName='wangwu'"; db.Database.ExecuteSqlCommand(sql);
查询多条记录
sql = "select * from UserInfo"; var users = db.Database.SqlQuery<UserInfo>(sql); foreach (var user in users) { ObjectDumper.Write(user);//打印对象 }
总结:操作方式与ADO.NET类似,EF框架自动为我们封装了数据库链接和查询对象。
完整案例:
//1.创建上下文对象 using (var db = new Entities()) { //增 string sql = "insert into UserInfo values('zhangsan','123456')"; db.Database.ExecuteSqlCommand(sql); //参数化新增 sql = "insert into UserInfo values(@UserName,@UserPass)"; var param = new SqlParameter[] { new SqlParameter("@UserName","lisi"), new SqlParameter("@UserPass","123456") }; db.Database.ExecuteSqlCommand(sql, param); //删 sql = "delete from UserInfo where UserName='zhangsan'"; db.Database.ExecuteSqlCommand(sql); //参数化删除 sql = "delete from UserInfo where UserName=@UserName"; db.Database.ExecuteSqlCommand(sql, new SqlParameter("@UserName", "lisi")); //改 sql = "update UserInfo set UserName='wangwu'"; db.Database.ExecuteSqlCommand(sql); //原生sql查询 sql = "select * from UserInfo"; var users = db.Database.SqlQuery<UserInfo>(sql); foreach (var user in users) { ObjectDumper.Write(user);//打印对象 } }