c# - stored - Parámetro del valor de tabla de procedimiento almacenado de Entity Framework

procedimientos almacenados con code first

Question

Intento llamar a un procedimiento almacenado que acepta un parámetro de valor de tabla. Sé que esto aún no está soportado directamente en Entity Framework, pero por lo que entiendo, puede hacerlo utilizando el comando ExecuteStoreQuery fuera del ObjectContext . Tengo un repositorio de marco de entidad genérico donde tengo el siguiente método ExecuteStoredProcedure :

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 cadena de comandos termina así:

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

Intenté ejecutar este método en un procedimiento almacenado que acepta un parámetro valorado en la tabla y se rompe. Leí here que los parámetros necesarios para ser del tipo SqlParameter y el parámetro de la tabla valorada deben tener el SqlDbType establecido en Structured . Así que hice esto y me aparece un error que dice:

The table type parameter p6 must have a valid type name

Entonces, configuré el SqlParameter.TypeName al nombre del tipo definido por el usuario que creé en la base de datos y luego cuando ejecuto la consulta obtengo el siguiente error verdaderamente útil:

Incorrect syntax near ''0''.

Puedo ejecutar la consulta si regreso a ADO.NET y ejecuto un lector de datos, pero esperaba que funcionara usando el contexto de datos.

¿Hay alguna forma de pasar un parámetro de valor de tabla usando ExecuteStoreQuery ? Además, en realidad estoy utilizando Entity Framework Code First y lanzando el DbContext a ObjectContext para obtener el método ExecuteStoreQuery disponible. ¿Es esto necesario o puedo hacer esto también contra DbContext ?

Answer 1

Bien, aquí hay una actualización de 2018 : solución integral que describe cómo invocar el procedimiento almacenado con el parámetro de tabla desde Entity Framework sin paquetes nuget

Estoy usando EF 6.xx, SQL Server 2012 y VS2017

1. Su prameter de valor de tabla

Digamos que tiene un tipo de tabla simple definido así (solo una columna)

go
create type GuidList as table (Id uniqueidentifier)

2. Su procedimiento almacenado

y un procedimiento almacenado con varios parámetros como:

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. Código SQL para usar este procedimiento almacenado

En SQL usarías algo como eso:

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. Código C # para usar este procedimiento almacenado

Y así es como puede llamar a ese Procedimiento almacenado desde Entity Framework (dentro de WebAPI):

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

¡Espero que ayude! 🙂

Answer 2

Cambia el código de concatenación de cadenas para producir algo como:

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

Answer 3

El enfoque de DataTable es la única forma, pero construir un DataTable y llenarlo manualmente es fugly. Quería definir mi DataTable directamente desde mi IEnumerable en un estilo similar al del generador de modelos de EF. Asi que:

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

Lo publiqué en dontnetfiddle: https://dotnetfiddle.net/ZdpYM3 (tenga en cuenta que no puede ejecutarlo allí porque no todos los ensamblajes están cargados en el violín)

Answer 4

Quiero compartir mi solución sobre este problema:

He almacenado procedimientos con varios parámetros de valor de tabla y descubrí que si lo llamas de esta manera:

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

obtienes una lista sin registros

Pero jugué más con esto y esta línea me dio una idea:

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

Cambié mi parámetro @SomeParameter con su valor real ''SomeParameterValue'' en el texto de comando. Y funcionó :) Esto significa que si tenemos algo más que SqlDbType.Structured en nuestros parámetros, no los pasa todos correctamente y no obtenemos nada. Necesitamos reemplazar los parámetros reales con sus valores.

Por lo tanto, mi solución se ve de la siguiente manera:

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

El código seguramente podría estar más optimizado, pero espero que esto ayude.

Answer 5

ACTUALIZAR

He agregado soporte para esto en Nuget Package - https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4, EF5, EF6)

Consulte el repositorio GitHub para ejemplos de código.

Ligeramente fuera de cuestión, pero no menos útil para las personas que intentan pasar tablas definidas por el usuario en un procedimiento almacenado. Después de jugar con el ejemplo de Nick y otras publicaciones de , se me ocurrió esto:

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

y el procedimiento almacenado se ve así:

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

y la tabla definida por el usuario se ve así:

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

Las restricciones que encontré incluyen:

1. Los parámetros que pasa a ExecuteStoreCommand tienen que estar en orden con los parámetros en su procedimiento almacenado
2. Debe pasar cada columna a su tabla definida por el usuario, incluso si tienen valores predeterminados. Parece que no podría tener una columna IDENTIDAD (1,1) NOT NULL en mi UDT

Answer 6

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