In a project, I have some standard methods, for example:
//Execute Return List DapperORM.RetornoList<EmployeeModel> <=
public static IEnumerable<T> RetornoList<T>(string procedureName, DynamicParameters param = null)
{
using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
sqlCon.Open();
return sqlCon.Query<T>(procedureName, param, commandType: CommandType.StoredProcedure);
}
}
//Executa uma consulta sql pura para 1 registro
public static T ExecuteSqlPuroComRetorno<T>(string consultaSql)
{
using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
sqlCon.Open();
var sql = string.Format(consultaSql);
return sqlCon.Query<T>(sql).SingleOrDefault();
}
}
//Execute Without Return
public static void ExecuteSemRetorno(string procedureName, DynamicParameters param = null)
{
using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
sqlCon.Open();
sqlCon.Execute(procedureName, param, commandType: CommandType.StoredProcedure);
}
}
//Execute Return Scalar DapperORM.ExecuteComRetorno<int>(_,_)
public static T ExecuteComRetorno<T>(string procedureName, DynamicParameters param = null)
{
using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
sqlCon.Open();
return (T) Convert.ChangeType(sqlCon.ExecuteScalar(procedureName, param, commandType:
CommandType.StoredProcedure), typeof(T));
}
}
When there is a need to query where I have a relationship between multiple tables, I need to map the classes, for example:
EXAMPLE to list records from multiple tables
public static IEnumerable<ClienteModel> BuscarRegistroComJoin(int? EmployeeID)
{
//1 RECEBE OS PARÂMETROS
var parametros = new
{
EmployeeID
};
using (var conexao = new SqlConnection(connectionString))
{
conexao.Open();
//2 RECEBE AS CLASSES QUE VAI SER UTLIZADO NO JOIN, EXISTE UMA RELAÇÃO ENTRE ELES
return conexao.Query<ClienteModel, TelefoneModel, EmpresaModel, ClienteModel>(
"spr_BuscaRegistroComJoins", (cliente, telefone, empresa) =>
{
cliente.Telefones = telefone;
cliente.Empresa = empresa;
return cliente;
}, parametros, commandType: CommandType.StoredProcedure, splitOn: "IdCliente, IdTelefone, IdEmpresa");
}
}
I would like to know the opinion on if, instead of doing a procedure with joins, a View was made that would return the information and in the same class there was a new method, would it be a bad option? this way you could use the standard methods to make queries, filters, without having to make new queries.
namespace DapperProjeto.Models
{
//modelo atual
public class ClienteModel
{
public int IdCliente { get; set; }
public string Nome { get; set; }
public int IdEmpresa { get; set; }
public TelefoneModel Telefones { get; set; }
public EmpresaModel Empresa { get; set; }
}
//modelo dúvida
public class ClienteViewModel
{
public int IdCliente { get; set; }
public string Nome { get; set; }
public int IdTelefone { get; set; }
public string Numero { get; set; }
public int IdEmpresa { get; set; }
public string NomeEmpresa { get; set; }
}
}