示例#1
0
 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}");
     }
 }
示例#2
0
        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);
        }