public void Test_Select_5()
{
//结果
var result = new Str();
result.Append("Select *,");
result.AppendLine("(Select Count(*) ");
result.AppendLine("From [Test2] ");
result.AppendLine("Where [Name]=@_p_0) As [TestCount] ");
result.AppendLine("From [Test] ");
result.Append("Where [Age]=@_p_1");
//执行
_builder.Select("*").Select(builder =>
{
builder.Count().From("Test2").Where("Name", "a");
}, "TestCount")
.From("Test").Where("Age", 1);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(2, _builder.GetParams().Count);
Assert.Equal("a", _builder.GetParams()["@_p_0"]);
Assert.Equal(1, _builder.GetParams()["@_p_1"]);
}
public void Test_1()
{
//结果
var result = new Str();
result.AppendLine("Select * ");
result.AppendLine("From \"Test\" ");
result.Append("Where \"A\"=1 And \"B\"=2 And \"C\"=false And \"D\"=true And \"E\"=5 And \"F\"=6 And ");
result.Append("\"G\"=7 And \"H\"=8 And \"I\"=9 And \"J\"=10 And \"K\"=11 And \"L\"=12");
//执行
_builder.Select("*")
.From("Test")
.Where("A", 1)
.Where("B", 2)
.Where("C", false)
.Where("D", true)
.Where("E", 5)
.Where("F", 6)
.Where("G", 7)
.Where("H", 8)
.Where("I", 9)
.Where("J", 10)
.Where("K", 11)
.Where("L", 12);
//验证
Assert.Equal(result.ToString(), _builder.ToDebugSql());
}
public void Test_On_5()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.Append("On [a].[id]<@_p_0");
//操作
_clause.Join("t");
_clause.On("a.id", "b", Operator.Less);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_Where_17()
{
//结果
var result = new Str();
result.Append("Where ([Email]=@_p_0 And [StringValue] Like @_p_1 Or [IntValue]=@_p_2) ");
result.Append("And ([Email]=@_p_3 Or [IntValue]=@_p_4)");
//执行
_clause.Where <Sample>(t => t.Email == "a" && t.StringValue.Contains("b") || t.IntValue == 1);
_clause.Where <Sample>(t => t.Email == "c" || t.IntValue == 2);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_On_3()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.Append("On [a].[id]=@_p_0 And [c].[Aid]=@_p_1");
//操作
_clause.Join("t");
_clause.On("a.id", "b");
_clause.On("c.Aid", "d");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_AppendOn_1()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.Append("On a.id=b.id");
//操作
_clause.Join("t");
_clause.AppendOn("a.id=b.id");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_Union_1()
{
//结果
var result = new Str();
result.AppendLine("(Select [a],[b] ");
result.AppendLine("From [Test] ");
result.AppendLine("Where [c]=@_p_1 ");
result.AppendLine(") ");
result.AppendLine("Union ");
result.AppendLine("(Select [a],[b] ");
result.AppendLine("From [Test2] ");
result.AppendLine("Where [c]=@_p_0 ");
result.Append(")");
//执行
var builder2 = _builder.New().Select("a,b").From("Test2").Where("c", 1);
_builder.Select("a,b").From("Test").Where("c", 2).Union(builder2);
Output.WriteLine(builder2.ToSql());
Output.WriteLine(result.ToString());
Output.WriteLine(_builder.ToSql());
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(1, _builder.GetParams()["@_p_0"]);
Assert.Equal(2, _builder.GetParams()["@_p_1"]);
}
public void Test_On_15()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [a].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t2].[IntValue]>[t].[ShortValue]");
//操作
_clause.Join <Sample>("t");
_clause.On("a.id", "b.id");
_clause.Join <Sample2>("t2");
_clause.On <Sample, Sample2>((l, r) => r.IntValue > l.ShortValue);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_On_7()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [a].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t].[BoolValue]<[t2].[IntValue]");
//操作
_clause.Join <Sample>("t");
_clause.On("a.id", "b");
_clause.Join <Sample2>("t2");
_clause.On <Sample, Sample2>(t => t.BoolValue, t => t.IntValue, Operator.Less);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_On_13()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [a].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t].[ShortValue]>[t2].[IntValue] And [t].[IntValue]=@_p_1");
//操作
_clause.Join <Sample>("t");
_clause.On("a.id", "b");
_clause.Join <Sample2>("t2");
_clause.On <Sample, Sample2>((l, r) => l.ShortValue > r.IntValue && l.IntValue == 1);
//验证
Assert.Equal(result.ToString(), GetSql());
Assert.Equal("b", _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(1, _parameterManager.GetParams()["@_p_1"]);
}
public void Test_OrIfNotEmpty_4()
{
//结果
var result = new Str();
result.Append("Where (([Email]=@_p_0 Or ");
result.Append("[Email] In (@_p_1,@_p_2)) Or [Url]=@_p_3) ");
result.Append("And [Url]=@_p_4");
//执行
var list = new List <string> {
"a", "b"
};
_clause.Where <Sample>(t => t.Email == "b");
_clause.OrIfNotEmpty <Sample>(t => list.Contains(t.Email), t => t.Url == "a");
_clause.Where <Sample>(t => t.Url == "c");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_AppendOn_2()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.AppendLine("On [a].[id]=@_p_0 And a.id=b.id ");
result.Append("Join [v] ");
result.Append("On [v].[id]=@_p_1 And v.id=b.id");
//操作
_clause.Join("t");
_clause.On("a.id", "b");
_clause.AppendOn("a.id=b.id");
_clause.Join("v");
_clause.On("v.id", "c");
_clause.AppendOn("v.id=b.id");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_From_4()
{
//结果
var result = new Str();
result.AppendLine("Select * ");
result.Append("From ");
result.AppendLine("(Select Count(*) ");
result.AppendLine("From [Test2] ");
result.AppendLine("Where [Name]=@_p_0) As [test] ");
result.Append("Where [Age]=@_p_1");
//执行
_builder.From(builder => builder.Count().From("Test2").Where("Name", "a"), "test").Where("Age", 1);
Output.WriteLine(_builder.ToSql());
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(2, _builder.GetParams().Count);
Assert.Equal("a", _builder.GetParams()["@_p_0"]);
Assert.Equal(1, _builder.GetParams()["@_p_1"]);
}
public void Test_2()
{
//结果
var result = new Str();
result.AppendLine("Select `c` ");
result.Append("From `a.b`");
//执行
_builder.Select("c").From("a.b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Between_5()
{
//结果
var result = new Str();
result.Append("Where [a].[B]>=@_p_0");
//执行
_clause.Between("a.B", 1, null, Boundary.Both);
//验证
Assert.Equal(1, _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(result.ToString(), GetSql());
}
public void Test_On_12()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [t].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t2].[id]=@_p_1 ");
result.Append("And ([t].[ShortValue]>[t2].[IntValue] Or [t].[DisplayValue]=[t2].[StringValue]) ");
result.Append("And a.Id=b.Id");
//操作
_clause.Join <Sample>("t");
_clause.On("t.id", "b");
_clause.Join <Sample2>("t2");
_clause.On("t2.id", "b");
_clause.On <Sample, Sample2>((l, r) => l.ShortValue > r.IntValue || l.DisplayValue == r.StringValue);
_clause.AppendOn("a.Id=b.Id");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_Between_3()
{
//结果
var result = new Str();
result.Append("Where [a].[B]>@_p_0 And [a].[B]<@_p_1");
//执行
_clause.Between("a.B", 2, 1, Boundary.Neither);
//验证
Assert.Equal(1, _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(2, _parameterManager.GetParams()["@_p_1"]);
Assert.Equal(result.ToString(), GetSql());
}
public void Test_NotIn_3()
{
//结果
var result = new Str();
result.Append("Where [user].[Email] Not In (@_p_0,@_p_1)");
//执行
var list = new[] { "a", "b" };
_clause.NotIn("user.Email", list);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_In_4()
{
//结果
var result = new Str();
result.Append("Where [user].[Email] In (@_p_0,@_p_1,@_p_2)");
//执行
var list = new object[] { SampleEnum.One, SampleEnum.Two, SampleEnum.Three };
_clause.In("user.Email", list);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_Select_3()
{
//结果
var result = new Str();
result.AppendLine("Select [Email] As [e] ");
result.Append("From [c]");
//执行
_builder.Select <Sample>(t => t.Email, "e")
.From("c");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Sum_1()
{
//结果
var result = new Str();
result.AppendLine("Select Sum([a]) As [b] ");
result.Append("From [c]");
//执行
_builder.Sum("a", "b")
.From("c");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Join_8()
{
//结果
var result = new Str();
result.AppendLine("Join [a] ");
result.Append("Join [b]");
//操作
_clause.Join("a");
_clause.Join("b");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_From_2()
{
//结果
var result = new Str();
result.AppendLine("Select [c] ");
result.Append("From [b].[Sample] As [a]");
//执行
_builder.Select("c")
.From <Sample>("a", "b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Count_1()
{
//结果
var result = new Str();
result.AppendLine("Select Count(*) ");
result.Append("From [b]");
//执行
_builder.Count()
.From("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Select_2()
{
//结果
var result = new Str();
result.AppendLine("Select [Email],[IntValue] ");
result.Append("From [c]");
//执行
_builder.Select <Sample>(t => new object[] { t.Email, t.IntValue })
.From("c");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Select_8()
{
//结果
var result = new Str();
result.AppendLine("Select [s].[StringValue],[s].[IsDeleted] ");
result.Append("From [Sample3] As [s]");
//执行
_builder = new SqlServerBuilder(new DefaultEntityMatedata());
_builder.Select <Sample3>().From <Sample3>("s");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_From_5()
{
//结果
var result = new Str();
result.AppendLine("Select [c] ");
result.Append("From a");
//执行
_builder.Select("c")
.AppendFrom("a");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_RightJoin_4()
{
//结果
var result = new Str();
result.AppendLine("Join a ");
result.Append("Right Join b");
//操作
_clause.AppendJoin("a");
_clause.AppendRightJoin("b");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_Between_12()
{
//结果
var result = new Str();
result.Append("Where [IntValue]>=@_p_0 And [IntValue]<=@_p_1");
//执行
_clause.Between <Sample>(t => t.IntValue, 1, 2, Boundary.Both);
//验证
Assert.Equal(1, _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(2, _parameterManager.GetParams()["@_p_1"]);
Assert.Equal(result.ToString(), GetSql());
}
public void Test_Sum_2()
{
//结果
var result = new Str();
result.AppendLine("Select Sum([DoubleValue]) As [a] ");
result.Append("From [b]");
//执行
_builder.Sum <Sample>(t => t.DoubleValue, "a")
.From("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}