SELECT 3 different tables using DAPPER?

5

First of all I would like to say that I am a beginner in programming, and I am developing my first project. Using ASP.NET MVC Technology.

I need to query in 3 different tables: User, Country and State with the following fields

User: UserId, Name, Email, Password, DataCadastro, StateId

Country: CountryName, Name, Acronym

Status: StatusId, Name, Acronym, CountryId

How can I do this using Dapper?

    
asked by anonymous 14.02.2016 / 21:08

2 answers

4

First of all, it's important to say that Dapper works great as an add-on to the Entity Framework. Using only Dapper in your application can lead to a loss of productivity, since Dapper does not have, for example, lazy load, incremental database control, and dynamic SQL generation. Everything is up to the programmer, who has to write all the bank interface statements manually.

For this answer, I will assume that you will consider using both. No problem not to use, but the development will be more laborious and I hope to be able to show it here.

With this in mind, we can write a quick guide to Dapper.

Using with the Entity Framework

Dapper is basically a library that extends IDbConnection . Starting a new project with Entity Framework, using Dapper is quite simple:

using Dapper;

private EntityFrameworkContext context = new EntityFrameworkContext();
...

db.Database.Connection.Query(); // Query já é um dos métodos do Dapper.

Selecting

I'll use Pais for the example.

One Country

var umPais = context.Database.Connection.Query<Pais>("select * from Paises where PaisId = @Id", new { Id = 1 }).FirstOrDefault();

All Countries

var todosOsPaises = context.Database.Connection.Paises.Query<List<Pais>>("select * from Paises");

Joins

I'll now use Estado and Pais :

var sql = @"select e.EstadoId, e.Nome, e.Sigla, e.PaisId, p.PaisId, p.Nome, p.Sigla
            from Estados e
            inner join Paises p on e.PaisId = p.PaisId";

var estadosEPaises = db.Database.Connection
                       .Query<Estado, Pais, Estado>(sql, 
                                                    (e, p) => {
                                                                  e.Pais = p;
                                                                  return e;
                                                    },
                                                    splitOn: "PaisId");

Inserting

var resultadosAfetados = db.Database.Connection.Execute("insert into Paises (Nome, Sigla) values (@Nome, @Sigla)", new { Nome = "Brasil", Sigla = "BR" });

You can enter several:

var resultadosAfetados = db.Database.Connection.Execute("insert into Paises (Nome, Sigla) values (@Nome, @Sigla)", new[] { { Nome = "Brasil", Sigla = "BR" }, { Nome = "Portugal", Sigla = "PT" } });

Updating

Same thing:

var resultadosAfetados = db.Database.Connection.Execute("update Paises set Nome = @Nome where Sigla = @Sigla", new { Nome = "Brazil", Sigla = "BR" });

Excluding

Do I need to place?

Facilitating

Typing insertion, deletion, etc. methods can be tedious, so the Dapper team has created a method that does not require writing insertions and updates:

    using Dapper.Contrib.Extensions;

    public void Persistir(IDbConnection connection, Usuario usuario) 
    {
        if (usuario.UsuarioId == null) {
            SqlMapperExtensions.Insert(connection, usuario);
        }
        else {
            SqlMapperExtensions.Update(connection, usuario);
        }
    }
  

I saw that there is a Dapper.EntityFramework in the Dapper font. Why do not you use it?

Because it contains only two typologies to work properly with geographic and geometric coordinates. It has little use in the examples that are part of the question.

    
16.02.2016 / 02:53
2

If you just want to query the data without doing any processing of the information before returning, I think the best thing to do is to use inner join , so that the information in the tables is not all in memory until the query ends, and compared to other types of queries, is the most efficient.

select tab_1.field, tab_2.field, tab_3.field from tab_1 inner join tab_2 on tab_2.id = tab_1.id inner join tab_3 on tab_3.id = tab_2.id where ...

    
14.02.2016 / 21:33