本来想自己去实现这个功能的,后来吧,经过一番深思熟虑,我觉得还是太麻烦了。所以,打开Github,搜索现成的轮子。。。
即便是在GIithub上,关于用c#来做这个功能的我也没找到几个(也可能是我搜索姿势不太标准)。
说实话,里面的代码我也没有去研究,但大致就是把SQL语句经过一系列分析后,转换成HTM文件,然后用WebBrowser加载出来。看起来还是比较简单的,但是作者写的比较复杂,使我丧失了仔细研读的耐心。。。.
不过基于上面这种思想的话,我倒是觉得大可不必非要找C#语言的了,也可以同步看下js的,反正最后是加载html,直接拿过来用可能也比较简单。
原作者的这个工具写的比较完善,v2版本用了dev控件做了美化,而且是实时(定时)检测sql语句进行格式化。如下:
我就做的比较简单了,页面还是仿照了原作者的布局,但是去掉了一些代码,为了更简单的调用。。。
可以直接把BaiSqlFormatLib.dll拿来调用,或者集成源代码到项目中。
实现功能:
- 输入SQL语句进行格式化
开发环境:
- 开发工具:Visual Studio 2013
- .NET Framework版本:4.5
实现代码:
public Form1()
{
InitializeComponent();
Init();
}
#region 初始化配置
ISqlTokenizer _tokenizer;
ISqlTokenParser _parser;
ISqlTreeFormatter _formatter;
public void Init()
{
chk_default.Checked = true;
_tokenizer = new BaiSqlFormatLib.Tokenizers.TSqlStandardTokenizer();
_parser = new BaiSqlFormatLib.Parsers.TSqlStandardParser();
}
#endregion
#region 格式化
private void textBox1_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyData == Keys.F6)
{
txt_format.DocumentText = Format(txt_sql.Text);
}
}
private string Format(string inputSql)
{
SetFormatter();
var tokenizedSql = _tokenizer.TokenizeSQL(inputSql, inputSql.Length);
var parsedSql = _parser.ParseSQL(tokenizedSql);
string subSqlHtml = _formatter.FormatSQLTree(parsedSql);
return subSqlHtml;
}
/// <summary>
/// 设置格式化属性
/// </summary>
private void SetFormatter()
{
ISqlTreeFormatter innerFormatter = new BaiSqlFormatLib.Formatters.TSqlStandardFormatter(new BaiSqlFormatLib.Formatters.TSqlStandardFormatterOptions
{
IndentString = "\\s\\s\\s\\s", //缩进内容
SpacesPerTab = 4,
MaxLineWidth = Convert.ToInt32(txt_maxWidth.Text), //单行字符串最大长度
ExpandCommaLists = !chk_columnNotNewline.Checked, //false 字段换行
KeywordAlign = chk_keywordAlign.Checked, //字段对齐
TrailingCommas = true, //true 逗号在字段之后
SpaceAfterExpandedComma = true, //true 逗号后追加空格
ExpandBooleanExpressions = chk_conditionNewline.Checked, //true 条件换行
ExpandCaseStatements = chk_expandCase.Checked, //true case when换行
ExpandBetweenConditions = chk_expandBetween.Checked, //true between 换行
ExpandInLists = chk_expandIn.Checked, //true in 内容换行
BreakJoinOnSections = chk_expandOn.Checked, //true join on中on 条件换行
UppercaseKeywords = chk_uppercaseKeywords.Checked, //true 关键字大写
AllUpper = chk_allUpper.Checked, //true 全部大写
HTMLColoring = chk_coloring.Checked, //true HTML颜色标记 默认为true
KeywordStandardization = true,//true 关键字标准化
NewStatementLineBreaks = 2, //新语句换行数
NewClauseLineBreaks = 1,//遇到关键字 换行数
AllIndent = chk_allIndent.Checked, //整体缩进一个IndentString
AsAlign = chk_asAlign.Checked, //true as对齐
KeywordLengthOfAs = Convert.ToInt32(txt_asMaxWidth.Text)//as字段的最大长度
});
_formatter = new BaiSqlFormatLib.Formatters.HtmlPageWrapper(innerFormatter);
}
#endregion
#region 页面配置
private void chk_default_CheckedChanged(object sender, EventArgs e)
{
if (chk_default.Checked)
{
chk_custom.CheckState = CheckState.Unchecked;
txt_maxWidth.Text = "170";
chk_columnNotNewline.CheckState = CheckState.Unchecked;
chk_keywordAlign.CheckState = CheckState.Checked;
chk_conditionNewline.CheckState = CheckState.Checked;
chk_expandCase.CheckState = CheckState.Checked;
chk_expandBetween.CheckState = CheckState.Unchecked;
chk_expandIn.CheckState = CheckState.Unchecked;
chk_expandOn.CheckState = CheckState.Checked;
chk_uppercaseKeywords.CheckState = CheckState.Unchecked;
chk_allUpper.CheckState = CheckState.Unchecked;
chk_coloring.CheckState = CheckState.Checked;
chk_allIndent.CheckState = CheckState.Checked;
chk_asAlign.CheckState = CheckState.Checked;
txt_asMaxWidth.Text = "35";
chk_addSemicolon.CheckState = CheckState.Checked;
txt_maxWidth.Enabled = false;
chk_columnNotNewline.Enabled = false;
chk_keywordAlign.Enabled = false;
chk_conditionNewline.Enabled = false;
chk_expandCase.Enabled = false;
chk_expandBetween.Enabled = false;
chk_expandIn.Enabled = false;
chk_expandOn.Enabled = false;
chk_uppercaseKeywords.Enabled = false;
chk_allUpper.Enabled = false;
chk_coloring.Enabled = false;
chk_allIndent.Enabled = false;
chk_asAlign.Enabled = false;
txt_asMaxWidth.Enabled = false;
chk_addSemicolon.Enabled = false;
}
else
chk_custom.CheckState = CheckState.Checked;
}
private void chk_custom_CheckedChanged(object sender, EventArgs e)
{
if (chk_custom.Checked)
{
chk_default.CheckState = CheckState.Unchecked;
chk_columnNotNewline.Enabled = true;
chk_keywordAlign.Enabled = true;
chk_conditionNewline.Enabled = true;
chk_expandCase.Enabled = true;
chk_expandBetween.Enabled = true;
chk_expandIn.Enabled = true;
chk_expandOn.Enabled = true;
chk_uppercaseKeywords.Enabled = true;
chk_allUpper.Enabled = true;
chk_coloring.Enabled = true;
chk_allIndent.Enabled = true;
chk_asAlign.Enabled = true;
chk_addSemicolon.Enabled = true;
txt_maxWidth.Enabled = true;
txt_asMaxWidth.Enabled = true;
}
else
chk_default.CheckState = CheckState.Checked;
}
#endregion
实现效果:
在某些配置上我写的可能有点问题,也可能是git上的版本有问题,但不重要了,我只是想要格式化的功能而已。
为了表示对原作者的尊重,这里放下Git地址:
https://github.com/baihongbin/SqlFormat