public static List <Card> GetAll(int user)
{
List <Card> list = new List <Card>();
// query
string query = @"select carUniNumber, carNumber, carExpDate, carHolder, carCCV
from ANUser
Join User_Card on useNumber = ucUser
Join Card on ucCard = carUniNumber
where ucUser = @IDUSER";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@IDUSER", user); //assign value of id to the parameter
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
// read table rows
foreach (DataRow row in table.Rows)
{
//read each field
int id = (int)row["carUniNumber"];
string cardNumber = (string)row["carNumber"];
string expirationDate = (string)row["carExpDate"];
string cardHolder = (string)row["carHolder"];
string ccv = (string)row["carCCV"];
//add player to list
list.Add(new Card(id, cardNumber, expirationDate, cardHolder, ccv));
}
return(list); //return list
}
public Team(string name)
{
//query
string query = "select teamCode, teamName, teamJJ, teamJG, teamJE, teamJP, teamGF, teamGC, teamPoints from Teams where teamName = @NAME";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@NAME", name); //assign value of user number to the parameter
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
//read now
if (table.Rows.Count > 0)
{
//read first andd only row found
DataRow row = table.Rows[0];
//read each field andd assign the values to the attributes
_id = (string)row["teamCode"];
_name = (string)row["teamName"];
_gamesPlayed = (int)row["teamJJ"];
_wins = (int)row["teamJG"];
_draws = (int)row["teamJE"];
_losses = (int)row["teamJP"];
_goalsScored = (int)row["teamGF"];
_goalsAgainst = (int)row["teamGC"];
_points = (int)row["teamPoints"];
}
else
{
throw new RecordNotFoundException(name);
}
}
public static List <Match> GetMatchesOfTheWeek(int week)
{
List <Match> list = new List <Match>();
// query
string query = @"select matNumber, teamName, mtTeamBets, matDate
from Teams
Join Match_Team on mtTeam = teamCode
Join Match on mtMatch = matNumber
where matWeek = @WEEK";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@WEEK", week);
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
// read table rows
foreach (DataRow row in table.Rows)
{
//read each field
int matchId = (int)row["matNumber"];
string teamName = (string)row["teamName"];
int teamBets = (int)row["mtTeamBets"];
DateTime matchDate = (DateTime)row["matDate"];
list.Add(new Match(matchId, teamName, teamBets, matchDate, week));
}
return(list); //return list
}
public static List <Team> GetAll()
{
List <Team> list = new List <Team>();
// query
string query = @"select teamCode, teamName, teamJJ, teamJG, teamJE, teamJP, teamGF,
teamGC, teamPoints from Teams
order by teamPoints desc, teamJG desc, teamJE desc, teamGF desc;" ;
SqlCommand command = new SqlCommand(query); //command
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
// read table rows
foreach (DataRow row in table.Rows)
{
//read each field
string id = (string)row["teamCode"];
string teamName = (string)row["teamName"];
int teamJJ = (int)row["teamJJ"];
int teamJG = (int)row["teamJG"];
int teamJE = (int)row["teamJE"];
int teamJP = (int)row["teamJP"];
int teamGF = (int)row["teamGF"];
int teamGC = (int)row["teamGC"];
int teamPoints = (int)row["teamPoints"];
//add player to list
list.Add(new Team(id, teamName, teamJJ, teamJG, teamJE, teamJP, teamGF, teamGC, teamPoints));
}
return(list); //return list
}
public Match(int id)
{
//query
string query = "select matNumber, matDate, matWeek, matTotalBets from Match where matNumber = @ID";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@ID", id); //assign value of user number to the parameter
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
//read now
if (table.Rows.Count > 0)
{
//read first andd only row found
DataRow row = table.Rows[0];
//read each field andd assign the values to the attributes
_id = (int)row["matNumber"];
_date = (DateTime)row["matDate"];
_week = (int)row["matWeek"];
_teamBets = (int)row["matTotalBets"];
}
else
{
throw new RecordNotFoundException(id);
}
}
public User(int number)
{
//query
string query = @"select useNumber, useUser, useFirName, useFirSurname, useSecSurname, useEmail,
useTel, useCredits, usePassword, convert(datetime, useBirthDate, 101) as useBirthDate from ANUser where useNumber=@USERNUMBER";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@USERNUMBER", number); //assign value of user number to the parameter
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
//read now
if (table.Rows.Count > 0)
{
//read first andd only row found
DataRow row = table.Rows[0];
//read each field andd assign the values to the attributes
_useNumber = (int)row["useNumber"];
_useUser = (string)row["useUser"];
_useFirName = (string)row["useFirName"];
_useFirSurname = (string)row["useFirSurname"];
_useSecSurname = (string)row["useSecSurname"];
_useEmail = (string)row["useEmail"];
_useTel = (string)row["useTel"];
_useCredits = (int)row["useCredits"];
_usePassword = (string)row["usePassword"];
_useBirthDate = (DateTime)row["useBirthDate"];
}
else
{
throw new RecordNotFoundException(number);
}
}
public List <Flight> GetDepartures()
{
List <Flight> list = new List <Flight>();
//query
string query = @"select f.id, f.airlineId, a.name as airlineName, date, f.departureCityId, dc.name as departureCity, f.departureTime,
f.arrivalCityId, ac.name as arrivalCity, f.arrivalTime, f.status
from flights as f
join airlines as a on f.airlineId = a.id
join cities as dc on f.departureCityId = dc.id
join cities as ac on f.arrivalCityId = ac.id
where f.departureCityId = @ID
order by f.departureTime";
//command
SqlCommand command = new SqlCommand(query);
//parameters
command.Parameters.AddWithValue("@ID", _id);
//execute query
DataTable table = SqlServerConnection.ExecuteQuery(command);
//check if data is found
foreach (DataRow row in table.Rows)
{
//read columns
int id = (int)row["id"];
string airlineId = (string)row["airlineId"];
string airlineName = (string)row["airlineName"];
DateTime date = (DateTime)row["date"];
string departureCityId = (string)row["departureCityId"];
string departureCityName = (string)row["departureCity"];
DateTime departureTime = Convert.ToDateTime(row["departureTime"].ToString());
string arrivalCityId = (string)row["arrivalCityId"];
string arrivalCityName = (string)row["arrivalCity"];
DateTime arrivalTime = Convert.ToDateTime(row["arrivalTime"].ToString());
FlightStatus status = (FlightStatus)row["status"];
//Console.WriteLine("status: " + status);
Airline airline = new Airline(airlineId, airlineName);
//Console.WriteLine(airlineName);
City departureCity = new City(departureCityId, departureCityName);
City arrivalCity = new City(arrivalCityId, arrivalCityName);
Flight f = new Flight();
f.Airline = airline;
f.ArrivalCity = arrivalCity;
f.ArrivalTime = arrivalTime;
f.Date = date;
f.DepartureCity = departureCity;
f.DepartureTime = departureTime;
f.Id = id;
f.Status = status;
list.Add(f);
}
return(list);
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
txtMa.Enabled = false;
connection.Open();
cellphones = connection.ExecuteQuery <CellPhone>("Select * From CellPhone");
makers = connection.Select <Maker>().Rows().Execute();
colors = connection.Select <Color>().Rows().Execute();
BindingGrigViewCellPhone(cellphones);
AddDataMakerToComboBox(makers);
connection.Close();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
public User(string user, string password)
{
string sha1 = Encrypt(password);
string query = "select useNumber, useUser, usePassword from ANUser where useUser=@USER and usePassword=@PASSWORD";
SqlCommand command = new SqlCommand(query);
command.Parameters.AddWithValue("@USER", user);
command.Parameters.AddWithValue("@PASSWORD", sha1);
DataTable table = SqlServerConnection.ExecuteQuery(command);
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
_useNumber = (int)row["useNumber"];
_useUser = (string)row["useUser"];
_usePassword = (string)row["usePassword"];
}
}
public static bool AddCard(string number, string name, string expdate, string ccv, int user)
{
string query = "select MAX(carUniNumber) as LastCard from Card";
SqlCommand command = new SqlCommand(query); //command
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
int lastid = 0;
foreach (DataRow row in table.Rows)
{
lastid = (int)row["LastCard"];
}
lastid++;
if (lastid != 0)
{
string query2 = @"BEGIN TRANSACTION
INSERT INTO Card values(@carNumber, @carExpDate, @carHolder, @carCCV)
INSERT INTO User_Card VALUES(@ucUser, @ucCard)
COMMIT";
SqlCommand command2 = new SqlCommand(query2); //command
command2.Parameters.AddWithValue("@carNumber", number);
command2.Parameters.AddWithValue("@carExpDate", expdate);
command2.Parameters.AddWithValue("@carHolder", name);
command2.Parameters.AddWithValue("@carCCV", ccv);
command2.Parameters.AddWithValue("@ucUser", user);
command2.Parameters.AddWithValue("@ucCard", lastid);
if (SqlServerConnection.ExecuteNonQuery(command2) > 0)
{
return(true); //command executed successfully
}
else
{
return(false); //could not execute command
}
}
else
{
return(false);
}
}
public City(string id)
{
//query
string query = @"select id, name from cities where id = @ID";
//command
SqlCommand command = new SqlCommand(query);
//parameters
command.Parameters.AddWithValue("@ID", id);
//execute query
DataTable table = SqlServerConnection.ExecuteQuery(command);
//check if data is found
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
//read columns
_id = id;
_name = (string)row["name"];
}
}
public void GameDatabaseCommandsTest()
{
var connection = new SqlServerConnection();
Assert.AreEqual(connection.IsConnected, false);
var result = connection.Connect(ConnectionString, 0);
Assert.AreEqual(result, true);
// Select All
var game = new Game();
var selectQuery = game.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
var errorMessage = "";
var selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
var selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
if (selectResultList.Count > 0)
{
Assert.IsTrue(selectResultList.Count > 0);
}
// Insert
game = new Game(
0,
"Name",
"Description");
var insertCommand = game.GenerateInsertStatement();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
errorMessage = "";
var insertResult = connection.ExecuteCommand(insertCommand, ref errorMessage, out var newId);
Assert.IsTrue(insertResult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
game.Id = newId;
// Exists
var existsQuery = game.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
var existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
var existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
var recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// Select
selectQuery = game.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
Game foundGame = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGame = Game.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGame);
Assert.AreNotSame(game, foundGame);
Assert.AreEqual(game.Id, foundGame.Id);
Assert.AreEqual(game.Name, foundGame.Name);
Assert.AreEqual(game.Description, foundGame.Description);
// Update
var updateGame = new Game(
newId,
"Name Edited",
"Description Edited");
var updateCommand = updateGame.GenerateUpdateStatement();
Assert.IsFalse(string.IsNullOrEmpty(updateCommand));
errorMessage = "";
var updateResult = connection.ExecuteCommand(updateCommand, ref errorMessage);
Assert.AreEqual(updateResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// Exists
existsQuery = updateGame.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// Select
selectQuery = updateGame.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundGame = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGame = Game.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGame);
Assert.AreNotSame(updateGame, foundGame);
Assert.AreEqual(updateGame.Id, foundGame.Id);
Assert.AreEqual(updateGame.Name, foundGame.Name);
Assert.AreEqual(updateGame.Description, foundGame.Description);
// Delete
var deleteCommand = game.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
var deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// Exists
existsQuery = game.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.IsFalse(recordExists);
// Select
selectQuery = game.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundGame = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGame = Game.FromDictionary(dictionary);
break;
}
}
Assert.IsNull(foundGame);
}
public void RatingDatabaseCommandTest()
{
var connection = new SqlServerConnection();
Assert.AreEqual(connection.IsConnected, false);
var result = connection.Connect(ConnectionString, 0);
Assert.AreEqual(result, true);
//select all
var rating = new Rating();
var selectQuery = rating.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
var errorMessage = "";
var selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
var selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
if (selectResultList.Count > 0)
{
Assert.IsTrue(selectResultList.Count > 0);
}
// insert
rating = new Rating(0, "Name", "Description", "Symbol");
var insertcommand = rating.GenerateInsertStatment();
Assert.IsFalse(string.IsNullOrEmpty(insertcommand));
errorMessage = "";
var insertresult = connection.ExecuteCommand(insertcommand, ref errorMessage, out var newId);
Assert.IsTrue(insertresult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
rating.Id = newId;
// exist
var existsQuery = rating.GenerateExistQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
var existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
var existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
var recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// select
selectQuery = rating.GenerateSelectQuery();
Assert.AreEqual(string.IsNullOrEmpty(selectQuery), true);
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(!string.IsNullOrEmpty(errorMessage), false);
Assert.IsNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
Rating foundrating = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundrating = Rating.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundrating);
Assert.AreNotSame(rating, foundrating);
Assert.AreEqual(rating.Id, foundrating.Id);
Assert.AreEqual(rating.Name, foundrating.Name);
Assert.AreEqual(rating.Description, foundrating.Description);
Assert.AreEqual(rating.Symbol, foundrating.Symbol);
// update
var updaterating = new Rating(1, "Name", "Description", "Symbol");
var updatecommand = updaterating.GenerateUpdateStatement();
Assert.IsFalse(string.IsNullOrEmpty(updatecommand));
errorMessage = "";
var updateResult = connection.ExecuteCommand(updatecommand, ref errorMessage);
Assert.AreEqual(updateResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// exsits
existsQuery = updaterating.GenerateExistQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// select
selectQuery = updaterating.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.IsFalse(string.IsNullOrEmpty(errorMessage));
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundrating = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundrating = Rating.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundrating);
Assert.AreNotSame(rating, foundrating);
Assert.AreEqual(rating.Id, foundrating.Id);
Assert.AreEqual(rating.Name, foundrating.Name);
Assert.AreEqual(rating.Description, foundrating.Description);
Assert.AreEqual(rating.Symbol, foundrating.Symbol);
// delete
var deleteCommand = rating.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
var deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// exists
existsQuery = rating.GenerateExistQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.IsFalse(recordExists);
// select
selectQuery = rating.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundrating = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundrating = Rating.FromDictionary(dictionary);
break;
}
}
Assert.IsNull(foundrating);
}
public void GameImageDatabaseCommandsTest()
{
var connection = new SqlServerConnection();
Assert.AreEqual(connection.IsConnected, false);
var result = connection.Connect(ConnectionString, 0);
Assert.AreEqual(result, true);
// Add a Games
var game = new Game(
0,
"Name",
"Description");
var insertCommand = game.GenerateInsertStatement();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
var errorMessage = "";
var insertResult = connection.ExecuteCommand(insertCommand, ref errorMessage, out var newId);
Assert.IsTrue(insertResult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
game.Id = newId;
var updateGame = new Game(
0,
"Name1",
"Description1");
insertCommand = updateGame.GenerateInsertStatement();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
errorMessage = "";
insertResult = connection.ExecuteCommand(insertCommand, ref errorMessage, out newId);
Assert.IsTrue(insertResult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
updateGame.Id = newId;
// Select All
var gameImage = new GameImage();
var selectQuery = gameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
var selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
var selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
if (selectResultList.Count > 0)
{
Assert.IsTrue(selectResultList.Count > 0);
}
// Insert
gameImage = new GameImage(0, game.Id, _imageData);
insertCommand = gameImage.GenerateInsertStatment();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
errorMessage = "";
insertResult = connection.ExecuteCommand(insertCommand, ref errorMessage, out newId);
Assert.IsTrue(insertResult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
gameImage.Id = newId;
// Add image BLOB
var blobUpdateCommand = connection.CreateBlobUpdateStatement(GameImage.TableName, "Image", gameImage.GeneratePrimaryKeyWhereClause());
var blobResult = connection.WriteBlobData(blobUpdateCommand, "Image", "varbinary", null, _imageData);
Assert.IsTrue(blobResult);
// Exists
var existsQuery = gameImage.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
var existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
var existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
var recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// Select
selectQuery = gameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
GameImage foundGameImage = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGameImage = GameImage.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGameImage);
// Read BLOB Data
foundGameImage.Image = connection.ReadBlobData($"SELECT Image FROM {GameImage.TableName} WHERE Id = {foundGameImage.Id}", 0);
Assert.AreNotSame(gameImage, foundGameImage);
Assert.AreEqual(gameImage.Id, foundGameImage.Id);
Assert.AreEqual(gameImage.GameId, foundGameImage.GameId);
if (gameImage.Image != null)
{
Assert.IsTrue(gameImage.Image.SequenceEqual(foundGameImage.Image));
}
else
{
Assert.IsNull(gameImage.Image);
Assert.IsNull(foundGameImage.Image);
}
// Update
var updateGameImage = new GameImage(
newId,
updateGame.Id,
_updateImageData);
var updateCommand = updateGameImage.GenerateUpdateStatement();
Assert.IsFalse(string.IsNullOrEmpty(updateCommand));
errorMessage = "";
var updateResult = connection.ExecuteCommand(updateCommand, ref errorMessage);
Assert.AreEqual(updateResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// Update image BLOB
blobUpdateCommand = connection.CreateBlobUpdateStatement(GameImage.TableName, "Image", updateGameImage.GeneratePrimaryKeyWhereClause());
blobResult = connection.WriteBlobData(blobUpdateCommand, "Image", "varbinary", null, _updateImageData);
Assert.IsTrue(blobResult);
// Exists
existsQuery = updateGameImage.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// Select
selectQuery = updateGameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundGameImage = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGameImage = GameImage.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGameImage);
Assert.AreNotSame(updateGameImage, foundGameImage);
// Read BLOB Data
foundGameImage.Image = connection.ReadBlobData($"SELECT Image FROM {GameImage.TableName} WHERE Id = {foundGameImage.Id}", 0);
Assert.AreEqual(updateGameImage.Id, foundGameImage.Id);
Assert.AreEqual(updateGameImage.GameId, foundGameImage.GameId);
if (updateGameImage.Image != null)
{
Assert.IsTrue(updateGameImage.Image.SequenceEqual(foundGameImage.Image));
}
else
{
Assert.IsNull(updateGameImage.Image);
Assert.IsNull(foundGameImage.Image);
}
// Delete
var deleteCommand = gameImage.GenerateDeleteStatement();
var deleteGameImage = gameImage.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
var deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// Exists
existsQuery = gameImage.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.IsFalse(recordExists);
// Select
selectQuery = gameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundGameImage = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGameImage = GameImage.FromDictionary(dictionary);
break;
}
}
Assert.IsNull(foundGameImage);
// Delete the games
deleteCommand = game.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
deleteCommand = updateGame.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
}