//Gibt eine Liste der Videos anhand einer VideoDtoSearch
public void ReadVideos(VideoDtoSearch videoSearch, out IList <Loan> videos)
{
videos = new List <Loan>();
AData.Open(_dbConnection);
this.SqlSelectVideo(videoSearch, _dbCommand);
DbDataReader dbDataReader = _dbCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
int nColumns = dbDataReader.FieldCount;
while (dbDataReader.Read())
{
Loan video = new Loan()
{
ID = dbDataReader.GetInt32(0),
Title = dbDataReader.GetString(1),
Borrower = dbDataReader.GetString(2),
ReturnDate = dbDataReader.GetDateTime(3)
};
videos.Add(video);
}
}
if (!dbDataReader.IsClosed)
{
dbDataReader.Close();
}
AData.Close(_dbConnection);
}
internal CDialogMain(ILogic logic)
{
InitializeComponent();
_logic = logic;
_videoSearch = new VideoDtoSearch();
_videoLoan = new VideoDtoLoan();
_videoLoanExisting = new VideoDtoLoan();
}
//Sucht mit Filtern nach einem Video
public void ReadVideo(VideoDtoLoan videoLoan, out DataTable dataTable)
{
VideoDtoSearch videoSearch = new VideoDtoSearch()
{
ID = videoLoan.ID,
Title = videoLoan.Title,
Borrower = videoLoan.Borrower,
ReturnDate = videoLoan.ReturnDate,
Rated = 1,
};
_dataSearch.ReadVideos(videoSearch, out dataTable);
}
//Gibt eine Daten Tabelle der Videos anhand einer VideoDtoSearch
public void ReadVideos(VideoDtoSearch videoSearch, out DataTable dataTableVideos)
{
dataTableVideos = new DataTable("Videos");
AData.Open(_dbConnection);
DbDataAdapter dbDataAdapter = _dbProviderFactory.CreateDataAdapter();
this.SqlSelectVideo(videoSearch, _dbCommand);
_dbCommand.Connection = _dbConnection;
dbDataAdapter.SelectCommand = _dbCommand;
int records = dbDataAdapter.Fill(dataTableVideos);
AData.Close(_dbConnection);
}
internal CDialogSearch(ILogicSearch logicSearch, IDialog dialog)
{
InitializeComponent();
if (dialog is CDialogMain)
{
_dialogMain = dialog as CDialogMain;
}
else
{
CErrorHandling.ShowAndStop("Fehler beim Initialisieren von CDialogSearch", "Programmabbruch");
}
_logicSearch = logicSearch;
_videoSearch = new VideoDtoSearch();
}
public object[] ReadID(VideoDtoSearch value)
{
List <int> listID = new List <int>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string id = row["ID"].ToString();
listID.Add(Util.ParseInt(id, 0));
listID.Sort();
}
if (listID == null)
{
throw new CDataException("ID-Liste ist leer");
}
return(Util.ToArray(listID));
}
//Liest alla Titel aus
public object[] ReadTitle(VideoDtoSearch value)
{
List <string> listTitle = new List <string>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string title = row["Title"].ToString();
if (!listTitle.Contains(title))
{
listTitle.Add(title);
listTitle.Sort();
}
}
if (listTitle == null)
{
throw new CDataException("Titelliste ist leer");
}
return(Util.ToArray(listTitle));
}
//Gibt das Rückgabedatum zurück
public object[] ReadReturnDate(VideoDtoSearch value)
{
List <DateTime> listReturnDate = new List <DateTime>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string returnDate = row["ReturnDate"].ToString();
if (!listReturnDate.Contains(Util.ParseDate(returnDate, DateTime.MinValue)) && (Util.ParseDate("1.1.2001", DateTime.MinValue) != Util.ParseDate(returnDate, DateTime.MinValue)))
{
listReturnDate.Add(Util.ParseDate(returnDate, DateTime.MinValue));
listReturnDate.Sort();
}
}
if (listReturnDate == null)
{
throw new CDataException("ReturnDate-Liste ist leer");
}
return(Util.ToArray(listReturnDate));
}
//Gibt alle Ausleihenden aus
public object[] ReadBorrower(VideoDtoSearch value)
{
List <string> listBorrower = new List <string>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string borrower = row["Borrower"].ToString();
if (!listBorrower.Contains(borrower) && (borrower != ""))
{
listBorrower.Add(borrower);
listBorrower.Sort();
}
}
if (listBorrower == null)
{
throw new CDataException("Borrower-Liste ist leer");
}
return(Util.ToArray(listBorrower));
}
//Liest die FSK aus
public object[] ReadRated(VideoDtoSearch value)
{
List <int> listRated = new List <int>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string rated = row["Rated"].ToString();
if (!listRated.Contains(Util.ParseInt(rated, 1)))
{
listRated.Add(Util.ParseInt(rated, 1));
listRated.Sort();
}
}
if (listRated == null)
{
throw new CDataException("FSK-Liste ist leer");
}
return(Util.ToArray(listRated));
}
//Liest alle Laufzeiten aus
public object[] ReadRunningTime(VideoDtoSearch value)
{
List <int> listRunningTime = new List <int>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string runningTime = row["RunningTime"].ToString();
if (!listRunningTime.Contains(Util.ParseInt(runningTime, 0)))
{
listRunningTime.Add(Util.ParseInt(runningTime, 0));
listRunningTime.Sort();
}
}
if (listRunningTime == null)
{
throw new CDataException("Laufzeitliste-Liste ist leer");
}
return(Util.ToArray(listRunningTime));
}
//Liest alle Release Daten aus
public object[] ReadReleaseYear(VideoDtoSearch value)
{
List <int> listReleaseYear = new List <int>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string releaseYear = row["ReleaseYear"].ToString();
if (!listReleaseYear.Contains(Util.ParseInt(releaseYear, 0)))
{
listReleaseYear.Add(Util.ParseInt(releaseYear, 0));
listReleaseYear.Sort();
}
}
if (listReleaseYear == null)
{
throw new CDataException("Erscheinungsjahrliste ist leer");
}
return(Util.ToArray(listReleaseYear));
}
//Liest alle Preise aus
public object[] ReadBorrowingRate(VideoDtoSearch value)
{
List <double> listBorrowingRate = new List <double>();
ReadVideos(value, out DataTable dataTable);
foreach (DataRow row in dataTable.Rows)
{
string borrowingRate = row["BorrowingRate"].ToString();
if (!listBorrowingRate.Contains(Util.ParseDouble(borrowingRate, 0.0)))
{
listBorrowingRate.Add(Util.ParseDouble(borrowingRate, 0.0));
listBorrowingRate.Sort();
}
}
if (listBorrowingRate == null)
{
throw new CDataException("Preisiste-Liste ist leer");
}
return(Util.ToArray(listBorrowingRate));
}
private void ButtonConfirm_Click(object sender, EventArgs e)
{
// Übernehmen der Werte aus den Comboboxen
VideoDtoSearch videoSearch = _dialogMain.VideoSearch;
videoSearch.ID = Util.ParseInt(comboBoxID.Text, _videoSearch.ID);
videoSearch.Title = comboBoxTitle.Text;
videoSearch.Genre = comboBoxGenre.Text;
videoSearch.BorrowingRate = Util.ParseDouble(comboBoxBorrowingRate.Text, _videoSearch.BorrowingRate);
videoSearch.ReleaseYear = Util.ParseInt(comboBoxReleaseYear.Text, _videoSearch.ReleaseYear);
videoSearch.RunningTime = Util.ParseInt(comboBoxRunningTime.Text, _videoSearch.RunningTime);
videoSearch.Rated = Util.ParseInt(comboBoxRated.Text, 1);
videoSearch.Borrower = comboBoxBorrower.Text;
videoSearch.ReturnDate = Util.ParseDate(comboBoxReturnDate.Text, videoSearch.ReturnDate);
// Weitergeben der Daten und schließen des Fensters
this.VideoDtoSearch = videoSearch;
this.DialogResult = DialogResult.OK;
// Zurücksetzen aller Combobox-Items, wenn Ergebnis erhalten
ResetAll();
this.Close();
}
private void SuchenToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
// Fenster wird nach erfogreicher Suche erneut angezeigt
while (true)
{
// Zeigt Fenster zum Suchen
DialogResult dialogResult = DialogSearch.ShowDialog();
// wenn Button Bestätigen bei der Suche gedrückt
if (dialogResult == DialogResult.OK)
{
_videoSearch = DialogSearch.VideoDtoSearch;
// wird Suchen ausgeführt
_logic.Search.ReadVideos(_videoSearch, out DataTable dataTable);
// und das Ergebnis in DialogSearchView dargestellen
DialogSearchResult.ResultTable = dataTable;
dialogResult = DialogSearchResult.ShowDialog();
}
// wenn Button Abbrechen gedrückt, wird das Fenster geschlossen ohne eine Aktion auszuführen
else
{
return;
}
}
}
catch (DataException dataException)
{
MessageBox.Show(dataException.Message, "Abbruch Suchen",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Abbruch Suchen",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//Sucht nach einem Video
public void ReadVideos(VideoDtoSearch videoSearch, out DataTable datatable)
{
_dataSearch.ReadVideos(videoSearch, out datatable);
}
//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;";
}
