public void Test_1()
{
//结果
var result = new Str();
result.AppendLine("Select [a3].[a],[a1].[b1],[a2].[b2] ");
result.AppendLine("From [b] As [a2] ");
result.Append("Join [c] As [a3] On [a2].[d]=[a3].[e]");
//执行
_builder.Select("a,a1.b1,[a2].[b2]", "a3")
.From("b", "a2")
.Join("c", "a3").On("a2.d", "a3.[e]");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Or_2()
{
//结果
var result = new Str();
result.Append("Where [Email] In (@_p_0,@_p_1)");
//执行
var list = new List <string> {
"a", "b"
};
_clause.Or <Sample>(t => list.Contains(t.Email));
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void TestOn_2()
{
//结果
var result = new Str();
result.AppendLine("Select `a` ");
result.AppendLine("From `Sample` As `b` ");
result.Append("Join `Sample2` As `c` On `b`.`IntValue`<>`c`.`IntValue`");
//执行
_builder.Select("a")
.From <Sample>("b")
.Join <Sample2>("c").On <Sample, Sample2>(t => t.IntValue, t => t.IntValue, Operator.NotEqual);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_In_1()
{
//结果
var result = new Str();
result.Append("Where [user].[Email] In (@_p_0,@_p_1)");
//执行
var list = new List <string> {
"a", "b"
};
_clause.In("user.Email", list);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_NotIn_2()
{
//结果
var result = new Str();
result.Append("Where [Email] Not In (@_p_0,@_p_1)");
//执行
var list = new List <string> {
"a", "b"
};
_clause.NotIn <Sample>(t => t.Email, list);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_RightJoin_5()
{
//结果
var result = new Str();
result.AppendLine("Select [a] ");
result.AppendLine("From [b] ");
result.Append("Right Join c");
//执行
_builder.Select("a")
.From("b")
.AppendRightJoin("c");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_On_3()
{
//结果
var result = new Str();
result.AppendLine("Select [a] ");
result.AppendLine("From [Sample] As [b] ");
result.Append("Join [Sample2] As [c] On [b].[IntValue]<>[c].[IntValue]");
//执行
_builder.Select("a")
.From <Sample>("b")
.Join <Sample2>("c").On <Sample, Sample2>((l, r) => l.IntValue != r.IntValue);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_From_7()
{
//结果
var result = new Str();
result.AppendLine("Select [c] ");
result.Append("From [b].[Sample] As [a], [b].[Sample2] As [b]");
//执行
_builder.Select("c")
.From <Sample>("a", "b")
.From <Sample2>("b", "b");
Output.WriteLine(_builder.ToSql());
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Select_9()
{
//结果
var result = new Str();
result.AppendLine("Select [s].[IsDeleted],[s].[StringValue] As [a] ");
result.Append("From [Sample3] As [s]");
//执行
_builder = new SqlServerBuilder(new DefaultEntityMatedata());
_builder.Select <Sample3>()
.Select <Sample3>(t => t.StringValue, "a")
.From <Sample3>("s");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestAppendOrderBy_1()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Order By b");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.AppendOrderBy("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_OrIfNotEmpty_2()
{
//结果
var result = new Str();
result.Append("Where ([Email] In (@_p_0,@_p_1) Or [Url]=@_p_2)");
//执行
var list = new List <string> {
"a", "b"
};
_clause.OrIfNotEmpty <Sample>(t => list.Contains(t.Email), t => t.Url == "a");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_34()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Group By [a].[B],[c].[D]");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.GroupBy("a.B,c.[D]");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_30()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where [a].[Email] Is Not Null And [a].[Email]<>''");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.IsNotEmpty <Sample>(t => t.Email);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_27()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where ([a].[Email] Is Null Or [a].[Email]='')");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.IsEmpty("a.Email");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_LeftJoin_1()
{
//结果
var result = new Str();
result.AppendLine("Select [a] ");
result.AppendLine("From [b] ");
result.Append("Left Join [c] As [d]");
//执行
_builder.Select("a")
.From("b")
.LeftJoin("c", "d");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_RemoveSelect_1()
{
//结果
var result = new Str();
result.AppendLine("Select [s].[Description],[s].[DisplayName],[s].[StringValue],[s].[IntValue] ");
result.Append("From [Sample2] As [s]");
//执行
_builder = new SqlServerBuilder(new DefaultEntityMatedata());
_builder.Select <Sample2>()
.RemoveSelect <Sample2>(x => x.Display)
.From <Sample2>("s");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Join_2()
{
//结果
var result = new Str();
result.AppendLine("Select [a] ");
result.AppendLine("From [b] ");
result.Append("Join [d].[Sample] As [c]");
//执行
_builder.Select("a")
.From("b")
.Join <Sample>("c", "d");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_3()
{
//结果
var result = new Str();
result.AppendLine("Select `a3`.`a`,`a1`.`b1`,`a2`.`b2` ");
result.AppendLine("From `b` As `a2` ");
result.Append("Join `t.c` As `a3` On `a2`.`d`=@_p_0");
//执行
_builder.Select("a,a1.b1,a2.b2", "a3")
.From("b", "a2")
.Join("t.c", "a3").On("a2.d", "e");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_On_1()
{
//结果
var result = new Str();
result.AppendLine("Select [a] ");
result.AppendLine("From [b] ");
result.Append("Join [c] As [d] On [b].[Id]<>@_p_0");
//执行
_builder.Select("a")
.From("b")
.Join("c", "d").On("b.Id", "c", Operator.NotEqual);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestIsDeletedFilter_1()
{
//结果
var result = new Str();
result.AppendLine("Select [s].[StringValue] ");
result.AppendLine("From [Sample5] As [s] ");
result.AppendLine("Join [Sample2] As [s2] On [s].[IntValue]=[s2].[IntValue] ");
result.Append("Where [s].[IsDeleted]=@_p_0");
//执行
_builder.Select <Sample5>(t => t.StringValue).From <Sample5>("s").Join <Sample2>("s2").On <Sample5, Sample2>((l, r) => l.IntValue == r.IntValue);
//验证
_output.WriteLine(_builder.ToSql());
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_AppendGroupBy_2()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Group By b");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.AppendGroupBy("c", false)
.AppendGroupBy("b", true);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Between_10()
{
//结果
var result = new Str();
result.Append("Where [a].[B]>=@_p_0 And [a].[B]<@_p_1");
//执行
var min = DateTime.Parse("2000-1-1 10:10:10");
var max = DateTime.Parse("2000-1-2 10:10:10");
_clause.Between("a.B", min, max, false, null);
//验证
Assert.Equal(DateTime.Parse("2000-1-1"), _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(DateTime.Parse("2000-1-3"), _parameterManager.GetParams()["@_p_1"]);
Assert.Equal(result.ToString(), GetSql());
}
public void Test_Between_11()
{
//结果
var result = new Str();
result.Append("Where [a].[B]>@_p_0 And [a].[B]<@_p_1");
//执行
var min = DateTime.Parse("2000-1-1 10:10:10");
var max = DateTime.Parse("2000-1-2 10:10:10");
_clause.Between("a.B", min, max, true, Boundary.Neither);
//验证
Assert.Equal(min, _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(max, _parameterManager.GetParams()["@_p_1"]);
Assert.Equal(result.ToString(), GetSql());
}
public void TestLeftJoin_7()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email],[a].[BoolValue],[b].[Description],[b].[IntValue] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Left Join [Sample2] As [b] On [a].[Email]=[b].[StringValue] And [a].[IntValue]<>[b].[IntValue]");
//执行
_builder.Select <Sample>(t => new object[] { t.Email, t.BoolValue })
.Select <Sample2>(t => new object[] { t.Description, t.IntValue })
.From <Sample>("a")
.LeftJoin <Sample2>("b").On <Sample, Sample2>((l, r) => l.Email == r.StringValue && l.IntValue != r.IntValue);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestLeftJoin_6()
{
//结果
var result = new Str();
result.AppendLine("Select [a] ");
result.AppendLine("From [b] ");
result.Append("Left Join c");
//执行
_builder.Select("a")
.From("b")
.AppendLeftJoin("d", false)
.AppendLeftJoin("c", true);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_36()
{
//结果
var result = new Str();
result.AppendLine("Select * ");
result.AppendLine("From [Sample] As [k] ");
result.Append("Where [k].[Email]=@_p_0 And [k].[StringValue] Like @_p_1");
//执行
_builder.From <Sample>("k").Where <Sample>(t => t.Email == "a" && t.StringValue.Contains("b"));
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(2, _builder.GetParams().Count);
Assert.Equal("a", _builder.GetParams()["@_p_0"]);
Assert.Equal("%b%", _builder.GetParams()["@_p_1"]);
}
public void HandleAmount(Amount amount)
{
int width;
if (!First)
{
Str.AppendLine();
width = LatterWidth;
}
else
{
First = false;
width = FirstWidth;
}
string s = amount.Print(Flags);
Str.Append(UniString.Justify(s, width, Flags.HasFlag(AmountPrintEnum.AMOUNT_PRINT_RIGHT_JUSTIFY), Flags.HasFlag(AmountPrintEnum.AMOUNT_PRINT_COLORIZE) && amount.Sign < 0));
}
public void TestOn_4()
{
//结果
var result = new Str();
result.AppendLine("Select [a],[b] ");
result.AppendLine("From [Sample] As [s] ");
result.Append("Left Join [Sample2] As [s2] On [s].[IntValue]=[s2].[IntValue] And [s].[StringValue]=@_p_0");
//执行
_builder.Select("a,b")
.From <Sample>("s")
.LeftJoin <Sample2>("s2").On <Sample, Sample2>((l, r) => l.IntValue == r.IntValue && l.StringValue == "a");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
_output.WriteLine(_builder.ToSql());
Assert.Equal("a", _builder.GetParams()["@_p_0"]);
}
public void Test_With()
{
//结果
var result = new Str();
result.AppendLine("With Recursive `Test` ");
result.AppendLine("As (Select `a`,`b` ");
result.AppendLine("From `Test2`)");
result.AppendLine("Select `a`,`b` ");
result.Append("From `Test`");
//执行
var builder2 = _builder.New().Select("a,b").From("Test2");
_builder.Select("a,b").From("Test").With("Test", builder2);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_35()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where [a].[B]>=@_p_0 And [a].[B]<=@_p_1");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.Between("a.B", 1, 2);
//验证
Assert.Equal(1, _builder.GetParams()["@_p_0"]);
Assert.Equal(2, _builder.GetParams()["@_p_1"]);
Assert.Equal(result.ToString(), _builder.ToSql());
}
/// <summary>
/// 断言结果
/// </summary>
private void AssertResult(string @class = "", string options = "")
{
@class = @class.IsEmpty() ? "" : " " + @class;
options = options.IsEmpty() ? "" : " " + options;
Str result = new Str();
result.Append("<button type=\"button\" class=\"btn btn-default{0}\" data-toggle=\"button\"{1}>", @class, options);
result.Append("a</button>");
Assert.AreEqual(result.ToString(), _control.ToHtmlString());
Console.WriteLine(result.ToString());
}
