Paramètre de valeur de table de procédure stockée Entity Framework

J’essaie d’appeler une procédure stockée qui accepte un paramètre de valeur de table. Je sais que cela n’est pas directement pris en charge dans Entity Framework, mais d’après ce que je comprends, vous pouvez le faire en utilisant la commande ExecuteStoreQuery d’ ObjectContext . J’ai un référentiel d’entités génériques où je dispose de la méthode ExecuteStoredProcedure suivante:

 public IEnumerable ExecuteStoredProcedure(ssortingng procedureName, params object[] parameters) { SsortingngBuilder command = new SsortingngBuilder(); command.Append("EXEC "); command.Append(procedureName); command.Append(" "); // Add a placeholder for each parameter passed in for (int i = 0; i  0) command.Append(","); command.Append("{" + i + "}"); } return this.context.ExecuteStoreQuery(command.ToSsortingng(), parameters); } 

La chaîne de commande se termine comme ceci:

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

J’ai essayé d’exécuter cette méthode sur une procédure stockée qui accepte un paramètre de table et qui se casse. J’ai lu ici que les parameters doivent être de type SqlParameter et que le paramètre de valeur de table doit avoir le type SqlDbType défini sur Structured . J’ai donc fait ça et j’ai une erreur en indiquant:

 The table type parameter p6 must have a valid type name 

J’ai donc défini le SqlParameter.TypeName sur le nom du type défini par l’utilisateur que j’ai créé sur la firebase database, puis, lorsque j’exécute la requête, j’obtiens l’erreur très utile suivante:

 Incorrect syntax near '0'. 

Je peux faire en sorte que la requête s’exécute si je reviens à ADO.NET et exécute un lecteur de données, mais j’espérais le faire fonctionner avec le contexte des données.

Existe-t-il un moyen de passer un paramètre de valeur de table à l’aide d’ ExecuteStoreQuery ? En outre, j’utilise en fait Entity Framework Code First et DbContext en ObjectContext pour obtenir la méthode ExecuteStoreQuery . Est-ce nécessaire ou puis-je le faire également avec DbContext ?

METTRE À JOUR

J’ai ajouté le support pour ceci sur le paquet de Nuget – https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4, EF5, EF6)

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


Légèrement hors question, mais néanmoins utile pour les personnes essayant de passer des tables définies par l’utilisateur dans une procédure stockée. Après avoir joué avec l’exemple de Nick et d’autres messages Stackoverflow, j’ai trouvé ceci:

 class Program { static void Main(ssortingng[] 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.ExecuteStoredProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId); } } public static class ObjectContextExt { public static void ExecuteStoredProcedure(this ObjectContext context, ssortingng storedProcName, params object[] parameters) { ssortingng command = "EXEC " + storedProcName + " @caseid, @userid, @warnings"; context.ExecuteStoreCommand(command, parameters); } } 

et la procédure stockée ressemble à ceci:

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

et la table définie par l’utilisateur ressemble à ceci:

 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ées incluent:

  1. Les parameters que vous passez dans ExecuteStoreCommand doivent être en ordre avec les parameters de votre procédure stockée
  2. Vous devez transmettre chaque colonne à votre table définie par l’utilisateur, même si elles ont des valeurs par défaut. Donc, il semble que je ne pourrais pas avoir une colonne IDENTITY (1,1) NOT NULL sur mon UDT

Je veux partager ma solution sur ce problème:

J’ai stocké des procédures avec plusieurs parameters de valeur de table et j’ai découvert que si vous l’appelez de cette façon:

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

vous obtenez une liste sans enregistrement.

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

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

J’ai modifié mon paramètre @SomeParameter avec sa valeur réelle «SomeParameterValue» dans le texte de la commande. Et ça a fonctionné 🙂 Cela signifie que si nous avons quelque chose d’autre que SqlDbType.Structuré dans nos parameters, il ne les passe pas tous correctement et nous n’obtenons rien. Nous devons remplacer les parameters réels par leurs valeurs.

Donc, ma solution se présente comme suit:

 public static List ExecuteStoredProcedure(this ObjectContext dbContext, ssortingng storedProcedureName, params SqlParameter[] parameters) { var spSignature = new SsortingngBuilder(); 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(); 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().ToArray(); } else { for (int i = 0; i < parameters.Count(); i++) { spSignature.AppendFormat(" @{0}", parameters[i].ParameterName); if (i != length) spSignature.Append(","); } spParameters = parameters.Cast().ToArray(); } var query = dbContext.ExecuteStoreQuery(spSignature.ToSsortingng(), spParameters); var list = query.ToList(); return list; } 

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

Bon, voici une mise à jour de 2018 : une solution de bout en bout qui décrit comment appeler une procédure stockée avec un paramètre de table depuis Entity Framework sans les paquets nuget

J’utilise EF 6.xx, SQL Server 2012 et VS2017

1. Votre paramètre de valeur de table

Disons que vous avez un type de tableau simple défini comme ça (une seule colonne)

 go create type GuidList as table (Id uniqueidentifier) 

2. Votre procédure stockée

