c# - first - entity framework stored procedure return table



Entity Framework Stored Procedure-Tabellenwertparameter (4)

Ich versuche eine gespeicherte Prozedur aufzurufen, die einen Tabellenwertparameter akzeptiert. Ich weiß, dass dies in Entity Framework noch nicht direkt unterstützt wird, aber nach ExecuteStoreQuery Verständnis können Sie es mit dem ExecuteStoreQuery Befehl off des ExecuteStoreQuery . Ich habe ein generisches Entity-Framework-Repository, in dem ich die folgende ExecuteStoredProcedure Methode verwende:

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);
}

Die Befehlszeichenfolge endet folgendermaßen:

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

Ich habe versucht, diese Methode für eine gespeicherte Prozedur auszuführen, die einen Tabellenwert akzeptiert und bricht. Ich habe here gelesen here dass für die Parameter vom Typ SqlParameter und für den Tabellenwert der Parameter SqlDbType auf Structured . Also habe ich das gemacht und bekomme einen Fehler, der besagt:

The table type parameter p6 must have a valid type name

Also setze ich den SqlParameter.TypeName auf den Namen des benutzerdefinierten Typs, den ich in der Datenbank erstellt habe, und dann, wenn ich die Abfrage ausführe, erhalte ich den folgenden wirklich hilfreichen Fehler:

Incorrect syntax near '0'.

Ich kann die Abfrage ausführen, wenn ich zu ADO.NET zurückwechsle und einen Datenleser ausfühle, aber ich hoffte, dass ich ihn mithilfe des Datenkontextes zum Laufen bringen kann.

Gibt es eine Möglichkeit, einen Tabellenwertparameter mithilfe von ExecuteStoreQuery ? Außerdem verwende ich Entity Framework Code First und den DbContext auf einen ObjectContext , um die ExecuteStoreQuery Methode verfügbar zu machen. Ist das notwendig oder kann ich das auch gegen den DbContext tun?


Answer #1

Ändern Sie den Zeichenfolge-Verkettungscode, um etwas wie zu erzeugen:

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

Answer #2

Der DataTable-Ansatz ist der einzige Weg, aber das Konstruieren einer DataTable und das manuelle Auffüllen von Daten ist unscharf. Ich wollte meine DataTable direkt von meinem IEnumerable in einem Stil definieren, der dem fließenden Model Builder von EF ähnlich ist. Damit:

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()
                );

Ich habe das Ding auf dontnetfiddle gepostet: https://dotnetfiddle.net/ZdpYM3 (beachte, dass du es dort nicht ausführen kannst, weil nicht alle Assemblies in die Geige geladen sind)


Answer #3

Okay, hier ist ein Update 2018 : Ende-zu-Ende-Lösung, die beschreibt, wie gespeicherte Prozeduren mit Tabellenparametern von Entity Framework ohne nuget-Pakete aufgerufen werden

Ich verwende EF 6.xx, SQL Server 2012 und VS2017

1. Ihr Tabellenwertparameter

Nehmen wir an, Sie haben einen einfachen Tabellentyp definiert (nur eine Spalte)

go
create type GuidList as table (Id uniqueidentifier)

2. Ihre gespeicherte Prozedur

und eine gespeicherte Prozedur mit mehreren Parametern wie:

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. SQL-Code, um diese gespeicherte Prozedur zu verwenden

In SQL würden Sie so etwas verwenden:

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',@[email protected],@CreatedBy='FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'

4. C # -Code, um diese gespeicherte Prozedur zu verwenden

Und so können Sie diese Stored Procedure von Entity Framework (innerhalb von WebAPI) aufrufen:

    [HttpPost]
    [AuthorizeExtended(Roles = "User, Admin")]
    [Route("api/BillingToDo/GenerateInvoices")]
    public async Task<IHttpActionResult> GenerateInvoices(BillingToDoGenerateInvoice model)
    {
        try
        {
            using (var db = new YOUREntities())
            {
                //Build your record
                var tableSchema = new List<SqlMetaData>(1)
                {
                    new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
                }.ToArray();

                //And a table as a list of those records
                var table = new List<SqlDataRecord>();

                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);
        }
    }
}

Ich hoffe, es hilft! ­čÖé


Answer #4

AKTUALISIEREN

Ich habe Unterstützung dafür auf Nuget-Paket hinzugefügt - https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4, EF5, EF6)

Im GitHub Repository finden Sie Codebeispiele.

Etwas weniger Frage, aber nichtsdestoweniger nützlich für Leute, die versuchen, benutzerdefinierte Tabellen in eine gespeicherte Prozedur zu überführen. Nachdem ich mit Nicks Beispiel und anderen -Posts herumgespielt hatte, kam ich auf Folgendes:

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.ExecuteStoredProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);
    }
}

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

        context.ExecuteStoreCommand(command, parameters);
    }
}

und die gespeicherte Prozedur sieht folgendermaßen aus:

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

und die benutzerdefinierte Tabelle sieht folgendermaßen aus:

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

Constraints, die ich gefunden habe, schließen ein:

  1. Die Parameter, die Sie in ExecuteStoreCommand müssen mit den Parametern in Ihrer gespeicherten Prozedur ExecuteStoreCommand
  2. Sie müssen jede Spalte in Ihre benutzerdefinierte Tabelle eingeben, auch wenn sie Standardwerte haben. Es scheint also, dass ich keine IDENTITY (1,1) NOT NULL-Spalte auf meinem UDT haben könnte




table-valued-parameters