Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
c# SqlCommandのデバッグ用テキストファイルを出力する ref: http://qiita.com/Implem/items/ce4f03c60f9011d0e37f
use [dbname];
declare @p1 nvarchar(4); set @p1 = 'hoge';
select [abc].[a], [abc].[b], [abc].[c],
[abc].[d], [abc].[e]
from [abc]
where @p1
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');
}
}
}
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
You can’t perform that action at this time.