0

I'm new to C#, .NET Core and middleware in general, just to manage the expectation of the code included.

Regardless I have managed to build a very basic API which takes in a param, runs a stored procedure using that param and returns a single 'table' result. The stored procedure produces two rowsets however, only one makes it all the way back through the API and I have not been able to figure out why.

The rowsets are not relational, one simply has values that were calculated using the raw data which is returned in the second one stored procedure output.

Have tried a few things including setting MultipleActiveResultSets=True but the issue is probably with the and I have not been able to find a solution that works for me. Would be great if someone was able to point me in the right direction.

API output

[
  {
    "pcd": "BA1 1UA",
    "crimesInArea": 711,
    "density": 37.1,
    "crimeScore": 1916
  }
]

ASP.NET Core 5 web app components

Connection string

"MyConn": "Server=Server,1433;Initial Catalog=HouseValueChecker;Persist Security Info=False;User ID=user;Password=password;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Output table mapping

[Keyless]
public class Outputs
{
    //[Key]
    public string pcd { get; set; }
    public int CrimesInArea { get; set; }
    public double Density { get; set; }
    public int CrimeScore { get; set; }
}

public class CrimeList
{
    public string Crime_Type { get; set; }
    public float Latitude { get; set; }
    public float Longitude { get; set; }
}

Context

public class CallSPDBContext : DbContext
{
    public CallSPDBContext(DbContextOptions<CallSPDBContext> options):base(options)
    {
    }

    public DbSet<MoveHereAPI.Models.DB.Outputs> Outputs { get; set; }
    public DbSet<MoveHereAPI.Models.DB.Outputs> CrimeList { get; set; }
}

Controller

[HttpPost]
public async Task<ActionResult<IEnumerable<Outputs>>> GetOutputs(Input input)
{
    string storedProc = "exec usp_GetCrimeData " + "@Postcode = '" + input.Postcode + "'";
    return await _context.Outputs.FromSqlRaw(storedProc).ToListAsync();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KnifeWrench
  • 1
  • 1
  • Entity framework strikes again! [Does this help](https://stackoverflow.com/a/25305607/7165279)? I know the code looks scary, but you can blame entity framework for that. Maybe try [Dapper](https://stackoverflow.com/questions/6317937/dapper-net-and-stored-proc-with-multiple-result-sets) instead? :D – allmhuran Sep 01 '21 at 18:41
  • Side note: You should use proper parameterization, which `FromSqlRaw` supports, see the docs – Charlieface Sep 01 '21 at 19:51
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Sep 05 '21 at 10:06

1 Answers1

0

Try this

var postCodeParam = new SqlParameter("@Postcode",input.Postcode);

 return await _context.Outputs.FromSqlRaw("exec usp_GetCrimeData  @Postcode",postCodeParam).ToListAsync();
Serge
  • 40,935
  • 4
  • 18
  • 45