//Editerar en existerande modell
public static Bottle GetBottleUpdate(Bottle bottle)
{
using (SqlConnection conn = CreateConnection())
{
try
{
SqlCommand cmd = new SqlCommand("appSchema.usp_EditBottle", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(@"BottleID", SqlDbType.Int, 4).Value = bottle.BottleID;
cmd.Parameters.Add(@"Year", SqlDbType.Int, 4).Value = bottle.Year;
cmd.Parameters.Add(@"Price", SqlDbType.Decimal, 7).Value = bottle.Price;
cmd.Parameters.Add(@"Amount", SqlDbType.Int, 4).Value = bottle.Amount;
conn.Open();
cmd.ExecuteNonQuery();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var bottleIDIndex = reader.GetOrdinal("BottleID");
var YearIndex = reader.GetOrdinal("Year");
var PriceIndex = reader.GetOrdinal("Price");
var AmountIndex = reader.GetOrdinal("Amount");
return new Bottle
{
BottleID = reader.GetInt32(bottleIDIndex),
Year = reader.GetInt32(YearIndex),
Price = reader.GetDecimal(PriceIndex),
Amount = reader.GetInt32(AmountIndex)
};
}
}
}
catch (Exception)
{
throw new ApplicationException("Lyckades inte uppdatera whiksymärket");
}
}
//Hamnar aldrig vid returnen nedan. Eftersom catchen fångar fel.
return null;
}
//Editerar en existerande modell
public static Bottle GetBottleUpdate(Bottle bottle)
{
using (SqlConnection conn = CreateConnection())
{
try
{
SqlCommand cmd = new SqlCommand("appSchema.usp_EditBottle", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(@"BottleID", SqlDbType.Int, 4).Value = bottle.BottleID;
cmd.Parameters.Add(@"Year", SqlDbType.Int, 4).Value = bottle.Year;
cmd.Parameters.Add(@"Price", SqlDbType.Decimal, 7).Value = bottle.Price;
cmd.Parameters.Add(@"Amount", SqlDbType.Int, 4).Value = bottle.Amount;
conn.Open();
cmd.ExecuteNonQuery();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var bottleIDIndex = reader.GetOrdinal("BottleID");
var YearIndex = reader.GetOrdinal("Year");
var PriceIndex = reader.GetOrdinal("Price");
var AmountIndex = reader.GetOrdinal("Amount");
return(new Bottle
{
BottleID = reader.GetInt32(bottleIDIndex),
Year = reader.GetInt32(YearIndex),
Price = reader.GetDecimal(PriceIndex),
Amount = reader.GetInt32(AmountIndex)
});
}
}
}
catch (Exception)
{
throw new ApplicationException("Lyckades inte uppdatera whiksymärket");
}
}
//Hamnar aldrig vid returnen nedan. Eftersom catchen fångar fel.
return(null);
}
public static void SaveBottleProperties(BottleTable.Bottle bottle)
{
ICollection <ValidationResult> validationModelResults;
if (!bottle.Validate(out validationModelResults))
{
var ex = new ValidationException("Objektet kunde inte valideras");
ex.Data.Add("ValidationResults", validationModelResults);
throw ex;
}
if (bottle.BottleID == 0)
{
BottleTable.BottleDAL.InsertBottleProperties(bottle);
}
else
{
BottleTable.BottleDAL.GetBottleUpdate(bottle);
}
}
public static void InsertBottleProperties(Bottle bottle)
{
using (SqlConnection conn = CreateConnection())
try
{
SqlCommand cmd = new SqlCommand("appSchema.usp_AddBottleProperties", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ModelID", SqlDbType.Int, 50).Value = bottle.ModelID;
cmd.Parameters.Add("@Year", SqlDbType.Int, 50).Value = bottle.Year;
cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = bottle.Price;
cmd.Parameters.Add("@Amount", SqlDbType.Int, 50).Value = bottle.Amount;
cmd.Parameters.Add("@BottleID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
//Öppnar anslutning till databasen samt "ExecuteNonQuery" kommandot för att "INSERT" till databasen
conn.Open();
cmd.ExecuteNonQuery();
bottle.BottleID = (int)cmd.Parameters["@BottleID"].Value;
}
catch (Exception)
{
throw new ApplicationException("Error i åtkomstlagret i databasen");
}
}