[EF]如何利用EF執行raw sql
insert(不過update, delete的方法也都一樣):
using (MyEntity db = new MyEntity())
{
string sql = "";
sql = sql + " insert into tblHCK(FB_CLOCK_BRCH, FB_ONOFF_LOCATION, FB_CLOCK_CODE, FB_EMPL_BRCH, EMPLID ";
sql = sql + " , FB_LOST_IND, FB_PUNCH_DATE, FB_PUNCH_TIME, FB_ONOFF_IND) ";
sql = sql + " VALUES ";
sql = sql + " (@FB_CLOCK_BRCH, @FB_ONOFF_LOCATION, @FB_CLOCK_CODE, @FB_EMPL_BRCH, @EMPLID ";
sql = sql + " , @FB_LOST_IND, @FB_PUNCH_DATE, @FB_PUNCH_TIME, @FB_ONOFF_IND) ";
List<SqlParameter> paraOffList = new List<SqlParameter>();
paraOffList.Add(new SqlParameter("@FB_CLOCK_BRCH", "FCB"));
paraOffList.Add(new SqlParameter("@FB_ONOFF_LOCATION", emp.LOCATION));
paraOffList.Add(new SqlParameter("@FB_CLOCK_CODE", "01"));
paraOffList.Add(new SqlParameter("@FB_EMPL_BRCH", "FCB"));
paraOffList.Add(new SqlParameter("@EMPLID", emp.EMPLID));
paraOffList.Add(new SqlParameter("@FB_LOST_IND", "00"));
paraOffList.Add(new SqlParameter("@FB_PUNCH_DATE", startDate.ToString("yyyyMMdd")));
paraOffList.Add(new SqlParameter("@FB_PUNCH_TIME", "17" + rand.Next(30, 45).ToString() + "00"));
paraOffList.Add(new SqlParameter("@FB_ONOFF_IND", "0"));
noOfRowInserted = 0;
noOfRowInserted = db.Database.ExecuteSqlCommand(sql, paraOffList.ToArray());
if (noOfRowInserted == 0)
{
ErrMsg = "新增員工" + emp.EMPLID + "的下班的打卡資料的時候發生錯誤,sql=" + sql;
MessageBox.Show(ErrMsg);
return;
}
db.SaveChanges();
}
select query:
using (MyEntities db = new MyEntities())
{
string sql = " select * from Member where memberid = @id " ;
var memberList = db.Database.SqlQuery<Member>(sql, new SqlParameter("@id", "A123456788"))
.ToList();
memberList.Dump();
}
參考資料:
工作經驗