Run(CallForSpeech callForSpeech)
{
var temp = _mapper.Map <CallForSpeechTemp>(callForSpeech);
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
var q = SqlQueries.CallForSpeechInsert;
try
{
var result = await connection.QueryAsync <int>(q, temp);
int createdId = result.FirstOrDefault();
CallForSpeechIds ids = new CallForSpeechIds()
{
CreatedId = new CallForSpeechId(createdId),
UniqueId = callForSpeech.UniqueId
};
return(ExecutionStatus <CallForSpeechIds> .DbOk(ids));
}
catch (Exception ex)
{
if (ExecutionFlow.Options.ThrowExceptions)
{
throw;
}
return(ExecutionStatus <CallForSpeechIds> .DbError(ex));
}
}
public async Task <ExecutionStatus> Run(CategoryUniqueId categoryId)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
var q = @"DELETE FROM Categories WHERE UniqueId=@UniqueId;";
try
{
var result = await connection.QueryAsync <int>(q,
new
{
@UniqueId = categoryId.Value.ToString()
});
}
catch (Exception ex)
{
if (ExecutionFlow.Options.ThrowExceptions)
{
throw;
}
return(ExecutionStatus.DbError(ex));
}
return(ExecutionStatus.DbOk());
}
public async Task <ExecutionStatus> Run(CallForSpeechId id, JudgeId judge, CallForSpeechStatus status)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
var q = @"UPDATE CallForSpeakes
SET FinalDecision_DecisionBy = @JudgeId,
FinalDecision_Date = @Date,
Status = @Status
WHERE Id = @Id;";
try
{
var result = await connection.ExecuteAsync(q,
new
{
@JudgeId = judge.Value,
@Date = AppTime.Now().ToLongDateString(),
@Id = id.Value,
@Status = (int)status
});
return(ExecutionStatus.DbOk());
}
catch (Exception ex)
{
if (ExecutionFlow.Options.ThrowExceptions)
{
throw;
}
return(ExecutionStatus.DbError(ex));
}
}
public async Task <ExecutionStatus> Run(Judge entity, ByWhatId byWhatId)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
//Nie ma SQL INjection
var q2 = @"UPDATE Judges
SET Login = @Login, Password = @Password, BirthDate=@BirthDate
,Name_First = @Name_First, Name_Last = @Name_Last
WHERE Id = @Id; ";
var q = @"UPDATE Judges
SET Login = @Login, Password = @Password, BirthDate=@BirthDate
,Name_First = @Name_First, Name_Last = @Name_Last
WHERE UniqueId = @UniqueId; ";
var temp = _mapper.Map <JudgeTemp>(entity);
try
{
if (byWhatId == ByWhatId.CreatedId)
{
q = q2;
}
var result = await connection.ExecuteAsync(q, temp);
return(ExecutionStatus.DbOk());
}
catch (Exception ex)
{
return(ExecutionStatus.DbError(ex.Message));
}
}
public async Task <ExecutionStatus <Category> > Run(CategoryId categoryId)
{
using var connection = new SqliteConnection
(_geekLemonContext.ConnectionString);
var q = @"SELECT Id, DisplayName, Name,
WhatWeAreLookingFor,UniqueId ,Version FROM Categories
Where Id = @Id";
try
{
var r = await connection.
QueryFirstOrDefaultAsync <CategoryTemp>
(q, new
{
@Id = categoryId.Value,
});
var rmaped = _mapper.Map <Category>(r);
return(ExecutionStatus <Category> .DbIfDefaultThenError(rmaped));
}
catch (Exception ex)
{
return(ExecutionStatus <Category> .DbError(ex));
}
}
public async Task <ExecutionStatus <JudgeIds> > Run(Judge entity)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
var q = @"INSERT INTO Judges
(Login, Password, BirthDate, Name_First,
Name_Last,CategoryID,
UniqueId, Version )
VALUES(@Login, @Password, @BirthDate,
@Name_First, @Name_Last, @CategoryId, @UniqueId,@Version);
SELECT seq From sqlite_sequence Where Name='Judges'";
//var v = GetValuesFromJugde(entity);
//var result = await connection.QueryAsync<int>(q,
// new
// {
// @Id = entity.Id,
// @Login = entity.Login,
// @Password = entity.Password,
// @BirthDate = v.Birthdate,
// @Name_First = entity.Name.First,
// @Name_Last = entity.Name.Last,
// @Email_ForeConference = v.EmailForeConference,
// @Email_ForSpeakers = v.EmailForSpeakers,
// @Phone_ForSpekers = v.PhoneForSpekers,
// @Phone_ForConference = v.PhoneForConference,
// @CategoryID = entity.Category.Id.Value
// });
var temp = _mapper.Map <JudgeTemp>(entity);
try
{
var result = await connection.QueryAsync <int>(q, temp);
int createdId = result.FirstOrDefault();
JudgeIds ids = new JudgeIds()
{
CreatedId = new JudgeId(createdId),
UniqueId = entity.UniqueId
};
return(ExecutionStatus
<JudgeIds> .
DbIfDefaultThenError(ids));
}
catch (Exception ex)
{
return(ExecutionStatus <JudgeIds>
.DbError(ex.Message));
}
}
public async Task <ExecutionStatus <CategoryIds> > Run(Category entity)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
//Nie ma SQL INjection
var q = @"INSERT INTO Categories(Name, DisplayName, WhatWeAreLookingFor
,UniqueId, Version)
VALUES (@Name, @DisplayName, @WhatWeAreLookingFor,@UniqueId, @Version);
SELECT seq From sqlite_sequence Where Name='Categories'";
try
{
var result = await connection.QueryAsync <int>(q,
new
{
@Name = entity.Name,
@DisplayName = entity.DisplayName,
@WhatWeAreLookingFor = entity.WhatWeAreLookingFor,
@UniqueId = entity.UniqueId.Value.ToString(),
@Version = entity.Version,
});
//var cmd = connection.CreateCommand();
//cmd.CommandText = "SELECT last_insert_rowid()";
//Int64 i = (Int64)cmd.ExecuteScalar();
int createdId = result.FirstOrDefault();
CategoryIds ids = new CategoryIds()
{
CreatedId = new CategoryId(createdId),
UniqueId = entity.UniqueId
};
return(ExecutionStatus
<CategoryIds>
.DbOk(ids));
}
catch (Exception ex)
{
if (ExecutionFlow.Options.ThrowExceptions)
{
throw;
}
return(ExecutionStatus
<CategoryIds>
.DbError(ex));
}
}
public async Task <ExecutionStatus> Run(Category entity, ByWhatId byWhatId)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
//Nie ma SQL INjection
var q1 = @"UPDATE Categories
SET Name = @Name, DisplayName = @DisplayName, WhatWeAreLookingFor=@WhatWeAreLookingFor
,Version = @Version
WHERE UniqueId = @UniqueId; ";
var q = @"UPDATE Categories
SET Name = @Name, DisplayName = @DisplayName, WhatWeAreLookingFor=@WhatWeAreLookingFor
,Version = @Version
WHERE Id = @Id; ";
try
{
if (byWhatId == ByWhatId.UniqueId)
{
q = q1;
}
var result = await connection.QueryAsync <int>(q,
new
{
@UniqueId = entity.UniqueId.Value.ToString(),
@Id = entity.Id.Value,
@Name = entity.Name,
@Version = entity.Version,
@DisplayName = entity.DisplayName,
@WhatWeAreLookingFor = entity.WhatWeAreLookingFor
});
return(ExecutionStatus.DbOk());
}
catch (Exception ex)
{
if (ExecutionFlow.Options.ThrowExceptions)
{
throw;
}
return(ExecutionStatus.DbError(ex));
}
}
public async Task <ExecutionStatus> Run(JudgeUniqueId id)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
var q = "DELETE FROM Judges WHERE UniqueId=@UniqueId;";
try
{
var result = await connection.QueryAsync <int>(q,
new
{
@UniqueId = id.Value.ToString()
});
return(ExecutionStatus.DbOk());
}
catch (Exception ex)
{
return(ExecutionStatus.DbError(ex.Message));
}
}
public async Task <ExecutionStatus <IReadOnlyList <Category> > > Run()
{
using var connection = new SqliteConnection
(_geekLemonContext.ConnectionString);
try
{
var r = await connection.QueryAsync <CategoryTemp>
(@"SELECT Id, DisplayName, Name ,
WhatWeAreLookingFor,UniqueId ,Version FROM Categories;");
var rmaped = _mapper.Map <IEnumerable <Category> >(r);
return(ExecutionStatus <IReadOnlyList <Category> >
.DbIfDefaultThenError
(rmaped.ToList().AsReadOnly()));
}
catch (Exception ex)
{
return(ExecutionStatus <IReadOnlyList <Category> >
.DbError(ex));
}
}
public async Task <ExecutionStatus> Run(CallForSpeechId id,
CallForSpeechScoringResult score, CallForSpeechStatus status)
{
using var connection = new SqliteConnection(_geekLemonContext.ConnectionString);
var q = @"UPDATE CallForSpeakes
SET Score_Score = @Score,
Score_RejectExplanation = @RejectExplanation,
Score_WarringExplanation = @WarringExplanation,
Status = @Status
WHERE Id = @Id;";
try
{
var result = await connection.ExecuteAsync(q,
new
{
@Score = (int)score.Score,
@WarringExplanation = score.WarringExplanation,
@RejectExplanation = score.RejectExplanation,
@Id = id.Value,
@Status = (int)status
});
return(ExecutionStatus.DbOk());
}
catch (Exception ex)
{
if (ExecutionFlow.Options.ThrowExceptions)
{
throw;
}
return(ExecutionStatus.DbError(ex));
}
}