public StatusCode DeleteStatusCode(StatusCode entity) { string mySQL = " Delete[dbo].[TM_MDC_STATUS_CODES] " + $" WHERE[Status] = @{nameof(entity.Status)}"; int rowsAffected = _db.Execute(mySQL, entity); return(entity); }
public CommentType InsertType(CommentType entity) { string mySQL = "INSERT INTO [TM_MDC_COMMENT_TYPES] ([TYPE],[TYPE_DESC]) VALUES " + "('" + entity.CommentTypeCode + "','" + entity.TypeDesc.ToString() + "')"; int rowsAffected = _db.Execute(mySQL); return(entity); }
public PriorityCode InsertPriorityCode(PriorityCode entity) { string mySQL = " INSERT INTO [TM_MDC_LOG_PRIORITY] ([PRIORITY],[DESCR]) VALUES " + " ( '" + entity.Priority + "','" + entity.Descr + "')"; int rowsAffected = _db.Execute(mySQL); return(entity); }
public bool SubmitApproval(int nPRID) { string sql = " Update TH_PurchaseRequisition Set Status=3,SubmittedBy='" + System.Web.HttpContext.Current.User.Identity.Name + "' where id =@nPRID"; db.Execute(sql, new { nPRID }); db.Close(); return(true); }
public CommentType InsertType(CommentType entity) { string mySQL = "INSERT INTO [TM_MDC_COMMENT_TYPES] ([TYPE],[TYPE_DESC]) VALUES " + "('" + entity.CommentTypeCode + "','" + entity.TypeDesc.ToString() + "')"; int rowsAffected = _db.Execute(mySQL); if (rowsAffected == 0) { throw new Exception($"Record Does Not Exist: {entity.CommentTypeCode}"); } return(entity); }
public LogComment InsertComment(LogComment entity) { string mySQL = "INSERT INTO [dbo].[TM_DIST_CHG_LOG_COMMENTS] " + "([LOG_NUM] " + ",[BASIC_NUMBER] " + ",[TYPE] " + ",[LINK_TO_SEQ_NUM] " + ",[CREATED_DATE]" + ",[CREATED_BY_SEQ_NUM]" + ",[COMMENT_TEXT]" + ",[CREATED_BY]" + ",[TYPE_DESC]) VALUES (" + entity.LogNumber + ",'" + entity.BasicNumber + "'" + ",'" + entity.CommentTypeCode + "'" + "," + entity.LinkToSeqNum + ",'" + DateTime.Now + "'" + "," + entity.CreatedBySeqNum + ",'" + entity.Comment + "'" + ",'" + entity.CreatedBy + "'" + ",'" + entity.TypeDesc + "')"; int rowsAffected = _db.Execute(mySQL); return(entity); }
public void Update(Core.Domain.Building model) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); string sql = " Update tblItem Set CategoryID=@CategoryID,BrandID=@BrandID,ItemCode=@ItemCode,Name=@Name,Unit=@Unit Where id=@ID"; db.Execute(sql, new { model.ID }); db.Close(); }
private static void UpdateSquadEvents(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction) { string assignEventToSquadsSql = @"INSERT INTO SquadEvents (SquadId, EventId) VALUES ( (SELECT SquadId FROM Squads WHERE Guid = @SquadGuid), (SELECT EventId FROM Events WHERE Guid = @EventGuid));" ; foreach (var squad in @event.Squads) { var eventSquadParameters = new DynamicParameters(); eventSquadParameters.Add("@EventGuid", @event.Guid.ToString()); eventSquadParameters.Add("@SquadGuid", squad.Guid); connection.Execute(assignEventToSquadsSql, eventSquadParameters, transaction); } }
public string NewPurchaseReq(List <Core.Domain.Purchase_Requisition.PurchaseRequisitionDetail> PurchaseItem, Core.Domain.Purchase_Requisition.PurchaseRequisition PurchaseHeader) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); string sql; //First Add Header int ID = 0; string NewPrNO = "PR-" + DateTime.Now.ToLongTimeString(); sql = " INSERT INTO TH_PurchaseRequisition(PRNo,PRDate,KitchenID,Remarks)VALUES ('" + NewPrNO + "',@PRDate,1,@Remarks) " + "Select Cast(SCOPE_IDENTITY() AS int)"; try { ID = db.Query <int>(sql, new { PurchaseHeader.PRDate, PurchaseHeader.Remarks }).SingleOrDefault(); } catch (Exception e) { if (e.Message.IndexOf("") > 0) { db.Close(); return("-1"); } } foreach (PurchaseRequisitionDetail obj in PurchaseItem) { sql = " Insert into TD_PurchaseRequisition (PRID,ItemID,Qty,Unit,Specs) " + " Values (" + ID + ",@ItemID,@Qty,@Unit,@Specs)"; db.Execute(sql, new { obj.ItemId, obj.Qty, obj.Unit, obj.Specs }); } return(NewPrNO); }
private static void UpdateEventTrainingMaterials(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction) { string insertTrainingMaterialsSql = @"INSERT INTO EventTrainingMaterials (EventId, TrainingMaterialId) VALUES ( (SELECT EventId FROM Events WHERE Guid = @EventGuid), (SELECT TrainingMaterialId FROM TrainingMaterials WHERE Guid = @TrainingMaterialGuid))" ; if (@event.TrainingMaterials != null) { foreach (var material in @event.TrainingMaterials) { var materialParameters = new DynamicParameters(); materialParameters.Add("@EventGuid", @event.Guid.ToString()); materialParameters.Add("@TrainingMaterialGuid", material.Guid.ToString()); connection.Execute(insertTrainingMaterialsSql, materialParameters, transaction); } } }
private static void CreateEvent(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction) { string insertEventSql = @"INSERT INTO Events(ClubId, Guid, Title, StartDate, EndDate, Location, EventTypeId) SELECT C.ClubId, @EventGuid, @Title, @StartDate, @EndDate, @Location, @EventTypeId FROM CLUBS C WHERE C.Guid = @ClubGuid"; var insertEventParameters = new DynamicParameters(); insertEventParameters.Add("@ClubGuid", @event.ClubId.ToString()); insertEventParameters.Add("@EventGuid", @event.Guid.ToString()); insertEventParameters.Add("@Title", @event.Title); insertEventParameters.Add("@StartDate", @event.StartDate); insertEventParameters.Add("@EndDate", @event.EndDate); insertEventParameters.Add("@Location", @event.Location); insertEventParameters.Add("@EventTypeId", (byte)@event.EventType); connection.Execute(insertEventSql, insertEventParameters, transaction); }
private static void UpdateEvent(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction) { string updateEventSql = @" DELETE SquadEvents WHERE EventId = (SELECT EventId FROM Events WHERE Guid = @EventGuid); DELETE EventTrainingMaterials WHERE EventId = (SELECT EventId FROM Events WHERE Guid = @EventGuid); UPDATE Events SET Title = @Title, StartDate = @StartDate, EndDate = @EndDate, Location = @Location, EventTypeId = @EventTypeId WHERE Guid = @EventGuid;" ; var updateParameters = new DynamicParameters(); updateParameters.Add("@ClubGuid", @event.ClubId.ToString()); updateParameters.Add("@EventGuid", @event.Guid.ToString()); updateParameters.Add("@Title", @event.Title); updateParameters.Add("@StartDate", @event.StartDate); updateParameters.Add("@EndDate", @event.EndDate); updateParameters.Add("@Location", @event.Location); updateParameters.Add("@EventTypeId", (byte)@event.EventType); connection.Execute(updateEventSql, updateParameters, transaction); }
public string EditPurchaseReq(List <Core.Domain.Purchase_Requisition.PurchaseRequisitionDetail> PurchaseItem, Core.Domain.Purchase_Requisition.PurchaseRequisition PurchaseHeader) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); string sql; foreach (PurchaseRequisitionDetail obj in PurchaseItem) { sql = " Insert into TD_PurchaseRequisition (PRID,ItemID,Qty) " + " Values (@PRID,@ItemID,@Qty)"; db.Execute(sql, new { obj.PRID, obj.ItemId, obj.Qty }); } return(""); }