public ActionResult Modules(string dataSource, string churchId) { // If the input is an integer, assume that the user is providing a church id int church = int.MinValue; bool isInt = int.TryParse(churchId, out church); // If it is not, lookup the church id by using the church code if (!isInt) { church = Convert.ToInt32(Common.ExecuteDBQuery(dataSource, string.Format("SELECT TOP 1 CHURCH_ID FROM ChmChurch.dbo.CHURCH WHERE CHURCH_CODE = '{0}'", churchId))); ViewData["churchId"] = church; } else { ViewData["churchId"] = churchId; } ViewData["churchId"] = church; ViewData["dataSource"] = dataSource; var dataContext = new ModulesDataContext(Common.FetchDBConnectionString(dataSource)); var modules = from m in dataContext.MODULEs orderby m.MODULE_ID join cm in dataContext.CHURCH_MODULEs on new { m.MODULE_ID, f1 = church } equals new { cm.MODULE_ID, f1 = cm.CHURCH_ID } into mod from x in mod.DefaultIfEmpty().OrderBy(cm => cm.MODULE_ID) select new QAUtil.Models.Modules { ENABLED = x.MODULE_ID != null ? true : false, MODULE_ID = m.MODULE_ID, MODULE_NAME = m.MODULE_NAME, CREATED_DATE = x.CREATED_DATE, CREATED_BY_USER_ID = x.CREATED_BY_USER_ID, LAST_UPDATED_DATE = x.LAST_UPDATED_DATE, LAST_UPDATED_BY_USER_ID = x.LAST_UPDATED_BY_USER_ID, CREATED_BY_LOGIN = x.CREATED_BY_LOGIN, LAST_UPDATED_BY_LOGIN = x.LAST_UPDATED_BY_LOGIN }; //select ISNULL(CONVERT(BIT, c_m.church_id), 0) AS ENABLED, * //from module m with (nolock) //left join church_module c_m with (nolock) //on m.module_id = c_m.module_id and c_m.church_id = 15 return(View(modules)); }
public ActionResult UpdateModules(List <QAUtil.Models.Modules> grid, FormCollection form) { int churchId = Convert.ToInt32(form["church"]); ViewData["churchId"] = churchId; ViewData["dataSource"] = form["dataSource"]; var dataContext = new ModulesDataContext(Common.FetchDBConnectionString(form["dataSource"])); foreach (var item in grid) { bool enabled = item.ENABLED; int module_id = item.MODULE_ID; // Enable or disable the module if (enabled) { if (!dataContext.CHURCH_MODULEs.Any(c => c.MODULE_ID == item.MODULE_ID && c.CHURCH_ID == churchId)) { CHURCH_MODULE cm = new CHURCH_MODULE() { CHURCH_ID = churchId, MODULE_ID = item.MODULE_ID, CREATED_DATE = DateTime.Now, LAST_UPDATED_DATE = DateTime.Now, CREATED_BY_LOGIN = HttpContext.User.Identity.Name }; dataContext.CHURCH_MODULEs.InsertOnSubmit(cm); dataContext.SubmitChanges(); } } else { if (dataContext.CHURCH_MODULEs.Any(c => c.MODULE_ID == item.MODULE_ID && c.CHURCH_ID == churchId)) { dataContext.CHURCH_MODULEs.DeleteOnSubmit(dataContext.CHURCH_MODULEs.Where(cm => cm.MODULE_ID == item.MODULE_ID && cm.CHURCH_ID == churchId).Select(cm => cm).Single()); dataContext.SubmitChanges(); } } } return(RedirectToAction("Modules", new { dataSource = ViewData["dataSource"], churchId = ViewData["churchId"] })); }