Entité Cadre De La Procédure Stockée Tableau De La Valeur Du Paramètre

Question

J'essaie d'appeler une procédure stockée qui accepte un tableau de valeur de paramètre. Je sais que ce n'est pas directement pris en charge dans le Cadre de l'Entité, mais encore de ce que je comprends, vous pouvez le faire en utilisant le ExecuteStoreQuery de commande hors de la ObjectContext. J'ai un générique de l'entité cadre référentiel dans lequel j'ai le texte suivant ExecuteStoredProcecdure méthode:

public IEnumerable<T> ExecuteStoredProcedure<T>(string procedureName, params object[] parameters)
{
    StringBuilder command = new StringBuilder();
    command.Append("EXEC ");
    command.Append(procedureName);
    command.Append(" ");

    // Add a placeholder for each parameter passed in
    for (int i = 0; i < parameters.Length; i++)
    {
        if (i > 0)
            command.Append(",");

        command.Append("{" + i + "}");
    }

    return this.context.ExecuteStoreQuery<T>(command.ToString(), parameters);
}

La chaîne de commande se termine comme ceci:

EXEC someStoredProcedureName {0},{1},{2},{3},{4},{5},{6},{7}

J'ai essayé cette méthode sur une procédure stockée qui accepte une table d'une valeur de paramètre et il se casse. J'ai lu ici que les paramètres nécessaires pour être de type SqlParameter et la table d'une valeur de paramètre doit avoir la SqlDbType ensemble Structuré. J'ai donc fait cela et je reçois un message d'erreur indiquant:

The table type parameter p6 must have a valid type name

Donc, j'ai mis le SqlParameter.TypeName pour le nom du type défini par l'utilisateur que j'ai créé sur la base de données et puis, quand j'exécute la requête j'ai le vraiment utile d'erreur:

Incorrect syntax near '0'.

Je peux obtenir l'exécution de la requête si je revenir à ADO.NET et et exécuter un lecteur de données, mais j'espérais pour le faire fonctionner en utilisant les données de contexte.

Est-il possible de passer un tableau de la valeur du paramètre à l'aide de ExecuteStoreQuery? Aussi, je me suis fait à l'aide de Entity Framework Code First et le casting du DbContext pour un ObjectContext pour obtenir le ExecuteStoreQuery méthode disponible. Est-ce nécessaire ou peut-on le faire à l'encontre de la DbConext ainsi?

Answer 1

Mise à JOUR

j'ai ajouté le support pour le Package Nuget - https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4,EF5,EF6)

découvrez le GitHub de référentiel pour des exemples de code.

Légèrement hors de question, mais n'en est pas moins utile pour les personnes qui tentent de passe Défini par l'Utilisateur des Tables dans un magasin de proc. Après avoir joué avec Nick exemple et d'autres Stackoverflow posts, j'ai trouvé ceci:

  class Program
{
    static void Main(string[] args)
    {
        var entities = new NewBusinessEntities();

        var dt = new DataTable();
        dt.Columns.Add("WarningCode");
        dt.Columns.Add("StatusID");
        dt.Columns.Add("DecisionID");
        dt.Columns.Add("Criticality");

        dt.Rows.Add("EO01", 9, 4, 0);
        dt.Rows.Add("EO00", 9, 4, 0);
        dt.Rows.Add("EO02", 9, 4, 0);

        var caseId = new SqlParameter("caseid", SqlDbType.Int);
        caseId.Value = 1;

        var userId = new SqlParameter("userid", SqlDbType.UniqueIdentifier);
        userId.Value = Guid.Parse("846454D9-DE72-4EF4-ABE2-16EC3710EA0F");

        var warnings = new SqlParameter("warnings", SqlDbType.Structured);
        warnings.Value= dt;
        warnings.TypeName = "dbo.udt_Warnings";

        entities.ExecuteStoreProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);

    }
}

public static class ObjectContextExt
{
    public static void ExecuteStoreProcedure(this ObjectContext context, string storeProcName, params object[] parameters)
    {
        string command = "EXEC " + storeProcName + " @caseid, @userid, @warnings";

        context.ExecuteStoreCommand(command, parameters);
    }

}

et le Magasin proc ressemble à la suivante...

ALTER PROCEDURE [dbo].[usp_RaiseWarnings_rs]
(
     @CaseID int 
    ,@UserID uniqueidentifier = '846454D9-DE72-4EF4-ABE2-16EC3710EA0F' --Admin
    ,@Warnings dbo.udt_Warnings READONLY
)
AS

et Définis par l'Utilisateur Table se présente comme suit...

CREATE TYPE [dbo].[udt_Warnings] AS TABLE(
[WarningCode] [nvarchar](5) NULL,
[StatusID] [int] NULL,
[DecisionID] [int] NULL,
[Criticality] [int] NULL DEFAULT ((0))

)

Les contraintes que j'ai trouvé inclure:

1. Les paramètres que vous passez dans ExecuteStoreCommand avoir à être dans l'ordre avec les paramètres dans votre magasin proc
2. Vous devez passer chaque colonne à votre Défini par l'Utilisateur de la Table, même si elles ont des valeurs par défaut. Il semble donc que je ne pouvais pas avoir une IDENTITÉ(1,1) colonne not NULL sur mon UDT

