webentwicklung-frage-antwort-db.com.de

Tabellenwertparameter für gespeicherte Entity Framework-Prozeduren

Ich versuche, eine gespeicherte Prozedur aufzurufen, die einen Tabellenwertparameter akzeptiert. Ich weiß, dass dies in Entity Framework noch nicht direkt unterstützt wird, aber soweit ich weiß, können Sie es mit dem Befehl ExecuteStoreQuery aus dem Befehl ObjectContext tun. 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-Parameter akzeptiert, und sie bricht ab. Ich las hier , dass die Parameter vom Typ SqlParameter sein mussten und dass für den Tabellenwert-Parameter SqlDbType auf Structured festgelegt werden musste. Also habe ich das gemacht und ich bekomme eine Fehlermeldung:

The table type parameter p6 must have a valid type name

Daher setze ich SqlParameter.TypeName auf den Namen des benutzerdefinierten Typs, den ich in der Datenbank erstellt habe. Wenn ich dann die Abfrage ausführe, wird der folgende wirklich hilfreiche Fehler angezeigt:

Incorrect syntax near '0'.

Ich kann die Abfrage ausführen, wenn ich wieder zu ADO.NET zurückkehre und einen Datenleser ausführe, aber ich hatte gehofft, dass sie im Datenkontext funktioniert.

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

64
Nick Olsen

[~ # ~] Update [~ # ~]

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

Im GitHub -Repository finden Sie Codebeispiele.


Keine Frage, aber dennoch nützlich für Benutzer, die versuchen, benutzerdefinierte Tabellen an eine gespeicherte Prozedur zu übergeben. Nachdem ich mit Nicks Beispiel und anderen Stackoverflow-Beiträgen 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 so aus:

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

Einschränkungen, die ich gefunden habe, sind:

  1. Die Parameter, die Sie an ExecuteStoreCommand übergeben, müssen mit den Parametern in Ihrer gespeicherten Prozedur übereinstimmen
  2. Sie müssen jede Spalte an Ihre benutzerdefinierte Tabelle übergeben, auch wenn sie Standardwerte enthält. Es scheint also, dass ich keine IDENTITY (1,1) NOT NULL-Spalte in meinem UDT haben konnte
90
Mike

Okay, hier ist also eine 2018-Aktualisierung: End-to-End-Lösung, die beschreibt, wie eine gespeicherte Prozedur mit einem Tabellenparameter aus dem Entity Framework aufgerufen wird ohne Nuget-Pakete

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

1. Ihr Tabellenwert-Parameter

Angenommen, Sie haben einen einfachen Tabellentyp wie diesen 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 zur Verwendung dieser gespeicherten Prozedur

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 zur Verwendung dieser gespeicherten Prozedur

Und so können Sie diese gespeicherte Prozedur in 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! ????

13
Pavel Kovalev

Ich möchte meine Lösung für dieses Problem mitteilen:

Ich habe Prozeduren mit mehreren Tabellenwertparametern gespeichert und festgestellt, dass, wenn Sie es so aufrufen:

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

sie erhalten eine Liste ohne Einträge.

Aber ich habe mehr damit gespielt und diese Zeile brachte mich auf eine Idee:

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

Ich habe meinen Parameter @ SomeParameter mit seinem tatsächlichen Wert 'SomeParameterValue' im Befehlstext geändert. Und es hat funktioniert :) Dies bedeutet, dass wenn wir etwas anderes als SqlDbType.Structured in unseren Parametern haben, es nicht alle richtig übergibt und wir nichts bekommen. Wir müssen die tatsächlichen Parameter durch ihre Werte ersetzen.

Meine Lösung sieht also so aus:

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

Der Code könnte sicherlich optimiert werden, aber ich hoffe, dass dies helfen wird.

8
Andrey Borisko

Der DataTable-Ansatz ist der einzige Weg, aber das Erstellen und manuelle Auffüllen einer DataTable ist mühsam. Ich wollte meine DataTable direkt aus meiner IEnumerable in einem Stil definieren, der dem von EF für den fließenden Modellbauer ähnelt. So:

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/ZdpYM (beachte, dass du es dort nicht ausführen kannst, weil nicht alle Assemblys in die Geige geladen sind)

2
Toby Couchman
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<bool?>("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();
1
souvik sett

Ändern Sie den Code für die Verkettung von Zeichenfolgen, um Folgendes zu erhalten:

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