Call stored procedure in MVC

Problem:

Migrating an application with existing database into MVC 5.  Things are going great with EF model but have some reports written as stored procedures and needed a way to call them in the new design.

Solution:

The key is to call sproc using the datacontext from your model.  This can then be put in the controller and the result set is passed out to a viewmodel object which you define in your project.  The view is then run against the view model to display the results.




Controller



var spParameters = new object[5];

spParameters[0] = new SqlParameter()
{
 ParameterName = "office_IDs",
 Value = OfficeIds
};


spParameters[1] = new SqlParameter()
{
 ParameterName = "country",
 Value = country
};

spParameters[2] = new SqlParameter()
{
 ParameterName = "branchofservice",
Value = service
};
spParameters[3] = new SqlParameter()
{
ParameterName = "dateBegin",
Value = dateBegin
};
spParameters[4] = new SqlParameter()
{
ParameterName = "dateEnd",
Value = dateEnd
};

DbContext context = new DbContext("ModelContext");


var datalist = context.Database.SqlQuery<OtherOffenseItemizationModelView>("exec dbo.getOtherOffenseItemization @office_IDs,@country,@branchOfService,@dateBegin,@dateEnd", spParameters).ToList();



return View(datalist);




View


@model IEnumerable<FCJC.ModelView.OtherOffenseItemizationModelView>
<div>
    <table class="table table-striped">
        <tr>
            <th colspan="5" class="text-center table-bordered">
      TITLE
            </th>
        </tr>
        <tr class="alert-info table-bordered">
            <th>
                @Html.DisplayNameFor(model => model.OffenseOther)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.MIL)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.CIV)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.DEP)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.TOTAL)
            </th>
           
        </tr>
        @foreach (var item in Model)
        {
            <tr class="table-bordered">
                <td>
                    @Html.DisplayFor(modelItem => item.OffenseOther)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.MIL)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.CIV)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.DEP)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.TOTAL)
                </td>
            </tr>
        }
    </table>

</div>
<p>
    @Html.ActionLink("Back to List", "Index")
</p>

Add total row to end of sql result

Problem:
I had an aggregate query which rolled up number for a query.  The customer wanted me to tack on a summary count at the end of the query.  Used the WITH and UNION commands supported in TSQL.


Solution:

;WITH temp as
( Select
OffenseOther,
[MIL] = SUM(CASE(MilitaryStatus) WHEN 'MIL' THEN 1 ELSE 0 END),
[OTHER] = SUM(CASE(MilitaryStatus) WHEN 'OTHER' THEN 1 ELSE 0 END),
[UNK] = SUM(CASE(MilitaryStatus) WHEN 'UNK' THEN 1 ELSE 0 END),
Count(*) [TOTAL]
From
FcjcRecords
inner join Office o on o.Office_ID = f.Office_ID
inner join MilitaryStatus ms on ms.MilitaryStatus_ID = f.MilitaryStatus_ID
WHERE
f.Offense_ID = 'N'
And
o.Address_ID in (select address_id from Address where LLO_Country = @country)
And
f.Service_ID = @branchOfService
And
f.DateFjCategory between @dateBegin And @dateEnd
And
f.MilitaryStatus_ID is not null
AND
f.FcjcCategory is not null
Group By
f.OffenseOther
)

select OffenseOther,MIL,OTHER,UNK,TOTAL
from Temp
UNION ALL
select 'Total' as TOTAL , SUM(MIL),SUM(OTHER),SUM(UNK),SUM(TOTAL)
from temp



Source:
http://stackoverflow.com/questions/22838347/adding-a-total-row-to-the-end-of-query-result

tf80006 - Can't publish project plan to TFS

Problem:

tf80006 - Can't Publish from Project to TFS.
TF80006: Team Foundation needs a work item type to publish work items.

Solution:

One of gotchas that bit me was that I did not set the Work Item Type in Project. Once I set it to 'Task' I was able to publish my schedule to TFS.

Source:

http://stackoverflow.com/questions/8838358/cant-publish-project-plan-to-tfs

Remove TFS association from Microsoft Project file

Problem:

I was trying to build an MS project file for a new program we are starting.  I had built out my backlog in TFS.  I wanted to try out the MS project linking.  I ran into issues when I associated the file with the incorrect TFS collection.  There is no way to unlink in MS 2007!

Solution:

1. Close Microsoft Project
2. Right click on the mpp file and choose Properties.
3. Choose the Custom tab in the Properties window.
4. Choose any Property listed there named “VS  Team System Data Do Not Edit” and click Remove.
5. Click Apply or Ok.
6. Open the mpp file, and you will get a message saying its not associated with any TFS server. Click Ok and Save it.
7. You will not get the error message every time you open the mpp file as long as you save it once after the message is received.

Source:

https://blogs.msdn.microsoft.com/buckh/2006/07/19/removing-the-team-foundation-server-association-in-microsoft-project-and-microsoft-excel-files/

Detecting an Empty Guid in Sql Server in T-Sql Syntax

Problem:

How to search for Guid.Empty in TSQL.  Thanks, Bruce!

Solution:

declare @emptyGuid uniqueidentifier      
set @emptyGuid = cast(cast(0 as binary) as uniqueidentifier)


select * from FcjcRecords
where office_id=@emptyGuid

Source:

http://www.ifinity.com.au/Blog/EntryId/81/Detecting-an-Empty-Guid-in-Sql-Server-in-T-Sql-Syntax

Inner Join using LINQ

Create query:

var queryOfficeInfo = from ofc in db.Offices

join llo in db.LLOs
on ofc.Office_Name equals llo.LLO_Name
select new

{

ofc.Office_ID,

llo.tp_ID,

ofc.Office_Name

};

Retrieve Query:



var result = queryOfficeInfo.Where(q => q.tp_ID.Equals(obj.LLO_ID));

//Update the FCJC Record to reflect correct office relationship

obj.Office_ID = result.Select(a => a.Office_ID).FirstOrDefault();