ASP.NET MVC and Stored Procedures not working correctly

Question

So I most likely have something wrong, I know this already. I am just unable to figure out what exactly is wrong. I have tried this two different ways and I get different results from each way. Well here goes, I am trying to use Stored Procedures to get data for the view. I have two View Models that are as such:

public class CharacterCraftNamesListViewModel
    {
        public string CharFullName { get; set; }
        public string ProfName { get; set; }
    }

and

public class CharacterCraftCraftListViewModel
    {
        public string CraftClassName { get; set; }
        public int CharCraftCharID { get; set; }
        public int CharCraftClassID { get; set; }
        public int CharCraftLevelID { get; set; }
        public bool CraftLevelSet { get; set; }
        public string CraftLevelName { get; set; }
        public bool CraftLevelMastery { get; set; }

    }

I also have the two corresponding Stored Procedures in the database.

CREATE PROCEDURE [dbo].[GetCharacterCraftCharacterNameProfessionName]
                @CharID int = NULL
    AS
    WITH CHCRNames_CTE ( [CCCID], [CharFull], [ProfName] )
    AS
        (SELECT 
            Character_Char_ID,
            CASE 
                    WHEN b.Char_Last_Name IS NULL THEN b.Char_First_Name
                    ELSE b.Char_First_Name + '  ' + b.Char_Last_Name
            END AS FullName,
            c.Profession_Name
            FROM CharacterCraft a LEFT OUTER JOIN
            [Character] b ON a.Character_Char_ID = b.Char_ID LEFT OUTER JOIN
            [Profession] c ON c.Profession_ID = b.Profession_Profession_ID
            )
    SELECT DISTINCT CharFull, ProfName
    FROM CHCRNames_CTE
    WHERE CCCID = @CharID

and

CREATE PROCEDURE [dbo].[GetCharacterCraftRank]
                @CharID int = NULL,
                @Rank int = NULL
    AS  
    WITH CHCR_CTE ( [Rank], [CCID], [CCCCID], [CCName], [CLCLID], [CLName], [CLTier], [CLS], [CLM])
    AS
        (SELECT
                DENSE_RANK() OVER(PARTITION BY(a.Character_Char_ID)ORDER BY (a.CraftClass_Craft_Class_ID)) AS [Rank],
                a.Character_Char_ID,
                CraftClass_Craft_Class_ID,
                c.Craft_Class_Name,
                CraftLevel_Craft_Level_ID,
                d.Craft_Level_Name,
                d.Craft_Level_Tier,
                Craft_Level_Set,
                Craft_Level_Mastery
        FROM CharacterCraft a LEFT OUTER JOIN
        [Character] b ON a.Character_Char_ID = b.Char_ID LEFT OUTER JOIN
        [CraftClass] c ON a.CraftClass_Craft_Class_ID = c.Craft_Class_ID LEFT OUTER JOIN
        [CraftLevel] d ON a.CraftLevel_Craft_Level_ID = d.Craft_Level_ID
        )
    SELECT  [CCID], [CCCCID], [CCName], [CLCLID], [CLS], [CLName], [CLM] 
    FROM CHCR_CTE 
    WHERE [CCID]= @CharID AND [Rank] = @Rank
    ORDER BY [Rank], [CLTier]

Inside my controller I have the following:

public async Task<ActionResult> Edit(int? id)
            {
                if (id == null)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                } 
    var names = await db.Database.SqlQuery<CharacterCraftNamesListViewModel>( sql: "GetCharacterCraftCharacterNameProfessionName", parameters: new object[] { id } ).ToListAsync();
            var alist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( sql: "GetCharacterCraftRank", parameters: new object[] { id, 1 } ).ToListAsync();
            var blist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( sql: "GetCharacterCraftRank", parameters: new object[] { id, 2 }  ).ToListAsync();
            var clist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( sql: "GetCharacterCraftRank", parameters: new object[] { id, 3 }  ).ToListAsync();
            var characterCraft = new CharacterCraftViewModel()
            {
                CharNames = names.AsEnumerable(),
                CraftListA = alist.AsEnumerable(),
                CraftListB = blist.AsEnumerable(),
                CraftListC = clist.AsEnumerable()
            };
    if (characterCraft == null)
        {
            return HttpNotFound();
        }
        return View(characterCraft);
    }

When I look at the debugger I see the following:

• id 1
• names Count=0
• alist Count=0
• blist Count=0
• clist Count=0
• characterCraft
  {LotroMvc.Models.CharacterCraftViewModels.CharacterCraftViewModel}

