예제 #1
0
        public static List<Genre> GetGenre()
        {
            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";

            List<Genre> genreList = new List<Genre>();

            string genreSQL = "SELECT genre_id, genre_name FROM Genre";

            SqlCommand objGCommand = null;
            SqlConnection objGConn = null;
            SqlDataReader genreReader = null;

            try
            {
                using (objGConn = new SqlConnection(connectionString))
                {
                    //Open the connection to the datbase
                    objGConn.Open();
                    //Command object created with the SQL statement
                    using (objGCommand = new SqlCommand(genreSQL, objGConn))
                    {
                        //Execute the SQL and return a DataReader
                        using ((genreReader = objGCommand.ExecuteReader(CommandBehavior.CloseConnection)))
                        {
                            while (genreReader.Read())
                            {
                                Genre objGenre = new Genre();
                                objGenre.GenreId = genreReader["genre_id"].ToString();
                                objGenre.GenreName = genreReader["genre_name"].ToString();

                                //Add Genre to collection
                                genreList.Add(objGenre);
                            }
                        }
                    }

                    return genreList;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (objGConn != null)
                {
                    objGConn.Close();
                }
            }
        }
예제 #2
0
 private void btnDelGenre_Click(object sender, EventArgs e)
 {
     if (txtGenreID.Text.Trim() == String.Empty)
     {
         MessageBox.Show("Please enter a Genre ID #.", "",
                             MessageBoxButtons.OK, MessageBoxIcon.Error);
         txtGenreID.Focus();
         return;
     }
     
     Genre objGenre = new Genre();
     objGenre.GenreId = txtGenreID.Text.Trim();
     try
     {
         bool status = GenreDB.DeleteGenre(objGenre);
         if (status) //You can use this syntax as well..if (status ==true)
         {
             MessageBox.Show("Genre deleted from the database.", "",
                                 MessageBoxButtons.OK, MessageBoxIcon.Information);
             ClearGenreFields();
             GenreListLoad();
         }
         else
         {
             MessageBox.Show("Genre was not deleted from the database.", "",
                                 MessageBoxButtons.OK, MessageBoxIcon.Information);
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
 }
예제 #3
0
        public static Genre GetGenre(int genNumber)
        {
            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";

            string genreSQL = "SELECT genre_id, genre_name FROM Genre" +
                              " WHERE genre_id = @genre_id";

            SqlCommand objGCommand = null;
            SqlConnection objGConn = null;
            SqlDataReader genReader = null;
            Genre objGenre = null;
            try
            {
                using (objGConn = new SqlConnection(connectionString))
                {
                    //Open the connection to the datbase
                    objGConn.Open();
                    //Create a command object with the SQL statement
                    using (objGCommand = new SqlCommand(genreSQL, objGConn))
                    {
                        //Set command parameter
                        objGCommand.Parameters.AddWithValue("@genre_id", genNumber);
                        //Execute the SQL and return a DataReader
                        using ((genReader = objGCommand.ExecuteReader(CommandBehavior.CloseConnection)))
                        {
                            while (genReader.Read())
                            {
                                objGenre = new Genre();
                                //Fill the customer object if found
                                objGenre.GenreId = genReader["genre_id"].ToString();
                                objGenre.GenreName = genReader["genre_name"].ToString();
                            }
                        }
                    }
                    return objGenre;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (objGConn != null)
                {
                    objGConn.Close();
                }
            }
        }
예제 #4
0
        public static bool DeleteGenre(Genre objGenre)
        {
            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";

            int rowsAffected = 0;
            string genreSQL;

            SqlCommand objGCommand = null;
            SqlConnection objGConn = null;

            try
            {
                using (objGConn = new SqlConnection(connectionString))
                {
                    //Open the connection to the datbase
                    objGConn.Open();
                    genreSQL = "DELETE Genre WHERE genre_id = @genre_id";

                    //Create a command object with the SQL statement
                    using (objGCommand = new SqlCommand(genreSQL, objGConn))
                    {
                        //Use the command parameters method to set the paramater values of the SQL Insert statement
                        objGCommand.Parameters.AddWithValue("@genre_id", objGenre.GenreId);
                        //Execute the SQL and return the number of rows affected
                        rowsAffected = objGCommand.ExecuteNonQuery();
                    }
                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (objGConn != null)
                {
                    objGConn.Close();
                }
            }
        }