Use of server-defined functions/procedures



  • Suppose there is the following: Scale function stored in bd server:

    CREATE FUNCTION dbo.spTest ()
    RETURNS INT
      BEGIN
        DECLARE @result INT;
        SET @result = 1;
        RETURN @result;
      END
    GO
    

    How to trigger this function using entity frameworkAnd get the results?


    I tried to do this:

    var cmd = _context.Database.Connection.CreateCommand();
    cmd.CommandText = "[dbo].[spTest]";
    

    if (cmd.Connection.State != ConnectionState.Open)
    cmd.Connection.Open();
    try
    {
    var result = cmd.ExecuteReader();
    }
    catch (Exception)
    {
    throw;
    }

    result does not contain values. https://msdn.microsoft.com/ru-ru/data/jj691402

    I found the next one. https://stackoverflow.com/a/20911004/5610621

    On the basis of which he tried to:

    var query = _context.Database
    .SqlQuery(typeof(int),"dbo.spTest").ToListAsync();
    var downtime = (int)query.Result.Single();

    for which the following exception is granted:

    The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.



  • We need to change. cmd.CommandText:

    cmd.CommandText = "SELECT [dbo].[spTest] ()";
    

    If the function parameters are:

    cmd.CommandText = "SELECT [dbo].[spTest](@param)";
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2