private void LogQuery(SqlResult result)
{
logger.LogDebug(result.Sql);
}
public async Task <T> UniqueResultAsync <T>(IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = Compiler();
return(await connection.QueryFirstOrDefaultAsync <T>(result.Sql, result.NamedBindings, transaction, commandTimeout, commandType));
}
public async Task <IEnumerable <T> > ListAsync <T>(IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = Compiler();
return(await connection.QueryAsync <T>(result.Sql, result.NamedBindings, transaction, commandTimeout, commandType));
}
/// <summary>
/// Create query parameter place-holders for an array.
/// </summary>
/// <param name="ctx"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual string Parameterize <T>(SqlResult ctx, IEnumerable <T> values)
{
return(string.Join(", ", values.Select(x => Parameter(ctx, x))));
}
public async Task <bool> SaveInsertAsync(IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = Compiler();
return(await connection.ExecuteAsync(result.Sql, result.NamedBindings, transaction, commandTimeout, commandType) == 1);
}
protected virtual SqlResult CompileInsertQuery(Query query)
{
var ctx = new SqlResult
{
Query = query
};
if (!ctx.Query.HasComponent("from", EngineCode))
{
throw new InvalidOperationException("No table set to insert");
}
var fromClause = ctx.Query.GetOneComponent <FromClause>("from", EngineCode);
if (fromClause is null)
{
throw new InvalidOperationException("Invalid table expression");
}
var table = Wrap(fromClause.Table);
var inserts = ctx.Query.GetComponents <AbstractInsertClause>("insert", EngineCode);
if (inserts[0] is InsertClause insertClause)
{
var columns = string.Join(", ", WrapArray(insertClause.Columns));
var values = string.Join(", ", Parameterize(ctx, insertClause.Values));
ctx.RawSql = $"INSERT INTO {table} ({columns}) VALUES ({values})";
if (insertClause.ReturnId && !string.IsNullOrEmpty(LastId))
{
ctx.RawSql += ";" + LastId;
}
}
else
{
var clause = inserts[0] as InsertQueryClause;
var columns = "";
if (clause.Columns.Any())
{
columns = $" ({string.Join(", ", WrapArray(clause.Columns))}) ";
}
var subCtx = CompileSelectQuery(clause.Query);
ctx.Bindings.AddRange(subCtx.Bindings);
ctx.RawSql = $"INSERT INTO {table}{columns}{subCtx.RawSql}";
}
if (inserts.Count > 1)
{
foreach (var insert in inserts.GetRange(1, inserts.Count - 1))
{
var clause = insert as InsertClause;
ctx.RawSql += ", (" + string.Join(", ", Parameterize(ctx, clause.Values)) + ")";
}
}
return(ctx);
}
protected SqlResult PrepareResult(SqlResult ctx)
{
ctx.NamedBindings = generateNamedBindings(ctx.Bindings.ToArray());
ctx.Sql = Helper.ReplaceAll(ctx.RawSql, parameterPlaceholder, i => parameterPrefix + i);
return(ctx);
}
private static string ConvertToSqlServer(this SqlResult query)
{
return(query.Sql);
}
private static async Task <CompileInfo> CompileAsync(IDatabase database, string tableName, IRedis redis, Query query)
{
var method = query.Method;
if (method == "update")
{
query.Method = "select";
}
string sql;
Dictionary <string, object> namedBindings;
var compiler = DbUtils.GetCompiler(database.DatabaseType, database.ConnectionString);
var compiled = compiler.Compile(query);
if (method == "update")
{
var bindings = new List <object>();
var setList = new List <string>();
var components = query.GetComponents("update");
components.Add(new BasicCondition
{
Column = nameof(Entity.LastModifiedDate),
Value = DateTime.Now
});
foreach (var clause in components)
{
if (clause is RawCondition raw)
{
var set = compiler.WrapIdentifiers(raw.Expression);
if (setList.Contains(set, StringComparer.OrdinalIgnoreCase))
{
continue;
}
setList.Add(set);
if (raw.Bindings != null)
{
bindings.AddRange(raw.Bindings);
}
}
else if (clause is BasicCondition basic)
{
var set = compiler.Wrap(basic.Column) + " = ?";
if (setList.Contains(set, StringComparer.OrdinalIgnoreCase))
{
continue;
}
setList.Add(set);
bindings.Add(basic.Value);
}
}
bindings.AddRange(compiled.Bindings);
var result = new SqlResult
{
Query = query
};
var where = compiler.CompileWheres(result);
sql = $"UPDATE {tableName} SET { string.Join(", ", setList)} {where}";
//sql = Helper.ExpandParameters(sql, "?", bindings.ToArray());
sql = Helper.ReplaceAll(sql, "?", i => "@p" + i);
namedBindings = Helper.Flatten(bindings).Select((v, i) => new { i, v })
.ToDictionary(x => "@p" + x.i, x => x.v);
}
else
{
sql = compiled.Sql;
namedBindings = compiled.NamedBindings;
}
var compileInfo = new CompileInfo
{
Sql = sql,
NamedBindings = namedBindings
};
var cacheList = query.GetComponents <CachingCondition>("cache");
if (cacheList != null && cacheList.Count > 0)
{
var cacheManager = await CachingUtils.GetCacheManagerAsync(redis);
foreach (var caching in cacheList)
{
if (caching.Action == CachingAction.Remove && caching.CacheKeysToRemove != null)
{
foreach (var cacheKey in caching.CacheKeysToRemove)
{
cacheManager.Remove(cacheKey);
}
}
else if (caching.Action == CachingAction.Get)
{
compileInfo.Caching = caching;
}
}
}
return(compileInfo);
}
private IList<ISqlResult> ExecuteMultiSelect(string sql)
{
AboutToExecuteSql(sql);
IList<ISqlResult> result;
using (var connection = GetConnection())
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
result = new List<ISqlResult>();
while (reader.Read())
{
var sqlRes = new SqlResult();
sqlRes.SetData(reader);
result.Add(sqlRes);
}
}
}
SqlExecuted(sql);
return result;
}
private IList<ISqlResult> ExecuteSingleSelect(string sql)
{
AboutToExecuteSql(sql);
List<ISqlResult> finalResult;
using (var connection = GetConnection())
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
if (!reader.Read()) return new List<ISqlResult>();
var result = new SqlResult();
result.SetData(reader);
finalResult = new List<ISqlResult> { result };
}
}
SqlExecuted(sql);
return finalResult;
}
void Query(int offset)
{
try
{
int serverId = ServerDropDownList.SelectedServerId;
GameServer server = ServerDropDownList.SelectedGameServer;
if (server == null)
{
LabelOpMsg.Text = string.Format(StringDef.MsgCannotBeNone, StringDef.GameServer);
return;
}
if (!server.IsConnected)
{
LabelOpMsg.Text = StringDef.NoConnectionAlert;
return;
}
ArrayList tempParamList = new ArrayList();
ArrayList paramList = new ArrayList();
StringBuilder searchCondition = new StringBuilder();
switch (ListBoxWay.SelectedValue)
{
case "All":
searchCondition.Append(string.Format("WHERE (LogEvent='{0}' OR LogEvent='{1}')", LogEvent.BuyStatistic, LogEvent.NpcDropStatistic));
break;
case "Buy":
searchCondition.Append(string.Format("WHERE LogEvent='{0}'", LogEvent.BuyStatistic));
break;
case "Drop":
searchCondition.Append(string.Format("WHERE LogEvent='{0}'", LogEvent.NpcDropStatistic));
break;
}
string itemId = TextBoxItemId.Text.Trim();
if (itemId == null || itemId.Length == 0)
{
LabelOpMsg.Text = string.Format(StringDef.MsgCannotBeNone, StringDef.ItemID);
return;
}
searchCondition.Append(string.Format(" AND {0}='{1}' ", FS2TableString.LogFieldLogKey2, itemId));
_start = StartDate.SelectedDate;
if (_start == DateTime.MinValue)
{
LabelOpMsg.Text = StringDef.ParameterInputError;
return;
}
_end = EndDate.SelectedDate;
if (_end == DateTime.MinValue)
{
LabelOpMsg.Text = StringDef.ParameterInputError;
return;
}
searchCondition.Append(string.Format(" AND {0}>='{1}' AND {0} <'{2}'", FS2TableString.LogFieldLogTime, _start, _end));
string baseCmdString = string.Format("SELECT LogKey1,LogKey2,LogEvent,SUM(LogKey6) AS SubSum FROM {0} {1} GROUP BY LogKey1",
"{0}", searchCondition.ToString());
string addTableCmdString;
WebUtil.AddTableNameToCmd(CurrentUser.Id, server, baseCmdString, tempParamList, _start, _end, out addTableCmdString, out paramList);
if (addTableCmdString.Length == 0)
{
//
return;
}
int limitCount = _recordPerPage;
string limitStatement = string.Format(" LIMIT {0},{1}", offset, limitCount);
string cmdString = "SELECT LogKey1,LogKey2,LogEvent,SUM(SubSum) AS Total FROM ({0}) AS A GROUP BY LogKey1 ORDER BY Total DESC {1}";
SqlCommand cmd = new SqlCommand(string.Format(cmdString, addTableCmdString, limitStatement), paramList.ToArray());
SqlResult result = WebUtil.QueryGameServerDb(CurrentUser.Id, server, cmd);
if (result != null && result.Success)
{
result.SetFieldType(new SqlDataType[] {
SqlDataType.String,
SqlDataType.String,
SqlDataType.Int32,
SqlDataType.Int32
});
object[] record = null;
ArrayList infos = new ArrayList();
while ((record = result.ReadRecord()) != null)
{
SearchItemInfo info = new SearchItemInfo();
info.sourceId = record[0] as string;
info.id = record[1] as string;
info.way = (int)record[2];
info.count = (int)record[3];
infos.Add(info);
}
uint total = 0;
string baseCmdTotalString = string.Format("SELECT SUM(LogKey6) AS SubSum FROM {0} {1}",
"{0}", searchCondition.ToString());
string addTableCmdTotalString;
WebUtil.AddTableNameToCmd(CurrentUser.Id, server, baseCmdTotalString, tempParamList, _start, _end, out addTableCmdTotalString, out paramList);
if (addTableCmdTotalString.Length != 0)
{
string cmdTotalString = "SELECT SUM(SubSum) FROM ({0}) AS A";
SqlCommand sqlTotal = new SqlCommand(string.Format(cmdTotalString, addTableCmdTotalString), paramList.ToArray());
SqlResult sqlTotalResult = WebUtil.QueryGameServerDb(CurrentUser.Id, server, sqlTotal);
if (sqlTotalResult != null && sqlTotalResult.Success)
{
sqlTotalResult.SetFieldType(new SqlDataType[] {
SqlDataType.UInt32
});
record = sqlTotalResult.ReadRecord();
if (record != null && record[0] != null)
{
total = (uint)record[0];
}
}
}
ViewState[WebConfig.SessionQueryLogOffset] = offset;
ButtonPreviousPage.Enabled = (offset > 0);
ButtonFirstPage.Enabled = (offset > 0);
ButtonNextPage.Enabled = (infos.Count >= limitCount);
if (infos.Count != 0)
{
PanelResult.Visible = true;
CreateSearchResultList((SearchItemInfo[])infos.ToArray(typeof(SearchItemInfo)), total);
LabelResult.Text = string.Format(StringDef.LabelStatisticResult, server.Group.Name,
server.Name, StringDef.Item, StringDef.Drop);
}
else
{
PanelResult.Visible = false;
LabelOpMsg.Text = StringDef.NoMatchingRecord;
}
}
else
{
if (result == null)
{
LabelOpMsg.Text = StringDef.QueryTimeOut;
}
else
{
LabelOpMsg.Text = StringDef.OperationFail;
}
}
}
catch (Exception ex)
{
LabelOpMsg.Text = ex.Message;
PanelResult.Visible = false;
}
}
public virtual async Task <Tuple <int, IEnumerable <Customer> > > GetPagedCustomers(string username, string email, int pageIndex, int pageSize)
{
#region TotalCount
IDbSession session = await Task.Run(() => DbSession);
var totalCountQuery = new Query().FromRaw($"{TableName} WITH (NOLOCK)").WhereFalse("Deleted").AsCount();
if (!string.IsNullOrEmpty(username))
{
totalCountQuery = totalCountQuery.WhereStarts("Username", username);
}
if (!string.IsNullOrEmpty(email))
{
totalCountQuery = totalCountQuery.WhereStarts("Email", email);
}
SqlResult totalCountResult = GetSqlResult(totalCountQuery);
int totalCount = await session.Connection.QueryFirstOrDefaultAsync <int>(totalCountResult.Sql, totalCountResult.NamedBindings);
#endregion
#region Paged Customers
int totalPage = totalCount <= pageSize ? 1 : totalCount > pageSize && totalCount < (pageSize * 2) ? 2 : totalCount / pageSize; // 总页数
int midPage = totalPage / 2 + 1; //中间页数，大于该页数则采用倒排优化
bool isLastPage = pageIndex == totalPage; // 是否最后一页，是最后一页则需要进行取模算出最后一页的记录数（可能小于PageSize）
int descBound = (totalCount - pageIndex * pageSize); // 重新计算limit偏移量
int lastPageSize = 0; // 计算最后一页的记录数
if (isLastPage)
{
lastPageSize = totalCount % pageSize; // 取模得到最后一页的记录数
descBound -= lastPageSize; // 重新计算最后一页的偏移量
}
else
{
descBound -= pageSize; // 正常重新计算除最后一页的偏移量
}
bool useDescOrder = pageIndex <= midPage; // 判断是否采取倒排优化
Query customerQuery = new Query(TableName).Select("Id", "Username", "Email", "Active", "CreationTime").WhereFalse("Deleted");
if (!string.IsNullOrEmpty(username))
{
customerQuery = customerQuery.WhereStarts("Username", username);
}
if (!string.IsNullOrEmpty(email))
{
customerQuery = customerQuery.WhereStarts("Email", email);
}
customerQuery = customerQuery.Limit(isLastPage ? lastPageSize : pageSize).Offset(useDescOrder ? pageIndex * pageSize : descBound);
customerQuery = useDescOrder ? customerQuery.OrderByDesc("Id") : customerQuery.OrderBy("Id");
SqlResult customerResult = GetSqlResult(customerQuery);
try
{
var customers = await session.Connection.QueryAsync <Customer>(customerResult.Sql, customerResult.NamedBindings);
return(new Tuple <int, IEnumerable <Customer> >(totalCount, customers));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
session.Dispose();
}
#endregion
}
void Query()
{
try
{
GameServer server = ServerDropDownList.SelectedGameServer;
if (server == null)
{
LabelOpMsg.Text = string.Format(StringDef.MsgCannotBeNone, StringDef.GameServer);
return;
}
if (!server.IsConnected)
{
LabelOpMsg.Text = StringDef.NoConnectionAlert;
return;
}
ArrayList paramList = new ArrayList();
StringBuilder searchCondition = new StringBuilder();
string RoleSexQueryText = "SELECT RoleType, RoleSex,TongGUID,SkillSeries FROM rolesfirst {0}";
int startLevel = int.Parse(TextBoxStartLevel.Text);
if (startLevel > 1)
{
searchCondition.Append(string.Format(" AND {0}>={1}", FS2TableString.RolesfirstFieldRoleLevel, startLevel));
}
int endLevel = int.Parse(TextBoxEndLevel.Text);
if (endLevel < FS2GameDataManager.MaxLevel)
{
searchCondition.Append(string.Format(" AND {0}<={1}", FS2TableString.RolesfirstFieldRoleLevel, endLevel));
}
DateTime activeDate = DateCalendar.SelectedDate;
if (DropDownListActivePlayer.SelectedIndex != 0 && activeDate == DateTime.MinValue)
{
LabelOpMsg.Text = string.Format(StringDef.MsgInputParamNotCorrect, StringDef.Date);
return;
}
switch (DropDownListActivePlayer.SelectedIndex)
{
case 1:
//活跃玩家
searchCondition.AppendFormat(" AND {0}>='{1}' ", FS2TableString.RolesfirstFieldLastPlayingDate, activeDate.AddDays(-WebConfig.ActivePlayerDaySpan));
break;
case 2:
//不活跃玩家
searchCondition.AppendFormat(" AND {0}<'{1}' ", FS2TableString.RolesfirstFieldLastPlayingDate, activeDate.AddDays(-WebConfig.ActivePlayerDaySpan));
break;
}
if (searchCondition.Length > 0)
{
searchCondition.Remove(0, 4);
searchCondition.Insert(0, " WHERE");
}
SqlCommand cmd = new SqlCommand(string.Format(RoleSexQueryText, searchCondition.Length == 0 ? string.Empty : searchCondition.ToString()), string.Empty);
SqlResult result = WebUtil.QueryGameServerDb(CurrentUser.Id, server, cmd);
if (result != null && result.Success)
{
result.SetFieldType(
new SqlDataType[] {
SqlDataType.SByte,
SqlDataType.SByte,
SqlDataType.String,
SqlDataType.Int32
});
object[] record = null;
RoleSexTotalInfo totalInfo = new RoleSexTotalInfo();
ArrayList infos = new ArrayList();
while ((record = result.ReadRecord()) != null)
{
//RoleType, RoleSex,TongGUID,SkillSeries
FS2RoleDataInfo info = new FS2RoleDataInfo();
info.SkillSeries = (int)record[3];
switch ((SByte)record[0])
{
case (SByte)0:
if (info.SkillSeries == 0)
{
info.RoleType = FS2RoleType.Xuanfeng;
}
else if (info.SkillSeries == 1)
{
info.RoleType = FS2RoleType.Xingtian;
}
else
{
info.RoleType = FS2RoleType.Jiashi;
}
break;
case (SByte)1:
if (info.SkillSeries == 0)
{
info.RoleType = FS2RoleType.Zhenren;
}
else if (info.SkillSeries == 1)
{
info.RoleType = FS2RoleType.Tianshi;
}
else
{
info.RoleType = FS2RoleType.Daoshi;
}
break;
case (SByte)2:
if (info.SkillSeries == 0)
{
info.RoleType = FS2RoleType.Shoushi;
}
else if (info.SkillSeries == 1)
{
info.RoleType = FS2RoleType.Yishi;
}
else
{
info.RoleType = FS2RoleType.Yiren;
}
break;
}
info.RoleSex = (FS2RoleSex)((SByte)record[1]);
info.TongName = record[2] as string;
switch (DropDownListSocialRelation.SelectedIndex)
{
case 0:
infos.Add(info);
break;
case 1:
if (info.TongName != null && info.TongName != string.Empty)
{
infos.Add(info);
}
break;
case 2:
if (info.TongName == null || info.TongName == string.Empty)
{
infos.Add(info);
}
break;
//case 3:
// if (IsInZhuhou(info)) infos.Add(info);
// break;
//case 4:
// if (IsInNation(info)) infos.Add(info);
// break;
}
}
if (infos.Count != 0)
{
PanelResult.Visible = true;
_statInfo = (FS2RoleDataInfo[])infos.ToArray(typeof(FS2RoleDataInfo));
CreateSearchRoleSexList((FS2RoleDataInfo[])infos.ToArray(typeof(FS2RoleDataInfo)));
LabelResult.Text = string.Format(StringDef.LabelStatisticResult, server.Group.Name, server.Name,
string.Format(StringDef.LabelRoleLevel, startLevel, endLevel),
StringDef.ProfessionalSex);
}
else
{
PanelResult.Visible = false;
LabelOpMsg.Text = StringDef.NoMatchingRecord;
}
}
}
catch (Exception ex)
{
LabelOpMsg.Text = ex.Message;
PanelResult.Visible = false;
}
}
protected string CompileTwoColumnsCondition(SqlResult ctx, TwoColumnsCondition clause)
{
var op = clause.IsNot ? "NOT " : "";
return($"{op}{Wrap(clause.First)} {clause.Operator} {Wrap(clause.Second)}");
}
protected virtual string CompileRawCondition(SqlResult ctx, RawCondition x)
{
ctx.Bindings.AddRange(x.Bindings);
return(WrapIdentifiers(x.Expression));
}
public SqlResult Select(string sql, params object[] args)
{
if (connection == null)
return null;
lock (mysqlLock)
{
try
{
using (var sqlCommand = new MySqlCommand(sql, connection))
{
var mParams = new List<MySqlParameter>(args.Length);
foreach (var a in args)
mParams.Add(new MySqlParameter("", a));
sqlCommand.Parameters.AddRange(mParams.ToArray());
using (var sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default))
{
using (var retData = new SqlResult())
{
retData.Load(sqlData);
retData.Count = retData.Rows.Count;
return retData;
}
}
}
}
catch (MySqlException exception)
{
LogManager.Write("Database Manager", "An exception occured while selecting data from the database!");
LogManager.Write("Database Manager", "Exception: {0}", exception.Message);
}
}
return null;
}
protected virtual SqlResult OnBeforeCompile(SqlResult ctx)
{
return(ctx);
}
protected virtual SqlResult OnBeforeSelect(SqlResult ctx)
{
return(ctx);
}
public static SqlResult CompileWithLastId(this MySqlCompiler compiler, Query query)
{
SqlResult result = compiler.Compile(query);
return(new SqlResult(result.Sql + ";SELECT LAST_INSERT_ID();", result.RawBindings));
}
public virtual string Parameter <T>(SqlResult ctx, T value)
{
ctx.Bindings.Add(value);
return("?");
}
public async Task <T> SaveInsertForMysqlAsync <T>(IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = ((MySqlCompiler)compiler).CompileWithLastId(this);
return(await connection.QueryFirstOrDefaultAsync <T>(result.Sql, result.Bindings, transaction, commandTimeout, commandType));
}
protected override string CompileBasicDateCondition(SqlResult context, BasicDateCondition condition)
{
string column = wrapper.Wrap(condition.Column);
string value = Parameter(context, condition.Value);
string sql = "";
string valueFormat = "";
bool isDateTime = (condition.Value is DateTime dt);
switch (condition.Part)
{
case "date": // assume YY-MM-DD format
if (isDateTime)
{
valueFormat = $"{value}";
}
else
{
valueFormat = $"TO_DATE({value}, 'YY-MM-DD')";
}
sql = $"TO_CHAR({column}, 'YY-MM-DD') {condition.Operator} TO_CHAR({valueFormat}, 'YY-MM-DD')";
break;
case "time":
if (isDateTime)
{
valueFormat = $"{value}";
}
else
{
// assume HH:MM format
if (condition.Value.ToString().Split(':').Count() == 2)
{
valueFormat = $"TO_DATE({value}, 'HH24:MI')";
}
else // assume HH:MM:SS format
{
valueFormat = $"TO_DATE({value}, 'HH24:MI:SS')";
}
}
sql = $"TO_CHAR({column}, 'HH24:MI:SS') {condition.Operator} TO_CHAR({valueFormat}, 'HH24:MI:SS')";
break;
case "year":
case "month":
case "day":
case "hour":
case "minute":
case "second":
sql = $"EXTRACT({condition.Part.ToUpperInvariant()} FROM {column}) {condition.Operator} {value}";
break;
default:
sql = $"{column} {condition.Operator} {value}";
break;
}
if (condition.IsNot)
{
return($"NOT ({sql})");
}
return(sql);
}
public async Task <T> SaveInsertForPostGresAsync <T>(string primaryKeyName = "id", IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = ((PostgresCompiler)compiler).CompileWithLastId(this, primaryKeyName);
return(await connection.QueryFirstOrDefaultAsync <T>(result.Sql, result.Bindings, transaction, commandTimeout, commandType));
}
public T FindOne <T>(IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = Compiler();
return(connection.QueryFirstOrDefault <T>(result.Sql, result.NamedBindings, transaction, commandTimeout, commandType));
}
public async Task <T> SaveInsertForSqlServerAsync <T>(string primaryKeyName = "id", IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = Result <T>((SqlServerCompiler)compiler);
return(await connection.QueryFirstOrDefaultAsync <T>(result.Sql, result.Bindings, transaction, commandTimeout, commandType));
}
public IEnumerable <T> List <T>(IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = Compiler();
return(connection.Query <T>(result.Sql, result.NamedBindings, transaction, buffered, commandTimeout, commandType));
}
private void QueryAndDisplay(bool firstTimeQuery)
{
string roleGUID = roleGUIDTextBox.Text;
GameServer gameCenter = AdminServer.TheInstance.GameServerManager.GetGameServer(int.Parse(serverGroupDropDownList.SelectedValue));
ArrayList paramList = new ArrayList();
string queryString1 = " select logtime as time, '升级' as event, CONCAT('升到<', CAST(field2 as char(8)),'>级') as remark"
+ " from log_3_1"
+ " where field1='?'";
string queryString2 = " select logtime as time, '加入门派' as event, CONCAT('加入<', (select field2 from faction where log_3_2.field3=faction.field1 and faction.field3=0), '>，当时为<', CAST(field2 as char(8)), '>级') as remark"
+ " from log_3_2"
+ " where field1='?'";
string queryString3 = " select logtime as time, '出师' as event, CONCAT('出师自<', (select field2 from faction where log_3_3.field3=faction.field1 and faction.field3=0), '>，路线<', (select field2 from faction where log_3_3.field3=faction.field1 and log_3_3.field4=faction.field3), '>，当时为<', CAST(field2 as char(8)), '>级') as remark"
+ " from log_3_3"
+ " where field1='?'";
string queryString4 = " select logtime as time, '加入家族' as event, CONCAT('加入<', (select field2 from kin where log_3_4.field3=kin.field1), '>，当时为<', CAST(field2 as char(8)), '>级') as remark"
+ " from log_3_4"
+ " where field1='?'";
string queryString5 = " select logtime as time, '退出家族' as event, CONCAT('退出<', (select field2 from kin where log_3_5.field3=kin.field1), '>，当时为<', CAST(field2 as char(8)), '>级') as remark"
+ " from log_3_5"
+ " where field1='?'";
string queryString6 = " select logtime as time, '完成任务' as event, CONCAT('主任务:', CAST(field4 as char), ' 子任务:', CAST(field5 as char)) as remark"
+ " from log_3_6"
+ " where field1='?'";
string queryString7 = " select logtime as time,"
+ " (case field2"
+ " when 0 then '下线'"
+ " when 1 then '登录'"
+ " when 2 then '开始离线托管'"
+ " when 3 then '离线托管超时'"
+ " when 4 then '离线托管踢号'"
+ " else '未知事件'"
+ " end) as event,"
+ " field3 as remark"
+ " from log_3_7"
+ " where field1='?'";
//+ " union"
//+ " select logtime as time, '选择路线' as event, CONCAT('选择路线<', (select field2 from faction where log_3_8.field3=faction.field1 and log_3_8.field4=faction.field3), '>，当时为<', CAST(field2 as char(8)), '>级') as remark"
//+ " from log_3_8"
//+ " where field1='?'"
//+ " and logtime between '?' and '?'"
ArrayList queryStringsArrayList = new ArrayList();
//根据eventTypeCheckBoxList的勾选情况来确定哪几个queryString被加入到最终的SQL语句中
if (eventTypeCheckBoxList.SelectedIndex == -1)
{
LabelSuccess.Visible = false;
LabelOpMsg.Text = "未选择事件类型 - 至少要选中一种事件";
LabelOpMsg.Visible = true;
resultDataGrid.Visible = false;
resultPagingInfoPanel.Visible = false;
return;
}
if (eventTypeCheckBoxList.Items.FindByValue("log_3_1").Selected == true)
{
queryStringsArrayList.Add(queryString1);
}
if (eventTypeCheckBoxList.Items.FindByValue("log_3_2,log_3_3,log_3_8").Selected == true)
{
queryStringsArrayList.Add(queryString2);
queryStringsArrayList.Add(queryString3);
}
if (eventTypeCheckBoxList.Items.FindByValue("log_3_4,log_3_5").Selected == true)
{
queryStringsArrayList.Add(queryString4);
queryStringsArrayList.Add(queryString5);
}
if (eventTypeCheckBoxList.Items.FindByValue("log_3_6").Selected == true)
{
queryStringsArrayList.Add(queryString6);
}
if (eventTypeCheckBoxList.Items.FindByValue("log_3_7").Selected == true)
{
queryStringsArrayList.Add(queryString7);
}
//若两个时间的TextBox不是都为空
//注意，TextBox为空时，SelectedDate属性的值为DateTime.MinValue
if (!((RecentLoginTime_start.SelectedDate == DateTime.MinValue) && (RecentLoginTime_end.SelectedDate == DateTime.MinValue)))
{
DateTime time1;
DateTime time2;
if (RecentLoginTime_start.SelectedDate == DateTime.MinValue)
{
time1 = DateTime.Now;
}
else
{
time1 = RecentLoginTime_start.SelectedDate;
}
if (RecentLoginTime_end.SelectedDate == DateTime.MinValue)
{
time2 = DateTime.Now;
}
else
{
time2 = RecentLoginTime_end.SelectedDate;
}
for (int i = 0; i < queryStringsArrayList.Count; i++)
{
queryStringsArrayList[i] += " and logtime between '?' and '?'";
paramList.Add(roleGUID);
if (time1 <= time2)
{
paramList.Add(time1.ToString("yyyy-MM-dd HH:mm:ss"));
paramList.Add(time2.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
paramList.Add(time2.ToString("yyyy-MM-dd HH:mm:ss"));
paramList.Add(time1.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
}
else
{
for (int i = 0; i < queryStringsArrayList.Count; i++)
{
paramList.Add(roleGUID);
}
}
string queryString = String.Join(" union all ", (string[])queryStringsArrayList.ToArray(typeof(String)));
queryString += " order by Time DESC ";
queryString = queryString.Insert(7, " SQL_CALC_FOUND_ROWS ");
int currentPage = (int)ViewState["currentPage"];
int resultsPerPage = (int)ViewState["resultsPerPage"];
int offset = resultsPerPage * currentPage;
queryString += " limit " + offset.ToString() + "," + resultsPerPage.ToString();
currentPage++;
ViewState["currentPage"] = currentPage;
SqlCommand sqlcmd = new SqlCommand(queryString, paramList.ToArray());
SqlResult queryResult = WebUtil.QueryGameServerDb(CurrentUser.Id, gameCenter, sqlcmd);
if (queryResult != null && queryResult.Success)
{
object[] record = null;
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("time", typeof(DateTime)));
dt.Columns.Add(new DataColumn("event", typeof(String)));
dt.Columns.Add(new DataColumn("remark", typeof(String)));
DataRow dr;
queryResult.SetFieldType
(
new SqlDataType[]
{
SqlDataType.DateTime,
SqlDataType.String,
SqlDataType.String,
}
);
while ((record = queryResult.ReadRecord()) != null)
{
dr = dt.NewRow();
dr[0] = (DateTime)record[0];
dr[1] = (String)record[1];
dr[2] = (String)record[2];
dt.Rows.Add(dr);
}
DataView dv = new DataView(dt);
if (dt.Rows.Count == 0)
{
LabelSuccess.Visible = false;
LabelOpMsg.Text = "没有满足条件的记录";
LabelOpMsg.Visible = true;
resultDataGrid.Visible = false;
resultPagingInfoPanel.Visible = false;
return;
}
resultDataGrid.DataSource = dv;
resultDataGrid.DataBind();
resultDataGrid.Visible = true;
LabelOpMsg.Visible = false;
LabelSuccess.Text = "查询成功完成";
LabelSuccess.Visible = true;
//只有在首次查询时，也就是点击查询按钮时才去查结果的总数，otherwise，总数已经在ViewState["totalResults"]中了
if (firstTimeQuery == true)
{
SqlCommand sqlcmdRowCount = new SqlCommand("select found_rows()", paramList.ToArray());
SqlResult queryResultRowCount = WebUtil.QueryGameServerDb(CurrentUser.Id, gameCenter, sqlcmdRowCount);
queryResultRowCount.SetFieldType(new SqlDataType[] { SqlDataType.String });
object[] rowCountResult = queryResultRowCount.ReadRecord();
ViewState["totalResults"] = rowCountResult[0];
}
int rowCount = int.Parse((string)ViewState["totalResults"]);
resultInfoLabel.Text = "共有" + rowCount.ToString() + "条记录，每页" + resultsPerPage.ToString() + "条记录，当前页数：" + currentPage.ToString();
//当前页数大于1时才允许点击“第一页”按钮和“上一页”按钮
if (currentPage >= 2)
{
ButtonFirstPage.Enabled = true;
ButtonPreviousPage.Enabled = true;
}
else
{
ButtonFirstPage.Enabled = false;
ButtonPreviousPage.Enabled = false;
}
//当下一页还能有记录的时候才允许点击“下一页”按钮
if ((resultsPerPage * currentPage) < rowCount)
{
ButtonNextPage.Enabled = true;
}
else
{
ButtonNextPage.Enabled = false;
}
resultPagingInfoPanel.Visible = true;
}
else
{
if (queryResult == null)
{
LabelSuccess.Visible = false;
LabelOpMsg.Text = StringDef.NoConnectionAlert;
LabelOpMsg.Visible = true;
resultDataGrid.Visible = false;
resultPagingInfoPanel.Visible = false;
}
else
{
LabelSuccess.Visible = false;
LabelOpMsg.Text = StringDef.OperationFail;
LabelOpMsg.Visible = true;
resultDataGrid.Visible = false;
resultPagingInfoPanel.Visible = false;
}
}
}
public bool SaveUpdate(IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
SqlResult result = Compiler();
return(connection.Execute(result.Sql, result.NamedBindings, transaction, commandTimeout, commandType) == 1);
}
/// <summary>
/// 根据表达式生成where的sql
/// </summary>
/// <param name="expression">表达式</param>
/// <param name="paramLength">多一个参数，该数字+1,这样可以保证sql参数名的唯一性</param>
/// <param name="preParam">参数名前缀</param>
/// <returns></returns>
public static SqlResult ExpressionToWhereSql(this System.Linq.Expressions.Expression expression, ref int paramLength, string preParam = "@")
{
SqlResult sqlResult = new SqlResult();
if (expression is BinaryExpression binaryExpression)
{
string symbol = string.Empty;
if (expression.NodeType == ExpressionType.AndAlso)
{
symbol = "AND";
}
else if (expression.NodeType == ExpressionType.OrElse)
{
symbol = "OR";
}
else if (expression.NodeType == ExpressionType.Equal)
{
symbol = "=";
}
else if (expression.NodeType == ExpressionType.NotEqual)
{
symbol = "!=";
}
else if (expression.NodeType == ExpressionType.Add)
{
symbol = "+";
}
else if (expression.NodeType == ExpressionType.Subtract)
{
symbol = "-";
}
else if (expression.NodeType == ExpressionType.Multiply)
{
symbol = "*";
}
else if (expression.NodeType == ExpressionType.Divide)
{
symbol = "/";
}
else if (expression.NodeType == ExpressionType.Modulo)
{
symbol = "%";
}
else if (expression.NodeType == ExpressionType.And)
{
symbol = "&";
}
else if (expression.NodeType == ExpressionType.Or)
{
symbol = "|";
}
else if (expression.NodeType == ExpressionType.ExclusiveOr)
{
symbol = "^";
}
else if (expression.NodeType == ExpressionType.LeftShift)
{
symbol = "<<";
}
else if (expression.NodeType == ExpressionType.RightShift)
{
symbol = ">>";
}
else if (expression.NodeType == ExpressionType.LessThan)
{
symbol = "<";
}
else if (expression.NodeType == ExpressionType.GreaterThan)
{
symbol = ">";
}
else if (expression.NodeType == ExpressionType.LessThanOrEqual)
{
symbol = "<=";
}
else if (expression.NodeType == ExpressionType.GreaterThanOrEqual)
{
symbol = ">=";
}
if (symbol != string.Empty)
{
SqlResult leftSqlResult = ExpressionToWhereSql(binaryExpression.Left, ref paramLength, preParam);
SqlResult rightSqlResult = ExpressionToWhereSql(binaryExpression.Right, ref paramLength, preParam);
if (leftSqlResult.SqlString.ToString() == "null" && rightSqlResult.SqlString.ToString() != "null")
{
symbol = "IS";
sqlResult.SqlString.Append($"({rightSqlResult.SqlString} {symbol} {leftSqlResult.SqlString})");
foreach (var param in rightSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (leftSqlResult.SqlString.ToString() != "null" && rightSqlResult.SqlString.ToString() == "null")
{
symbol = "IS";
sqlResult.SqlString.Append($"({leftSqlResult.SqlString} {symbol} {rightSqlResult.SqlString})");
foreach (var param in leftSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (leftSqlResult.SqlString.ToString() != "null" && rightSqlResult.SqlString.ToString() != "null")
{
sqlResult.SqlString.Append($"({leftSqlResult.SqlString} {symbol} {rightSqlResult.SqlString})");
foreach (var param in leftSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
foreach (var param in rightSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else
{
sqlResult.SqlString.Append($"(1 = 1)");
}
}
}
else if (expression is UnaryExpression unaryExpression)
{
if (unaryExpression.NodeType == ExpressionType.Convert)
{
SqlResult convertResult = ExpressionToWhereSql(unaryExpression.Operand, ref paramLength, preParam);
sqlResult.SqlString.Append(convertResult.SqlString.ToString());
foreach (var param in convertResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
}
else if (expression is ConstantExpression constantExpression)
{
object value = constantExpression.Value;
string paramName = $"{preParam}mtconst{++paramLength}";
sqlResult.SqlString.Append($"{(preParam.StartsWith("@") ? "" : "@")}{paramName}");
sqlResult.Params.TryAdd(paramName, value);
}
else if (expression is MemberExpression memberExpression)
{
if (memberExpression.Expression.NodeType == ExpressionType.Parameter)
{
sqlResult.SqlString.Append($"`{memberExpression.Member.Name}`");
}
else
{
var obj = Expression.Lambda(memberExpression).Compile().DynamicInvoke();
if (obj == null)
{
sqlResult.SqlString.Append("null");
}
else
{
string paramName = $"{preParam}mtconst{++paramLength}";
sqlResult.SqlString.Append($"{(preParam.StartsWith("@") ? "" : "@")}{paramName}");
sqlResult.Params.TryAdd(paramName, obj);
}
}
}
else if (expression is MethodCallExpression methodCallExpression)
{
Type stringType = typeof(String);
List <string> argStrings = new List <string>();
foreach (var arg in methodCallExpression.Arguments)
{
SqlResult argResult = ExpressionToWhereSql(arg, ref paramLength, preParam);
foreach (var param in argResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
argStrings.Add(argResult.SqlString.ToString());
}
if (methodCallExpression.Method.ReflectedType == stringType)
{
if (methodCallExpression.Method.Name == nameof(String.Concat))
{
sqlResult.SqlString.Append($"CONCAT({string.Join(",", argStrings)})");
}
else if (methodCallExpression.Method.Name == nameof(String.Contains))
{
SqlResult objResult = ExpressionToWhereSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"INSTR({objResult.SqlString},{argStrings.FirstOrDefault()})");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (methodCallExpression.Method.Name == nameof(String.Equals))
{
SqlResult objResult = ExpressionToWhereSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"({objResult.SqlString}={argStrings.FirstOrDefault()})");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (methodCallExpression.Method.Name == nameof(String.StartsWith))
{
SqlResult objResult = ExpressionToWhereSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"{objResult.SqlString} like {argStrings.FirstOrDefault()}%");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (methodCallExpression.Method.Name == nameof(String.EndsWith))
{
SqlResult objResult = ExpressionToWhereSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"{objResult.SqlString} like %{argStrings.FirstOrDefault()}");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (methodCallExpression.Method.Name == nameof(String.IsNullOrEmpty))
{
sqlResult.SqlString.Append($"({argStrings.FirstOrDefault()} is null or {argStrings.FirstOrDefault()} = '')");
}
else if (methodCallExpression.Method.Name == nameof(String.IsNullOrWhiteSpace))
{
sqlResult.SqlString.Append($"({argStrings.FirstOrDefault()} is null or trim({argStrings.FirstOrDefault()})='' )");
}
else if (methodCallExpression.Method.Name == nameof(String.Trim))
{
SqlResult objResult = ExpressionToWhereSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"trim({objResult.SqlString})");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (methodCallExpression.Method.Name == nameof(String.TrimStart))
{
SqlResult objResult = ExpressionToWhereSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"ltrim({objResult.SqlString})");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (methodCallExpression.Method.Name == nameof(String.TrimEnd))
{
SqlResult objResult = ExpressionToWhereSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"rtrim({objResult.SqlString})");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
}
}
return(sqlResult);
}
public virtual async Task <Tuple <int, IEnumerable <Customer> > > GetPagedCustomers(string username, string email, int pageIndex, int pageSize)
{
#region TotalCount
IDbSession session = await Task.Run(() => DbSession);
var totalCountQuery = new Query().FromRaw($"{TableName} WITH (NOLOCK)").WhereFalse("Deleted").AsCount();
if (!string.IsNullOrEmpty(username))
{
totalCountQuery = totalCountQuery.WhereStarts("Username", username);
}
if (!string.IsNullOrEmpty(email))
{
totalCountQuery = totalCountQuery.WhereStarts("Email", email);
}
SqlResult totalCountResult = GetSqlResult(totalCountQuery);
int totalCount = await session.Connection.QueryFirstOrDefaultAsync <int>(totalCountResult.Sql, totalCountResult.NamedBindings);
#endregion
#region Paged Customers
int totalPage = totalCount <= pageSize ? 1 : totalCount > pageSize && totalCount < (pageSize * 2) ? 2 : totalCount / pageSize; // total pages
int midPage = totalPage / 2 + 1; //The number of middle pages, if more than this number of pages, inverted optimization is used
bool isLastPage = pageIndex == totalPage; // Whether the last page is the last page, it needs to be modulo to calculate the number of records on the last page（May be less than pageSize）
int descBound = (totalCount - pageIndex * pageSize); // Recalculate the limit offset
int lastPageSize = 0; // Count the number of records on the last page
if (isLastPage)
{
lastPageSize = totalCount % pageSize; // Take the modulo to get the number of records on the last page
descBound -= lastPageSize; // Recalculate the offset of the last page
}
else
{
descBound -= pageSize; // Normally recalculate the offset except the last page
}
bool useDescOrder = pageIndex <= midPage; // Determine whether to adopt inverted optimization
Query customerQuery = new Query(TableName).Select("Id", "Username", "Email", "Active", "CreationTime").WhereFalse("Deleted");
if (!string.IsNullOrEmpty(username))
{
customerQuery = customerQuery.WhereStarts("Username", username);
}
if (!string.IsNullOrEmpty(email))
{
customerQuery = customerQuery.WhereStarts("Email", email);
}
customerQuery = customerQuery.Limit(isLastPage ? lastPageSize : pageSize).Offset(useDescOrder ? pageIndex * pageSize : descBound);
customerQuery = useDescOrder ? customerQuery.OrderByDesc("Id") : customerQuery.OrderBy("Id");
SqlResult customerResult = GetSqlResult(customerQuery);
try
{
var customers = await session.Connection.QueryAsync <Customer>(customerResult.Sql, customerResult.NamedBindings);
return(new Tuple <int, IEnumerable <Customer> >(totalCount, customers));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
session.Dispose();
}
#endregion
}
/// <summary>
/// 根据表达式生成Select的sql
/// </summary>
/// <param name="expression">表达式</param>
/// <param name="paramLength">多一个参数，该数字+1,这样可以保证sql参数名的唯一性</param>
/// <param name="preParam">参数名前缀</param>
/// <returns></returns>
public static SqlResult ExpressionToSelectSql(this System.Linq.Expressions.Expression expression, ref int paramLength, string preParam = "@")
{
SqlResult sqlResult = new SqlResult();
if (expression is ParameterExpression parameterExpression)
{
sqlResult.SqlString.Append(string.Join(",", parameterExpression.Type.GetProperties().Select(x => x.Name)));
}
else if (expression is ConstantExpression constantExpression)
{
if (constantExpression.Value == null)
{
sqlResult.SqlString.Append("null");
}
else
{
string paramName = $"{preParam}mtconst{++paramLength}";
sqlResult.SqlString.Append($"{(preParam.StartsWith("@") ? "" : "@")}{paramName}");
sqlResult.Params.TryAdd(paramName, constantExpression.Value);
}
}
else if (expression is MemberExpression memberExpression)
{
if (memberExpression.Expression.NodeType == ExpressionType.Parameter)
{
sqlResult.SqlString.Append($"`{memberExpression.Member.Name}`");
}
else
{
var obj = Expression.Lambda(memberExpression).Compile().DynamicInvoke();
if (obj == null)
{
sqlResult.SqlString.Append("null");
}
else
{
string paramName = $"{preParam}mtconst{++paramLength}";
sqlResult.SqlString.Append($"{(preParam.StartsWith("@") ? "" : "@")}{paramName}");
sqlResult.Params.TryAdd(paramName, obj);
}
}
}
else if (expression is BinaryExpression binaryExpression)
{
string symbol = string.Empty;
if (expression.NodeType == ExpressionType.AndAlso)
{
symbol = "AND";
}
else if (expression.NodeType == ExpressionType.OrElse)
{
symbol = "OR";
}
else if (expression.NodeType == ExpressionType.Equal)
{
symbol = "=";
}
else if (expression.NodeType == ExpressionType.NotEqual)
{
symbol = "!=";
}
else if (expression.NodeType == ExpressionType.Add)
{
symbol = "+";
}
else if (expression.NodeType == ExpressionType.Subtract)
{
symbol = "-";
}
else if (expression.NodeType == ExpressionType.Multiply)
{
symbol = "*";
}
else if (expression.NodeType == ExpressionType.Divide)
{
symbol = "/";
}
else if (expression.NodeType == ExpressionType.Modulo)
{
symbol = "%";
}
else if (expression.NodeType == ExpressionType.And)
{
symbol = "&";
}
else if (expression.NodeType == ExpressionType.Or)
{
symbol = "|";
}
else if (expression.NodeType == ExpressionType.ExclusiveOr)
{
symbol = "^";
}
else if (expression.NodeType == ExpressionType.LeftShift)
{
symbol = "<<";
}
else if (expression.NodeType == ExpressionType.RightShift)
{
symbol = ">>";
}
else if (expression.NodeType == ExpressionType.LessThan)
{
symbol = "<";
}
else if (expression.NodeType == ExpressionType.GreaterThan)
{
symbol = ">";
}
else if (expression.NodeType == ExpressionType.LessThanOrEqual)
{
symbol = "<=";
}
else if (expression.NodeType == ExpressionType.GreaterThanOrEqual)
{
symbol = ">=";
}
if (symbol != string.Empty)
{
SqlResult leftSqlResult = ExpressionToSelectSql(binaryExpression.Left, ref paramLength, preParam);
SqlResult rightSqlResult = ExpressionToSelectSql(binaryExpression.Right, ref paramLength, preParam);
if (leftSqlResult.SqlString.ToString() == "null" && rightSqlResult.SqlString.ToString() != "null")
{
symbol = "IS";
sqlResult.SqlString.Append($"({rightSqlResult.SqlString} {symbol} {leftSqlResult.SqlString})");
foreach (var param in rightSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (leftSqlResult.SqlString.ToString() != "null" && rightSqlResult.SqlString.ToString() == "null")
{
symbol = "IS";
sqlResult.SqlString.Append($"({leftSqlResult.SqlString} {symbol} {rightSqlResult.SqlString})");
foreach (var param in leftSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (leftSqlResult.SqlString.ToString() != "null" && rightSqlResult.SqlString.ToString() != "null")
{
sqlResult.SqlString.Append($"({leftSqlResult.SqlString} {symbol} {rightSqlResult.SqlString})");
foreach (var param in leftSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
foreach (var param in rightSqlResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else
{
sqlResult.SqlString.Append($"(1 = 1)");
}
}
}
else if (expression is UnaryExpression unaryExpression)
{
if (unaryExpression.NodeType == ExpressionType.Convert)
{
SqlResult convertResult = ExpressionToSelectSql(unaryExpression.Operand, ref paramLength, preParam);
sqlResult.SqlString.Append(convertResult.SqlString.ToString());
foreach (var param in convertResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
}
else if (expression is NewArrayExpression newArrayExpression)
{
foreach (Expression itemExp in newArrayExpression.Expressions)
{
SqlResult itemExpResult = ExpressionToSelectSql(itemExp, ref paramLength, preParam);
sqlResult.SqlString.Append(itemExpResult.SqlString.ToString());
foreach (var item in itemExpResult.Params)
{
sqlResult.Params.TryAdd(item.Key, item.Value);
}
}
}
else if (expression is MethodCallExpression methodCallExpression)
{
Type stringType = typeof(String);
List <string> argStrings = new List <string>();
foreach (var arg in methodCallExpression.Arguments)
{
SqlResult argResult = ExpressionToSelectSql(arg, ref paramLength, preParam);
foreach (var param in argResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
argStrings.Add(argResult.SqlString.ToString());
}
if (methodCallExpression.Method.ReflectedType == stringType)
{
if (methodCallExpression.Method.Name == nameof(String.IndexOf))
{
SqlResult objResult = ExpressionToSelectSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append($"INSTR({objResult.SqlString},{argStrings.FirstOrDefault()})");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
else if (methodCallExpression.Method.Name == nameof(String.Concat))
{
if (methodCallExpression.Arguments.Count == 1)
{
Expression firstArgExp = methodCallExpression.Arguments[0];
if (methodCallExpression.Arguments[0] is NewArrayExpression newArrayExpression2)
{
if (newArrayExpression2.Expressions.Count == 1)
{
firstArgExp = newArrayExpression2.Expressions[0];
if (firstArgExp is MethodCallExpression methodCallExpression1 &&
methodCallExpression1.Method.Name == nameof(string.Join) &&
methodCallExpression1.Method.ReflectedType == stringType)
{
if (methodCallExpression1.Arguments[1] is NewArrayExpression newArrayExpression1 &&
((newArrayExpression1.Expressions[0] is MemberExpression memberExpression1 &&
memberExpression1.Expression.NodeType == ExpressionType.Parameter) || (newArrayExpression1.Expressions[0].NodeType == ExpressionType.Call)))
{
SqlResult separatorResult = ExpressionToSelectSql(methodCallExpression1.Arguments[0], ref paramLength, preParam);
SqlResult objResult = ExpressionToSelectSql(methodCallExpression1.Arguments[1], ref paramLength, preParam);
sqlResult.SqlString.Append($"group_concat({objResult.SqlString} separator {separatorResult.SqlString})");
foreach (var param in separatorResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
}
else
{
if (firstArgExp is MemberExpression memberExpression1 &&
memberExpression1.Expression.NodeType == ExpressionType.Parameter)
{
SqlResult objResult = ExpressionToSelectSql(firstArgExp, ref paramLength, preParam);
sqlResult.SqlString.Append($"group_concat({objResult.SqlString} separator ',')");
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
}
}
else
{
List <string> concatItems = new List <string>();
foreach (var itemExpression in newArrayExpression2.Expressions)
{
SqlResult itemExpressionResult = ExpressionToSelectSql(itemExpression, ref paramLength, preParam);
foreach (var param in itemExpressionResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
concatItems.Add(itemExpressionResult.SqlString.ToString());
}
sqlResult.SqlString.Append($"CONCAT({string.Join(",", concatItems)})");
}
}
}
else
{
sqlResult.SqlString.Append($"CONCAT({string.Join(",", argStrings)})");
}
}
}
else if (methodCallExpression.Method.ReflectedType == typeof(Enumerable))
{
if (methodCallExpression.Method.Name == nameof(Enumerable.Distinct))
{
SqlResult objResult = ExpressionToSelectSql(methodCallExpression.Arguments[0], ref paramLength, preParam);
if (objResult.SqlString.ToString().Contains("group_concat(", StringComparison.OrdinalIgnoreCase))
{
sqlResult.SqlString.Append(objResult.SqlString.Replace("group_concat(", "group_concat(distinct "));
}
else
{
sqlResult.SqlString.Append($"distinct({objResult.SqlString})");
}
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
}
else if (methodCallExpression.Method.ReflectedType == typeof(object))
{
if (methodCallExpression.Method.Name == nameof(object.ToString))
{
SqlResult objResult = ExpressionToSelectSql(methodCallExpression.Object, ref paramLength, preParam);
sqlResult.SqlString.Append(objResult.SqlString.ToString());
foreach (var param in objResult.Params)
{
sqlResult.Params.TryAdd(param.Key, param.Value);
}
}
}
}
protected virtual string CompileNullCondition(SqlResult ctx, NullCondition item)
{
var op = item.IsNot ? "IS NOT NULL" : "IS NULL";
return(Wrap(item.Column) + " " + op);
}
void UpdateQueryResultCache(uint sessionId, byte[] data, bool done, bool success)
{
lock (_queryResultCache.SyncRoot)
{
SqlResult result = null;
if (_queryResultCache.Contains(sessionId))
{
result = _queryResultCache[sessionId] as SqlResult;
}
else
{
result = new SqlResult(sessionId);
_queryResultCache.Add(sessionId, result);
}
if (result != null)
{
result.AppendData(data);
if (done)
{
result.Done(success);
}
}
}
}
