How to check if all the data in my list exists in the database?

Question

How to check if all the data in my list exists in the database?

Navigation

#1 by (3 votes)

2

I need to get the files that are in a list and compare them with the files that are in a table in the database, and know if the amount of files in my list is the same amount as the table.

 using Dapper;

 public bool ValidarPessoas(List <int> pessoas) {
  Dictionary < string, object > parametros = new Dictionary < string, object > ();
  parametros.Add("@pessoas", pessoas);
  parametros.Add("@quantidadePessoas", pessoas.Count);

  var query = @ " SELECT CASE WHEN EXISTS 
   (
    SELECT COUNT(ID) FROM dbo.[Pessoa] WHERE ID in (@pessoas) HAVING COUNT(Pessoa.ID) = @quantidadePessoas
   )
  THEN CAST(1 AS BIT)
  ELSE CAST(0 AS BIT)
  END ";

  string strConexao = ConfigurationManager.ConnectionStrings["conexao"].ConnectionString;
  using(var sqlConnection = new SqlConnection(strConexao))

  {
   return sqlConnection.QueryFirstOrDefault < bool > (query, parametros);
  }
 }

When I run this code, I get the error:

System.Data.SqlClient.SqlException: 'Incorrect syntax near', '.'

sql c# dapper

asked by anonymous 13.06.2018 / 19:58

1 answer

Downloading libraries by MAVEN with proxy How to test connection to SQL Server database

score 3 · Accepted Answer

In the dapper you do not have to () in IN (as done traditionally in a query), this is done directly.

Dapper allows you to pass in IEnumerable and parametrize automatically.

I've changed your code to get a bit more readable, realize that my query is simple and straightforward, it returns the amount of people you have on your list, so just compare if the total returned by the bank is the same as is on your list.

using Dapper;
using System.Linq;

public bool ValidarPessoas(List <int> pessoas) {
 var query = @ "SELECT COUNT(ID) 
                  FROM Pessoa 
                 WHERE ID 
                    IN @pessoas;"

 string strConexao = ConfigurationManager.ConnectionStrings["conexao"].ConnectionString;
 using(var sqlConnection = new SqlConnection(strConexao))

 {
  return sqlConnection.Query<int> (query, new {
    pessoas = pessoas
  }).Single() == pessoas.Count();
 }
}