SELECT 3 different tables using DAPPER?

Asked

Viewed 1,283 times

5

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

I need to make a query in 3 different tables: User, Parents and Status with the following fields

User: User, Name, Email, Password, Datacadastro, Estadoid

Parents: Paisid, Nome, Sigla

State: Estadoid, Nome, Sigla, Paisid

How can I do this using Dapper ?

asp.net-mvc dapper

  • Add the schema of the tables and the relationships between them for easy analysis.

2 answers

4


First, it is important to say that Dapper works very well as an addition to the Entity Framework. Using only Dapper in your application can result in a loss of productivity, since Dapper does not have, for example, lazy load, incremental bank control and dynamic SQL generation. Everything is the responsibility of the programmer, who has to write all the interface sentences with the bank manually.

For this answer, I’ll assume you’re considering using both. No problem not using, but the development will get more work 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 example.

A 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 use it now 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 insert 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

I need to put?

Facilitating

Write methods of insertion, deletion, etc., can be well tedious, so the Dapper team created a method that makes it not necessary to write inserts 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 source. Why don’t you use?

Because it contains only two types to work properly with geographical and geometric coordinates. Has little use in the examples that are part of the question.

  • Thank you for that excellent reply. In case I am wanting to avoid EF because it is a small project, then what would be better, use pure ADO with stored procedures ?

  • But the EF serves well both small and large projects. You can model the domain with EF and then switch it all to a Dapper repository to feel it’s better that way, but opinionated, I don’t think it’s a good one.

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 the inner join, because this way the information of the tables does not stay in memory until the end of the query, and compared to other types of query, it 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 ...

Browser other questions tagged asp.net-mvc dapper

You are not signed in. Login or sign up in order to post.