Comment capturer le SQL avec des paramètres substitués ? (.NET, SqlCommand)

Question

Existe-t-il un moyen simple de récupérer une instruction SQL complète après la substitution de paramètres ? Je veux garder un fichier journal de tous les SQL que ce programme exécute.

Ou bien, si je veux faire cela, dois-je simplement me débarrasser des paramètres et faire toute la requête à l'ancienne, dans une seule grande chaîne ?

Exemple simple : Je veux capturer la sortie :

SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1

de ce code :

    Dim subCatSQL As String = "SELECT subcatId FROM EnrollmentSubCategory WHERE catid = @catId"
    Dim connectionString As String = "X"
    Dim conn As New SqlConnection(connectionString)
    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If
    Dim cmd As New SqlCommand(subCatSQL, conn)
    With cmd
        .Parameters.Add(New SqlParameter("@catId", SqlDbType.Int, 1)) 
    End With

    Console.WriteLine("Before: " + cmd.CommandText)
    cmd.Prepare()
    Console.WriteLine("After: " + cmd.CommandText)

J'avais supposé que Prepare() ferait les substitutions, mais apparemment non.

Réflexions ? Des conseils ? Merci d'avance.

Answer 1

Non, .Prepare ne fait pas ça, et en fait rien dans la commande ne le fait. Les valeurs des paramètres ne sont jamais substituées dans la chaîne de commande réelle. Elle est envoyée à la base de données séparément, ce qui est une bonne chose pour plusieurs raisons :

• Cela permet à sqlserver (ou d'autres bases de données) de mettre en cache le plan de requête pour votre requête et de le réutiliser la prochaine fois. Lorsqu'une chaîne de texte de commande différente est envoyée à la base de données à chaque fois, la base de données doit développer un plan à chaque fois.
• En envoyant les paramètres au db de manière compartimentée, il existe une défense naturelle contre les attaques par injection sql.

Sauf si vous utilisez une très vieille base de données (sql server 7 ?), .Prepare() n'est pas nécessaire et ne fait rien pour vous. Auparavant, il était utile car il compilait la requête sur le serveur, mais cela est fait pour vous automatiquement maintenant. Je n'ai pas utilisé .Prepare() depuis longtemps.

Hmmmmm. Vous cherchez aquí il semble .Prepare() fait quand même quelque chose pour vous : si une valeur est plus grande que la longueur définie pour le paramètre, le .Prepare() tronque la valeur, de sorte que lorsque vous exécutez, vous n'obtenez pas l'erreur et la requête réussit. cool.

Answer 2

J'ajoute généralement une fonction utilitaire à mes projets qui, étant donné un objet DbCommand (la classe mère de OracleCommand, SqlCommand, etc.), enregistre la requête et les valeurs des paramètres. Cela pourrait ressembler à ceci :

Public Shared Sub LogQuery(ByRef cmd As DbCommand)
    If cmd.CommandText Is Nothing Or cmd.CommandText.Length = 0 Then
        Return
    End If

    Dim logFile As CLog = New CLog("sql.log")
    Dim msg As New StringBuilder

    msg.AppendLine("*** Query ***")
    msg.AppendLine(cmd.CommandText)
    msg.AppendLine("*** End Query ***")
    msg.AppendLine("*** Parameters ***")

    For Each p As DbParameter In cmd.Parameters
        msg.AppendLine(String.Format("{0}: {1}", p.ParameterName, p.Value.ToString()))
    Next

    msg.AppendLine("*** End Parameters ***")

    logFile.WriteLog(msg.ToString() & System.Environment.NewLine)
End Sub

Pendant que j'écrivais ceci, il m'est venu à l'esprit qu'au lieu d'enregistrer les valeurs des paramètres séparément, vous pourriez faire un petit String.Replace()ing pour substituer les valeurs des paramètres dans la requête et ensuite l'enregistrer :

For Each p As DbParameter In cmd.Parameters
    msg = msg.Replace(p.ParameterName, p.Value.ToString())
Next

Answer 3

Les paramètres ne sont pas substitués. Le sql tel quel, et les paramètres sont eux-mêmes passés en tant que paramètres à la proc stockée du système sp_executesql.

Answer 4

Ce qui est envoyé au serveur SQL n'est pas des variables substituées par des valeurs mais une déclaration préparée, de cette façon elle peut être réutilisée.

Ouvrez le profileur dans SQL Server et vous verrez que ce qui est exécuté n'est pas SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1

Answer 5

C'était récemment demandé ici . Comme le soulignent les autres réponses, les paramètres sont envoyés hors bande.

Le Profiler est probablement ce que vous voulez utiliser pour la capture si vous voulez le faire au niveau du serveur, et vous pouvez créer une trace et l'utiliser comme tâche de démarrage .