public GetAccessToken_QRM Handle(GetLatestMemberAccessToken_Q query)
{
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select top 1
t.ID
,t.MemberID
,t.AccessToken
,t.ParentAccessToken
,t.ValidFrom
,t.ValidTo
,t.OrganizationID
from [Authentication].[Tokens] t
where t.MemberID = @MemberID
and t.OrganizationID = @OrganizationID
order by
t.ID desc
";
var res = connection.Query <GetAccessToken_QRM>(sql, new
{
MemberID = query.MemberID,
OrganizationID = query.OrganizationID
}).FirstOrDefault();
return(res);
}
}
public Member Handle(GetMemberByAccessToken_Q query)
{
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
m.ID
,m.UserName
,m.RSIHandle
,m.OrganizationID
,m.UserType
,m.IsActive
,m.CreatedOn
,m.CreatedBy
,m.ModifiedOn
,m.ModifiedBy
from [People].[Members] m
join [Authentication].[Tokens] t
on t.MemberID = m.ID
and t.AccessToken = @AccessToken
where m.IsActive = 1
";
var res = connection.Query <Member>(sql, new {
AccessToken = query.AccessToken
}).FirstOrDefault();
return(res);
}
}
public List <Organization> Handle(string accessTokenID, ListOrganizations_Q query)
{
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
o.ID
,o.OrganizationName
,o.OrganizationSpectrumID
,o.OrganizationWebsiteURL
,o.IsActive
,o.CreatedOn
,o.CreatedBy
,o.ModifiedOn
,o.ModifiedBy
from [Organizations].[Organizations] o
";
var res = connection.Query <Organization>(sql).ToList();
return(res);
}
}
public void Get_ShouldCreateConnectionWhenConnectionStringIsValid()
{
SQLConnectionFactory connFactory = new SQLConnectionFactory(@"Data Source=localhost;initial catalog=Master;integrated security=true");
System.Data.IDbConnection connection = connFactory.GetConnection();
Assert.IsNotNull(connection);
}
public Organization Handle(string accessTokenID, GetOrganizationBySpectrumID_Q query)
{
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
o.ID
,o.OrganizationName
,o.OrganizationSpectrumID
,o.OrganizationWebsiteURL
,o.IsActive
,o.CreatedOn
,o.CreatedBy
,o.ModifiedOn
,o.ModifiedBy
from [Organizations].[Organizations] o
where o.OrganizationSpectrumID = @SpectrumID
";
var res = connection.Query <Organization>(sql, new { SpectrumID = query.SpectrumID }).FirstOrDefault();
return(res);
}
}
public GetAccessToken_QRM Handle(GetAccessTokenByValue_Q query)
{
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
t.ID
,t.MemberID
,t.AccessToken
,t.ParentAccessToken
,t.ValidFrom
,t.ValidTo
,t.OrganizationID
from [Authentication].[Tokens] t
where t.AccessToken = @AccessToken
";
var res = connection.Query <GetAccessToken_QRM>(sql, new
{
AccessToken = query.AccessToken
}).FirstOrDefault();
return(res);
}
}
public Result Handle(IUserContext context, ExpireAccessToken_C command)
{
if (string.IsNullOrEmpty(command.AccessToken))
{
throw new ArgumentNullException(nameof(command.AccessToken));
}
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd = @"
update t set
t.ValidTo = SYSUTCDATETIME()
from [Authentication].[Tokens] t
where t.MemberID = @MemberID
and t.AccessToken = @AccessToken
";
using (var transaction = connection.BeginTransaction())
{
try
{
int rowsAffected = connection.Execute(cmd, new {
MemberID = command.MemberID,
AccessToken = command.AccessToken
}, transaction);
if (rowsAffected == 1)
{
transaction.Commit();
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.ItemIDs.Add(command.AccessToken.ToString());
result.Status = HttpStatusCode.OK;
result.StatusDescription = "AccessToken Expired Successfully!";
return(result);
}
}
public AuthenticateMember_QRM Handle(AuthenticateMember_Q query)
{
if (string.IsNullOrEmpty(query.UserName))
{
throw new ArgumentNullException(nameof(query.UserName));
}
if (string.IsNullOrEmpty(query.Password))
{
throw new ArgumentNullException(nameof(query.Password));
}
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
m.ID
,m.UserName
,m.RSIHandle
,m.OrganizationID
,m.IsActive
,m.CreatedOn
,m.CreatedBy
,m.ModifiedOn
,m.ModifiedBy
from [People].[Members] m
join [Authentication].[Credentials] c
on c.MemberID = m.ID
and c.[PasswordHash] = @Password
where m.IsActive = 1
and m.UserName = @UserName
";
var res = connection.Query <AuthenticateMember_QRM>(sql, new
{
UserName = query.UserName,
Password = query.Password
}).FirstOrDefault();
return(res);
}
}
public List <Member> Handle(string accessTokenID, ListMembersForOrganization_Q query)
{
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
m.ID
,m.UserName
,m.RSIHandle
,m.OrganizationID
,m.UserType
,m.IsActive
,m.CreatedOn
,m.CreatedBy
,m.ModifiedOn
,m.ModifiedBy
,pg.ID [PayGradeID]
,pg.PayGradeName
,pg.PayGradeDisplayName
,pg.PayGradeGroup
,r1.ID [RankID]
,r1.RankName
,r1.RankAbbr
,r1.RankType
,r1.RankImage
,r1.RankGroupName
,r1.RankGroupImage
from People.Members m
join People.Ranks r1
on r1.ID = m.RankID
join People.PayGrades pg
on pg.ID = r1.PayGradeID
where m.OrganizationID = @OrganizationID
";
var res = connection.Query <Member>(sql, new { OrganizationID = query.OrganizationID }).ToList();
return(res);
}
}
public List <PayGrade> Handle(string accessTokenID, ListPayGrades_Q query)
{
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
pg.ID
,pg.PayGradeName
,pg.PayGradeDisplayName
,pg.PayGradeOrderBy
,pg.PayGradeGroup
,pg.IsActive
from People.PayGrades pg
order by
pg.PayGradeOrderBy
";
var res = connection.Query <PayGrade>(sql).ToList();
return(res);
}
}
public Result Handle(IUserContext context, InitPayGradesForOrgRole_C command)
{
if (command.SupportedPayGrades == null || command.SupportedPayGrades.Count < 1)
{
throw new ArgumentNullException(nameof(command.SupportedPayGrades));
}
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd0 = @"
DELETE m
from Organizations.PayGradeOrgRoleMap m
where m.OrgRoleID = @OrgRoleID;
";
var cmd = @"
INSERT INTO [Organizations].[PayGradeOrgRoleMap] (
PayGradeID
,OrgRoleID
,IsActive
,CreatedOn
,CreatedBy
,ModifiedOn
,ModifiedBy
)
select
pg.ID
,@OrgRoleID
,1 [IsActive]
,@CreatedOn
,@CreatedBy
,@ModifiedOn
,@ModifiedBy
from [People].[PayGrades] pg
where pg.PayGradeName in @SupportedPayGrades
and pg.IsActive = 1
";
using (var transaction = connection.BeginTransaction())
{
try
{
int rowsAffected0 = connection.Execute(cmd0, new
{
OrgRoleID = command.OrgRoleID
}, transaction);
int rowsAffected = connection.Execute(cmd, new
{
OrgRoleID = command.OrgRoleID,
SupportedPayGrades = command.SupportedPayGrades,
IsActive = true,
CreatedOn = DateTime.UtcNow,
CreatedBy = context.MemberID.ToString(),
ModifiedOn = DateTime.UtcNow,
ModifiedBy = context.MemberID.ToString()
}, transaction);
if (rowsAffected == command.SupportedPayGrades.Count)
{
transaction.Commit();
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "PayGrade OrgRoles Added Successfully!";
return(result);
}
}
public Result Handle(IUserContext context, CreateMemberRankHistory_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd = @"
INSERT INTO [People].[MemberRankHistory] (
MemberID
,PreviousRankID
,NewRankID
,CreatedOn
,CreatedBy
,ModifiedOn
,ModifiedBy
) VALUES (
@MemberID
,@PreviousRankID
,@NewRankID
,@CreatedOn
,@CreatedBy
,@ModifiedOn
,@ModifiedBy
)
";
using (var transaction = connection.BeginTransaction())
{
try
{
int rowsAffected = connection.Execute(cmd, new
{
MemberID = command.MemberID,
PreviousRankID = command.PreviousRankID,
NewRankID = command.NewRankID,
CreatedOn = DateTime.UtcNow,
CreatedBy = context.MemberID.ToString(),
ModifiedOn = DateTime.UtcNow,
ModifiedBy = context.MemberID.ToString()
}, transaction);
if (rowsAffected == 1)
{
transaction.Commit();
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "MemberRankHistory Added Successfully!";
return(result);
}
}
public void Get_ShouldFailWhenConnectionStringIsNotValid()
{
SQLConnectionFactory connFactory = new SQLConnectionFactory(_fixture.Create <string>());
Assert.Throws <ArgumentException>(() => connFactory.GetConnection());
}
public Result Handle(IUserContext context, CreateAccessToken_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var newToken = Guid.NewGuid().ToString();
var cmd = @"
INSERT INTO [Authentication].[Tokens] (
MemberID
,AccessToken
,ParentAccessToken
,ValidFrom
,ValidTo
,OrganizationID
,CreatedOn
,CreatedBy
) VALUES (
@MemberID
,@AccessToken
,@ParentAccessToken
,@ValidFrom
,@ValidTo
,@OrganizationID
,SYSUTCDATETIME()
,@CreatedBy
)
";
using (var transaction = connection.BeginTransaction())
{
try
{
int rowsAffected = connection.Execute(cmd, new
{
MemberID = command.MemberID,
AccessToken = newToken,
ParentAccessToken = command.ParentAccessToken,
ValidFrom = command.ValidFrom,
ValidTo = command.ValidTo,
OrganizationID = command.OrganizationID,
CreatedBy = context.MemberID
}, transaction);
if (rowsAffected == 1)
{
transaction.Commit();
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "AccessToken Created Successfully!";
result.ItemIDs.Add(newToken);
return(result);
}
}
public Result Handle(IUserContext context, UpdateOrgRole_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd = @"
update r set
r.OrganizationID = @OrganizationID
,r.RoleName = @RoleName
,r.RoleShortName = @RoleShortName
,r.RoleDisplayName = @RoleDisplayName
,r.RoleType = @RoleType
,r.RoleOrderBy = @RoleOrderBy
,r.IsActive = @IsActive
,r.IsHidden = @IsHidden
,r.ModifiedOn = @ModifiedOn
,r.ModifiedBy = @ModifiedBy
from Organizations.Roles r
where r.ID = @RoleID
";
using (var transaction = connection.BeginTransaction())
{
try
{
int rowsAffected = connection.Execute(cmd, new
{
RoleID = command.ID,
OrganizationID = command.OrganizationID,
RoleName = command.RoleName,
RoleShortName = command.RoleShortName,
RoleDisplayName = command.RoleDisplayName,
RoleType = command.RoleType,
RoleOrderBy = command.RoleOrderBy,
IsActive = command.IsActive,
IsHidden = command.IsHidden,
ModifiedOn = DateTime.UtcNow,
ModifiedBy = context.MemberID.ToString()
}, transaction);
if (rowsAffected == 1)
{
transaction.Commit();
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "OrgRole Updated Successfully!";
return(result);
}
}
public Result Handle(IUserContext context, CreatePayGrade_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd = @"
INSERT INTO [People].[PayGrades] (
PayGradeName
,PayGradeDisplayName
,PayGradeOrderBy
,PayGradeGroup
,PayGradeDescriptionText
,PayGradeNotes
,IsActive
,CreatedOn
,CreatedBy
,ModifiedOn
,ModifiedBy
) VALUES (
@PayGradeName
,@PayGradeDisplayName
,@PayGradeOrderBy
,@PayGradeGroup
,@PayGradeDescriptionText
,@PayGradeNotes
,@IsActive
,@CreatedOn
,@CreatedBy
,@ModifiedOn
,@ModifiedBy
)
SELECT CAST(SCOPE_IDENTITY() as int)
";
using (var transaction = connection.BeginTransaction())
{
try
{
int?id = connection.Query <int>(cmd, new
{
PayGradeName = command.PayGradeName,
PayGradeDisplayName = command.PayGradeDisplayName,
PayGradeOrderBy = command.PayGradeOrderBy,
PayGradeGroup = command.PayGradeGroup,
PayGradeDescriptionText = command.PayGradeDescriptionText,
PayGradeNotes = command.PayGradeNotes,
IsActive = command.IsActive,
CreatedOn = DateTime.UtcNow,
CreatedBy = context.MemberID.ToString(),
ModifiedOn = DateTime.UtcNow,
ModifiedBy = context.MemberID.ToString()
}, transaction).FirstOrDefault();
if (id != null)
{
transaction.Commit();
result.ItemIDs.Add(id.ToString());
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "PayGrade Created Successfully!";
return(result);
}
}
public List <OrgRole> Handle(string accessTokenID, ListRolesForOrganization_Q query)
{
Dictionary <int, OrgRole> xRoles = new Dictionary <int, OrgRole>();
Dictionary <int, HashSet <PayGrade> > xRolesPayGrades = new Dictionary <int, HashSet <PayGrade> >();
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
r.ID
,r.OrganizationID
,r.RoleName
,r.RoleDisplayName
,r.RoleType
,r.RoleOrderBy
,r.IsActive
,r.IsHidden
,g.ID
,g.PayGradeName
,g.PayGradeDisplayName
,g.PayGradeOrderBy
,g.PayGradeGroup
,g.IsActive
from Organizations.Roles r
join Organizations.PayGradeOrgRoleMap m
on m.OrgRoleID = r.ID
join People.PayGrades g
on g.ID = m.PayGradeID
where r.OrganizationID = @OrganizationID
";
var res = connection.Query <OrgRole, PayGrade, OrgRole>(sql, (role, paygrade) =>
{
if (!xRoles.ContainsKey(role.ID))
{
xRoles[role.ID] = role;
}
if (paygrade != null)
{
if (!xRolesPayGrades.ContainsKey(role.ID))
{
xRolesPayGrades[role.ID] = new HashSet <PayGrade>();
}
xRolesPayGrades[role.ID].Add(new PayGrade
{
ID = paygrade.ID,
PayGradeName = paygrade.PayGradeName,
PayGradeDisplayName = paygrade.PayGradeDisplayName,
PayGradeOrderBy = paygrade.PayGradeOrderBy,
PayGradeGroup = paygrade.PayGradeGroup,
IsActive = paygrade.IsActive
});
}
return(role);
}, new { OrganizationID = query.OrganizationID }).ToList();
foreach (var r in xRoles.Values)
{
HashSet <PayGrade> payGrades = null;
xRolesPayGrades.TryGetValue(r.ID, out payGrades);
if (payGrades != null)
{
r.SupportedPayGrades = payGrades.ToList();
}
}
return(xRoles.Values.ToList());
}
}
public Result Handle(IUserContext context, CreateCredential_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var newToken = Guid.NewGuid().ToString();
var cmd = @"
INSERT INTO [Authentication].[Credentials] (
MemberID
,UserName
,PasswordHash
,OrganizationID
,CreatedOn
,CreatedBy
,ModifiedOn
,ModifiedBy
) VALUES (
@MemberID
,@UserName
,@PasswordHash
,@OrganizationID
,@CreatedOn
,@CreatedBy
,@ModifiedOn
,@ModifiedBy
)
SELECT CAST(SCOPE_IDENTITY() as int)
";
using (var transaction = connection.BeginTransaction())
{
try
{
var id = connection.Query <int>(cmd, new
{
MemberID = command.MemberID,
UserName = command.UserName,
PasswordHash = command.PasswordHash,
OrganizationID = command.OrganizationID,
CreatedOn = DateTime.UtcNow,
CreatedBy = context.MemberID.ToString(),
ModifiedOn = DateTime.UtcNow,
ModifiedBy = context.MemberID.ToString()
}, transaction);
if (id != null)
{
transaction.Commit();
result.ItemIDs.Add(id.ToString());
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "Credential Created Successfully!";
return(result);
}
}
public Result Handle(IUserContext context, CreateOrganization_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd = @"
INSERT INTO [Organizations].[Organizations] (
OrganizationName
,OrganizationSpectrumID
,OrganizationWebsiteURL
,IsActive
,CreatedOn
,CreatedBy
,ModifiedOn
,ModifiedBy
)
VALUES (
@OrganizationName
,@OrganizationSpectrumID
,@OrganizationWebsiteURL
,@IsActive
,@CreatedOn
,@CreatedBy
,@ModifiedOn
,@ModifiedBy
)
SELECT CAST(SCOPE_IDENTITY() as int)
";
using (var transaction = connection.BeginTransaction())
{
try
{
int?id = connection.Query <int>(cmd, new
{
OrganizationName = command.OrganizationName,
OrganizationSpectrumID = command.OrganizationSpectrumID,
OrganizationWebsiteURL = command.OrganizationWebsiteURL,
IsActive = command.IsActive,
CreatedOn = DateTime.UtcNow,
CreatedBy = context.MemberID.ToString(),
ModifiedOn = DateTime.UtcNow,
ModifiedBy = context.MemberID.ToString()
}, transaction).FirstOrDefault();
if (id != null)
{
transaction.Commit();
result.ItemIDs.Add(id.ToString());
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "Organization Created Successfully!";
return(result);
}
}
public List <ListRanks_QRM> Handle(string accessTokenID, ListRanks_Q query)
{
Dictionary <int, ListRanks_QRM> xRanks = new Dictionary <int, ListRanks_QRM>();
Dictionary <int, HashSet <OrgRole> > xRanksOrgRoles = new Dictionary <int, HashSet <OrgRole> >();
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var sql = @"
select
r.ID [RankID]
,r.PayGradeID
,pg.PayGradeName
,pg.PayGradeDisplayName
,pg.PayGradeOrderBy
,pg.PayGradeGroup
,pg.PayGradeDescriptionText
,pg.PayGradeNotes
,r.RankName
,r.RankAbbr
,r.RankType
,r.RankImage
,r.RankGroupName
,r.RankGroupImage
,mr.ID
,mr.RoleName
,mr.RoleShortName
,mr.RoleDisplayName
,mr.RoleType
,mr.RoleOrderBy
,mr.IsActive
,mr.IsHidden
from People.Ranks r
join People.PayGrades pg
on pg.ID = r.PayGradeID
outer apply (
select
r.*
from Organizations.PayGradeOrgRoleMap m
join Organizations.Roles r
on r.ID = m.OrgRoleID
where m.PayGradeID = pg.ID
) mr
order by
pg.PayGradeOrderBy
,r.RankType
,r.RankName
";
var res = connection.Query <ListRanks_QRM, OrgRole, ListRanks_QRM>(sql, (rank, role) =>
{
if (!xRanks.ContainsKey(rank.RankID))
{
xRanks[rank.RankID] = rank;
}
if (role != null)
{
if (!xRanksOrgRoles.ContainsKey(rank.RankID))
{
xRanksOrgRoles[rank.RankID] = new HashSet <OrgRole>();
}
xRanksOrgRoles[rank.RankID].Add(new OrgRole
{
ID = role.ID,
RoleName = role.RoleName,
RoleShortName = role.RoleShortName,
RoleDisplayName = role.RoleDisplayName,
RoleType = role.RoleType,
IsActive = role.IsActive,
IsHidden = role.IsHidden
});
}
return(rank);
}).ToList();
foreach (var r in xRanks.Values)
{
HashSet <OrgRole> orgRoles = null;
xRanksOrgRoles.TryGetValue(r.RankID, out orgRoles);
if (orgRoles != null)
{
r.SupportedOrgRoles = orgRoles.ToList();
}
}
return(xRanks.Values.ToList());
}
}
public Result Handle(IUserContext context, UpdateMember_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
result.ItemIDs.Add(command.ID.ToString());
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd = @"
update m set
m.UserName = @UserName
,m.RSIHandle = @RSIHandle
,m.UserType = @UserType
,m.RankID = @RankID
,m.IsActive = @IsActive
,m.ModifiedOn = @ModifiedOn
,m.ModifiedBy = @ModifiedBy
from People.Members m
where m.ID = @MemberID
";
using (var transaction = connection.BeginTransaction())
{
try
{
int rowsAffected = connection.Execute(cmd, new
{
MemberID = command.ID,
UserName = command.UserName,
RSIHandle = command.RSIHandle,
UserType = command.UserType,
RankID = command.RankID,
IsActive = command.IsActive,
CreatedOn = DateTime.UtcNow,
CreatedBy = context.MemberID.ToString(),
ModifiedOn = DateTime.UtcNow,
ModifiedBy = context.MemberID.ToString()
}, transaction);
if (rowsAffected == 1)
{
transaction.Commit();
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "Member Updated Successfully!";
return(result);
}
}
public Result Handle(IUserContext context, DeleteMember_C command)
{
Result result = new Result()
{
Status = HttpStatusCode.BadRequest
};
result.ItemIDs.Add(command.ID.ToString());
using (var connection = _connFactory.GetConnection())
{
connection.Open();
var cmd = @"
--Delete Credential
delete c
from [Authentication].[Credentials] c
where c.MemberID = @MemberID
--Delete Tokens
delete t
from [Authentication].[Tokens] t
where t.MemberID = @MemberID
--Delete MemberRankHistory
delete h
from [People].[MemberRankHistory] h
where h.MemberID = @MemberID
--Delete Member
delete m
from [People].[Members] m
where m.ID = @MemberID
";
using (var transaction = connection.BeginTransaction())
{
try
{
int rowsAffected = connection.Execute(cmd, new
{
MemberID = command.ID
}, transaction);
if (rowsAffected > 0)
{
transaction.Commit();
}
else
{
transaction.Rollback();
result.Status = HttpStatusCode.InternalServerError;
result.StatusDescription = "Updated row count does not match submitted row count. Transaction rolled back.";
return(result);
}
}
catch
{
transaction.Rollback();
throw;
}
}
result.Status = HttpStatusCode.OK;
result.StatusDescription = "Member Deleted Successfully!";
return(result);
}
}