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

System.InvalidOperationException: Migrations is enabled for context but the database does not exist or contains no mapped tables.

Problem:
Entity Framework throws migration exception when trying to move code to Staging Server.


I have been trying out the EF migration and Code First approach on a new project.
I was able to get the migration and update working on my dev box.  I then went to move
the code base to our staging using xcopy.   I then hit the website and got a nasty no mapped
tables error.


Solution:


For me I don't need to move the migrations to the staging or production server.  I just needed to disable the migration.  This can be done using the web.config:




This is the basic syntax for setting an initializer in your config file:
<appSettings>
< add key=”DatabaseInitializerForType MyAssemblyQualifiedContextType”
value=”MyAssemblyQualifedInitializerType” />
< /appSettings>
The key things to notice here are:

• The entry goes in the appSettings section of your config file
• The key must always start with the string “DatabaseInitializerForType” followed by whitespace
• The second part of the key is the assembly-qualified name of the context type for which the initializer should be set
• The value is the assembly-qualified name of the initializer to set, which must expose a public, parameterless constructor so that EF can create an instance of it

Example:


<add key="DatabaseInitializerForType FCJC.Models.FCJCContext"
value="Disabled"/>


I placed this in my web.staging.config so that each time I build out my solution for deployment to staging, it will insert this flag and no more error!






Source:
https://blog.oneunicorn.com/2011/03/31/configuring-database-initializers-in-a-config-file/