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();
}
}
}
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);
}
}
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();
}
}
}
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();
}
}
}