public void Test_Select_7()
{
//结果
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");
//执行
var builder2 = _builder.New().Count().From("Test2").Where("Name", "a");
_builder.Select("*").AppendSelect("(").Select(builder2, "").AppendSelect(") As 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_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_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_Select_6()
{
//结果
var result = new Str();
result.AppendLine("Select a ");
result.Append("From [c]");
//执行
_builder.AppendSelect("a")
.From("c");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Distinct()
{
//结果
var result = new Str();
result.AppendLine("Select Distinct [a] ");
result.Append("From [b]");
//执行
_builder.Distinct().Select("a")
.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_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_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_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_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_Count_2()
{
//结果
var result = new Str();
result.AppendLine("Select Count([DoubleValue]) As [a] ");
result.Append("From [b]");
//执行
_builder.Count <Sample>(t => t.DoubleValue, "a")
.From("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_33()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where [a].[Email] In (@_p_0,@_p_1)");
//执行
var list = new List <string> {
"a", "b"
};
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.In <Sample>(t => t.Email, list);
//验证
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 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 TestFrom_6()
{
//结果
var result = new Str();
result.AppendLine("Select [c] ");
result.Append("From b");
//执行
_builder.Select("c")
.AppendFrom("a", false)
.AppendFrom("b", true);
//验证
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 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 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_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_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_On_4()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.AppendLine("On [a].[id]=@_p_0 And [c].[Aid]=@_p_1 ");
result.Append("Join [n] ");
result.Append("On [t].[id]=@_p_2 And [t].[Aid]=@_p_3");
//操作
_clause.Join("t");
_clause.On("a.id", "b");
_clause.On("c.Aid", "d");
_clause.Join("n");
_clause.On("t.id", "e");
_clause.On("t.Aid", "f");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_On_4()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.AppendLine("On [a].[id]=[b].[id] And [c].[Aid]=[d].[Bid] ");
result.Append("Join [n] ");
result.Append("On [t].[id]=[n].[id] And [t].[Aid]=[n].[Bid]");
//操作
_clause.Join("t");
_clause.On("a.id", "b.id");
_clause.On("c.Aid", "d.Bid");
_clause.Join("n");
_clause.On("t.id", "n.id");
_clause.On("t.Aid", "n.Bid");
//验证
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_55()
{
//结果
var result = new Str();
result.AppendLine("Select Count(*) ");
result.AppendLine("From (");
result.AppendLine("Select [Age] ");
result.AppendLine("From [Test] ");
result.AppendLine("Where [Age]=@_p_0 ");
result.AppendLine("Group By [Age]");
result.Append(") As t");
//执行
_builder.From("Test").Where("Age", 1).GroupBy("Age");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestWith_2()
{
//结果
var result = new Str();
result.AppendLine("With [Test] ");
result.AppendLine("As (Select [a],[b] ");
result.AppendLine("From [Test2]),");
result.AppendLine("[Test3] ");
result.AppendLine("As (Select [a],[b] ");
result.AppendLine("From [Test3])");
result.AppendLine("Select [a],[b] ");
result.Append("From [Test]");
//执行
var builder2 = _builder.New().Select("a,b").From("Test2");
var builder3 = _builder.New().Select("a,b").From("Test3");
_builder.Select("a,b").From("Test").With("Test", builder2).With("Test3", builder3);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
/// <summary>
/// 异常处理
/// </summary>
/// <param name="context">异常上下文</param>
public override void OnException(ExceptionContext context)
{
if (context == null)
{
return;
}
if (context.Exception is OperationCanceledException)
{
context.ExceptionHandled = true;
context.Result = new ObjectResult(new Result <object>(StateCode.Fail, R.CanceledMessage))
{
StatusCode = (int)HttpStatusCode.BadRequest
};
}
else if (context.Exception is UnauthorizedAccessException)
{
context.Result = new ObjectResult(new Result <object>(StateCode.Fail, context.Exception.Message))
{
StatusCode = (int)HttpStatusCode.Unauthorized
};
}
else if (context.Exception is ValidationException)
{
var ex = context.Exception as ValidationException;
var message = ex.Failures.Select(c => c.Value.Join("，")).Join("");
var localizer = context.HttpContext.RequestServices.GetRequiredService <IStringLocalizer <SubCode> >();
var local = localizer[SubCode.Fail.GetName()];
context.Result = new ObjectResult(new Result <object>()
{
code = 0,
subCode = local.Value,
message = message,
data = null,
elapsedTime = -1,
operationTime = DateTime.Now
})
{
StatusCode = (int)HttpStatusCode.OK
};
}
else
{
var logger = context.HttpContext.RequestServices.GetService <ILogger <ApiErrorAttribute> >();
if (logger.IsEnabled(LogLevel.Error))
{
var routes = context.GetRouteValues().Select(c => $"{c.Key}={c.Value}").Join("，");
var str = new Str();
str.AppendLine("WebApi全局异常");
str.AppendLine($"路由信息：{routes}");
str.AppendLine($"IP：{context.HttpContext.Connection.RemoteIpAddress}");
str.AppendLine($"请求方法：{context.HttpContext.Request.Method}");
str.AppendLine($"请求地址：{context.HttpContext.Request.Scheme}://{context.HttpContext.Request.Host}{context.HttpContext.Request.Path}{context.HttpContext.Request.QueryString}");
logger.LogError(context.Exception.FormatMessage(str.ToString()));
}
context.Result = new ObjectResult(new Result <object>(StateCode.Fail, context.Exception.Message))
{
StatusCode = (int)HttpStatusCode.InternalServerError
};
base.OnException(context);
}
}
