public DVDdb Add(DVDdb dvd)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString =
ConfigurationManager.ConnectionStrings["DvdDB"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "AddDvd";
// Create parameter with Direction as Output (and correct name and type)
SqlParameter outputDvdIDParam = new SqlParameter("@DvdID", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outputDvdIDParam);
// Add other input parameters
cmd.Parameters.AddWithValue("@DvdTitle", dvd.title);
cmd.Parameters.AddWithValue("@YearNR", dvd.realeaseYear);
cmd.Parameters.AddWithValue("@DirectorName", dvd.director);
cmd.Parameters.AddWithValue("@RatingType", dvd.rating);
cmd.Parameters.AddWithValue("@DvdNotes", dvd.notes);
//Open the connection and run the SP
conn.Open();
cmd.ExecuteNonQuery();
//Retrieve output parameter value
dvd.dvdId = (int)outputDvdIDParam.Value;
}
return(dvd);
}
public DVDdb Add(DVDdb dvd)
{
using (var context = new DvdLibContextModel())
{
var idParam = new SqlParameter
{
ParameterName = "@DvdID", SqlDbType = SqlDbType.Int, Value = 0, Direction = ParameterDirection.Output
};
var titleParam = new SqlParameter
{
ParameterName = "@DvdTitle", SqlDbType = SqlDbType.VarChar, Value = dvd.title
};
var yearParam = new SqlParameter
{
ParameterName = "@YearNR", SqlDbType = SqlDbType.VarChar, Value = dvd.realeaseYear
};
var directorParam = new SqlParameter
{
ParameterName = "@DirectorName", SqlDbType = SqlDbType.VarChar, Value = dvd.director
};
var ratingParam = new SqlParameter
{
ParameterName = "@RatingType", SqlDbType = SqlDbType.VarChar, Value = dvd.rating
};
var notesParam = new SqlParameter
{
ParameterName = "@DvdNotes", SqlDbType = SqlDbType.VarChar, Value = dvd.notes
};
context.Database.SqlQuery <DVDdb>("AddDvd @DvdID OUTPUT, @DvdTitle, @YearNR, @DirectorName, @RatingType, @DvdNotes", idParam, titleParam, yearParam, directorParam, ratingParam, notesParam).SingleOrDefault();
dvd.dvdId = (int)idParam.Value;
}
return(dvd);
}
public Dictionary <int, DVDdb> GetAll()
{
Dictionary <int, DVDdb> dvds = new Dictionary <int, DVDdb>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString =
ConfigurationManager.ConnectionStrings["DvdDB"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetAllDvds";
//Open the connection
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DVDdb dvd = new DVDdb();
//Populate the dvd object
dvd.dvdId = (int)dr["dvdId"];
dvd.title = dr["title"].ToString();
dvd.realeaseYear = dr["realeaseYear"].ToString();
dvd.director = dr["director"].ToString();
dvd.rating = dr["rating"].ToString();
dvd.notes = dr["notes"].ToString();
//Add to dictionary return object
dvds.Add(dvd.dvdId, dvd);
}
}
}
return(dvds);
}
public DVDdb Edit(DVDdb dvd, int ID)
{
dvd.dvdId = ID; //Just incase the ID was not set in the dvd object
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString =
ConfigurationManager.ConnectionStrings["DvdDB"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "EditDvd";
// Add input parameters
cmd.Parameters.AddWithValue("@DvdID", ID);
cmd.Parameters.AddWithValue("@DvdTitle", dvd.title);
cmd.Parameters.AddWithValue("@YearNR", dvd.realeaseYear);
cmd.Parameters.AddWithValue("@DirectorName", dvd.director);
cmd.Parameters.AddWithValue("@RatingType", dvd.rating);
cmd.Parameters.AddWithValue("@DvdNotes", dvd.notes);
//Open the connection and run the SP
conn.Open();
cmd.ExecuteNonQuery();
}
return(dvd);
}
public DVDdb Get(int ID)
{
DVDdb dvd = new DVDdb();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString =
ConfigurationManager.ConnectionStrings["DvdDB"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetDvdByID";
// Add input parameters
cmd.Parameters.AddWithValue("@DvdID", ID);
//Open the connection
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
//Result set is a single row
dr.Read();
//Populate the return object
dvd.dvdId = (int)dr["dvdId"];
dvd.title = dr["title"].ToString();
dvd.realeaseYear = dr["realeaseYear"].ToString();
dvd.director = dr["director"].ToString();
dvd.rating = dr["rating"].ToString();
dvd.notes = dr["notes"].ToString();
}
}
return(dvd);
}
public DVDdb Edit(DVDdb dvd, int ID)
{
dvd.dvdId = ID; //Just in case the wrong ID was set...
using (var context = new DvdLibContextModel())
{
var idParam = new SqlParameter
{
ParameterName = "@DvdID", SqlDbType = SqlDbType.Int, Value = ID
};
var titleParam = new SqlParameter
{
ParameterName = "@DvdTitle", SqlDbType = SqlDbType.VarChar, Value = dvd.title
};
var yearParam = new SqlParameter
{
ParameterName = "@YearNR", SqlDbType = SqlDbType.VarChar, Value = dvd.realeaseYear
};
var directorParam = new SqlParameter
{
ParameterName = "@DirectorName", SqlDbType = SqlDbType.VarChar, Value = dvd.director
};
var ratingParam = new SqlParameter
{
ParameterName = "@RatingType", SqlDbType = SqlDbType.VarChar, Value = dvd.rating
};
var notesParam = new SqlParameter
{
ParameterName = "@DvdNotes", SqlDbType = SqlDbType.VarChar, Value = dvd.notes
};
context.Database.SqlQuery <DVDdb>("EditDvd @DvdID, @DvdTitle, @YearNR, @DirectorName, @RatingType, @DvdNotes", idParam, titleParam, yearParam, directorParam, ratingParam, notesParam).SingleOrDefault();
}
return(dvd);
}
public static DVDdb Edit(DVDdb dvd, int ID)
{
if (_dvds.ContainsKey(ID))
{
_dvds[ID] = dvd;
return(_dvds[ID]);
}
else
{
return(null);
}
}
public IHttpActionResult Delete(int dvdID)
{
DVDdb dvd = DvdRepositoryFactory.GetRepository().Get(dvdID);
if (dvd == null)
{
return(NotFound());
}
DvdRepositoryFactory.GetRepository().Delete(dvdID);
return(Ok());
}
public IHttpActionResult Get(int dvdID)
{
DVDdb dvd = DvdRepositoryFactory.GetRepository().Get(dvdID);
if (dvd == null)
{
return(NotFound());
}
else
{
return(Ok(dvd));
}
}
public static DVDdb Delete(int ID)
{
if (_dvds.ContainsKey(ID))
{
DVDdb old = _dvds[ID];
_dvds.Remove(ID);
return(old);
}
else
{
return(null);
}
}
public DVDdb Get(int ID)
{
DVDdb dvd = new DVDdb();
using (var context = new DvdLibContextModel())
{
var idParam = new SqlParameter
{
ParameterName = "@DvdID", SqlDbType = SqlDbType.Int, Value = ID
};
dvd = context.Database.SqlQuery <DVDdb>("GetDvdByID @DvdID", idParam).Single();
}
return(dvd);
}
public DVDdb Delete(int ID)
{
DVDdb deletedDvd = this.Get(ID);
using (var context = new DvdLibContextModel())
{
var idParam = new SqlParameter
{
ParameterName = "@DvdID", SqlDbType = SqlDbType.Int, Value = ID
};
context.Database.SqlQuery <DVDdb>("DeleteDvd @DvdID", idParam).SingleOrDefault();
}
return(deletedDvd);
}
public static DVDdb Add(DVDdb dvd)
{
int newKey;
if (_dvds.Keys.Count() == 0)
{
newKey = 1;
}
else
{
newKey = _dvds.Keys.Max() + 1;
}
dvd.dvdId = newKey;
_dvds.Add(newKey, dvd);
return(_dvds[newKey]);
}
public IHttpActionResult Add(AddDvdRequest request)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
DVDdb dvd = new DVDdb()
{
title = request.title,
realeaseYear = request.realeaseYear,
director = request.director,
rating = request.rating,
notes = request.notes //Not Required
};
DvdRepositoryFactory.GetRepository().Add(dvd);
return(Created($"dvds/get/{dvd.dvdId}", dvd));
}
public IHttpActionResult Update(UpdateDvdRequest request)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
DVDdb dvd = DvdRepositoryFactory.GetRepository().Get(request.dvdID);
if (dvd == null)
{
return(NotFound());
}
dvd.title = request.title;
dvd.realeaseYear = request.realeaseYear;
dvd.director = request.director;
dvd.rating = request.rating;
dvd.notes = request.notes; //Not Required
DvdRepositoryFactory.GetRepository().Edit(dvd, dvd.dvdId);
return(Ok(dvd));
}
public DVDdb Delete(int ID)
{
DVDdb deletedDvd = this.Get(ID);
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString =
ConfigurationManager.ConnectionStrings["DvdDB"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "DeleteDvd";
// Add input parameters
cmd.Parameters.AddWithValue("@DvdID", ID);
//Open the connection and run the SP
conn.Open();
cmd.ExecuteNonQuery();
}
return(deletedDvd);
}
public DVDdb Edit(DVDdb dvd, int ID)
{
return(DvdStaticMemoryRepo.Edit(dvd, ID));
}
public DVDdb Add(DVDdb dvd)
{
return(DvdStaticMemoryRepo.Add(dvd));
}
