DOBON.NET DOBON.NETプログラミング掲示板過去ログ

コマンドテキストからSQLを取り出したい

環境/言語:[VB.NET2005]
分類:[.NET]

こんにちは。

SqlCommand.CommandText プロパティ に
SELECT * FROM dbo.Customers WHERE CustomerID = @CustomerID

このような文字列を割り当てた後に
実際に送付される、@CostomerIDに値が代入された
SQL文を取得したいのですが
どのように行えばよいのでしょうか？


何をしたいかといいますと、
デバッグをしていて、
@hogehoge1 @hogehoge2
という変数が非常に多いSQLを取り出していまして
自分でSQLを投げて動作を確認したいと思っています。

よろしくお願いします。
2010/01/06(Wed) 19:57:07 編集(投稿者)

猫に犬が返事をするのもアレですが。

SqlCommand.Parameters.AddWithValue("@CustomerID", 割り当てるID)

だったはず。
詳しいことはパラメータクエリか何かで検索すれば出てくると思いますよ。
2010/01/06(Wed) 20:14:18 編集(投稿者)

あ、すみません。
よく読むと割り当てた後の文字でしたか。
先ほどのは忘れてください。

通信時にはパラメータの入った平文のSQL文を投げて、そこからサーバにパラメータに値をBindさせるので割り当て後のSQL文はクライアント側では取得できないと思います。
SQL Server Profiler を使えばサーバーに投げられたクエリをトレースできますが、
これで ADO.NET から投げられたクエリのパラメータの実値を
見ることができたかどうか、すぐ確かめることができないため不明です。

参考までに以下 URL です。

http://msdn.microsoft.com/ja-jp/library/ms173757.aspx
2010/01/07(Thu) 10:49:30 編集(投稿者)

黒犬さん、ひらぽんさん、情報ありがとうございます。

パラメータの入ったSQLは、クライアント側で生成されてから
DBに送られるわけではなく、サーバー側で修正されるのですね。
よくわかりました。
概念を今まで間違っておりました。

SQL Server Profilerを使用して
@hogeを含んだままのSQL文と、@hogeの内容とを取得することはできましたので
手間は少しかかりましたが、
自分で文字列置き換えをして、テスト動作させてみることができました。

ありがとうございます。

とても勉強になりました。


■No26192に返信(ひらぽんさんの記事)
> SQL Server Profiler を使えばサーバーに投げられたクエリをトレースできますが、
> これで ADO.NET から投げられたクエリのパラメータの実値を
> 見ることができたかどうか、すぐ確かめることができないため不明です。
>
> 参考までに以下 URL です。
>
> http://msdn.microsoft.com/ja-jp/library/ms173757.aspx
>
>
解決済み!
■No26194に返信(FutoNekoさんの記事)
> 2010/01/07(Thu) 10:49:30 編集(投稿者)

> パラメータの入ったSQLは、クライアント側で生成されてから
> DBに送られるわけではなく、サーバー側で修正されるのですね。
> よくわかりました。
> 概念を今まで間違っておりました。
>
> SQL Server Profilerを使用して
> @hogeを含んだままのSQL文と、@hogeの内容とを取得することはできましたので
> 手間は少しかかりましたが、
> 自分で文字列置き換えをして、テスト動作させてみることができました。
>
> ありがとうございます。


解決してよかったです。
SQL Server Profiler は動かせる環境にあったのですが、深夜だったので
さすがに .NET のプロジェクト作ってまで試せる余裕がなかったのでした。

しかしトレースされたクエリはパラメータが変換されたものではないのですね。
MySQL だとログにパラメータが変換されたクエリが保存されるので、
SQL Server ももしかしたら同じかな？と思っていたら違うようで残念です。

> 手間は少しかかりましたが、

確かにめんどくさいですよね。＞＜
ありがとうございます。

そうなんですよ。
プロファイラを使って insert ... @hoge1 という文と
@hoge='abc'という文字列は取得できるのですが

手動というか、ちょっとしたスクリプト的なものをつくり
一括置き換えはしているのですがデバッグに手間暇がかかります。

@hoge1 @hoge2 @hogeA @hogeB
というように多用されているプログラムだとかなりきついです。

よりいい方法をご存じの方おられましたら教えてもらいたいです。

■No26195に返信(ひらぽんさんの記事)
> 解決してよかったです。
> SQL Server Profiler は動かせる環境にあったのですが、深夜だったので
> さすがに .NET のプロジェクト作ってまで試せる余裕がなかったのでした。
>
> しかしトレースされたクエリはパラメータが変換されたものではないのですね。
> MySQL だとログにパラメータが変換されたクエリが保存されるので、
> SQL Server ももしかしたら同じかな？と思っていたら違うようで残念です。
>
>>手間は少しかかりましたが、
>
> 確かにめんどくさいですよね。＞＜
SQL Server だと sp_executesql というストアドを使って投げられるので、プロファイラを使っても期待するようなクエリは取得できないですね。

自分の場合は、SqlCommand.CommandText プロパティの文字列と、SqlCommand.Parameters プロパティの内容を使って、自前でパラメータを展開してログに書き出すようにしています。
DB に投げられたクエリと同じではないですが、クライアント側でできる手としてはこのくらいしか思いつかない。
■No26196に返信(FutoNekoさんの記事)
> ありがとうございます。
>
> そうなんですよ。
> プロファイラを使って insert ... @hoge1 という文と
> @hoge='abc'という文字列は取得できるのですが
>
> 手動というか、ちょっとしたスクリプト的なものをつくり
> 一括置き換えはしているのですがデバッグに手間暇がかかります。
>
> @hoge1 @hoge2 @hogeA @hogeB
> というように多用されているプログラムだとかなりきついです。
>
> よりいい方法をご存じの方おられましたら教えてもらいたいです。

TableAdapter を使った場合に限定されますが、こんな方法もあるようです。

http://d.hatena.ne.jp/dotnetmemo/20061125/1164428169
> TableAdapter を使った場合に限定されますが、こんな方法もあるようです。
>
> http://d.hatena.ne.jp/dotnetmemo/20061125/1164428169
>
ソース眺めただけだけど、これってパラメータ展開まではしてないと思う。
CommandText プロパティの値を出力してるだけだし。 
> ソース眺めただけだけど、これってパラメータ展開まではしてないと思う。
> CommandText プロパティの値を出力してるだけだし。

・・・orz
思いっきり外しました。よく見たらそのとおりです。

でも副産物で、BaseClass プロパティなるものを初めて知りました。
自前のクラスを作れば、簡単に IDE で設定できるのですね。
色々検索すると面白そうなことやってるブログが見つかります。

あと BeforeExecuteDbCommandMethod の実装を、
単なるコマンドテキストの出力から実値で置き換えするよう修正して試したら、
出力ウィンドウに綺麗に出力できたのを確認できました。

ちなみに VS2010 bata2 で実装したので、改行箇所に _ は入ってません。

Protected Overridable Sub BeforeExecuteDbCommandMethod(ByVal cmd As DbCommand)

    Dim query As String = cmd.CommandText
    Dim params = From p As DbParameter In cmd.Parameters
                 Select New With {.Name = p.ParameterName, .Value = p.Value.ToString}
    For Each param In params
        query = query.Replace(param.Name, param.Value)
    Next
    Trace.WriteLine(query)

End Sub
DOBON.NET | プログラミング道 | プログラミング掲示板