public string GetSql()
{
Template result = TemplateLibrary.Select;
result.Append(SnippetLibrary.Table(this.TableName, this.Format, this.TableAlias));
result.Append(SnippetLibrary.Columns(this.Columns.GetSql(this.TableAlias)));
if (this.Join.Count > 0)
{
string joinTable = string.IsNullOrEmpty(this.TableAlias) ? this.TableName : this.TableAlias;
result.Append(SnippetLibrary.Join(this.Join.GetSql(joinTable)));
}
if (this.Where.Count > 0)
{
result.Append(SnippetLibrary.Where(this.Where.GetSql(this.TableAlias)));
}
if (this.GroupBy.Count > 0)
{
result.Append(SnippetLibrary.GroupBy(this.GroupBy.GetSql(this.TableAlias)));
}
if (this.OrderBy.Count > 0)
{
result.Append(SnippetLibrary.OrderBy(this.OrderBy.GetSql(this.TableAlias)));
}
return(result.GetSql(this.Format));
}
public string GetSql(bool EndOfStatement = true)
{
string table = Reflection.GetTableName <T>();
ITemplate result = TemplateLibrary.Select;
result.Append(SnippetLibrary.Table(table, this.TableAlias));
result.Append(SnippetLibrary.Columns(this.Columns.GetSql(this.TableAlias)));
if (this.Join.Count > 0)
{
string joinTable = string.IsNullOrEmpty(this.TableAlias) ? table : this.TableAlias;
result.Append(SnippetLibrary.Join(this.Join.GetSql(joinTable)));
}
if (this.Where.Count > 0)
{
result.Append(SnippetLibrary.Where(this.Where.GetSql(this.TableAlias)));
}
if (this.GroupBy.Count > 0)
{
result.Append(SnippetLibrary.GroupBy(this.GroupBy.GetSql(this.TableAlias)));
}
if (this.OrderBy.Count > 0)
{
result.Append(SnippetLibrary.OrderBy(this.OrderBy.GetSql(this.TableAlias)));
}
return(result.GetSql(EndOfStatement));
}
public string GetSql(bool endOfStatement = true)
{
var table = Reflection.GetTableName <T>();
var result = TemplateLibrary.Select;
result.Append(SnippetLibrary.Table(table, TableAlias));
result.Append(SnippetLibrary.Columns(Columns.GetSql(TableAlias)));
if (Join.Count > 0)
{
var joinTable = string.IsNullOrEmpty(TableAlias) ? table : TableAlias;
result.Append(SnippetLibrary.Join(Join.GetSql(joinTable)));
}
if (Where.Count > 0)
{
result.Append(SnippetLibrary.Where(Where.GetSql(TableAlias)));
}
if (GroupBy.Count > 0)
{
result.Append(SnippetLibrary.GroupBy(GroupBy.GetSql(TableAlias)));
}
if (OrderBy.Count > 0)
{
result.Append(SnippetLibrary.OrderBy(OrderBy.GetSql(TableAlias)));
}
return(result.GetSql(endOfStatement));
}
public void TemplateDelete()
{
ITemplate t = TemplateLibrary.Delete;
t.Append(SnippetLibrary.Table("users"),
SnippetLibrary.Where("[id]=@id"));
string sql = t.GetSql();
Assert.AreEqual(sql, "DELETE FROM [users] WHERE [id]=@id;");
}
public string GetSql()
{
Template result = TemplateLibrary.Insert;
result.Append(SnippetLibrary.Table(this.TableName, this.Format));
result.Append(SnippetLibrary.Columns(this.Columns.GetSql(this.TableAlias)));
result.Append(SnippetLibrary.Values(this.Values.GetSql()));
return(result.GetSql(this.Format));
}
public void TemplateInsert()
{
ITemplate t = TemplateLibrary.Insert;
t.Append(SnippetLibrary.Table("users"),
SnippetLibrary.Columns("[a],[b],[c]"),
SnippetLibrary.Values("@a,@b,@c"));
string sql = t.GetSql();
Assert.AreEqual(sql, "INSERT INTO [users]([a],[b],[c]) VALUES(@a,@b,@c);");
}
public void TemplateUpdate()
{
ITemplate t = TemplateLibrary.Update;
t.Append(SnippetLibrary.Table("users"),
SnippetLibrary.Sets("[a]=@a,[b]=@b,[c]=@c"),
SnippetLibrary.Where("age>=18"));
string sql = t.GetSql();
Assert.AreEqual(sql, "UPDATE [users] SET [a]=@a,[b]=@b,[c]=@c WHERE age>=18;");
}
public string GetSql(bool EndOfStatement = true)
{
string table = Reflection.GetTableName <T>();
ITemplate result = TemplateLibrary.Insert;
result.Append(SnippetLibrary.Table(table));
result.Append(SnippetLibrary.Columns(this.Columns.GetSql(this.TableAlias)));
result.Append(SnippetLibrary.Values(this.Values.GetSql()));
return(result.GetSql(EndOfStatement));
}
public void TemplateEndOfStatementSelect()
{
ITemplate t = TemplateLibrary.Select;
t.Append(SnippetLibrary.Table("users"),
SnippetLibrary.Columns("*"),
SnippetLibrary.Where("age>=18"),
SnippetLibrary.OrderBy("age ASC"));
string sql = t.GetSql(false);
Assert.AreEqual(sql, "SELECT * FROM [users] WHERE age>=18 ORDER BY age ASC");
}
public void TemplateSelect()
{
Template t = TemplateLibrary.Select;
t.Append(SnippetLibrary.Table("users", global::SqlBuilder.Format.MsSQL),
SnippetLibrary.Columns("*"),
SnippetLibrary.Where("age>=18"),
SnippetLibrary.OrderBy("age ASC"));
string sql = t.GetSql(global::SqlBuilder.Format.MsSQL);
Assert.AreEqual(sql, "SELECT * FROM [users] WHERE age>=18 ORDER BY age ASC;");
}
public string GetSql()
{
Template result = TemplateLibrary.Update;
result.Append(SnippetLibrary.Table(this.TableName, this.Format, this.TableAlias));
result.Append(SnippetLibrary.Sets(this.Sets.GetSql(this.TableAlias)));
if (this.Where.Count > 0)
{
result.Append(SnippetLibrary.Where(this.Where.GetSql(this.TableAlias)));
}
return(result.GetSql(this.Format));
}
public string GetSql(bool EndOfStatement = true)
{
string table = Reflection.GetTableName <T>();
ITemplate result = TemplateLibrary.Delete;
result.Append(SnippetLibrary.Table(table, this.TableAlias));
if (this.Where.Count > 0)
{
result.Append(SnippetLibrary.Where(this.Where.GetSql(tableAlias: this.TableAlias)));
}
return(result.GetSql(EndOfStatement));
}
public string GetSql()
{
Template result = TemplateLibrary.Delete;
result.Append(SnippetLibrary.Table(this.TableName, this.Format, this.TableAlias));
if (this.Where.Count > 0)
{
string whereSql = this.Where.GetSql(tableAlias: this.TableAlias);
Snippet whereSnipper = SnippetLibrary.Where(whereSql);
result.Append(whereSnipper);
}
return(result.GetSql(this.Format));
}
public string GetSql(bool endOfStatement = true)
{
var pattern = Pattern;
if (endOfStatement)
{
Append(SnippetLibrary.End(Parameters.EndOfStatement.ToString()));
}
foreach (var snippet in _expressions)
{
var text = ESC_START + snippet.Name + ESC_END;
if (pattern.Contains(text))
{
pattern = pattern.Replace(text, snippet.Prefix + snippet.Code + snippet.Postfix);
}
}
pattern = Regex.Replace(pattern, ESC_START + "([A-Za-z0-9_]+)" + ESC_END, string.Empty, RegexOptions.IgnoreCase | RegexOptions.Singleline);
return(pattern);
}