So with this I just end up getting a blank page.

Now I have tried placing the stored procedures in the controller itself, and have ended up with a different output in the debugger.

Inside the controller I tried:

public async Task<ActionResult> Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
var query = "WITH CHCRNames_CTE( [CCCID], [CharFull], [ProfName] ) "
                    + "AS "
                    + "( SELECT "
                    + "Character_Char_ID, "
                    + "CASE "
                    + "WHEN b.Char_Last_Name IS NULL THEN b.Char_First_Name "
                    + "ELSE b.Char_First_Name + '  ' + b.Char_Last_Name "
                    + "END AS FullName, "
                    + "c.Profession_Name "
                    + "FROM CharacterCraft a LEFT OUTER JOIN "
                    + "dbo.[Character] b ON a.Character_Char_ID = b.Char_ID LEFT OUTER JOIN "
                    + "dbo.[Profession] c ON c.Profession_ID = b.Profession_Profession_ID "
                    + ") "
                    + "SELECT DISTINCT CharFull, ProfName "
                    + "FROM CHCRNames_CTE "
                    + "WHERE CCCID = @p0";
        var names = await db.Database.SqlQuery<CharacterCraftNamesListViewModel>( query, id ).ToListAsync();
        var rank = "WITH CHCR_CTE( [Rank], [CCID], [CCCCID], [CCName], [CLCLID], [CLName], [CLTier], [CLS], [CLM] )"
                    + "AS "
                    + "( SELECT "
                    + "DENSE_RANK() OVER(PARTITION BY(a.Character_Char_ID)ORDER BY (a.CraftClass_Craft_Class_ID)) AS [Rank], "
                    + "a.Character_Char_ID, "
                    + "CraftClass_Craft_Class_ID, "
                    + "c.Craft_Class_Name, "
                    + "CraftLevel_Craft_Level_ID, "
                    + "d.Craft_Level_Name, "
                    + "d.Craft_Level_Tier, "
                    + "Craft_Level_Set, "
                    + "Craft_Level_Mastery "
                    + "FROM CharacterCraft a LEFT OUTER JOIN "
                    + "[Character] b ON a.Character_Char_ID = b.Char_ID LEFT OUTER JOIN "
                    + "[CraftClass] c ON a.CraftClass_Craft_Class_ID = c.Craft_Class_ID LEFT OUTER JOIN "
                    + "[CraftLevel] d ON a.CraftLevel_Craft_Level_ID = d.Craft_Level_ID "
                    + ") "
                    + "SELECT  [CCID], [CCCCID], [CCName], [CLCLID], [CLS], [CLName], [CLM] "
                    + "FROM CHCR_CTE "
                    + "WHERE [CCID]= @p0 AND [Rank] = @p1 "
                    + "ORDER BY [Rank], [CLTier]";
        var alist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( rank, parameters: new object[] { id, 1 } ).ToListAsync();
        var blist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( rank, parameters: new object[] { id, 2 }  ).ToListAsync();
        var clist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( rank, parameters: new object[] { id, 3 }  ).ToListAsync();
        var characterCraft = new CharacterCraftViewModel()
        {
            CharNames = names.AsEnumerable(),
            CraftListA = alist.AsEnumerable(),
            CraftListB = blist.AsEnumerable(),
            CraftListC = clist.AsEnumerable()
        };
if (characterCraft == null)
    {
        return HttpNotFound();
    }
    return View(characterCraft);
}

This gives me the following in the debugger:

• this {LotroMvc.Controllers.CharacterCraftsController}
• id 1
• query "WITH CHCRNames_CTE( [CCCID], [CharFull], [ProfName] ) AS ( SELECT Character_Char_ID, CASE WHEN b.Char_Last_Name IS NULL THEN
  b.Char_First_Name ELSE b.Char_First_Name + ' ' + b.Char_Last_Name
  END AS FullName, c.Profession_Name FROM CharacterCraft a LEFT OUTER
  JOIN dbo.[Character] b ON a.Character_Char_ID = b.Char_ID LEFT OUTER JOIN dbo.[Profession] c ON c.Profession_ID =
  b.Profession_Profession_ID ) SELECT DISTINCT CharFull, ProfName FROM CHCRNames_CTE WHERE CCCID = @p0" names Count = 1
