public List <Marca> GetAll()
{
return(BaseSQL.Execute("SELECT * FROM Marcas")
.AsEnumerable()
.Select(s => new Marca(s))
.ToList());
}
public Marca GetById(int id)
{
return(BaseSQL.Execute(String.Format(@"SELECT * FROM Marcas WHERE MarcaId = {0}", id))
.AsEnumerable()
.Select(s => new Marca(s))
.FirstOrDefault());
}
public List <Marca> Exist(int id, Marca item)
{
return(BaseSQL.Execute(String.Format(@"SELECT * FROM Marcas WHERE MarcaId = {0} OR MarcaId = {1} OR Nome = '{2}'", id, item.Id, item.Nome))
.AsEnumerable()
.Select(s => new Marca(s.Field <int>("MarcaId"), s.Field <string>("Nome")))
.ToList());
}
public int Insert(Marca item)
{
BaseSQL.Execute(String.Format("INSERT INTO Marcas VALUES({0},'{1}')",
item.Id,
item.Nome));
return(item.Id);
}
public List <Marca> Exist(Marca item)
{
return(BaseSQL.Execute(String.Format(@"SELECT MarcaId, Nome
FROM Marcas
WHERE MarcaId = {0} OR Nome = '{1}'", item.Id, item.Nome))
.AsEnumerable()
.Select(s => new Marca(s))
.ToList());
}
public List <Patrimonio> GetAll()
{
return(BaseSQL.Execute(String.Format(@"SELECT Patrimonios.*,Marcas.Nome as Marca
FROM Patrimonios
INNER JOIN Marcas ON Patrimonios.MarcaId = Marcas.MarcaId"))
.AsEnumerable()
.Select(s => new Patrimonio(s))
.ToList());
}
public int Insert(Patrimonio item)
{
DataTable result = BaseSQL.Execute(String.Format("INSERT INTO Patrimonios VALUES('{0}',{1},{2}); SELECT SCOPE_IDENTITY();",
item.Nome,
item.Marca != null ? item.Marca.Id : item.MarcaId,
item.Descricao != null ? String.Format("'{0}'", item.Descricao) : "null"));
return(Decimal.ToInt32((Decimal)result.Rows[0][0]));
}
public Patrimonio GetById(int id)
{
return(BaseSQL.Execute(String.Format(@"SELECT Patrimonios.*,Marcas.Nome as Marca
FROM Patrimonios
INNER JOIN Marcas ON Patrimonios.MarcaId = Marcas.MarcaId
WHERE PatrimonioId = {0}", id))
.AsEnumerable()
.Select(s => new Patrimonio(s))
.FirstOrDefault());
}
public void Update(int id, Marca item)
{
string query = "UPDATE Marcas SET ";
query += item.Id != 0 ? String.Format("MarcaId = {0},", item.Id) : String.Empty;
query += !String.IsNullOrEmpty(item.Nome) ? String.Format("Nome = '{0}'", item.Nome) : String.Empty;
if (query[query.Length - 1] == ',')
{
query = query.Remove(query.Length - 1);
}
query += String.Format(" WHERE MarcaId = {0}", id);
BaseSQL.Execute(query);
}
public void Update(int id, Patrimonio item)
{
String query = "UPDATE Patrimonios SET ";
query += !String.IsNullOrEmpty(item.Nome) ? String.Format("Nome = '{0}',", item.Nome) : String.Empty;
query += item.MarcaId != 0 ? String.Format("MarcaId = {0},", item.Marca.Id) :
item.Marca != null?String.Format("MarcaId = {0},", item.Marca.Id) : String.Empty;
query += !String.IsNullOrEmpty(item.Descricao) ? String.Format("Descrição = '{0}',", item.Descricao) : String.Empty;
if (query[query.Length - 1] == ',')
{
query = query.Remove(query.Length - 1);
}
query += String.Format(" WHERE PatrimonioId = {0}", id);
BaseSQL.Execute(query);
}
public Marca GetByItem(Marca item)
{
string query = @"SELECT * FROM Marcas WHERE ";
if (item.Id != 0 && !String.IsNullOrEmpty(item.Nome))
{
query += String.Format("MarcaId = {0} AND Nome = '{1}'", item.Id, item.Nome);
}
else if (item.Id != 0)
{
query += String.Format("MarcaId = {0}", item.Id);
}
else
{
query += String.Format("Nome = {0}", item.Nome);
}
return(BaseSQL.Execute(query).AsEnumerable()
.Select(s => new Marca(s))
.FirstOrDefault());
}
public void Delete(int id)
{
BaseSQL.Execute(String.Format("DELETE FROM Marcas WHERE MarcaId = {0}", id));
}
public void Delete(int id)
{
BaseSQL.Execute(String.Format("DELETE FROM Patrimonios WHERE PatrimonioId = {0}", id));
}
