public static Question Select(Guid questionId)
{
var sqlInteractor = new SqliteInteractor();
Question baseObject = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.QuerySingle <Question>(@"SELECT [Id], [QuestionText] FROM [Questions] WHERE [Id] = @Id", new { Id = questionId })
);
baseObject.Answer = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Query <AnswerKey>(
@"SELECT [Id], [Text], [Description], [Order], [QuestionId] FROM [AnswerKeys] WHERE [QuestionId] = @QuestionId",
new { QuestionId = questionId }).OrderByDescending(ak => ak.Order)
);
var questionType = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.QuerySingle <Guid>(@"SELECT [QuestionTypeId] FROM [Questions] WHERE [Id] = @Id", new { Id = questionId })
);
baseObject.Type = QuestionType.Select(questionType);
var questionDifficulty = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.QuerySingle <Guid>(@"SELECT [QuestionDifficultyId] FROM [Questions] WHERE [Id] = @Id", new { Id = questionId })
);
baseObject.Difficulty = QuestionDifficulty.Select(questionDifficulty);
baseObject.Category = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.QuerySingle <Category>(
@"SELECT [Categories].[Id], [Categories].[Name] FROM [Questions] LEFT OUTER JOIN [Categories] ON [Categories].[Id] = [Questions].[CategoryId] WHERE [Questions].[Id] = @Id",
new { Id = questionId }
)
);
return(baseObject);
}
public Guid Insert()
{
var sqlInteractor = new SqliteInteractor();
var questionId = this.Id;
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteScalarQuery <Guid>(
@"INSERT INTO Questions (Id, QuestionText, QuestionTypeId, QuestionDifficultyId, CategoryId) VALUES (@Id, @QuestionText, @QuestionTypeId, @QuestionDifficultyId, @CategoryId);",
new
{
Id = questionId,
QuestionText = this.QuestionText,
QuestionTypeId = this.Type.Id,
QuestionDifficultyId = this.Difficulty.Id,
CategoryId = this.Category.Id
}
)
);
foreach (var answerKey in this.Answer)
{
answerKey.QuestionId = questionId;
answerKey.Insert();
}
return(questionId);
}
public bool Delete()
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Delete(this)
));
}
public static Location Select(string locationName)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.GetAll <Location>().FirstOrDefault(l => l.Name.Equals(locationName, StringComparison.InvariantCultureIgnoreCase))
));
}
public static QuestionType Select(Guid typeId)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Get <QuestionType>(typeId)
));
}
public static QuestionType Select(string typeName)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.GetAll <QuestionType>().FirstOrDefault(t => t.Name.Equals(typeName, StringComparison.InvariantCultureIgnoreCase))
));
}
public static Category Select(Guid categoryId)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Get <Category>(categoryId)
));
}
public void Update()
{
var sqlInteractor = new SqliteInteractor();
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Update(this)
);
}
public static IEnumerable <Address> Select()
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Query <Address>(@"SELECT [Id], [Address1], [Address2], [City], [State], [ZipCode], [SpecialDirections] FROM [Addresses]")
));
}
public static IEnumerable <QuestionDifficulty> Select()
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.GetAll <QuestionDifficulty>()
));
}
public static QuestionDifficulty Select(Guid difficultyId)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Get <QuestionDifficulty>(difficultyId)
));
}
public static Team Select(Guid teamId)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Get <Team>(teamId)
));
}
private int GetCountOfTeamsWithName()
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.GetAll <Team>().Count(team => team.Name == this.Name)
));
}
public static Category Select(string categoryName)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.GetAll <Category>()
.FirstOrDefault(category => category.Name.Equals(categoryName, StringComparison.InvariantCultureIgnoreCase))
));
}
public static QuestionDifficulty Select(string difficultyName)
{
var sqlInteractor = new SqliteInteractor();
return
(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.GetAll <QuestionDifficulty>()
.FirstOrDefault(d => d.Name.Equals(difficultyName, StringComparison.InvariantCultureIgnoreCase))
));
}
public void Insert()
{
var sqlInteractor = new SqliteInteractor();
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteNonQuery(
"INSERT INTO [QuestionHistory] (QuestionId, [DateUsed], [_LocationId]) VALUES (@QuestionId, @DateUsed, @LocationId)",
new { QuestionId = this.QuestionId, DateUsed = this.DateUsed, LocationId = this.Location.Id })
);
}
public Address Select(Guid locationId)
{
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.QuerySingle <Address>(
@"SELECT [Id], [Address1], [Address2], [City], [State], [ZipCode], [SpecialDirections], [LocationId] FROM [Addresses] WHERE [LocationId] = @Id",
new { Id = locationId }
)
));
}
public void Update()
{
var sqlInteractor = new SqliteInteractor();
if (GetCountOfTeamsWithName() == 0)
{
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Update(this)
);
}
}
public void Update()
{
var sqlInteractor = new SqliteInteractor();
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteNonQuery(
@"UPDATE [Locations] SET [Name] = @Name, [Phone] = @Phone WHERE [Id] = @LocationId",
new { Name = this.Name, Phone = this.Phone, LocationId = this.Id }
)
);
Address.Update();
}
public bool Delete()
{
foreach (var answerKey in this.Answer)
{
answerKey.Delete();
}
var sqlInteractor = new SqliteInteractor();
return(sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Delete(this)
));
}
public void Insert()
{
var sqlInteractor = new SqliteInteractor();
if (GetCountOfTeamsWithName() == 0)
{
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Insert(new Team()
{
Name = this.Name
})
);
}
}
public Guid Insert()
{
var sqlInteractor = new SqliteInteractor();
var locationId = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteScalarQuery <Guid>(
@"INSERT INTO [Locations] ([Id], [Name], [Phone]) VALUES (@Id, @Name, @Phone)",
new { Id = Id, Name = this.Name, Phone = this.Phone }
)
);
Address.Insert(Id);
return(locationId);
}
public static IEnumerable <Location> Select()
{
var sqlInteractor = new SqliteInteractor();
var baseLocations = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.Query <Location>(@"SELECT [Id], [Name], [Phone] FROM [Locations]")
);
foreach (var baseLocation in baseLocations)
{
baseLocation.Address = baseLocation.Address.Select(baseLocation.Id);
}
return(baseLocations);
}
public static Location Select(int locationId)
{
var sqlInteractor = new SqliteInteractor();
var location = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.QuerySingle <Location>(
@"SELECT [Id], [Name], [Phone] FROM [Locations] WHERE [Id] = @LocationId",
new { LocationId = locationId }
)
);
location.Address = location.Address.Select(location.Id);
return(location);
}
public static Question GetRandomQuestionByDifficulty(Guid difficulty)
{
var sqlInteractor = new SqliteInteractor();
var commandParameters = new { difficultyId = difficulty };
var commandDefinition = new CommandDefinition(
"SELECT Id FROM [Questions] WHERE [QuestionDifficultyId] = @difficultyId ORDER BY RANDOM() LIMIT 1",
commandParameters
);
var questionId = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteScalar <Guid>(commandDefinition)
);
return
(Select(questionId));
}
public void Update()
{
var sqlInteractor = new SqliteInteractor();
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteNonQuery(
@"UPDATE [Addresses] SET [Address1] = @Address1, [Address2] = @Address2, [City] = @City, [State] = @State, [ZipCode] = @ZipCode, [SpecialDirections] = @SpecialDirections
WHERE [Id] = @AddressId" ,
new
{
Address1 = this.Address1,
Address2 = this.Address2,
City = this.City,
State = this.State,
ZipCode = this.ZipCode,
SpecialDirections = this.SpecialDirections,
AddressId = this.Id
}
)
);
}
public void Update()
{
var sqlInteractor = new SqliteInteractor();
sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteNonQuery(
@"UPDATE [Questions] SET [QuestionText] = @QuestionText, [QuestionTypeId] = @QuestionType, [QuestionDifficultyId] = @Difficulty, [CategoryId] = @CategoryId WHERE [QuestionId] = @QuestionId",
new
{
QuestionText = this.QuestionText,
QuestionType = this.Type.Id,
Difficulty = this.Type.Id,
CategoryId = this.Category,
QuestionId = this.Id
}
));
foreach (var answerKey in Answer)
{
answerKey.Update();
}
}
public Guid Insert(Guid locationId)
{
var sqlInteractor = new SqliteInteractor();
var addressId = sqlInteractor.PerformDatabaseOperation((connection) =>
connection.ExecuteScalarQuery <Guid>(
@"INSERT INTO [Addresses] ([Id], [Address1], [Address2], [City], [State], [ZipCode], [SpecialDirections], [LocationId])
VALUES (@Id, @Address1, @Address2, @City, @State, @ZipCode, @SpecialDirections, @LocationId)" ,
new
{
Id = Id,
Address1 = this.Address1,
Address2 = this.Address2,
City = this.City,
State = this.State,
ZipCode = this.ZipCode,
SpecialDirections = this.SpecialDirections,
locationId
}
)
);
return(addressId);
}
