How do I verify that all data on my list exists in the database?

Asked

Viewed 77 times

2

I need to take the files that are in a list and compare with the files that are in a table in the database, and know if the amount of files that is 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 ','.'

c# sql dapper

  • You can include the value of the variable in the question query before executing?

  • I posted an answer, the idea is good, but I do not know if the code has typo, if you have correct please :)

  • Thank you so much for the answer, Marconi. We took your code as the basis for perfecting ours. It worked perfectly

  • Nice Emmanuel, too good I could help. If there’s something wrong with my code please edit my answer because it might be for future readers of the post!

1 answer

3


In the there’s no need to put () in(as is traditionally done in a query), this is done directly.

Dapper allows you to pass Ienumerable and parametrize automatically your query.

I changed your code to be a little more readable, realize that my query is simple and direct, it returns the amount of people you have on your list, with this just compare if the total returned by the bank is the same as in 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();
 }
}

Browser other questions tagged c# sql dapper

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