• [0] {LotroMvc.Models.CharacterCraftViewModels.CharacterCraftNamesListViewModel}
• CharFullName null
• ProfName "Historian"
• rank "WITH CHCR_CTE( [Rank], [CCID], [CCCCID], [CCName], [CLCLID], [CLName], [CLTier], [CLS], [CLM] )AS ( SELECT DENSE_RANK() OVER(PARTITION BY(a.Character_Char_ID)ORDER BY (a.CraftClass_Craft_Class_ID)) AS [Rank], a.Character_Char_ID, CraftClass_Craft_Class_ID, c.Craft_Class_Name, CraftLevel_Craft_Level_ID, d.Craft_Level_Name, d.Craft_Level_Tier, Craft_Level_Set, Craft_Level_Mastery FROM CharacterCraft a LEFT OUTER JOIN [Character] b ON a.Character_Char_ID = b.Char_ID LEFT OUTER JOIN [CraftClass] c ON a.CraftClass_Craft_Class_ID = c.Craft_Class_ID LEFT OUTER JOIN [CraftLevel] d ON a.CraftLevel_Craft_Level_ID = d.Craft_Level_ID ) SELECT [CCID], [CCCCID], [CCName], [CLCLID], [CLS], [CLName], [CLM] FROM CHCR_CTE WHERE [CCID]= @p0 AND [Rank] = @p1 ORDER BY [Rank], [CLTier]"
• alist Count = 9
• [0] {LotroMvc.Models.CharacterCraftViewModels.CharacterCraftCraftListViewModel}
• CharCraftCharID 0
• CharCraftClassID 0
• CharCraftLevelID 0
• CraftClassName null
• CraftLevelMastery false
• CraftLevelName null
• CraftLevelSet false
• (and so forth)

While the data in the alist is definitely wrong the count for it is correct. The names displays the correct ProfName but the incorrect data on the CharFullName. So I am lost with what to do here. If I execute the stored procedures in T-SQL I get the correct data displayed, but that is on the server alone. I have been unable to make MVC and SQL play correctly, and I know it is my code. I just cannot see what is wrong with the code. Any thoughts to where I went wrong?

Answer 1

Ok, I have figured this out. There were two things wrong that were causing the issue. With the following code, the program was not calling it to the SQL Server correctly:

var names = await db.Database.SqlQuery<CharacterCraftNamesListViewModel>( sql: "GetCharacterCraftCharacterNameProfessionName", parameters: new object[] { id } ).ToListAsync();
        var alist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( sql: "GetCharacterCraftRank", parameters: new object[] { id, 1 } ).ToListAsync();
        var blist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( sql: "GetCharacterCraftRank", parameters: new object[] { id, 2 }  ).ToListAsync();
        var clist = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>( sql: "GetCharacterCraftRank", parameters: new object[] { id, 3 }  ).ToListAsync();

While the SQLQuery call is correct through everything I have read, when I placed into SSMS what was outputted by the program, I got null sets. So I changed the sql: "GetCharacterNameProfessionName" and sql: "GetCharacterCraftRank" to sql: "GetCharacterNameProfessionName @p0" and sql: "GetCharacterCraftRank @p0, @p1". This then game me an output similar to when I wrote the queries out in the controller.

The next issue came down to naming convention. I was really baffled when I did a Database First Model to see what would happen, and it actually worked. But I realized as I was doing that I was able to map the names of my stored procedure columns to columns I had in the code. That is when it dawned on me, as simple change to my stored procedures, to have the column names match what I was putting in the program and everything worked correctly. A few minor tweaks and now my get controller looks like this:

public async Task<ActionResult> Edit(int? id)
    {
        if (id == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }
        var characterCraft = new CharacterCraftViewModel()
        {
            CharNames = await db.Database.SqlQuery<CharacterCraftNamesListViewModel>(sql: "GetCharacterCraftCharacterNameProfessionName @p0", parameters: new object[] { id }).FirstAsync(),
            CraftListA = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>(sql: "GetCharacterCraftRank @p0, @p1", parameters: new object[] { id, 1 }).ToListAsync(),
            CraftListB = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>(sql: "GetCharacterCraftRank @p0, @p1", parameters: new object[] { id, 2 }).ToListAsync(),
            CraftListC = await db.Database.SqlQuery<CharacterCraftCraftListViewModel>(sql: "GetCharacterCraftRank @p0, @p1", parameters: new object[] { id, 3 }).ToListAsync()
        };

        if (characterCraft == null)
        {
            return HttpNotFound();
        }
        return View(characterCraft);
    }

I hope that this helps someone else.