[EF]如何利用EF執行raw sql

  • 138
  • 0

[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();								

}



參考資料:
工作經驗