public void RemoveFilter(Guid itemId, int userId, SyncReasons reason, int tenantId) { using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName))) { connection.Open(); connection.Execute(@"merge into ServiceTickets_syncfilter as t using (values(@Itemid, @Reason, @UserId, @TenantId)) as s(ItemId,AppliedRules,UserId,TenantId) on t.itemId = s.ItemId and t.UserId = s.UserId and t.tenantId = s.TenantId when matched then update set t.appliedrules &= ~s.appliedrules, --(case when t.appliedrules & s.appliedrules = s.appliedrules then t.appliedrules^s.appliedrules else t.appliedrules end), -- whenever the appliedrules value changes from >0 to 0 or vice versa, update the date t.modified = (case when (t.appliedrules > 0 and t.appliedrules & ~s.appliedrules = 0) then @@DBTS+1 else t.modified end), -- whenever the appliedrules value changes to 0, setup the deleted datetime (so we can delete all ""deleted"" stuff after e.g. 3 months) t.deletiontime = (case when (t.appliedrules > 0 and t.appliedrules & ~s.appliedrules = 0) then getutcdate() else '1753-01-01' end) when not matched then insert (ItemId,Modified,AppliedRules,UserId,Tenantid,DeletionTime) values (s.ItemId,@@DBTS+1,s.AppliedRules,s.UserId,s.TenantId, getUtcDate());" , new { ItemId = itemId, Reason = (int)reason, UserId = userId, TenantId = tenantId }); connection.Close(); } }
public void Delete(ServiceTicket ticket) { using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName))) { connection.Open(); connection.Execute("delete from ServiceTickets where ID = @ID", ticket); connection.Close(); } }
public void Update(ServiceTicket ticket) { var props = typeof(ServiceTicket).GetProperties() .Where(p => !string.Equals(p.Name, "id", StringComparison.InvariantCultureIgnoreCase)).ToList(); var setter = string.Join(", ", props.Select(p => $"{p.Name}=@{p.Name}")); var query = $"Update ServiceTickets set {setter} where ID = @ID"; using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName))) { connection.Open(); connection.Execute(query, ticket); connection.Close(); } }
public IEnumerable <ServiceTicket> GetServiceTickets() { IEnumerable <ServiceTicket> principals; using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName))) { connection.Open(); principals = connection.Query <ServiceTicket>("select * from ServiceTickets").ToList(); connection.Close(); } return(principals); }
public IEnumerable <PrincipalEdge> GetPrincpialEdges() { IEnumerable <PrincipalEdge> principals; using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName))) { connection.Open(); principals = connection.Query <PrincipalEdge>("select * from PrincipalEdge").ToList(); connection.Close(); } return(principals); }
public void RemoveGroupMember(int groupId, int userId, int tenantId) { using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName))) { connection.Open(); var cmd = new SqlCommand("[dbo].[RemoveEdgeWithSpaceSavingsPrincipal] @StartVertexId, @EndVertexId, @PrincipalType, @TenantId", connection); cmd.Parameters.Add("@StartVertexId", SqlDbType.BigInt).Value = userId; cmd.Parameters.Add("@EndVertexId", SqlDbType.BigInt).Value = groupId; cmd.Parameters.Add("@PrincipalType", SqlDbType.TinyInt).Value = 1; cmd.Parameters.Add("@TenantId", SqlDbType.Int).Value = tenantId; cmd.ExecuteNonQuery(); connection.Close(); } }