none
C# - how to record parameter's info when parameter is structured RRS feed

  • Pergunta

  • Hi,

    I'm asking in my favorite forum in case someone already solved this problem although the question is more C# question. I have the following code allowing me to record parameters I'm passing to my SQL command. Today I've added a new structured TVP parameter and that code no longer works correctly. I'm wondering how to adjust it to record my TVP parameter (similar to what we see when we run SQL Profiler)?

    If you already have the code which works and willing to share, that would be great.

    This is my current code:

    if (this.LastCommand.Parameters.Count > 0)
                        {
                            info.Append("declare ");
                            for (int i = 0; i < this.LastCommand.Parameters.Count; i++)
                            {
                                string delimiter = "";
                                var parameter = this.LastCommand.Parameters[i];
                                string sizeInfo = "";
                                if (CharOrDateDbTypes.Contains(parameter.SqlDbType))
                                {
                                    delimiter = "'";
                                    if (parameter.Size == -1)
                                    {
                                        sizeInfo = "(max)";
                                    }
                                    else if (parameter.Size != 0) { sizeInfo = $"({parameter.Size})"; }
                                }
    
                                if (parameter.Precision!=0)
                                {
                                    sizeInfo = $"({parameter.Precision},{parameter.Scale})";
                                }
                                if (parameter.SqlDbType == SqlDbType.Bit)
                                {
                                    delimiter = "'";
                                }
    
                                info.Append($"{parameter.ParameterName} {parameter.SqlDbType.ToString().TrimEnd()}{sizeInfo} = ");
                                
                                // Had to NOT use string.format or appendformat here because if the Value has embedded non-printing
                                // characters, we got "System.FormatException: Input string was not in a correct format" errors.
                                if (parameter.Value != null && parameter.Value!=DBNull.Value)

    E.g. in the profiler I see:

    declare @p5 dbo.siriusType_CharId
    insert into @p5 values(N'HIKEGUIDE1')
    insert into @p5 values(N'HIKEGUIDE2')

    etc. and I'd like to be able to generate this as part of my parameters recording method

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    quarta-feira, 5 de fevereiro de 2020 19:11
    Moderador

Todas as Respostas

  • Hi Naomi,

    According to an old thread, you could not convert a null value to a meaningful value through Convert.  Your best option is to check for null first and then assign a value of 0 (or whatever) to the result (wherever Convert was sending its results too).

    Here is it: Input string was not in a correct format.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    quinta-feira, 6 de fevereiro de 2020 04:23
  • Probably you should detect this kind of parameters:

    if( parameter.SqlDbType == SqlDbType.Structured )

    {

       . . .

    }

    else

    {

       . . . your current code for other parameters

    }

    Inside this if, display the suitable definition. For example, parameter.TypeName is the type of table-valued parameter. See the properties of SqlParameter.

    • Editado Viorel_MVP quinta-feira, 6 de fevereiro de 2020 06:35
    quinta-feira, 6 de fevereiro de 2020 06:33
  • Hi Viorel,

    Yes, I added that case already yesterday (e.g. the if), but I didn't yet figure out how to see the actual rows inside that parameter to produce something similar to the Profiler. May be it's not too important to log them (especially if we would send lots of rows), but it would have been nice, I think.

    So I checked a few MS documentation links, but as always, I didn't find any good info I can use. Say, this one https://docs.microsoft.com/en-us/dotnet/api/system.data.sqldbtype?view=netframework-4.8 doesn't really tell me anything helpful about structured parameter type.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    quinta-feira, 6 de fevereiro de 2020 13:58
    Moderador
  • It seems that parameter.Value is a DataTable: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters. Therefore, if you did not try yet, check this:

       DataTable dt = parameter.Value as DataTable;

    If dt is not null, use Rows to extract the values in a loop. An expression like ‘dt.Rows[i].ItemArray[j].ToString()’ should give a value as a string (i is row, j is column).

    quinta-feira, 6 de fevereiro de 2020 19:21
  • This sounds promising, I'll play with it when I have a chance.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    quinta-feira, 6 de fevereiro de 2020 21:15
    Moderador
  • Hi Naomi,

    Thank you very much for your reply. If Viorel's way works, it's so kind of you to come back and share it with us. By doing so, it will benefit all community members who are having this similar issue.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    sexta-feira, 7 de fevereiro de 2020 01:50