public void Create(Franqueado entity)
{
using (var das = MySession.CreateDataAccessScope(true))
{
SqlCommand cmd = CreateCommand();
cmd.CommandText = "INSERT INTO FRANQUEADO (nif,nome,morada) OUTPUT INSERTED.ID VALUES(@Nif,@Nome,@Morada);";
SqlParameter p1 = new SqlParameter("@Nif", entity.Nif);
cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@Nome", entity.Nome);
cmd.Parameters.Add(p2);
SqlParameter p3 = new SqlParameter("@Morada", entity.Morada);
cmd.Parameters.Add(p3);
try
{
entity.Id = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine("Franchisee inserted with id: {0}", entity.Id);
}
catch (SqlException e)
{
Console.WriteLine("SQL Error inserting franchisee: " + e.GetBaseException().Message);
}
das.Commit(); // my vote is YES
}
}
public void ShouldTestUpdate()
{
Franqueado f = new Franqueado();
f.Nome = "Meu Super Moscavide";
f.Id = 1;
f.Nif = 123456781;
f.Morada = "R. Laureano de Oliveira 19 B, 1885-051 Lisboa";
using (FranqueadoSession s = new FranqueadoSession())
{
using (var das = s.CreateDataAccessScope(true))
{
IMapperFranqueado map = s.CreateMapperFranqueado();
Franqueado old = map.Read(1);
map.Update(f);
Franqueado newF = map.Read(1);
Assert.AreNotEqual(old.Morada, newF.Morada);
Assert.AreNotEqual(old.Nif, newF.Nif);
Assert.AreEqual(f.Morada, newF.Morada);
}
}
}
public void Delete(Franqueado key)
{
using (var ctx = new SI2_Bom_e_BaratoEntities())
{
var f = ctx.Franqueado.Find(key);
if (f != null)
{
try
{
ctx.HistoricoVendas.RemoveRange(ctx.HistoricoVendas.Where(x => x.franq_id == f.id));
ctx.Entrega.RemoveRange(ctx.Entrega.Where(x => x.franq_id == f.id));
ctx.ProdVendidoPorFranqueado.RemoveRange(ctx.ProdVendidoPorFranqueado.Where(x => x.franq_id == f.id));
ctx.Franqueado.Remove(f);
ctx.SaveChanges();
Console.WriteLine("Franchisee with ID {0} removed.", key);
}
catch (Exception e)
{
Console.WriteLine("SQL Error removing Franchisee: {0}", e.GetBaseException());
}
}
else
{
Console.WriteLine("Error removing Franchisee {0}", key);
}
}
}
public void Update(Franqueado entity)
{
using (var das = new DataAccessScope(true))
{
using (var ctx = new SI2_Bom_e_BaratoEntities())
{
var franq_found = ctx.Franqueado.Find(entity.id);
if (franq_found != null)
{
var f = (from a in ctx.Franqueado where a.id == entity.id select a).SingleOrDefault();
f.id = entity.id;
f.nif = entity.nif;
f.morada = entity.morada;
f.nome = entity.nome;
try
{
ctx.SaveChanges();
Console.WriteLine("Franchisee {0} updated.", entity.id);
}
catch (Exception e)
{
Console.WriteLine(e.GetBaseException());
}
}
else
{
Console.WriteLine("Error updating Franchisee {0}", entity.id);
}
}
das.Commit();
}
}
public void Update(Franqueado entity)
{
using (var das = MySession.CreateDataAccessScope(true)) // Porquê true?
{
SqlCommand cmd = CreateCommand();
cmd.CommandText = "UPDATE FRANQUEADO SET Nome=@Nome, Morada=@Morada, Nif=@Nif WHERE Id=@Id;";
SqlParameter p1 = new SqlParameter("@Nome", entity.Nome);
cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@Morada", entity.Morada);
cmd.Parameters.Add(p2);
SqlParameter p3 = new SqlParameter("@Nif", entity.Nif);
cmd.Parameters.Add(p3);
SqlParameter p4 = new SqlParameter("@Id", entity.Id);
cmd.Parameters.Add(p4);
int nRows = cmd.ExecuteNonQuery();
das.Commit();
if (nRows > 0)
{
Console.WriteLine("Franchisee {0} updated.", entity.Nif);
}
else
{
Console.WriteLine("Error updating franchisee {0}", entity.Nif);
}
}
}
public void Create(Franqueado entity)
{
using (var das = new DataAccessScope(true))
{
using (var ctx = new SI2_Bom_e_BaratoEntities())
{
ctx.Franqueado.Add(entity);
ctx.SaveChanges();
}
das.Commit();
}
}
private void InsertFranqueado()
{
Franqueado f = PromptUserForFranchiseeInfo();
using (var das = new DataAccessScope(true))
{
IMapperFranqueado franq = new MapperFranqueado();
franq.Create(f);
das.Commit();
Console.WriteLine("Franchisee inserted with id: {0}.", f.id);
}
}
public void Delete(Franqueado a)
{
SqlCommand cmd = this.CreateCommand("franq_del");
SqlParameter param;
cmd.CommandType = CommandType.StoredProcedure;
param = cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Decimal, 4));
param.Value = a.id;
cmd.ExecuteNonQuery();
MySession.EndTransaction(true, isMyTransaction);
MySession.CloseConnection(isMyConnection);
}
public void DeleteEverythingAboutFranqueado(Franqueado a)
{
SqlCommand cmd = CreateCommand();
cmd.CommandText = "DELETE FROM Franqueado_Vende_Produto Where id_franqueado" + a.id;
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM Franqueado Where id_franqueado" + a.id;
cmd.ExecuteNonQuery();
MySession.EndTransaction(true, isMyTransaction);
MySession.CloseConnection(isMyConnection);
}
public void ShouldTestRead()
{
using (FranqueadoSession s = new FranqueadoSession())
{
using (var das = s.CreateDataAccessScope(true))
{
IMapperFranqueado map = s.CreateMapperFranqueado();
Franqueado f = map.Read(1);
Assert.IsTrue(f.Id == 1);
Assert.IsTrue(f.Nif > 0);
das.Commit();
}
}
}
public IEnumerable <Franqueado> GetAll()
{
using (var das = MySession.CreateDataAccessScope(true))
{
SqlCommand cmd = CreateCommand();
cmd.CommandText = "select * from franqueado;";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
yield return(Franqueado.Parse(dr));
}
dr.Close();
das.Commit();
}
}
private Franqueado PromptUserForFranchiseeInfo()
{
Franqueado f = new Franqueado();
Console.WriteLine("Insert info of the franchisee");
Console.Write("Nif (9 digits) : ");
f.nif = (int)GetInput(typeof(int));
Console.Write("Name of Franchisee : ");
f.nome = (String)GetInput(typeof(String));
Console.Write("Address :");
f.morada = (String)GetInput(typeof(String));
return(f);
}
public void Delete(Franqueado entity)
{
SqlCommand cmd = CreateCommand();
cmd.CommandText = "Delete from FRANQUEADO WHERE @Id = Id";
SqlParameter p4 = new SqlParameter("@Id", entity.Id);
cmd.Parameters.Add(p4);
int nRows = cmd.ExecuteNonQuery();
if (nRows > 0)
{
Console.WriteLine("Francisee with ID {0} removed.", entity.Id);
}
}
private void UpdateFranqueado()
{
Console.WriteLine("Insert id of franchisee to update");
int key = (int)GetInput(typeof(int));
Franqueado f = PromptUserForFranchiseeInfo();
f.id = key;
using (var das = new DataAccessScope(true))
{
IMapperFranqueado franq = new MapperFranqueado();
franq.Update(f);
das.Commit();
}
}
public void ShouldTestDelete()
{
Franqueado f = new Franqueado();
f.Morada = "Morada Teste";
f.Nif = 123456777;
f.Nome = "Nome Teste";
using (FranqueadoSession s = new FranqueadoSession())
{
using (var das = s.CreateDataAccessScope(true))
{
IMapperFranqueado map = s.CreateMapperFranqueado();
map.Create(f);
map.Delete(f);
map.Read(f.Id);
}
}
}
public void ShouldTestCreate()
{
Franqueado f = new Franqueado();
f.Nif = 123456798;
f.Nome = "B&B Oriente";
f.Morada = "Rua Conselheiro Lopo Vaz AB 1ºD";
using (FranqueadoSession s = new FranqueadoSession())
{
using (var das = s.CreateDataAccessScope(true))
{
IMapperFranqueado map = s.CreateMapperFranqueado();
map.Create(f);
Assert.IsTrue(f.Id > 0);
//Rollback
}
}
}
public void Update(Franqueado a)
{
SqlCommand cmd = this.CreateCommand("franq_up");
SqlParameter param;
cmd.CommandType = CommandType.StoredProcedure;
param = cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Decimal, 4));
param.Value = a.id;
param = cmd.Parameters.Add(new SqlParameter("@nif", SqlDbType.Decimal, 10));
param.Value = a.nif;
param = cmd.Parameters.Add(new SqlParameter("@nome", SqlDbType.VarChar, 100));
param.Value = a.nome;
param = cmd.Parameters.Add(new SqlParameter("@morada", SqlDbType.VarChar, 100));
param.Value = a.morada;
cmd.ExecuteNonQuery();
MySession.EndTransaction(true, isMyTransaction);
MySession.CloseConnection(isMyConnection);
}
public Franqueado Read(int id)
{
Franqueado f = new Franqueado();
using (var das = MySession.CreateDataAccessScope(true)) // Porquê true?
{
SqlCommand cmd = CreateCommand();
cmd.CommandText = "SELECT TOP 1 @Nome=Nome,@Morada = Morada, @Nif = Nif from Franqueado WHERE @Id=Id";
SqlParameter p1 = cmd.Parameters.Add("@Nome", SqlDbType.VarChar, 100);
p1.Direction = ParameterDirection.Output;
SqlParameter p2 = cmd.Parameters.Add("@Morada", SqlDbType.VarChar, 100);
p2.Direction = ParameterDirection.Output;
SqlParameter p3 = cmd.Parameters.Add("@Nif", SqlDbType.Decimal);
p3.Direction = ParameterDirection.Output;
p3.Precision = 10;
p3.Scale = 0;
SqlParameter p4 = new SqlParameter("@Id", id);
cmd.Parameters.Add(p4);
cmd.ExecuteNonQuery();
if (p1.Value is System.DBNull)
{
Console.WriteLine("No franchisee found with id " + id);
}
else
{
f.Id = id;
f.Nome = (string)p1.Value;
f.Morada = (string)p2.Value;
f.Nif = Convert.ToInt32(p3.Value);
das.Commit();
}
}
return(f);
}
