public bool ValidaEmailExistente(string email, int id)
{
IConnection conn = ConnectionFactory.GetConnection();
string sql = $@"
Select *
From {ClassManipulation.GetTableName<Usuario>()}
Where Email = {conn.SqlParameter}email
And EmpresaID = {EmpresaId}
And ({id} <> UsuarioId)
";
var command = conn.Db.CreateCommand();
var param = command.CreateParameter();
param.ParameterName = "email";
param.Value = email;
command.Parameters.Add(param);
command.CommandText = sql;
using (DbDataReader reader = command.ExecuteReader())
return(reader.Read());
}
public Usuario Login(string user, string pwd, int empresaId)
{
IConnection conn = ConnectionFactory.GetConnection();
string sql = $@"
Select *
From {ClassManipulation.GetTableName<Usuario>()}
Where Login = {conn.SqlParameter}user
And Senha = {conn.SqlParameter}pwd
And Ativo = 1
And EmpresaID = {empresaId}
";
var command = conn.Db.CreateCommand();
var param = command.CreateParameter();
param.ParameterName = "user";
param.Value = user;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "pwd";
param.Value = pwd;
command.Parameters.Add(param);
command.CommandText = sql;
using (DbDataReader reader = command.ExecuteReader())
return(DataReaderManipulation.DataReaderToClass <Usuario>(reader));
}
public T Insert(T model)
{
List <string> columns = new List <string>();
foreach (var item in ClassManipulation.GetColumns <T>())
{
columns.Add(item);
}
string sql = $" Insert Into {ClassManipulation.GetTableName<T>()} (";
for (var i = 0; i < columns.Count; i++)
{
sql += columns[i];
if (i + 1 != columns.Count)
{
sql += ",";
}
}
sql += ") values (";
for (var i = 0; i < columns.Count; i++)
{
sql += string.Concat(ConnectionFactory.SqlParameter, columns[i]);
if (i + 1 != columns.Count)
{
sql += ",";
}
}
sql += ")";
IConnection conn = ConnectionFactory.GetConnection();
var command = conn.Db.CreateCommand();
command.CommandText = sql;
foreach (var item in columns)
{
var param = command.CreateParameter();
param.Value = typeof(T).GetProperty(item).GetValue(model);
param.ParameterName = item;
command.Parameters.Add(param);
}
using (IDataReader reader = command.ExecuteReader())
{
return(model);
}
}
private void SetDefaultValues(T model)
{
if (((model as IChildEmpresaObject) != null) && EmpresaId.HasValue)
{
var empresaProp = ClassManipulation.GetColumn <T>("EmpresaId");
if (empresaProp != null)
{
empresaProp.SetValue(model, EmpresaId.Value);
}
}
if (((model as IChildUsuarioObject) != null) && UsuarioId > 0)
{
var usuarioProp = ClassManipulation.GetColumn <T>("UsuarioId");
if (usuarioProp != null)
{
usuarioProp.SetValue(model, UsuarioId);
}
}
}
public T Activate(T model)
{
var activateObject = model as IActivableObject;
if (activateObject != null)
{
var id = ClassManipulation.GetIdentityProp <T>();
if (id != null)
{
model = Find(Convert.ToInt32(id.GetValue(model)));
if (model != null)
{
(model as IActivableObject).Ativo = 1;
return(Update(model));
}
}
}
return(default(T));
}
public void Delete(T model)
{
string identityColumn = ClassManipulation.GetIdentityColumn <T>();
string sql = $@" Delete From {ClassManipulation.GetTableName<T>()}
Where {identityColumn} = {string.Concat(ConnectionFactory.SqlParameter, identityColumn)}";
IConnection conn = ConnectionFactory.GetConnection();
var command = conn.Db.CreateCommand();
var param = command.CreateParameter();
param.Value = typeof(T).GetProperty(identityColumn).GetValue(model);
param.ParameterName = identityColumn;
command.Parameters.Add(param);
command.CommandText = sql;
command.ExecuteNonQuery();
}
public T Update(T model)
{
List <string> columns = new List <string>();
string identityColumn = ClassManipulation.GetIdentityColumn <T>();
foreach (var item in ClassManipulation.GetColumns <T>())
{
columns.Add(item);
}
string sql = $" Update {ClassManipulation.GetTableName<T>()} Set ";
for (var i = 0; i < columns.Count; i++)
{
sql += columns[i] + " = " + string.Concat(ConnectionFactory.SqlParameter, columns[i]);
if (i + 1 != columns.Count)
{
sql += ",";
}
}
sql += $" Where {identityColumn} = {string.Concat(ConnectionFactory.SqlParameter, identityColumn)}";
IConnection conn = ConnectionFactory.GetConnection();
var command = conn.Db.CreateCommand();
columns.Add(identityColumn);
foreach (var item in columns)
{
var param = command.CreateParameter();
param.Value = typeof(T).GetProperty(item).GetValue(model);
param.ParameterName = item;
command.Parameters.Add(param);
}
command.CommandText = sql;
command.ExecuteNonQuery();
return(model);
}
public T UpdateImage(T model, HttpPostedFile imagem)
{
ConnectionFactory.BeginTransaction();
BeforeUpdate(model);
BeforeSave(model, BusinessObjectAcaoEnum.Update);
if (imagem != null)
{
SalvarImagem(model, imagem);
}
else
{
var find = Find((int)ClassManipulation.GetIdentityProp <T>().GetValue(model));
if (find != null)
{
if ((model as IFileUploadObject) != null)
{
(model as IFileUploadObject).Imagem = (find as IFileUploadObject).Imagem;
}
else
{
throw new BrokenRulesException("Não foi possível carregar a imagem cadastrada!");
}
}
else
{
throw new BrokenRulesException("Não foi possível carregar a imagem cadastrada!");
}
}
var retorno = GetDAO().Update(model);
ConnectionFactory.Commit();
SaveLog(model, LogSistemaAcaoEnum.Editar);
return(retorno);
}
public List <T> List()
{
string sql = $@"
Select *
From {ClassManipulation.GetTableName<T>()}";
if (typeof(T) is IChildEmpresaObject)
{
if (!EmpresaId.HasValue)
{
throw new BrokenRulesException("Não foi informado o código da empresa logada");
}
sql += $" Where EmpresaId == {EmpresaId.Value}";
}
IConnection conn = ConnectionFactory.GetConnection();
var command = conn.Db.CreateCommand();
command.CommandText = sql;
DbDataReader reader = command.ExecuteReader();
return(DataReaderManipulation.DataReaderMapToList <T>(reader));
}
public T Find(int identity)
{
string identityColumn = ClassManipulation.GetIdentityColumn <T>();
string sql = $@"
Select *
From {ClassManipulation.GetTableName<T>()}
Where {identityColumn} = {string.Concat(ConnectionFactory.SqlParameter, identityColumn)}";
IConnection conn = ConnectionFactory.GetConnection();
var command = conn.Db.CreateCommand();
var param = command.CreateParameter();
param.Value = identity;
param.ParameterName = identityColumn;
command.Parameters.Add(param);
command.CommandText = sql;
using (IDataReader reader = command.ExecuteReader())
{
return(DataReaderManipulation.DataReaderToClass <T>(reader));
}
}
public List <T> List(FilterObject <T> filter)
{
var model = filter.Model;
string sql = $@"
Select *
From {ClassManipulation.GetTableName<T>()}";
IConnection conn = ConnectionFactory.GetConnection();
var command = conn.Db.CreateCommand();
if (model != null && filter != null && filter.Properties.Count > 0)
{
sql += " Where ";
for (var i = 0; i < filter.Properties.Count; i++)
{
var prop = filter.Properties[i];
sql += prop.LogicalOperator;
var property = ClassManipulation.GetColumn <T>(prop.Property);
sql += property.Name + " "
+ prop.Operator + " "
+ ConnectionFactory.SqlParameter + property.Name;
var param = command.CreateParameter();
param.ParameterName = property.Name;
if (prop.OperatorEnum == OperatorEnum.Like)
{
param.Value = prop.PreAppend + property.GetValue(model) + prop.PosAppend;
}
else
{
param.Value = property.GetValue(model);
}
command.Parameters.Add(param);
}
if (typeof(T) is IChildEmpresaObject)
{
if (!EmpresaId.HasValue)
{
throw new BrokenRulesException("Não foi informado o código da empresa logada");
}
sql += $" And EmpresaId == {EmpresaId.Value}";
}
}
else
{
if (typeof(T) is IChildEmpresaObject)
{
if (!EmpresaId.HasValue)
{
throw new BrokenRulesException("Não foi informado o código da empresa logada");
}
sql += $" Where EmpresaId == {EmpresaId.Value}";
}
}
command.CommandText = sql;
using (DbDataReader reader = command.ExecuteReader())
return(DataReaderManipulation.DataReaderMapToList <T>(reader));
}
public T IdentityInsert(T model)
{
List <string> columns = new List <string>();
foreach (var item in ClassManipulation.GetColumns <T>())
{
columns.Add(item);
}
string sql = $" Insert Into {ClassManipulation.GetTableName<T>()} (";
for (var i = 0; i < columns.Count; i++)
{
sql += columns[i];
if (i + 1 != columns.Count)
{
sql += ",";
}
}
sql += ") values (";
for (var i = 0; i < columns.Count; i++)
{
sql += string.Concat(ConnectionFactory.SqlParameter, columns[i]);
if (i + 1 != columns.Count)
{
sql += ",";
}
}
sql += ")";
IConnection conn = ConnectionFactory.GetConnection();
var command = conn.Db.CreateCommand();
foreach (var item in columns)
{
var param = command.CreateParameter();
param.Value = typeof(T).GetProperty(item).GetValue(model);
param.ParameterName = item;
command.Parameters.Add(param);
}
command.CommandText = sql;
var identityProp = typeof(T).GetProperty(ClassManipulation.GetIdentityColumn <T>());
command.ExecuteNonQuery();
int pkValue = 0;
if (ConnectionFactory.ConnectionType == ConnectionEnum.MySql)
{
var cmmd = conn.Db.CreateCommand();
command.CommandText = "SELECT LAST_INSERT_ID()";
}
else if (ConnectionFactory.ConnectionType == ConnectionEnum.SqlServer)
{
var cmmd = conn.Db.CreateCommand();
command.CommandText = "SELECT SCOPE_IDENTITY()";
}
using (IDataReader reader = command.ExecuteReader())
{
if (reader != null && reader.Read())
{
pkValue = Convert.ToInt32(reader.GetInt64(0));
}
identityProp.SetValue(model, pkValue);
return(model);
}
}
