//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;";
}