Add custom stored procedure result for procedure with int result

[jakoss]

I have old SQL Server 2005 database and i cannot modify it it any way. I'm trying to generate stored procedure call for one of the SPs, but i have issue with custom result.

First of all, procedure returns 2 result sets. Both have spaces and polish marks in column names. First:

SELECT
		CAST(RankPosCW AS nvarchar)				AS [Bieżący tydzień]
		--
		, CASE
			WHEN WeeksOnReportTotal IS NULL THEN N'NEW'
			WHEN WeeksOnReportTotal IS NOT NULL AND ((RankPosCW - [RankPosW-1]) <> 0 OR (RankPosCW - [RankPosW-1]) = 0) THEN CAST(([RankPosW-1] - RankPosCW) as nvarchar)
			ELSE 'RETURN'
		END AS [TrendValue]
		--
		, ISNULL(CAST([RankPosW-1] AS nvarchar), '-')			AS [Poprzedni tydzień]
		, ISNULL(CAST([RankPosW-2] AS nvarchar), '-')			AS [Dwa tyg. temu]
		, ISNULL(CAST(WeeksOnReportTotal AS nvarchar), '-')		AS [Liczba tyg. na liście]
		, COALESCE(CAST(WeeksOnReportTop AS nvarchar), CAST(RankPosCW AS nvarchar), '-')	AS [Najwyżej na liście]
        , ISNULL(CAST(Title AS nvarchar(255)), '-')				AS [Tytuł utworu]
        , ISNULL(CAST(Artist AS nvarchar(255)), '-')			AS [Wykonawca]
        , ISNULL(CAST(SLP AS nvarchar(255)), '-')				AS [Wytwórnia]
		, ISNULL(CAST(TotalPlayCount AS nvarchar), '-')			AS [Odtworzenia]
		, ISNULL(CAST(ROUND(ImpactCW, 0) AS nvarchar), '-')		AS [Impact '000]
		, ISNULL(CAST(ROUND([ImpactW-1], 0) AS nvarchar), '-')	AS [Poprzedni tydzień '000]
		-- DEBUG
		--, [Year]				AS _rok
		--, WeekOfYear			AS _tydzien_w_roku
		--, MediaObjectId			AS _MediaObjectId
		--, CASE
		--	WHEN WeeksOnReportTotal IS NULL THEN N'New'
		--	WHEN WeeksOnReportTotal IS NOT NULL AND (RankPosCW - [RankPosW-1]) = 0 THEN N'NonMover'
		--	WHEN WeeksOnReportTotal IS NOT NULL AND (RankPosCW - [RankPosW-1]) < 0 THEN N'Climber'
		--	WHEN WeeksOnReportTotal IS NOT NULL AND (RankPosCW - [RankPosW-1]) > 0 THEN N'Faller'
		--	ELSE 'Return'
		--END AS _TrendType
	FROM #AirPlayReportTmp
	ORDER BY RankPosCW

and second:

SELECT	[Wytwórnia]
			, [Liczba utworów]
			, CAST([Odtworzenia] AS int) AS [Odtworzenia]
			, CAST([Impact '000] AS int) AS [Impact '000]
			, CAST([Impact W-1 '000] AS int) AS [Impact W-1 '000]
			, ([Impact '000]/@SumImpactW) AS PercentShare
			, ([Impact W-1 '000]/@SumImpactWm1) AS [PercentShare W-1]
	FROM	#LabelSharesReport
	WHERE	IsDisplayed = 1
	ORDER BY [Impact '000] DESC

Since these procedures are huge and have a lot of temporary tables i can understand that getting automatic return types is impossible or really hard. Generated code is:

public int GenerateAirPlayTop100RadioImpactReportTopNAgrLabelWithLabelShares(DateTime? recalculationDate, int? topRows, bool? overrideLabel, out short? weekNo, out string dateRange)
        {
            var recalculationDateParam = new SqlParameter { ParameterName = "@RecalculationDate", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = recalculationDate.GetValueOrDefault() };
            if (!recalculationDate.HasValue)
                recalculationDateParam.Value = DBNull.Value;

            var topRowsParam = new SqlParameter { ParameterName = "@TopRows", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = topRows.GetValueOrDefault(), Precision = 10, Scale = 0 };
            if (!topRows.HasValue)
                topRowsParam.Value = DBNull.Value;

            var overrideLabelParam = new SqlParameter { ParameterName = "@OverrideLabel", SqlDbType = SqlDbType.Bit, Direction = ParameterDirection.Input, Value = overrideLabel.GetValueOrDefault() };
            if (!overrideLabel.HasValue)
                overrideLabelParam.Value = DBNull.Value;

            var weekNoParam = new SqlParameter { ParameterName = "@WeekNo", SqlDbType = SqlDbType.SmallInt, Direction = ParameterDirection.Output, Precision = 5, Scale = 0 };
            var dateRangeParam = new SqlParameter { ParameterName = "@DateRange", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Output, Size = 60 };
            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };

            Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "EXEC @procResult = [dbo].[Generate_AirPlayTop100RadioImpact_ReportTopN_AgrLabel_WithLabelShares] @RecalculationDate, @TopRows, @OverrideLabel, @WeekNo OUTPUT, @DateRange OUTPUT", recalculationDateParam, topRowsParam, overrideLabelParam, weekNoParam, dateRangeParam, procResultParam);

            if (IsSqlParameterNull(weekNoParam))
                weekNo = null;
            else
                weekNo = (short) weekNoParam.Value;

            if (IsSqlParameterNull(dateRangeParam))
                dateRange = default(string);
            else
                dateRange = (string) dateRangeParam.Value;

            return (int)procResultParam.Value;
        }

And that's fine, i can create those models directly. Here's when i'm lost. How to create models for columns with polish marks and dots in it? I tried creating simple models with first 3 fields:

public class AirplayReturnModel
    {
        public class ResultSetModel1
        {
            public string BieżącyTydzień { get; set; }
            public string TrendValue { get; set; }
            public string PoprzedniTydzień { get; set; }
        }

        public List<ResultSetModel1> ResultSet1;
    }

and i configured .tt file like:

Settings.StoredProcedureReturnTypes.Add("GenerateAirPlayTop100RadioImpactReportTopNAgrLabelWithLabelShares", "AirplayReturnModel");

But for some reason the generated method still have int result type. No error during generating. Other changes in .tt are properly taken into account.

I found that in your code:

https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/blob/99f6527024be6bbecf7a4ec2f2493194c23cc3b7/Generator/StoredProcedure.cs#L363

This if check is based on ReturnModels.Count, but this count ignores Settings.StoredProcedureReturnTypes. I believe that's a bug, since if i cannot modify SP i'm blocked by this?

[sjh37]

Hi @jakoss The return model should of been created for you automatically. The problem with stored procedures is the return model column names have to match exactly the same casing and name from the stored procedure, which can be a problem if the names are illegal in C#.

One good solution is to create a simple wrapper stored procedure to call the real stored procedure, and to return valid column names.

[jakoss]

Well, i guess i'll have to do that. And what about the if check on ReturnModels.Count ignoring Settings.StoredProcedureReturnTypes? It might not solve my case, but i still believe that this might affect somebody with better column naming

[sjh37]

That is true. I will keep this case open and investigate that! Many thanks for spotting it.