Get Parameters of sqlcommand to string

Question

I'm creating an error log for a webpage. I need a way to get the parameters from a SQL Command to output to string so that the parameters passed through keep the query as dynamic as possible for use throughout the site.

Heres the SQL Command Type I'm talking about:

SqlCommand cmdErrReport = new SqlCommand("ERROR_LOG", conn.sbConn);
cmdErrReport.CommandType = CommandType.StoredProcedure;
cmdMarineResort.Parameters.Add("@Statement", SqlDbType.VarChar).Value = "FindSeason";

I was trying something like this:

cmdMarineResort.Parameters.ToString()

but I can't find within the .Parameters query how to find parameter names or values.

How would I be able to get the values to string? Would i have to list the parameters and the loop through that list writing the parameter and its value to string? If so, how?

Thirisangu Ramanathan · Accepted Answer · 2014-11-18 12:44:08Z

4

cmdMarineResort.Parameters is a SqlParameterCollection.

You can iterate through it using a foreach statement:

   foreach(SqlParameter Para in cmdMarineResort.Parameters)
    {
       Console.WriteLine((string)Para.Value); //value of the parameter as an object
    }

edited Nov 18, 2014 at 12:44

Thirisangu Ramanathan

6141 gold badge12 silver badges20 bronze badges

answered Nov 18, 2014 at 12:12

Slappywag

1,2531 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TLCONE Over a year ago

Thanks Chris this is exactly what I needed. string ExtParams = ""; foreach (SqlParameter Para in cmdMarineResort.Parameters) { ExtParams = ExtParams + Para.ParameterName + '=' + Para.Value + ", "; } I needed it in a string so I just altered it a tiny bit, it's working great. Thanks for the help!

Craig Jones · Accepted Answer · 2022-04-23 04:46:33Z

0

This worked for me Using MSSQL Server:

    private static string getQueryFromCommand(SqlCommand cmd)
    {
        string CommandTxt = cmd.CommandText;

        foreach (SqlParameter parms in cmd.Parameters)
        {
            string val = String.Empty;
            
            
                
            CommandTxt+=parms.ParameterName + "=";

            if ((parms.Value == null)) // string.IsNullOrEmpty(parms.Value))
            {
                CommandTxt+="NULL,  ";
            }
            else
            {
                CommandTxt+=(string)parms.Value.ToString() + ",  ";
            }
        }
        return (CommandTxt);
    }

answered Apr 23, 2022 at 4:46

Craig Jones

211 bronze badge

Collectives™ on Stack Overflow

Get Parameters of sqlcommand to string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related