C# – Get the generated SQL statement from a SqlCommand object

ado.netcvb.net

I have the following code:

Using cmd As SqlCommand = Connection.CreateCommand
    cmd.CommandText = "UPDATE someTable SET Value = @Value"
    cmd.CommandText &= " WHERE Id = @Id"
    cmd.Parameters.AddWithValue("@Id", 1234)
    cmd.Parameters.AddWithValue("@Value", "myValue")
    cmd.ExecuteNonQuery
End Using

I wonder if there is any way to get the final SQL statment as a String, which should look like this:

UPDATE someTable SET Value = "myValue" WHERE Id = 1234

If anyone wonders why I would do this:

for logging (failed) statements
for having the possibility to copy & paste it to the Enterprise Manager for testing purposes

Best Answer

For logging purposes, I'm afraid there's no nicer way of doing this but to construct the string yourself:

string query = cmd.CommandText;

foreach (SqlParameter p in cmd.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}

Related Solutions

C# – How to get the index of the current iteration of a foreach loop

Ian Mercer posted a similar solution as this on Phil Haack's blog:

foreach (var item in Model.Select((value, i) => new { i, value }))
{
    var value = item.value;
    var index = item.i;
}

This gets you the item (item.value) and its index (item.i) by using this overload of LINQ's Select:

the second parameter of the function [inside Select] represents the index of the source element.

The new { i, value } is creating a new anonymous object.

Heap allocations can be avoided by using ValueTuple if you're using C# 7.0 or later:

foreach (var item in Model.Select((value, i) => ( value, i )))
{
    var value = item.value;
    var index = item.i;
}

You can also eliminate the item. by using automatic destructuring:

<ol>
foreach ((MyType value, Int32 i) in Model.Select((value, i) => ( value, i )))
{
    <li id="item_@i">@value</li>
}
</ol>

C# – How to get the path of the assembly the code is in

I've defined the following property as we use this often in unit testing.

public static string AssemblyDirectory
{
    get
    {
        string codeBase = Assembly.GetExecutingAssembly().CodeBase;
        UriBuilder uri = new UriBuilder(codeBase);
        string path = Uri.UnescapeDataString(uri.Path);
        return Path.GetDirectoryName(path);
    }
}

The Assembly.Location property sometimes gives you some funny results when using NUnit (where assemblies run from a temporary folder), so I prefer to use CodeBase which gives you the path in URI format, then UriBuild.UnescapeDataString removes the File:// at the beginning, and GetDirectoryName changes it to the normal windows format.

Best Answer

Related Solutions

C# – How to get the index of the current iteration of a foreach loop

C# – How to get the path of the assembly the code is in

Related Topic