Format unicode before get from database

Question

I create datagridview with unicode column like:

  private void GetUnicodeFormat()
        {
            string unicodeString = "\u2605";

            OneStar = unicodeString;
            TwoStar = unicodeString + unicodeString;
            ThreeStar = unicodeString + unicodeString + unicodeString;
            FourStar = unicodeString + unicodeString + unicodeString + unicodeString;
            FiveStar = unicodeString + unicodeString + unicodeString + unicodeString + unicodeString;
        }

Then I set it in datagridview like:

  foreach (DataGridViewRow row in dgvProjectRanking.Rows)
            {
                var cellNumberValue = int.Parse(row.Cells[contractedColumnIndex].Value.ToString());

                if (cellNumberValue <= 20)
                {
                    row.Cells[scoreColumnIndex].Value = OneStar;
                }
                else if (cellNumberValue > 20 && cellNumberValue < 40)
                {
                    row.Cells[scoreColumnIndex].Value = TwoStar;
                }
                else if (cellNumberValue >= 40 && cellNumberValue < 60)
                {
                    row.Cells[scoreColumnIndex].Value = ThreeStar;

                }
                else if (cellNumberValue >= 60 && cellNumberValue < 80)
                {
                    row.Cells[scoreColumnIndex].Value = FourStar;
                }
                else if (cellNumberValue >= 80 && cellNumberValue <= 100)
                {
                    row.Cells[scoreColumnIndex].Value = FiveStar;

                }

            }

This works, I get stars in DataGridView. But now instead do this I want to send values via SQL so I create function in sql like:

CREATE FUNCTION [HELPER].[udf_ProjectContractedPercentage_Set]
(
             @ProjectContractedPercentage INT
)
RETURNS VARCHAR(MAX)
AS
        BEGIN
            DECLARE @NewProjectContractedPercentage VARCHAR(MAX)
                    ,@StarUnicode  VARCHAR(10) = '\u2605';
          SELECT
             @NewProjectContractedPercentage = (CASE
                                                WHEN @ProjectContractedPercentage <= 20
                                                THEN @StarUnicode
                                                WHEN @ProjectContractedPercentage > 20
                                                    AND @ProjectContractedPercentage < 40
                                                THEN CONCAT(@StarUnicode , @StarUnicode)
                                                WHEN @ProjectContractedPercentage >= 40
                                                    AND @ProjectContractedPercentage < 60
                                                THEN CONCAT(@StarUnicode , @StarUnicode , @StarUnicode)
                                                WHEN @ProjectContractedPercentage >= 60
                                                    AND @ProjectContractedPercentage < 80
                                                THEN CONCAT(@StarUnicode , @StarUnicode , @StarUnicode , @StarUnicode )
                                                WHEN @ProjectContractedPercentage >= 80
                                                    AND @ProjectContractedPercentage <= 100
                                                THEN CONCAT(@StarUnicode , @StarUnicode , @StarUnicode , @StarUnicode , @StarUnicode)
                                            END)


            RETURN
            @NewProjectContractedPercentage

        END

So I receive values like:

What I need to do in c# to convert this value in stars?, I don't know what is the difference if I have unicode value in c# code or receiving from stored procedure. I think it's exactly the same but is not working so I don't have an idea why not. Help is very appreciated!

This might be of interest I think: https://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar — ADyson, May 15 '19 at 20:21
Why not just store a value 0 to 5 indicating how many stars? If you ever change the idiom to 'thumbs up' or some other symbol it will be trivial to change. You almost never have to loop thru a DGV to control appearance - use the cellformatting or rowprepaint events — Ňɏssa Pøngjǣrdenlarp, May 15 '19 at 20:25
I alter my function to nvarchar but still getting same result @ADyson — Jonathan, May 15 '19 at 20:32

score 2 · Accepted Answer · answered May 15 '19 at 20:33

2

You need two things. 1. to use NVARCHAR because you are using Unicode. 2. NCHAR function which converts codes to characters.

So instead of @StarUnicode VARCHAR(10) = '\u2605';

Use: @StarUnicode NVARCHAR(10) = NCHAR(0x2605);

answered May 15 '19 at 20:33

MotKohn

3,485
1
24
41

score 1 · Answer 2 · answered May 15 '19 at 20:42

The below change should work. I tested it on SQL 2017.

The character (star) you're working with is a Unicode character. When working with Unicode characters, you should use NCHAR and NVARCHAR.

ALTER FUNCTION [HELPER].[udf_ProjectContractedPercentage_Set]
(
             @ProjectContractedPercentage INT
)
RETURNS NVARCHAR(MAX)
AS
        BEGIN
            DECLARE @NewProjectContractedPercentage NVARCHAR(MAX)
                    ,@StarUnicode  NCHAR(1) = NCHAR(9733);

          SELECT
             @NewProjectContractedPercentage = (CASE
                                                WHEN @ProjectContractedPercentage <= 20
                                                THEN @StarUnicode
                                                WHEN @ProjectContractedPercentage > 20
                                                    AND @ProjectContractedPercentage < 40
                                                THEN CONCAT(@StarUnicode , @StarUnicode)
                                                WHEN @ProjectContractedPercentage >= 40
                                                    AND @ProjectContractedPercentage < 60
                                                THEN CONCAT(@StarUnicode , @StarUnicode , @StarUnicode)
                                                WHEN @ProjectContractedPercentage >= 60
                                                    AND @ProjectContractedPercentage < 80
                                                THEN CONCAT(@StarUnicode , @StarUnicode , @StarUnicode , @StarUnicode )
                                                WHEN @ProjectContractedPercentage >= 80
                                                    AND @ProjectContractedPercentage <= 100
                                                THEN CONCAT(@StarUnicode , @StarUnicode , @StarUnicode , @StarUnicode , @StarUnicode)
                                            END)


            RETURN
            @NewProjectContractedPercentage

        END

Format unicode before get from database

2 Answers2