Liaison d'une liste vide ou d'une valeur nulle à un paramètre de table dans une procédure stockée (.NET)

Question

J'ai créé une procédure stockée qui prend en paramètre une table de valeurs qui est une table avec une seule colonne de type int. L'idée est de simplement passer une liste d'identifiants dans la procédure stockée et permettre à la procédure stockée de travailler avec les données. Cependant, dans le cas où il n'y a pas de données à passer, je rencontre des problèmes (les choses fonctionnent correctement quand j'ai des données). Je convertis une List en un IEnumerable, et lie cela au paramètre de valeur de table pour la procédure stockée. J'ai essayé de lier une List vide, ce qui a provoqué l'erreur :

System.ArgumentException: Il n'y a aucun enregistrement dans l'énumération SqlDataRecord. Pour envoyer un paramètre à valeurs de table sans lignes, utilisez une référence nulle à la place.

J'ai ensuite essayé de lier une valeur null (ce que je pensais être le sens du message ci-dessus), mais cela n'a entraîné qu'un message d'erreur différent

System.NotSupportedException: La valeur DBNull pour le paramètre '@MainItemIdList' n'est pas prise en charge. Les paramètres à valeurs de table ne peuvent pas être DBNull.

Il ne semble pas possible de déclarer le paramètre de valeur de table comme nullable dans la déclaration de la procédure stockée. Quelle est la méthode correcte pour lier une liste vide à un paramètre de valeur de table?

Answer 1

Le truc, c'est : ne pas passer du tout le paramètre. La valeur par défaut pour un paramètre de type table est une table vide.

C'est dommage que le message d'exception soit si peu utile.

Answer 2

J'étais un peu confus par ce que signifie l'affirmation ''ne pas passer le paramètre''. Ce qui finit par fonctionner pour Entity Framework ExecuteSqlCommandAsync() est ceci :

  new SqlParameter("votreNomDeParamètre", SqlDbType.Structured)
  {
      Direction = ParameterDirection.Input,
      TypeName = "votreTypeUdt",
      Value = null
  };

Cela passera le paramètre comme étant 'par défaut'.

Answer 3

Ne pas passer de valeur fonctionne, mais pas dans le cas où j'avais plusieurs paramètres de valeurs de table à passer dans la procédure. J'ai résolu le problème en spécifiant une valeur de DEFAULT dans ma chaîne de requête. Par exemple,

string sqlQuery = "[dbo].[GetOrderData] @QueueId";

if (OrderIdList.Any())
{
    sqlQuery = sqlQuery + ", @OrderIdList";
}
else
{
    sqlQuery = sqlQuery + ", DEFAULT";
}

if (RegionIdList.Any())
{
    sqlQuery = sqlQuery + ", @RegionIdList";
}
else
{
    sqlQuery = sqlQuery + ", DEFAULT";
}

Bravo à http://www.sommarskog.se/arrays-in-sql-2008.html#Invoking où j'ai trouvé la solution à ce problème.

Answer 4

Je crois que vous ne pouvez pas simplement prendre une List (ou List dans ce cas) et la retourner en tant qu'IEnumerable. Vous devez créer un objet qui hérite de List et implémente IEnumerable. Ensuite, vous pouvez définir ce qui suit :

IEnumerator IEnumerable.GetEnumerator()
{
    // code pour créer et retourner un objet SqlDataRecord
    // L'objet SqlDataRecord doit avoir un schéma qui correspond
    // au type de table défini par l'utilisateur pour lequel la paire à valeurs de table est déclarée
}

Lorsque ExecuteNonQuery est appelée pour exécuter une procédure stockée avec un paramètre SqlDbType.Structured (paramètre à valeurs de table), la collection passée pour la valeur du paramètre est censée implémenter IEnumerable afin que IEnumerable.GetEnumerator puisse être appelé en interne pour récupérer chaque nouvel enregistrement à envoyer au serveur.

Je ne suis pas sûr que cela permette de passer à travers l'énumération vide, mais je crois que c'est la manière dont vous êtes censé créer une collection pour un TVP.

Answer 5

Je reçois l'erreur lorsque je passe un System.Collections.Generic.IEnumerable vide mais ça fonctionne bien lorsque je passe un List vide à la place.