CODEWITHSUNDEEP
c#sqlado.net
Mandar Jogalekar
Mandar Jogalekar

Reputation: 3281

write sql command to text file

I want to write sql command to text file with parameter values. Following is my code for replacing parameter with appropriate values.

string commandText = commandInfo.CommandText;

if (commandInfo.Parameters.Count > 0)
{
    foreach (SqlParameter parameter in commandInfo.Parameters)
    {
        commandText=commandText.Replace(parameter.ParameterName,parameter.Value==(object)DBNull.Value?(object)DBNull.Value:("'"+parameter.Value.ToString()+"'"));
    }
}

the catch is although all other parameter values are replaced correctly.those having null values are taken up as blank i.e 'parameter1',,'param2'

between the two is the null valued parameter in final string.

What can be the alternative?

Upvotes: 2

Views: 598

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062600

Frankly, replacing the parameters with values is (IMO) the wrong thing to do; what we do in mini-profiler is to spoof declare statements at the top of the output, so that you can copy and paste it into SSMS, without needing to worry about what is a parameter and what was hard-coded in the original TSQL. For example, glancing at the mini-profiler output for this page, I see

DECLARE @id int = 18121022,
        @type tinyint = 10;

(and then lots of tsql that is very specific to us)

You can glance at the mini-profiler code to see how we output this, but basically it just involves walking over the parameters, writing a simple declare. If you are using something like ASP.NET, mini-profiler also avoids the need to write a file (instead making it available live on the site, to your developers).

Upvotes: 1

Related Questions