CODEWITHSUNDEEP
c#sqlasp.net-mvcstored-procedures
Der Deutsche
Der Deutsche

Reputation: 61

How to execute a stored procedure join without creating a new asp.net model?

I am creating stored procedures in SQL Server database and have been having issues returning the data when called.

I have managed to get it to work, but it feels as if it is a hack job and that I am doing it incorrectly. Please see the code below and let me know if there is a better way to go about doing this. Thank you for taking the time to help me.

create procedure FetchSumOfEmpSalariesByCity
as
begin
    select 
        sum(e.SAL) as TotalSalary, d.LOC as Location 
    from 
        EMPS e 
    join 
        DEPTs d on e.DEPTNO = d.DEPTNO 
    group by 
        d.LOC
end

    public class SumOfEmpsSalaryByCity
    {
        [Key]
        public int TotalSalary { get; set; }
        public string Location { get; set; }
    }

    [HttpGet]
    [Route("salary")]
    public IHttpActionResult GetEMPsSal()
    {
            using (var db = new KemmitContext())
            {
                var sp = db.SumOfEmpsSalaryByCities.SqlQuery("FetchSumOfEmpSalariesByCity");
                return Ok(sp.ToList());
            }
    }

I want to do this the correct way. Is there a way to do this without a model? Or am I going about this the right way?

Upvotes: 2

Views: 592

Answers (1)

Steven Frank
Steven Frank

Reputation: 621

I break these tasks down like this; should it be done with EF or in the database and if it's in the database, should it be a View or an Sp?

Whenever I'm simply selecting data, I use EF either direct to the table for very simple queries or I create a database View for any joins, etc. This can be done in EF but it's god-awful, in any case, IMO these tasks belong in the database, right tool, right job. If you're using code-first, getting your Views across is a bit involved, let me know if you're doing that.

var model = db.v_ObservationAutoComplete // This can be direct to a table or a view
   .Where(oa => oa.Observation.Contains(term))
   .OrderBy(oa => oa.Observation)
   .Select(oa => new
   {
       label = oa.Observation
   }).Take(10);

When I have to update something in a single table, I use EF

t_Section eSection = new t_Section
    {
        SectionId = model.SectionId,
        Section = model.Section,
        SectionTypeId = model.SectionTypeId,
        SectionOrdinal = model.SectionOrdinal,
        ModifyDate = DateTime.Now,
        ModifyUserName = User.Identity.Name,
        LastChangeId = newChangeId
    };

    db.Entry(eSection).State = EntityState.Modified;
    db.SaveChanges();

If I have to do a multi-table update, I have a couple of different methodologies; 1) returning a simple bool value for a status code/scalar value, or I have to return a result set after doing whatever update I made.

This returns a List

List<string> elements = new List<string>();

try
{
    SqlParameter[] parms = new[]
    {
        new SqlParameter("mpid", myProtocolsId),
        new SqlParameter("elid", elementId)
    };
    elements = db.Database.SqlQuery<string>("p_MyProtocolsOverviewElementRemove @myProtocolsId = @mpid, @elementId = @elid", parms).ToList();

    return Json(elements);
}
catch (Exception e)
{
    return Json(null);
}

And if I just need a simple value back, something like this;

SqlParameter[] parms = new[]
{
    new SqlParameter("chid", changeId),
    new SqlParameter("prid", change.ObjectId),
    new SqlParameter("psid", change.ProtocolSectionId),
    new SqlParameter("inid", change.SecondaryObjectId),
    new SqlParameter("acun", User.Identity.Name)
};
result = db.Database.SqlQuery<int>("p_MyProtocolsContentUpdateInterventionAdd @changeId = @chid, @protocolId = @prid, @protocolSectionId = @psid, @interventionId = @inid, @acceptUserName = @acun", parms).FirstOrDefault();

Hope this helps!

Upvotes: 2

Related Questions