在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","")
};
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","")
};
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);//打印对象
}
}