public static void insertMovie(SqlCommand command, MovieClass movie)
{
command.Parameters.Clear();
command.CommandText = "INSERT INTO MOVIES (TITLE, RELEASEDATE, GENRE, SUMMARY) " +
"VALUES (@TITLE, @RELEASEDATE, @GENRE, @SUMMARY) " +
"SELECT SCOPE_IDENTITY();";
command.Parameters.Add("@TITLE", SqlDbType.VarChar, 100);
command.Parameters.Add("@RELEASEDATE", SqlDbType.DateTime);
command.Parameters.Add("@GENRE", SqlDbType.TinyInt);
command.Parameters.Add("@SUMMARY", SqlDbType.VarChar, 250);
command.Prepare();
command.Parameters["@TITLE"].Value = movie.t;
command.Parameters["@RELEASEDATE"].Value = DBTypesEvaluator.ToDateTime(movie.rd);
command.Parameters["@GENRE"].Value = movie.g;
command.Parameters["@SUMMARY"].Value = movie.s;
movie.i = DBTypesEvaluator.ToInteger(command.ExecuteScalar());
if (movie.i != 0)
{
/* insert directors */
inserDirectors(command, movie);
/* insert actors */
insertActors(command, movie);
}
}
public static int getLastRefreshedId(SqlCommand command)
{
command.Parameters.Clear();
command.CommandText = "SELECT * FROM SYSTEMPARAMS WHERE PARAMNAME = 'LASTREFRESHEDMOVIEID' ";
command.Prepare();
int lastRefreshedId = 0;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
lastRefreshedId = DBTypesEvaluator.ToInteger(reader["PARAMINTEGER"]);
}
reader.Close();
return(lastRefreshedId);
}
public static void updateMovie(SqlCommand command, MovieClass movie)
{
command.Parameters.Clear();
command.CommandText = "UPDATE MOVIES SET TITLE = @TITLE, RELEASEDATE = @RELEASEDATE, " +
"GENRE = @GENRE, SUMMARY = @SUMMARY " +
"WHERE ID = @ID ";
command.Parameters.Add("@TITLE", SqlDbType.VarChar, 100);
command.Parameters.Add("@RELEASEDATE", SqlDbType.DateTime);
command.Parameters.Add("@GENRE", SqlDbType.TinyInt);
command.Parameters.Add("@SUMMARY", SqlDbType.VarChar, 250);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Prepare();
command.Parameters["@TITLE"].Value = movie.t;
command.Parameters["@RELEASEDATE"].Value = DBTypesEvaluator.ToDateTime(movie.rd);
command.Parameters["@GENRE"].Value = movie.g;
command.Parameters["@SUMMARY"].Value = movie.s;
command.Parameters["@ID"].Value = movie.i;
command.ExecuteNonQuery();
// delete directories
command.Parameters.Clear();
command.CommandText = "DELETE FROM MOVIEDIRECTORS WHERE MOVIEID = @MOVIEID ";
command.Parameters.Add("@MOVIEID", SqlDbType.Int);
command.Prepare();
command.Parameters["@MOVIEID"].Value = movie.i;
command.ExecuteNonQuery();
// delete actors
command.Parameters.Clear();
command.CommandText = "DELETE FROM MOVIEACTORS WHERE MOVIEID = @MOVIEID ";
command.Parameters.Add("@MOVIEID", SqlDbType.Int);
command.Prepare();
command.Parameters["@MOVIEID"].Value = movie.i;
command.ExecuteNonQuery();
/* insert directors */
inserDirectors(command, movie);
/* insert actors */
insertActors(command, movie);
}
public static List <MovieClass> getMovieTitles(SqlCommand command, int lastRefreshedId)
{
command.Parameters.Clear();
command.CommandText = "SELECT TOP 50 * FROM MOVIES WHERE ID > @LASTREFRESHEDID ORDER BY ID ASC ";
command.Parameters.Add("@LASTREFRESHEDID", SqlDbType.Int);
command.Prepare();
command.Parameters["@LASTREFRESHEDID"].Value = lastRefreshedId;
List <MovieClass> list = new List <MovieClass>();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
MovieClass movie = new MovieClass();
movie.i = DBTypesEvaluator.ToInteger(reader["ID"]);
movie.t = DBTypesEvaluator.ToString(reader["TITLE"]);
list.Add(movie);
}
reader.Close();
return(list);
}
public static MovieClass getMovie(SqlCommand command, string title)
{
MovieClass result = null;
command.Parameters.Clear();
command.CommandText = "SELECT TOP 1 * FROM MOVIES WHERE TITLE LIKE @TITLE ";
command.Parameters.Add("@TITLE", SqlDbType.VarChar, 100);
command.Prepare();
command.Parameters["@TITLE"].Value = "%" + title + "%";
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
result = new MovieClass();
result.i = DBTypesEvaluator.ToInteger(reader["ID"]);
result.t = DBTypesEvaluator.ToString(reader["TITLE"]);
result.rd = DBTypesEvaluator.ToFullTimeStr(reader["RELEASEDATE"]);
result.g = DBTypesEvaluator.ToInteger(reader["GENRE"]);
result.s = DBTypesEvaluator.ToString(reader["SUMMARY"]);
}
reader.Close();
if (result != null)
{
/* read movie director names */
command.Parameters.Clear();
command.CommandText = "SELECT * FROM MOVIEDIRECTORS WHERE MOVIEID = @MOVIEID ";
command.Parameters.Add("@MOVIEID", SqlDbType.Int);
command.Prepare();
command.Parameters["@MOVIEID"].Value = result.i;
List <string> directorList = new List <string>();
reader = command.ExecuteReader();
while (reader.Read())
{
directorList.Add(DBTypesEvaluator.ToString(reader["DIRECTOR"]));
}
reader.Close();
result.md = directorList;
/* read movie actor names */
command.Parameters.Clear();
command.CommandText = "SELECT * FROM MOVIEACTORS WHERE MOVIEID = @MOVIEID ";
command.Parameters.Add("@MOVIEID", SqlDbType.Int);
command.Prepare();
command.Parameters["@MOVIEID"].Value = result.i;
List <string> actorList = new List <string>();
reader = command.ExecuteReader();
while (reader.Read())
{
actorList.Add(DBTypesEvaluator.ToString(reader["ACTOR"]));
}
reader.Close();
result.ma = actorList;
}
return(result);
}
