Last active
August 29, 2015 14:21
-
-
Save Implem/82870b394fd71d5ba5b0 to your computer and use it in GitHub Desktop.
c# SqlCommandのデバッグ用テキストファイルを出力する ref: http://qiita.com/Implem/items/ce4f03c60f9011d0e37f
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
use [dbname]; | |
declare @p1 nvarchar(4); set @p1 = 'hoge'; | |
select [abc].[a], [abc].[b], [abc].[c], | |
[abc].[d], [abc].[e] | |
from [abc] | |
where @p1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.Collections.Generic; | |
using System.Data.SqlClient; | |
using System.Diagnostics; | |
using System.IO; | |
using System.Linq; | |
using System.Text; | |
using System.Text.RegularExpressions; | |
namespace Implem.Sample | |
{ | |
public static class Debugs | |
{ | |
[Conditional("DEBUG")] | |
public static void WriteCommandTextForDebugging(string dbName, SqlCommand sqlCommand) | |
{ | |
// SqlCommandデバッグ用テキスト作成 | |
var commandTextForDebugging = new StringBuilder(); | |
commandTextForDebugging.Append("use [", dbName, "];\r\n"); | |
commandTextForDebugging.Append(GetDeclareParametersText(sqlCommand)); | |
commandTextForDebugging.Append(GetFormattedCommandText(sqlCommand)); | |
// テキストファイルに書き込み | |
WriteFile(commandTextForDebugging.ToString(), @"c:\temp\CommandTextForDebugging.sql"); | |
} | |
private static string GetFormattedCommandText(SqlCommand sqlCommand) | |
{ | |
var commandTextFormatted = new StringBuilder(); | |
var commandTextTemp = sqlCommand.CommandText; | |
// 特定の命令の前に改行を入れて読みやすくする | |
commandTextTemp.RegexMatches( | |
GetDelimitersForNewLine().Select(o => @"(?<!\n)\b" + o + @"\b").Join("|"), | |
RegexOptions.Multiline) | |
.Cast<Match>() | |
.Select(o => o.Value) | |
.Distinct() | |
.ForEach(match => | |
commandTextTemp = commandTextTemp.Replace(match, "\r\n" + match)); | |
// 1行あたりのカンマ区切りが3つを超えたら改行を入れて読みやすくする | |
commandTextTemp.SplitReturn().ForEach(line => | |
commandTextFormatted.Append(line.Split(',') | |
.Chunk(3) | |
.Select(o => string.Join(",", o)) | |
.Join(",\r\n") + "\r\n")); | |
return commandTextFormatted.ToString(); | |
} | |
private static string GetDeclareParametersText(SqlCommand sqlCommand) | |
{ | |
var commandParameters = new StringBuilder(); | |
// パラメータ宣言を作成する | |
foreach (SqlParameter parameter in sqlCommand.Parameters) | |
{ | |
commandParameters.Append( | |
"{0, -50}".Params(GetDeclareParameterText(parameter)), | |
"set @", parameter.ParameterName, | |
" = '", | |
parameter.Value.ToString(), | |
"';\r\n"); | |
} | |
return commandParameters.ToString(); | |
} | |
private static string GetDeclareParameterText(SqlParameter parameter) | |
{ | |
if (parameter.Size == 0) | |
{ | |
return "declare @{0} {1}; ".Params( | |
parameter.ParameterName, | |
parameter.SqlDbType.ToString().ToLower()); | |
} | |
else | |
{ | |
return "declare @{0} {1}({2}); ".Params( | |
parameter.ParameterName, | |
parameter.SqlDbType.ToString().ToLower(), | |
parameter.Size); | |
} | |
} | |
private static string[] GetDelimitersForNewLine() | |
{ | |
// ここに列挙したキーワードの手前で改行(好みで追加/削除) | |
return new string[] | |
{ | |
"begin", "commit", "select", "insert into", "values", "update", "delete", | |
"from", "inner join", "left outer join", "where","order by" | |
}; | |
} | |
private static IEnumerable<IEnumerable<T>> Chunk<T>( | |
this IEnumerable<T> ienumerable, int size) | |
{ | |
while (ienumerable.Any()) | |
{ | |
yield return ienumerable.Take(size); | |
ienumerable = ienumerable.Skip(size); | |
} | |
} | |
public static void WriteFile(string str, string path, string encoding = "utf-8") | |
{ | |
using (var writer = new StreamWriter(path, false, Encoding.GetEncoding(encoding))) | |
{ | |
writer.Write(str); | |
} | |
} | |
private static string Join(this IEnumerable<string> list, string delimiter = ",") | |
{ | |
return string.Join(delimiter, list.ToArray()); | |
} | |
private static MatchCollection RegexMatches( | |
this string str, string pattern, RegexOptions regexOptions) | |
{ | |
return Regex.Matches(str, pattern, regexOptions); | |
} | |
private static void Append(this StringBuilder stringBuilder, params string[] strings) | |
{ | |
strings.ForEach(str => stringBuilder.Append(str)); | |
} | |
private static string Params(this string format, params object[] args) | |
{ | |
return string.Format(format, args); | |
} | |
private static string[] SplitReturn(this string str) | |
{ | |
return str.Replace("\r\n", "\n").Split('\n'); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.Data.SqlClient; | |
namespace Implem.Sample | |
{ | |
class Program | |
{ | |
static void Main(string[] args) | |
{ | |
var cmd = new SqlCommand("select [abc].[a], [abc].[b], [abc].[c], [abc].[d], [abc].[e] from [abc] where @p1"); | |
cmd.Parameters.AddWithValue("p1", "hoge"); | |
Debugs.WriteCommandTextForDebugging("dbname", cmd); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment