//Erstellt den SQL Befehl für das Erstellen einer neuen Ausleihe und setzt ihn in DbCommand command ein protected void SqlNewVideoLoan(Loan video, DbCommand dbCommand) { if (video.ID != 0) { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = $"UPDATE VideoTable " + $"SET " + $"Borrower = ?, ReturnDate = ? " + $"WHERE ID = ?;"; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "Borrower", video.Borrower); AData.AddParameter(dbCommand, "ReturnDate", video.ReturnDate); AData.AddParameter(dbCommand, "ID", video.ID); } else { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = $"UPDATE VideoTable " + $"SET " + $"Borrower = ?, ReturnDate = ? " + $"WHERE Title = ?" + $"And Borrower =?"; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "Borrower", video.Borrower); AData.AddParameter(dbCommand, "ReturnDate", video.ReturnDate); AData.AddParameter(dbCommand, "Title", video.Title); AData.AddParameter(dbCommand, "NewBorrower", ""); } }
//Erstellt den SQL Befehl für das Anpassen eines Eintrages und setzt ihn in DbCommand command ein protected void SqlUpdateVideo(Loan video, DbCommand dbCommand) { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = $"UPDATE VideoTable " + $"SET " + $"Borrower = ?, ReturnDate = ? " + $"WHERE ID = ?;"; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "Borrower", video.Borrower); AData.AddParameter(dbCommand, "ReturnDate", video.ReturnDate); AData.AddParameter(dbCommand, "ID", video.ID); }
//Erstellt den SQL Befehl für das Hinzufügen eines neus Eintrages und setzt ihn in DbCommand command ein protected virtual void SqlInsertVideo(Loan video, DbCommand dbCommand) { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = $"INSERT INTO VideoTable" + $" ( Title, Genre, BorrowingRate, ReleaseYear, RunningTime, Rated,Borrower,ReturnDate )" + $" VALUES " + $" ( @title, @genre, @borrowingRate, @releaseYear, @runningTime, @rated, @borrower, @returnDate);"; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@title", video.Title); AData.AddParameter(dbCommand, "@borrower", video.Borrower); AData.AddParameter(dbCommand, "@returnDate", video.ReturnDate); }
//Erstellt den SQL Befehl für das zurückgeben einer Ausleihe und setzt ihn in DbCommand command ein protected void SqlReturnVideoLoan(Loan video, DbCommand dbCommand) { if (video.ID != 0) { video.Borrower = ""; video.ReturnDate = DateTime.MinValue; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = $"UPDATE VideoTable " + $"SET " + $"Borrower = ?, ReturnDate = ? " + $"WHERE ID = ?;"; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "Borrower", video.Borrower); AData.AddParameter(dbCommand, "ReturnDate", video.ReturnDate); AData.AddParameter(dbCommand, "ID", video.ID); } else { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = @"UPDATE VideoTable "; dbCommand.CommandText += "SET "; dbCommand.CommandText += "Borrower = @NewBorrower, "; AData.AddParameter(dbCommand, "@NewBorrower", ""); dbCommand.CommandText += "ReturnDate = @newReturnDate "; AData.AddParameter(dbCommand, "@newReturnDate", DateTime.MinValue); if (video.Title != "" && video.Title != null) { dbCommand.CommandText += $"WHERE Title = @Title"; AData.AddParameter(dbCommand, "@Title", video.Title); dbCommand.CommandText += $" And Borrower = @OldBorrower"; AData.AddParameter(dbCommand, "@OldBorrower", video.Borrower); } else { dbCommand.CommandText += $"WHERE Borrower = @OldBorrower"; AData.AddParameter(dbCommand, "@OldBorrower", video.Borrower); } if (video.ReturnDate != DateTime.MinValue) { dbCommand.CommandText += $" AND ReturnDate >= @DateNow"; AData.AddParameter(dbCommand, "@DateNow", DateTime.MinValue.ToString("G")); dbCommand.CommandText += $" AND ReturnDate <= @TillReturnDate;"; AData.AddParameter(dbCommand, "@TillReturnDate", video.ReturnDate.ToString("G")); } } }
//Erstellt den SQL Befehl für das Ausgeben der Laufzeiten und setzt ihn in DbCommand command ein protected virtual void SqlGetRunningTime(int runningTime, DbCommand dbCommand) { if (runningTime != 0) { dbCommand.CommandText = @"SELECT DISTINCT RunningTime FROM VideoTable WHERE RunningTime = @RunningTime ORDER BY RunningTime"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@RunningTime", runningTime); } else { dbCommand.CommandText = @"SELECT DISTINCT RunningTime FROM VideoTable ORDER BY RunningTime"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der Title und setzt ihn in DbCommand command ein protected virtual void SqlGetTitle(string title, DbCommand dbCommand) { if (title != "") { dbCommand.CommandText = @"SELECT DISTINCT Title FROM VideoTable WHERE Title = @Title ORDER BY Title"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@Title", title); } else { dbCommand.CommandText = @"SELECT DISTINCT Title FROM VideoTable ORDER BY Title"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der Erscheinungsjahre und setzt ihn in DbCommand command ein protected virtual void SqlGetReleaseYear(int releaseYear, DbCommand dbCommand) { if (releaseYear != 0) { dbCommand.CommandText = @"SELECT DISTINCT ReleaseYear FROM VideoTable WHERE ReleaseYear = @ReleaseYear ORDER BY ReleaseYear"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@ReleaseYear", releaseYear); } else { dbCommand.CommandText = @"SELECT DISTINCT ReleaseYear FROM VideoTable ORDER BY ReleaseYear"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der Rückgabedaten und setzt ihn in DbCommand command ein protected virtual void SqlGetReturnDate(DateTime returnDate, DbCommand dbCommand) { if (returnDate != DateTime.MinValue) { dbCommand.CommandText = @"SELECT DISTINCT ReturnDate FROM VideoTable WHERE ReturnDate = @ReturnDate ORDER BY ReturnDate"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@ReturnDate", returnDate.ToString("G")); } else { dbCommand.CommandText = @"SELECT DISTINCT ReturnDate FROM VideoTable ORDER BY ReturnDate"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der Altersfreigaben und setzt ihn in DbCommand command ein protected virtual void SqlGetRated(int rated, DbCommand dbCommand) { if (rated != 0) { dbCommand.CommandText = @"SELECT DISTINCT Rated FROM VideoTable WHERE Rated = @Rated ORDER BY Rated"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@Rated", rated); } else { dbCommand.CommandText = @"SELECT DISTINCT Rated FROM VideoTable ORDER BY Rated"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der IDs und setzt ihn in DbCommand command ein protected virtual void SqlGetID(int ID, DbCommand dbCommand) { if (ID != 0) { dbCommand.CommandText = @"SELECT DISTINCT ID FROM VideoTable WHERE ID = @ID ORDER BY ID"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@ID", ID); } else { dbCommand.CommandText = @"SELECT DISTINCT ID FROM VideoTable ORDER BY ID"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der Preise und setzt ihn in DbCommand command ein protected virtual void SqlGetBorrowingRate(double borrowingRate, DbCommand dbCommand) { if (borrowingRate != 0.0) { dbCommand.CommandText = @"SELECT DISTINCT BorrowingRate FROM VideoTable WHERE BorrowingRate = @BorrowingRate ORDER BY BorrowingRate"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@BorrowingRate", borrowingRate); } else { dbCommand.CommandText = @"SELECT DISTINCT BorrowingRate FROM VideoTable ORDER BY BorrowingRate"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der Ausleiher und setzt ihn in DbCommand command ein protected virtual void SqlGetBorrower(string borrower, DbCommand dbCommand) { if (borrower != "") { dbCommand.CommandText = @"SELECT DISTINCT Borrower FROM VideoTable WHERE Borrower = @Borrower ORDER BY Borrower"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@Borrower", borrower); } else { dbCommand.CommandText = @"SELECT DISTINCT Borrower FROM VideoTable ORDER BY Borrower"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das Ausgeben der Genres und setzt ihn in DbCommand command ein protected virtual void SqlGetGenre(string genre, DbCommand dbCommand) { if (genre != "") { dbCommand.CommandText = @"SELECT DISTINCT Genre FROM VideoTable Genre Title = @Genre ORDER BY Genre"; dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "@Genre", genre); } else { dbCommand.CommandText = @"SELECT DISTINCT Genre FROM VideoTable ORDER BY Genre"; dbCommand.CommandType = CommandType.Text; } }
//Erstellt den SQL Befehl für das anpassen eines Eintrages und setzt ihn in DbCommand command ein protected void SqlUptadeVideoLoan(Loan video, DbCommand dbCommand) { if (video.ID != 0) { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = $"UPDATE VideoTable " + $"SET " + $"ReturnDate = ? " + $"WHERE ID = ?;"; dbCommand.Parameters.Clear(); AData.AddParameter(dbCommand, "ReturnDate", video.ReturnDate); AData.AddParameter(dbCommand, "ID", video.ID); } else { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = @"UPDATE VideoTable "; dbCommand.CommandText += "SET "; dbCommand.CommandText += "ReturnDate = @newReturnDate "; AData.AddParameter(dbCommand, "@newReturnDate", video.ReturnDate); if (video.Title != "" && video.Title != null) { dbCommand.CommandText += $"WHERE Title = @Title"; AData.AddParameter(dbCommand, "@Title", video.Title); dbCommand.CommandText += $" And Borrower = @Borrower"; AData.AddParameter(dbCommand, "@Borrower", video.Borrower); } else { dbCommand.CommandText += $"WHERE Borrower = @Borrower"; AData.AddParameter(dbCommand, "@Borrower", video.Borrower); } } }
//Erstellt den SQL Befehl für das Ausgeben der Videos und setzt ihn in DbCommand command ein protected virtual void SqlSelectVideo(VideoDtoSearch videoSearch, DbCommand dbCommand) { dbCommand.CommandType = CommandType.Text; dbCommand.Parameters.Clear(); dbCommand.CommandText = @"SELECT * FROM VideoTable"; if (videoSearch.ID == 0) { if (videoSearch.Title != "" && videoSearch.Title != null) { dbCommand.CommandText += " WHERE Title = @Title"; AData.AddParameter(dbCommand, "@Title", videoSearch.Title); } } else { dbCommand.CommandText += " WHERE ID = @ID"; AData.AddParameter(dbCommand, "@ID", videoSearch.ID); if (videoSearch.Title != "" && videoSearch.Title != null) { dbCommand.CommandText += " AND Title = @Title"; AData.AddParameter(dbCommand, "@Title", videoSearch.Title); } } if (videoSearch.Genre != "" && videoSearch.Genre != null) { if (dbCommand.CommandText == "SELECT * FROM VideoTable") { dbCommand.CommandText += " WHERE Genre = @Genre"; AData.AddParameter(dbCommand, "@Genre", videoSearch.Genre); } else { dbCommand.CommandText += " AND Genre = @Genre"; AData.AddParameter(dbCommand, "@Genre", videoSearch.Genre); } } if (videoSearch.BorrowingRate != 0.0 && videoSearch.BorrowingRate != 0) { if (dbCommand.CommandText == "SELECT * FROM VideoTable") { dbCommand.CommandText += " WHERE BorrowingRate >= @BorrowingRateMin"; AData.AddParameter(dbCommand, "@BorrowingRateMin", 0.0); dbCommand.CommandText += " AND BorrowingRate <= @BorrowingRate"; AData.AddParameter(dbCommand, "@BorrowingRate", videoSearch.BorrowingRate); } else { dbCommand.CommandText += " AND BorrowingRate >= @BorrowingRateMin"; AData.AddParameter(dbCommand, "@BorrowingRateMin", 0.0); dbCommand.CommandText += " AND BorrowingRate <= @BorrowingRate"; AData.AddParameter(dbCommand, "@BorrowingRate", videoSearch.BorrowingRate); } } if (videoSearch.ReleaseYear != 0) { if (dbCommand.CommandText == "SELECT * FROM VideoTable") { dbCommand.CommandText += " WHERE ReleaseYear = @ReleaseYear"; AData.AddParameter(dbCommand, "@ReleaseYear", videoSearch.ReleaseYear); } else { dbCommand.CommandText += " AND ReleaseYear = @ReleaseYear"; AData.AddParameter(dbCommand, "@ReleaseYear", videoSearch.ReleaseYear); } } if (videoSearch.RunningTime != 0) { if (dbCommand.CommandText == "SELECT * FROM VideoTable") { dbCommand.CommandText += " WHERE RunningTime >= @RunningTimeMin"; AData.AddParameter(dbCommand, "@RunningTimeMin", 0); dbCommand.CommandText += " AND RunningTime <= @RunningTime"; AData.AddParameter(dbCommand, "@RunningTime", videoSearch.RunningTime); } else { dbCommand.CommandText += " AND RunningTime >= @RunningTimeMin"; AData.AddParameter(dbCommand, "@RunningTimeMin", 0); dbCommand.CommandText += " AND RunningTime <= @RunningTime"; AData.AddParameter(dbCommand, "@RunningTime", videoSearch.RunningTime); } } if (videoSearch.Rated != 1) { if (dbCommand.CommandText == "SELECT * FROM VideoTable") { dbCommand.CommandText += " WHERE Rated = @Rated"; AData.AddParameter(dbCommand, "@Rated", videoSearch.Rated); } else { dbCommand.CommandText += " AND Rated = @Rated"; AData.AddParameter(dbCommand, "@Rated", videoSearch.Rated); } } if (videoSearch.Borrower != "" && videoSearch.Borrower != null) { if (dbCommand.CommandText == "SELECT * FROM VideoTable") { dbCommand.CommandText += " WHERE Borrower = @Borrower"; AData.AddParameter(dbCommand, "@Borrower", videoSearch.Borrower); } else { dbCommand.CommandText += " AND Borrower = @Borrower"; AData.AddParameter(dbCommand, "@Borrower", videoSearch.Borrower); } } if (videoSearch.ReturnDate != DateTime.MinValue) { if (dbCommand.CommandText == "SELECT * FROM VideoTable") { dbCommand.CommandText += " WHERE ReturnDate >= @ReturnDateMin"; AData.AddParameter(dbCommand, "@ReturnDateMin", DateTime.MinValue); dbCommand.CommandText += " AND ReturnDate <= @ReturnDate"; AData.AddParameter(dbCommand, "@ReturnDate", videoSearch.ReturnDate); } else { dbCommand.CommandText += " AND ReturnDate >= @ReturnDateMin"; AData.AddParameter(dbCommand, "@ReturnDateMin", DateTime.MinValue); dbCommand.CommandText += " AND ReturnDate <= @ReturnDate"; AData.AddParameter(dbCommand, "@ReturnDate", videoSearch.ReturnDate); } } dbCommand.CommandText += " ORDER BY ID;"; }