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); }