public static VideoGamesDAL.Models.System AddSystem(VideoGamesDAL.Models.System system)
{
//get connection
using (SqlConnection conn = DB.GetConnection())
{
//define a command
using (SqlCommand cmd = new SqlCommand())
{
//NOTE: this is done with SQL in the code...
//You are expected to create a Stored Proc
//to do the insert. Refer to update and delete
//examples
cmd.Connection = conn;
cmd.CommandText = "InsertSystem";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@systemName", system.Name);
cmd.Parameters.AddWithValue("@systemCompany", system.Company);
//run the command
int newId = (int)cmd.ExecuteScalar();
//set the id with the new row id
system.SystemID = newId;
//return the updated category object
//that now contains the id of the new category
return(system);
}
}
}
public static List <VideoGamesDAL.Models.System> GetSystemList()
{
//create empty list to hold objects
List <VideoGamesDAL.Models.System> systems = new List <VideoGamesDAL.Models.System>();
//get a connection from DB class
using (SqlConnection conn = DB.GetConnection())
{
//Create a comnmand using the connection
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM System";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//generate a new object
// and fill it with data from reader
VideoGamesDAL.Models.System system = new VideoGamesDAL.Models.System();
system.SystemID = reader.GetInt32(0);
system.Name = reader.GetString(1);
system.Company = reader.GetString(2);
//add the filled category to my list
systems.Add(system);
}
}
}
//return the list
return(systems);
}
public static VideoGamesDAL.Models.System GetSystem(int SystemID)
{
using (SqlConnection conn = DB.GetConnection())
{
//Create a comnmand using the connection
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "GetESRB";
cmd.Parameters.AddWithValue("@systemid", SystemID);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
// create an object
VideoGamesDAL.Models.System s = new VideoGamesDAL.Models.System();
s.SystemID = reader.GetInt32(0);
s.Name = reader.GetString(1);
s.Company = reader.GetString(2);
return(s);
}
else
{
return(null);
}
}
}
}
/// <summary>
/// Update an existing category
/// </summary>
/// <param name="category">The category (object) to update</param>
/// <returns>Number of rows affected</returns>
public static int UpdateSystem(VideoGamesDAL.Models.System system)
{
//get the connection
using (SqlConnection conn = DB.GetConnection())
{
//define the command
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "UpdateSystem";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//fill in all parameters that the stored proc expects
cmd.Parameters.AddWithValue("@systemid", system.SystemID);
cmd.Parameters.AddWithValue("@name", system.Name);
//run the command
int rowsAffected = cmd.ExecuteNonQuery();
return(rowsAffected);
}
}
}