private void ReadAllSQLTable(string connectionString, string sqlCommand)
{
// Connection to SQL database and loop to read and store all entries in list
string[] movieGenres = { "null", "Animation", "Action", "Comedy", "Drama", "Horror", "Mystery", "Romance", "Science Fiction", "Western" };
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sqlCommand, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var movieEntry = new MovieEntry();
movieEntry.Id = reader.GetInt32(0);
movieEntry.Title = reader.GetString(1);
movieEntry.Year = reader.GetInt32(2);
movieEntry.Director = reader.GetString(3);
if (reader.GetInt32(4) > 0 && reader.GetInt32(4) <= 9)
{
int genreValue = reader.GetInt32(4);
movieEntry.Genre = movieGenres[genreValue];
}
if (!reader.IsDBNull(5))
{
movieEntry.RottenTomatoesScore = reader.GetInt32(5);
}
if (!reader.IsDBNull(6))
{
movieEntry.TotalEarned = reader.GetDecimal(6);
}
movieEntries.Add(movieEntry);
}
}
connection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show($"Database conection failed. Error {ex.Message}");
}
}
private Boolean AlreadyInDatabase(String movie)
{
// Method to check if user entry already exists in database
Boolean inDatabase = false;
MovieEntry updateEntryCheck = new MovieEntry();
string connectionString = MovieManagerApplication.GetConnectionString();
string sqlCommand = "SELECT Id, Title FROM Movies " + "WHERE Title = @Title";
string searchValue = movie;
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sqlCommand, connection))
{
command.Parameters.Add("Title", SqlDbType.VarChar, 50).Value = searchValue;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
updateEntryCheck.Id = reader.GetInt32(0);
if (updateEntryCheck.Id != updateMovieEntry.Id)
{
inDatabase = true;
}
}
connection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show($"Database conection failed. Error {ex.Message}");
}
return(inDatabase);
}