Answer 2

Je veux partager ma solution à ce problème:

J'ai procédures stockées avec plusieurs tables de paramètres de valeur et j'ai trouvé que si vous appelez cela de cette façon:

var query = dbContext.ExecuteStoreQuery<T>(@"
EXECUTE [dbo].[StoredProcedure] @SomeParameter, @TableValueParameter1, @TableValueParameter2", spParameters[0], spParameters[1], spParameters[2]);
var list = query.ToList();

vous obtenez une liste avec pas de documents.

Mais j'ai joué avec plus et cette ligne m'a donné une idée:

var query = dbContext.ExecuteStoreQuery<T>(@"
EXECUTE [dbo].[StoredProcedure] 'SomeParameterValue', @TableValueParameter1, @TableValueParameter2",  spParameters[1], spParameters[2]);
var list = query.ToList();

J'ai changé le paramètre @SomeParameter avec sa valeur réelle 'SomeParameterValue' dans le texte de la commande. Et cela a fonctionné :) Cela signifie que si nous avons quelque chose d'autre que SqlDbType.Structuré dans notre paramètres, il n'est pas tous les passer correctement et nous ne recevons rien. Nous avons besoin de remplacer les paramètres avec leurs valeurs.

Donc, ma solution se présente comme suit:

public static List<T> ExecuteStoredProcedure<T>(this ObjectContext dbContext, string 

    storedProcedureName, params SqlParameter[] parameters)
            {
                var spSignature = new StringBuilder();
                object[] spParameters;
                bool hasTableVariables = parameters.Any(p => p.SqlDbType == SqlDbType.Structured);

                spSignature.AppendFormat("EXECUTE {0}", storedProcedureName);
                var length = parameters.Count() - 1;

                if (hasTableVariables)
                {
                    var tableValueParameters = new List<SqlParameter>();

                    for (int i = 0; i < parameters.Count(); i++)
                    {
                        switch (parameters[i].SqlDbType)
                        {
                            case SqlDbType.Structured:
                                spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
                                tableValueParameters.Add(parameters[i]);
                                break;
                            case SqlDbType.VarChar:
                            case SqlDbType.Char:
                            case SqlDbType.Text:
                            case SqlDbType.NVarChar:
                            case SqlDbType.NChar:
                            case SqlDbType.NText:
                            case SqlDbType.Xml:
                            case SqlDbType.UniqueIdentifier:
                            case SqlDbType.Time:
                            case SqlDbType.Date:
                            case SqlDbType.DateTime:
                            case SqlDbType.DateTime2:
                            case SqlDbType.DateTimeOffset:
                            case SqlDbType.SmallDateTime:
                                // TODO: some magic here to avoid SQL injections
                                spSignature.AppendFormat(" '{0}'", parameters[i].Value.ToString());
                                break;
                            default:
                                spSignature.AppendFormat(" {0}", parameters[i].Value.ToString());
                                break;
                        }

                        if (i != length) spSignature.Append(",");
                    }
                    spParameters = tableValueParameters.Cast<object>().ToArray();
                }
                else
                {
                    for (int i = 0; i < parameters.Count(); i++)
                    {
                        spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
                        if (i != length) spSignature.Append(",");
                    }
                    spParameters = parameters.Cast<object>().ToArray();
                }

                var query = dbContext.ExecuteStoreQuery<T>(spSignature.ToString(), spParameters);


                var list = query.ToList();
                return list;
            }

Le code sûrement pourrait être plus optimisé, mais j'espère que cela aidera.

Answer 3

La DataTable est le seul moyen, mais la construction d'un DataTable et de les remplir manuellement est moche dans cette ère moderne de la 4K téléviseurs et les Grands Collisionneurs de Hadrons. D'ailleurs, pourquoi utiliser 10 lignes de code quand vous pouvez utiliser 100? ;-) Je voulais définir ma DataTable directement à partir de mon IEnumerable dans un style similaire à EF modèle du générateur de truc. Donc:

var whatever = new[]
            {
                new
                {
                    Id = 1,
                    Name = "Bacon",
                    Foo = false
                },
                new
                {
                    Id = 2,
                    Name = "Sausage",
                    Foo = false
                },
                new
                {
                    Id = 3,
                    Name = "Egg",
                    Foo = false
                },
            };

            //use the ToDataTable extension method to populate an ado.net DataTable
            //from your IEnumerable<T> using the property definitions.
            //Note that if you want to pass the datatable to a Table-Valued-Parameter,
            //The order of the column definitions is significant.
            var dataTable = whatever.ToDataTable(
                whatever.Property(r=>r.Id).AsPrimaryKey().Named("item_id"),
                whatever.Property(r=>r.Name).AsOptional().Named("item_name"),
                whatever.Property(r=>r.Foo).Ignore()
                );

J'ai posté la même chose sur dontnetfiddle: https://dotnetfiddle.net/ZdpYM3 (notez que vous ne pouvez pas l'exécuter, il y parce que pas tous les assemblages sont chargés dans le fiddle)

Answer 4

Changer votre concaténation de chaîne de code à produire quelque chose comme:

EXEC someStoredProcedureName @p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7