public void ConcatTest()
{
var query = Db.T($"SELECT * FROM Users;");
Assert.AreEqual((query + query).TextTemplate, "SELECT * FROM Users; SELECT * FROM Users;", "两个纯文本模板连接测试失败");
Assert.AreEqual(query.Concat(query).TextTemplate, "SELECT * FROM Users; SELECT * FROM Users;", "两个纯文本模板连接测试失败");
Assert.AreEqual((query + query + query).TextTemplate, "SELECT * FROM Users; SELECT * FROM Users; SELECT * FROM Users;", "多个纯文本模板连接测试失败");
Assert.AreEqual(query.ConcatQueries(query, query).TextTemplate, "SELECT * FROM Users; SELECT * FROM Users; SELECT * FROM Users;", "多个纯文本模板连接测试失败");
query = Db.T($"SELECT * FROM Users WHERE UserID = {1};");
Assert.AreEqual((query + query + query).TextTemplate, "SELECT * FROM Users WHERE UserID = �#; SELECT * FROM Users WHERE UserID = #; SELECT * FROM Users WHERE UserID = #;", "多个带参数模板连接测试失败");
Assert.AreEqual(query.ConcatQueries(query, query).TextTemplate, "SELECT * FROM Users WHERE UserID = �#; SELECT * FROM Users WHERE UserID = #; SELECT * FROM Users WHERE UserID = #;", "多个带参数模板连接测试失败");
ParameterizedQuery query1 = null;
query += query1;
Assert.AreEqual(query.TextTemplate, "SELECT * FROM Users WHERE UserID = �#;", "参数化查询对象连接一个 null 值失败");
query += $"";
Assert.AreEqual(query.TextTemplate, "SELECT * FROM Users WHERE UserID = �#;", "参数化查询对象连接一个空字符串失败");
query += "DELETE Users;".AsTextQuery();
Assert.AreEqual(query.TextTemplate, "SELECT * FROM Users WHERE UserID = �#; DELETE Users;", "连接纯文本查询失败");
query += " DELETE Users;".AsTextQuery();
Assert.AreEqual(query.TextTemplate, "SELECT * FROM Users WHERE UserID = �#; DELETE Users; DELETE Users;", "连接空白字符开头查询失败");
}
/// <summary>
/// 创建查询命令
/// </summary>
/// <param name="query">参数化查询</param>
/// <returns>查询命令</returns>
public TCommand Parse(ParameterizedQuery query)
{
var regex = ParameterizedQuery.DbPartialPlaceholdRegex;
var text = regex.Replace(query.TextTemplate, (match) =>
{
if (match.Groups["index"].Success)
{
var index = int.Parse(match.Groups["index"].Value);
return(GetLiteralValue(DbValueConverter.ConvertTo(query.ParameterValues[index], null)));
}
else if (match.Groups["name"].Success)
{
var name = match.Groups["name"].Value.Replace("##", "#");
return(GetDbName(name));
}
else
{
throw new InvalidOperationException();
}
});
return(CreateCommand(text.Replace("##", "#")));
}
/// <summary>
/// 创建查询命令
/// </summary>
/// <param name="query">参数化查询</param>
/// <returns>查询命令</returns>
public TCommand Parse(ParameterizedQuery query)
{
var length = query.ParameterValues.Length;
TParameter[] parameters = new TParameter[length];
string[] parameterPlaceholders = new string[length];
lock ( SyncRoot )
{
var regex = ParameterizedQuery.ParameterPlaceholdRegex;
var text = regex.Replace(query.TextTemplate, (match) =>
{
var index = int.Parse(match.Groups["index"].Value);
if (index >= length)
{
throw new IndexOutOfRangeException("分析参数化查询时遇到错误，参数索引超出边界");
}
var placeholder = parameterPlaceholders[index];
if (placeholder == null)
{
placeholder = parameterPlaceholders[index] = GetParameterPlaceholder(DbValueConverter.ConvertTo(query.ParameterValues[index], null), index, out parameters[index]);
}
return(placeholder);
});
return(CreateCommand(text.Replace("##", "#"), parameters.ToArray()));
}
}
private void PutCachedQuery(ParameterizedQuery parameterizedQuery)
{
var cache = domain.QueryCache;
lock (cache) {
if (!cache.TryGetItem(queryKey, false, out _))
{
cache.Add(new Pair <object, TranslatedQuery>(queryKey, parameterizedQuery));
}
}
}
private ParameterContext CreateParameterContext(ParameterizedQuery query)
{
var parameterContext = new ParameterContext(outerContext);
if (query.QueryParameter != null)
{
parameterContext.SetValue(query.QueryParameter, queryTarget);
}
return(parameterContext);
}
private ParameterContext CreateParameterContext <TResult>(ParameterizedQuery <TResult> query)
{
var parameterContext = new ParameterContext();
if (query.QueryParameter != null)
{
using (parameterContext.Activate())
query.QueryParameter.Value = queryTarget;
}
return(parameterContext);
}
private MySqlCommand CreateCommand(ParameterizedQuery query)
{
var parser = Database.ServiceProvider.GetService <IParameterizedQueryParser <MySqlCommand> >();
if (parser == null)
{
throw new InvalidOperationException("service of type \"IParameterizedQueryParser<MySqlCommand>\" is not registered.");
}
return(parser.Parse(query));
}
public void TestDateSingleParamEquals()
{
var searchParams = new List <SearchParameter>
{
new SearchParameter
{
FieldName = "DateOfBirth",
SearchType = ComparisonType.Equals,
SearchValue = DateTime.Parse("1/5/1970")
}
};
var pq = new ParameterizedQuery <Person>(context.People, searchParams);
Assert.IsTrue(pq.Results().Count() == 1);
}
public void TestStringSingleParamEquals()
{
var searchParams = new List <SearchParameter>
{
new SearchParameter
{
FieldName = "FirstName",
SearchType = ComparisonType.Equals,
SearchValue = "John"
}
};
var pq = new ParameterizedQuery <MockPerson>(people.AsQueryable(), searchParams);
Assert.IsTrue(pq.Results().Count() == 1);
}
public void TestStringSingleParamEqualsWithNoMatch()
{
var searchParams = new List <SearchParameter>
{
new SearchParameter
{
FieldName = "FirstName",
SearchType = ComparisonType.Equals,
SearchValue = "Johnzzz"
}
};
var pq = new ParameterizedQuery <Person>(context.People, searchParams);
Assert.IsTrue(pq.Results().Count() == 0);
}
public void TestDateSingleParamEqualsWithNoMatch()
{
var searchParams = new List <SearchParameter>
{
new SearchParameter
{
FieldName = "DateOfBirth",
SearchType = ComparisonType.Equals,
SearchValue = DateTime.Parse("1/5/1971")
}
};
var pq = new ParameterizedQuery <MockPerson>(people.AsQueryable(), searchParams);
Assert.IsTrue(pq.Results().Count() == 0);
}
/// <summary>
/// 创建查询命令
/// </summary>
/// <param name="query">参数化查询</param>
/// <returns>查询命令</returns>
public TCommand Parse(ParameterizedQuery query)
{
var regex = ParameterizedQuery.ParameterPlaceholdRegex;
var text = regex.Replace(query.TextTemplate, (match) =>
{
var index = int.Parse(match.Groups["index"].Value);
return(GetLiteralValue(DbValueConverter.ConvertTo(query.ParameterValues[index], null)));
});
return(CreateCommand(text.Replace("##", "#")));
}
/// <summary>
/// 串联多个参数化查询对象
/// </summary>
/// <param name="firstQuery">第一个参数化查询对象</param>
/// <param name="otherQueries">要串联的其他参数化查询对象</param>
/// <returns>串联后的参数化查询对象</returns>
public static ParameterizedQuery Concat(this ParameterizedQuery firstQuery, params ParameterizedQuery[] otherQueries)
{
var builder = new ParameterizedQueryBuilder();
firstQuery.AppendTo(builder);
foreach (var query in otherQueries)
{
if (!builder.IsEndWithWhiteSpace() && !query.IsStartWithWhiteSpace() && Db.AddWhiteSpaceOnConcat)
{
builder.Append(' ');
}
query.AppendTo(builder);
}
return(builder.CreateQuery());
}
public void TestDateSingleParamBetween()
{
var searchParams = new List <SearchParameter>
{
new SearchParameter
{
FieldName = "DateOfBirth",
SearchType = ComparisonType.Between,
SearchValue = DateTime.Parse("1/1/1970"),
SearchValue2 = DateTime.Parse("1/30/1970")
}
};
var pq = new ParameterizedQuery <MockPerson>(people.AsQueryable(), searchParams);
Assert.IsTrue(pq.Results().Count() == 1);
}
private void Upsert(object target, UpsertQuery insertQuery)
{
insertQuery.Target = target;
var dataContext = insertQuery.DataContext;
using (var dbCommand = insertQuery.GetCommand())
{
// log first command
dataContext.WriteLog(dbCommand.Command);
// we may have two commands
int rowsCount = dbCommand.Command.ExecuteNonQuery();
// the second reads output parameters
if (!string.IsNullOrEmpty(insertQuery.IdQuerySql.ToString()))
{
var outputCommandTransaction = new ParameterizedQuery(dataContext, insertQuery.IdQuerySql, insertQuery.PrimaryKeyParameters);
outputCommandTransaction.Target = target;
var outputCommand = outputCommandTransaction.GetCommandTransactional(false);
// then run commands
outputCommand.Command.Transaction = dbCommand.Command.Transaction;
// log second command
dataContext.WriteLog(outputCommand.Command);
using (var dataReader = outputCommand.Command.ExecuteReader())
{
if (!dataReader.Read())
{
throw new InvalidOperationException("Could not retrieve data for inserted row on " + target.GetType());
}
int outputParameterIndex = 0;
for (IEnumerator <ObjectOutputParameterExpression> output = insertQuery.OutputParameters.GetEnumerator(); output.MoveNext(); ++outputParameterIndex)
{
var outputDbParameter = dataReader.GetValue(outputParameterIndex);
SetOutputParameterValue(target, output.Current, outputDbParameter);
}
}
}
dbCommand.Commit();
}
}
/// <summary>
/// 串联多个参数化查询对象
/// </summary>
/// <param name="firstQuery">第一个参数化查询对象</param>
/// <param name="otherQueries">要串联的其他参数化查询对象</param>
/// <returns>串联后的参数化查询对象</returns>
public static ParameterizedQuery ConcatQueries(this ParameterizedQuery firstQuery, params ParameterizedQuery[] otherQueries)
{
var builder = Db.ParameterizedQueryService.CreateQueryBuild();
var configures = firstQuery.Configures;
builder.AppendValue(firstQuery);
foreach (var query in otherQueries)
{
if (query == null || string.IsNullOrEmpty(query.TextTemplate))
{
continue;
}
configures = DbQueryConfigures.Merge(configures, query.Configures);
builder.AppendValue(query);
}
return(builder.BuildQuery(configures));
}
/// <summary>
/// 串联多个参数化查询对象
/// </summary>
/// <param name="firstQuery">第一个参数化查询对象</param>
/// <param name="otherQueries">要串联的其他参数化查询对象</param>
/// <returns>串联后的参数化查询对象</returns>
public static ParameterizedQuery Concat(this ParameterizedQuery firstQuery, params ParameterizedQuery[] otherQueries)
{
var builder = new ParameterizedQueryBuilder();
firstQuery.AppendTo(builder);
foreach (var query in otherQueries)
{
if (query == null || string.IsNullOrEmpty(query.TextTemplate))
{
continue;
}
if (!builder.IsEndWithWhiteSpace() && !query.IsStartWithWhiteSpace() && Db.AddWhiteSpaceOnConcat)
{
builder.Append(' ');
}
query.AppendTo(builder);
}
return(builder.CreateQuery());
}
/// <summary>
/// 串联两个参数化查询对象
/// </summary>
/// <param name="firstQuery">第一个参数化查询对象</param>
/// <param name="secondQuery">第二个参数化查询对象</param>
/// <returns>串联后的参数化查询对象</returns>
public static ParameterizedQuery Concat(this ParameterizedQuery firstQuery, FormattableString secondQuery)
{
return(ConcatQueries(firstQuery, Db.Template(secondQuery)));
}
/// <summary>
/// 串联两个参数化查询对象
/// </summary>
/// <param name="firstQuery">第一个参数化查询对象</param>
/// <param name="secondQuery">第二个参数化查询对象</param>
/// <returns>串联后的参数化查询对象</returns>
public static ParameterizedQuery Concat(this ParameterizedQuery firstQuery, ParameterizedQuery secondQuery)
{
return(ConcatQueries(firstQuery, secondQuery));
}
private MySqlCommand CreateCommand(ParameterizedQuery query)
{
return(new MySqlParameterizedQueryParser().Parse(query));
}
/// <summary>
/// 解析模板表达式，创建参数化查询对象
/// </summary>
/// <param name="query">参数化模板</param>
/// <returns>参数化查询</returns>
public static ParameterizedQuery T(ParameterizedQuery query)
{
return(query);
}
/// <summary>
/// 从参数化查询创建查询命令对象
/// </summary>
/// <param name="query">参数化查询对象</param>
/// <returns>SQL 查询命令对象</returns>
protected SqlCommand CreateCommand(ParameterizedQuery query)
{
return(new SqlParameterizedQueryParser().Parse(query));
}
private MySqlCommand CreateCommand(ParameterizedQuery query)
{
return(DbProvider.ServiceProvider.GetService <IParameterizedQueryParser <MySqlCommand> >().Parse(query));
}
private void PutCachedQuery(ParameterizedQuery parameterizedQuery) => domain.QueryCache.Add(new Pair <object, ParameterizedQuery>(queryKey, parameterizedQuery));
