Recently I have a requirement that log down the message when an error occurs on the server. If the error was caused by execution of SQL statement, log that as well.
It came to a problem that when the SQL is carried by a SqlCommand object, there is no any built-in function to get back the SQL statement in a string format. I wrote a small VB module to do the job.
The primary idea is going through the CommandText, record every place (replace index) that need to replace the parameter name (starts with “@"), and then replace them.
The code is written in VB.NET, but it should not be difficult to convert to C# by using http://www.developerfusion.com/tools/convert/vb-to-csharp/
Usage:
smply call SqlCommandToSql and pass in a SqlCommand object.
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "update abcd set a=@a,b=@ab, c=@abc where cd=@a"
cmd.Parameters.Add("@a", SqlDbType.VarChar).Value = "a@aa"cmd.Parameters.Add("@ab", SqlDbType.Bit).Value = Falsecmd.Parameters.Add("@abc", SqlDbType.DateTime).Value = NowMsgBox(SqlCommandToSql(cmd))
source code:
Imports System.Data.SqlClient
Friend Module Module1
Private Class ReplaceIndex
Implements IComparable
Public Index As Integer
Public ParaName As String
Public Sub New(ByVal idx As Integer, ByVal para As String)
Index = idx
ParaName = para
End Sub
Public Function CompareTo(ByVal obj As Object) As Integer Implements System.IComparable.CompareTo
Dim r As ReplaceIndex = DirectCast(obj, ReplaceIndex)
Return Me.Index - r.Index
End Function
End Class
Private Function GetParaValue(ByVal para As SqlParameter) As String
Dim value As String = ""
If para.Value Is Nothing Then
'it should throw an error if an parameter had not set value
'but here just make it through
value = "Nothing"
End If
If para.Value Is DBNull.Value Then
value = "NULL"
End If
value = para.Value.ToString()
'special handle for some types
Select Case para.SqlDbType
Case SqlDbType.VarChar, SqlDbType.Char, SqlDbType.Text, SqlDbType.NChar, SqlDbType.NText, SqlDbType.NVarChar
value = "'" & value.Replace("'", "''") & "'"
Case SqlDbType.Date, SqlDbType.DateTime, SqlDbType.DateTime2, SqlDbType.Time
value = "'" & value & "'"
Case SqlDbType.Binary, SqlDbType.Image, SqlDbType.VarBinary, SqlDbType.Timestamp
value = "0x" & BitConverter.ToString(TryCast(para.Value, Byte())).Replace("-", "")
Case SqlDbType.Bit
If TypeOf para.Value Is Boolean Then
value = IIf(para.Value, "1", "0")
End If
End Select
Return value
End Function
Private Sub Replace(ByRef source As String, ByVal startIdx As Integer, ByVal paraName As String, ByVal paraType As SqlDbType, ByVal value As String)
Dim len As Integer = paraName.Length
source = source.Remove(startIdx, len)
source = source.Insert(startIdx, value)
End Sub
Public Function SqlCommandToSql(ByVal cmd As SqlCommand) As String
'loop through the parameters
'record the start index where need to replace every para name
'loop the index array, replace para name one by one
'when replace one, need to adjust the rest indexs, since the string length might be changed
Dim index As New Hashtable()
Dim sql As String = cmd.CommandText
For Each p As SqlParameter In cmd.Parameters
FindIndex(index, sql, 0, p)
'index.Add(p, idx)
Next
Dim keyCollection As ICollection = index.Keys
Dim keys As ReplaceIndex() = New ReplaceIndex(keyCollection.Count - 1) {}
keyCollection.CopyTo(keys, 0)
'sort the index first
Array.Sort(keys)
Dim offset As Integer = 0
'loop through each replace point(index)
For i As Integer = 0 To keys.Length - 1
Dim r As ReplaceIndex = DirectCast(keys(i), ReplaceIndex)
Dim p As SqlParameter = DirectCast(index(r), SqlParameter)
Dim value As String = GetParaValue(p)
r.Index += offset
Replace(sql, r.Index, p.ParameterName, p.SqlDbType, value)
'calc the total offset
offset += value.Length - p.ParameterName.Length
Next
Return sql
End Function
Private Sub FindIndex(ByVal ht As Hashtable, ByVal source As String, ByVal startIdx As Integer, ByVal para As SqlParameter)
Dim idx As Integer = source.IndexOf(para.ParameterName, startIdx)
If idx < 0 Then
'no more match is found
Exit Sub
End If
Dim idx2 As Integer = source.LastIndexOf(para.ParameterName)
If idx = idx2 Then
'only one match in the remaining string
idx2 += para.ParameterName.Length
If idx2 >= source.Length Then
Dim r As New ReplaceIndex(idx, para.ParameterName)
ht.Add(r, para)
Else
Dim nextChar As String = source.Substring(idx2, 1)
'if the next char is special charater, this is the match we are looking for
If " ~!@#$%^&*()+-={}[]\|;:,./?'""".Contains(nextChar) Then
Dim r As New ReplaceIndex(idx, para.ParameterName)
ht.Add(r, para)
End If
End If
Exit Sub
End If
'more than once match the parameter name
'e.g. @a will match @a, @ab and @abc, etc.
'store each start index
Dim index As New List(Of Integer)
index.Add(idx)
'index.Add(idx2)
idx = source.IndexOf(para.ParameterName, idx + para.ParameterName.Length)
While idx <> idx2
index.Add(idx)
idx = source.IndexOf(para.ParameterName, idx + para.ParameterName.Length)
End While
'find the actual match index
For Each i As Integer In index
idx2 = i + para.ParameterName.Length
startIdx = idx2
If idx2 = source.Length Then
'this match is the last one (reach the end of source string)
Dim r As New ReplaceIndex(i, para.ParameterName)
ht.Add(r, para)
Exit Sub
Else
Dim nextChar As String = source.Substring(idx2, 1)
'if the next char is special charater, this is the match we are looking for
If " ~!@#$%^&*()+-={}[]\|;:,./?'""".Contains(nextChar) Then
Dim r As New ReplaceIndex(i, para.ParameterName)
ht.Add(r, para)
FindIndex(ht, source, startIdx, para)
Exit Sub
End If
End If
Next
FindIndex(ht, source, startIdx, para)
End Sub
End Module