et une procédure stockée avec plusieurs parameters comme:

 go create procedure GenerateInvoice @listIds GuidList readonly, @createdBy uniqueidentifier, @success int out, @errorMessage nvarchar(max) out as begin set nocount on; begin try begin tran; -- -- Your logic goes here, let's say a cursor or something: -- -- declare gInvoiceCursor cursor forward_only read_only for -- -- bla bla bla -- -- if (@brokenRecords > 0) -- begin -- RAISERROR(@message,16,1); -- end -- -- All good! -- Bonne chance mon ami! select @success = 1 select @errorMessage = '' end try begin catch --if something happens let's be notified if @@trancount > 0 begin rollback tran; end declare @errmsg nvarchar(max) set @errmsg = (select 'ErrorNumber: ' + cast(error_number() as nvarchar(50))+ 'ErrorSeverity: ' + cast(error_severity() as nvarchar(50))+ 'ErrorState: ' + cast(error_state() as nvarchar(50))+ 'ErrorProcedure: ' + cast(error_procedure() as nvarchar(50))+ 'ErrorLine: ' + cast(error_number() as nvarchar(50))+ 'error_message: ' + cast(error_message() as nvarchar(4000)) ) --save it if needed print @errmsg select @success = 0 select @errorMessage = @message return; end catch; --at this point we can commit everything if @@trancount > 0 begin commit tran; end end go 

3. Code SQL pour utiliser cette procédure stockée

En SQL, vous utiliseriez quelque chose comme ça:

 declare @p3 dbo.GuidList insert into @p3 values('f811b88a-bfad-49d9-b9b9-6a1d1a01c1e5') exec sp_executesql N'exec GenerateInvoice @listIds, @CreatedBy, @success',N'@listIds [dbo].[GuidList] READONLY,@CreatedBy uniqueidentifier',@listIds=@p3,@CreatedBy='FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF' 

4. Code C # pour utiliser cette procédure stockée

Et voici comment vous pouvez appeler cette procédure stockée depuis Entity Framework (dans WebAPI):

  [HttpPost] [AuthorizeExtended(Roles = "User, Admin")] [Route("api/BillingToDo/GenerateInvoices")] public async Task GenerateInvoices(BillingToDoGenerateInvoice model) { try { using (var db = new YOUREntities()) { //Build your record var tableSchema = new List(1) { new SqlMetaData("Id", SqlDbType.UniqueIdentifier) }.ToArray(); //And a table as a list of those records var table = new List(); for (int i = 0; i < model.elements.Count; i++) { var tableRow = new SqlDataRecord(tableSchema); tableRow.SetGuid(0, model.elements[i]); table.Add(tableRow); } //Parameters for your query SqlParameter[] parameters = { new SqlParameter { SqlDbType = SqlDbType.Structured, Direction = ParameterDirection.Input, ParameterName = "listIds", TypeName = "[dbo].[GuidList]", //Don't forget this one! Value = table }, new SqlParameter { SqlDbType = SqlDbType.UniqueIdentifier, Direction = ParameterDirection.Input, ParameterName = "createdBy", Value = CurrentUser.Id }, new SqlParameter { SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output, // output! ParameterName = "success" }, new SqlParameter { SqlDbType = SqlDbType.NVarChar, Size = -1, // "-1" equals "max" Direction = ParameterDirection.Output, // output too! ParameterName = "errorMessage" } }; //Do not forget to use "DoNotEnsureTransaction" because if you don't EF will start it's own transaction for your SP. //In that case you don't need internal transaction in DB or you must detect it with @@trancount and/or XACT_STATE() and change your logic await db.Database.ExecuteSqlCommandAsync(TransactionalBehavior.DoNotEnsureTransaction, "exec GenerateInvoice @listIds, @createdBy, @success out, @errorMessage out", parameters); //reading output values: int retValue; if (parameters[2].Value != null && Int32.TryParse(parameters[2].Value.ToString(), out retValue)) { if (retValue == 1) { return Ok("Invoice generated successfully"); } } string retErrorMessage = parameters[3].Value?.ToString(); return BadRequest(String.IsNullOrEmpty(retErrorMessage) ? "Invoice was not generated" : retErrorMessage); } } catch (Exception e) { return BadRequest(e.Message); } } } 

J'espère que ça aide! 🙂

L’approche DataTable est le seul moyen, mais la construction d’un DataTable et son remplissage manuel sont fugaces. Je voulais définir mon DataTable directement à partir de mon IEnumerable dans un style similaire à celui de EF. Alors:

 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 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 chose sur dontnetfiddle: https://dotnetfiddle.net/ZdpYM3 (notez que vous ne pouvez pas l’exécuter parce que tous les assemblages ne sont pas chargés dans le violon)

 var sqlp = new SqlParameter("@param3", my function to get datatable); sqlp.SqlDbType = System.Data.SqlDbType.Structured; sqlp.TypeName = "dbo.mytypename"; var v = entitycontext.Database.SqlQuery("exec [MyStorProc] @param1,@param2,@param3,@param4", new SqlParameter[] { new SqlParameter("@param1",value here), new SqlParameter("@param2",value here), sqlp, new SqlParameter("@param4",value here) }).FirstOrDefault(); 

Changez votre code de concaténation de chaîne pour produire quelque chose comme:

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