/// <summary>
/// Like
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql打包对象</param>
private static void Like(MethodCallExpression expression, SqlWrapper sqlWrapper)
{
if (expression.Object != null)
{
SqlExpressionProvider.Where(expression.Object, sqlWrapper);
}
SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper);
switch (sqlWrapper.DatabaseType)
{
case DatabaseType.SqlServer:
sqlWrapper += " LIKE '%' + ";
break;
case DatabaseType.MySql:
case DatabaseType.PostgreSql:
sqlWrapper += " LIKE CONCAT('%',";
break;
case DatabaseType.Oracle:
case DatabaseType.Sqlite:
sqlWrapper += " LIKE '%' || ";
break;
default:
break;
}
SqlExpressionProvider.Where(expression.Arguments[1], sqlWrapper);
switch (sqlWrapper.DatabaseType)
{
case DatabaseType.SqlServer:
sqlWrapper += " + '%'";
break;
case DatabaseType.MySql:
case DatabaseType.PostgreSql:
sqlWrapper += ",'%')";
break;
case DatabaseType.Oracle:
case DatabaseType.Sqlite:
sqlWrapper += " || '%'";
break;
default:
break;
}
}
/// <summary>
/// Trim
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql打包对象</param>
private static void Trim(MethodCallExpression expression, SqlWrapper sqlWrapper)
{
if (expression.Object != null)
{
if (expression.Arguments?.Count > 0)
{
var trimString = expression.Object.ToObject()?.ToString();
if (!trimString.IsNullOrEmpty())
{
string constant;
var argument = expression.Arguments[0].ToObject();
if (argument is char @char)
{
constant = trimString.Trim(@char);
}
else
{
constant = trimString.Trim((char[])argument);
}
SqlExpressionProvider.Where(Expression.Constant(constant), sqlWrapper);
}
}
else
{
if (sqlWrapper.DatabaseType == DatabaseType.SqlServer)
{
sqlWrapper += "LTRIM(RTRIM(";
}
else
{
sqlWrapper += "TRIM(";
}
SqlExpressionProvider.Where(expression.Object, sqlWrapper);
if (sqlWrapper.DatabaseType == DatabaseType.SqlServer)
{
sqlWrapper += "))";
}
else
{
sqlWrapper += ")";
}
}
}
}
/// <summary>
/// Insert
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql打包对象</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Insert(MemberInitExpression expression, SqlWrapper sqlWrapper)
{
if (sqlWrapper.DatabaseType != DatabaseType.Oracle)
{
sqlWrapper.Append("(");
}
var fields = new List <string>();
foreach (MemberAssignment m in expression.Bindings)
{
var type = m.Member.DeclaringType.IsAnonymousType() ?
sqlWrapper.DefaultType :
m.Member.DeclaringType;
var(columnName, isInsert, isUpdate) = sqlWrapper.GetColumnInfo(type, m.Member);
if (isInsert)
{
var value = m.Expression.ToObject();
if (value != null || (sqlWrapper.IsEnableNullValue && value == null))
{
sqlWrapper.AddDbParameter(value);
if (!fields.Contains(columnName))
{
fields.Add(columnName);
}
sqlWrapper += ",";
}
}
}
if (sqlWrapper[sqlWrapper.Length - 1] == ',')
{
sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
if (sqlWrapper.DatabaseType != DatabaseType.Oracle)
{
sqlWrapper.Append(")");
}
else
{
sqlWrapper.Append(" FROM DUAL");
}
}
sqlWrapper.Reset(string.Format(sqlWrapper.ToString(), string.Join(",", fields).TrimEnd(',')));
return(sqlWrapper);
}
// LoadRecentMsgsList unused?
/// <summary>
/// The load recent msgs list.
/// </summary>
/// <param name="charId">
/// The char Id.
/// </param>
/// <returns>
/// List of received messages
/// </returns>
public static Collection <RecentMsgsEntry> LoadRecentMsgsList(uint charId)
{
Collection <RecentMsgsEntry> reciviedMsgsList = new Collection <RecentMsgsEntry>();
SqlWrapper ms = new SqlWrapper();
string sqlQuery = "SELECT `ReceivedID` FROM `receivedmsgs` WHERE PlayerID =" + "'" + charId + "'";
DataTable dt = ms.ReadDatatable(sqlQuery);
foreach (DataRow msgsRow in dt.Rows)
{
RecentMsgsEntry rme = new RecentMsgsEntry {
ReceivedId = uint.Parse(msgsRow["ReceivedID"].ToString())
};
reciviedMsgsList.Add(rme);
}
return(reciviedMsgsList);
}
/// <summary>
/// Count
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Count(NewExpression expression, SqlWrapper sqlWrapper)
{
if (expression.Members != null)
{
for (var i = 0; i < expression.Members.Count; i++)
{
var argument = expression.Arguments[i];
SqlExpressionProvider.Count(argument, sqlWrapper);
}
}
else
{
sqlWrapper.AddField("*");
}
return(sqlWrapper);
}
/// <summary>
/// Having
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Having(ConstantExpression expression, SqlWrapper sqlWrapper)
{
//表达式左侧为bool类型常量
if (expression.NodeType == ExpressionType.Constant && expression.Value is bool res)
{
if (!res && sqlWrapper.EndsWith("AND", "OR"))
{
sqlWrapper += " 1 = 0 ";
}
}
else
{
sqlWrapper.AddDbParameter(expression.Value);
}
return(sqlWrapper);
}
/// <summary>
/// Write meshs to database
/// TODO: catch exceptions
/// </summary>
public void writeMeshstoSql()
{
SqlWrapper Sql = new SqlWrapper();
Sql.SqlDelete(
"DELETE FROM " + this.GetSqlTablefromDynelType() + "meshs WHERE ID=" + this.Id.ToString()
+ " AND playfield=" + this.PlayField.ToString());
int c;
for (c = 0; c < this.Meshs.Count; c++)
{
Sql.SqlInsert(
"INSERT INTO " + this.GetSqlTablefromDynelType() + "meshs VALUES (" + this.Id.ToString() + ","
+ this.PlayField.ToString() + "," + this.Meshs[c].Position.ToString() + ","
+ this.Meshs[c].Mesh.ToString() + "," + this.Meshs[c].OverrideTexture.ToString() + ")");
}
}
/// <summary>
/// Update the field data for this object to the db
/// </summary>
/// <returns>success status</returns>
public override bool Save()
{
var parms = new Dictionary <string, object>();
var sql = new StringBuilder();
sql.Append("update [dbo].[Material] set ");
sql.Append(" [Name] = @Name ");
sql.Append(", [Conductive] = @Conductive ");
sql.Append(", [Magnetic] = @Magnetic ");
sql.Append(", [Flammable] = @Flammable ");
sql.Append(", [Viscosity] = @Viscosity ");
sql.Append(", [Density] = @Density ");
sql.Append(", [Mallebility] = @Mallebility ");
sql.Append(", [Ductility] = @Ductility ");
sql.Append(", [Porosity] = @Porosity ");
sql.Append(", [SolidPoint] = @SolidPoint ");
sql.Append(", [GasPoint] = @GasPoint ");
sql.Append(", [TemperatureRetention] = @TemperatureRetention ");
sql.Append(", [Resistance] = @Resistance ");
sql.Append(", [Composition] = @Composition ");
sql.Append(", [LastRevised] = GetUTCDate()");
sql.Append(" where ID = @id");
parms.Add("id", ID);
parms.Add("Name", Name);
parms.Add("Conductive", Conductive);
parms.Add("Magnetic", Magnetic);
parms.Add("Flammable", Flammable);
parms.Add("Viscosity", Viscosity);
parms.Add("Density", Density);
parms.Add("Mallebility", Mallebility);
parms.Add("Ductility", Ductility);
parms.Add("Porosity", Porosity);
parms.Add("SolidPoint", SolidPoint);
parms.Add("GasPoint", GasPoint);
parms.Add("TemperatureRetention", TemperatureRetention);
parms.Add("Resistance", SerializeResistances());
parms.Add("Composition", SerializeCompositions());
SqlWrapper.RunNonQuery(sql.ToString(), CommandType.Text, parms);
return(true);
}
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Where(MemberExpression expression, SqlWrapper sqlWrapper)
{
//此处判断expression的Member是否是可空值类型
if (expression.Expression?.NodeType == ExpressionType.MemberAccess && expression.Member.DeclaringType.IsNullable())
{
expression = expression.Expression as MemberExpression;
}
if (expression != null)
{
if (expression.Expression?.NodeType == ExpressionType.Parameter)
{
var type = expression.Expression.Type != expression.Member.DeclaringType ?
expression.Expression.Type :
expression.Member.DeclaringType;
var tableName = sqlWrapper.GetTableName(type);
var tableAlias = (expression.Expression as ParameterExpression)?.Name;
tableAlias = sqlWrapper.GetTableAlias(tableName, tableAlias);
if (tableAlias.IsNotNullOrEmpty())
{
tableAlias += ".";
}
sqlWrapper += tableAlias + sqlWrapper.GetColumnInfo(expression.Member.DeclaringType, expression.Member).ColumnName;
//字段是bool类型
if (expression.NodeType == ExpressionType.MemberAccess && expression.Type.GetCoreType() == typeof(bool))
{
if (sqlWrapper.DatabaseType is DatabaseType.MySql or DatabaseType.PostgreSql)
{
sqlWrapper += " IS TRUE";
}
else
{
sqlWrapper += " = 1";
}
}
}
else
{
sqlWrapper.AddDbParameter(expression.ToObject());
}
}
/// <summary>
/// Write weaponpairs to database
/// TODO: catch exceptions
/// </summary>
public void WriteWeaponpairstoSql()
{
SqlWrapper Sql = new SqlWrapper();
Sql.SqlDelete(
"DELETE FROM " + this.GetSqlTablefromDynelType() + "weaponpairs WHERE ID=" + this.Id.ToString()
+ " AND playfield=" + this.PlayField.ToString() + ";");
int c;
for (c = 0; c < this.Weaponpairs.Count; c++)
{
Sql.SqlInsert(
"INSERT INTO " + this.GetSqlTablefromDynelType() + "weaponpairs VALUES (" + this.Id.ToString() + ","
+ this.PlayField.ToString() + "," + this.Weaponpairs[c].value1.ToString() + ","
+ this.Weaponpairs[c].value2.ToString() + "," + this.Weaponpairs[c].value3.ToString() + ","
+ this.Weaponpairs[c].value4.ToString() + ");");
}
}
/// <summary>
/// Join
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql打包对象</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Join(ConstantExpression expression, SqlWrapper sqlWrapper)
{
//表达式左侧为bool类型常量
if (expression.NodeType == ExpressionType.Constant && expression.Value is bool b)
{
var sql = sqlWrapper.ToString().ToUpper().Trim();
if (!b && (sql.EndsWith("AND") || sql.EndsWith("OR")))
{
sqlWrapper += " 1 = 0 ";
}
}
else
{
sqlWrapper.AddDbParameter(expression.Value);
}
return(sqlWrapper);
}
/// <summary>
/// Write newly created NPC to database
/// </summary>
public void AddToDB()
{
SqlWrapper Sql = new SqlWrapper();
Sql.SqlInsert(
"INSERT INTO " + this.GetSqlTablefromDynelType() + " (ID, Playfield) VALUES (" + this.Id.ToString()
+ "," + this.PlayField.ToString() + ")");
this.WriteCoordinatesToSql();
this.WriteHeadingToSql();
this.writeMainStatstoSql();
this.WriteStats();
this.WriteInventoryToSql();
this.WriteNanosToSql();
this.writeWaypointstoSql();
this.WriteWeaponpairstoSql();
this.writeMeshstoSql();
}
/// <summary>
/// Write Textures to Sql Table
/// </summary>
public void WriteTexturesToSql()
{
SqlWrapper sqlWrapper = new SqlWrapper();
int count;
string upd = "";
for (count = 0; count < this.Textures.Count; count++)
{
upd += "textures" + this.Textures[count].place.ToString() + "="
+ this.Textures[count].Texture.ToString();
if (count < this.Textures.Count - 1)
{
upd += ", ";
}
}
sqlWrapper.SqlUpdate(
"UPDATE " + this.GetSqlTablefromDynelType() + " SET " + upd + " WHERE ID=" + this.Id.ToString() + ";");
}
public void writeWaypointstoSQL()
{
SqlWrapper ms = new SqlWrapper();
int count;
ms.SqlDelete("DELETE FROM " + getSQLTablefromDynelType() + "waypoints WHERE ID=" + ID.ToString());
for (count = 0; count < Waypoints.Count; count++)
{
ms.SqlInsert("INSERT INTO " + getSQLTablefromDynelType() + "waypoints VALUES (" + ID.ToString() + "," + PlayField.ToString() + ","
+ String.Format(System.Globalization.CultureInfo.InvariantCulture, "'{0}'", Waypoints[count].x) + ","
+ String.Format(System.Globalization.CultureInfo.InvariantCulture, "'{0}'", Waypoints[count].y) + ","
+ String.Format(System.Globalization.CultureInfo.InvariantCulture, "'{0}'", Waypoints[count].z) + ")");
}
if (Waypoints.Count > 0)
{
}
}
/// <summary>
/// In
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql打包对象</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper In(NewArrayExpression expression, SqlWrapper sqlWrapper)
{
sqlWrapper += "(";
foreach (Expression expressionItem in expression.Expressions)
{
SqlExpressionProvider.In(expressionItem, sqlWrapper);
sqlWrapper += ",";
}
if (sqlWrapper[sqlWrapper.Length - 1] == ',')
{
sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
}
sqlWrapper += ")";
return(sqlWrapper);
}
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Where(UnaryExpression expression, SqlWrapper sqlWrapper)
{
var startIndex = sqlWrapper.Length;
if (new[] { ExpressionType.ArrayLength, ExpressionType.ArrayIndex }.Contains(expression.NodeType))
{
sqlWrapper.AddDbParameter(expression.ToObject());
}
else
{
SqlExpressionProvider.Where(expression.Operand, sqlWrapper);
//取非解析
ExpressionNotResolver(expression, sqlWrapper, startIndex);
}
return(sqlWrapper);
}
public void readWeaponpairsfromSQL()
{
AOWeaponpairs m_wp;
SqlWrapper Sql = new SqlWrapper();
Weaponpairs.Clear();
DataTable dt = Sql.ReadDT("SELECT * FROM " + getSQLTablefromDynelType() + "weaponpairs WHERE ID=" + ID.ToString() + " AND playfield=" + PlayField.ToString());
foreach (DataRow row in dt.Rows)
{
m_wp = new AOWeaponpairs();
m_wp.value1 = (Int32)row["value1"];
m_wp.value2 = (Int32)row["value2"];
m_wp.value3 = (Int32)row["value3"];
m_wp.value4 = (Int32)row["value4"];
Weaponpairs.Add(m_wp);
}
}
/// <summary>
///
/// </summary>
/// <param name="client"></param>
/// <param name="charid"></param>
public void DeleteChar(Client client, Int32 charid)
{
MemoryStream m_stream = new MemoryStream();
BinaryWriter m_writer = new BinaryWriter(m_stream);
SqlWrapper ms = new SqlWrapper();
try
{ /* delete char */
/* i assume there should be somewhere a flag, caus FC can reenable a deleted char.. */
string SqlQuery = "DELETE FROM `characters` WHERE ID = " + charid;
ms.SqlDelete(SqlQuery);
SqlQuery = "DELETE FROM `characters_stats` WHERE ID = " + charid;
ms.SqlDelete(SqlQuery);
SqlQuery = "DELETE FROM `organizations` WHERE ID = " + charid;
ms.SqlDelete(SqlQuery);
SqlQuery = "DELETE FROM `inventory` WHERE ID = " + charid;
ms.SqlDelete(SqlQuery);
}
catch (Exception e)
{
Console.WriteLine(this.m_name + e.Message);
}
/* Send Cleint delete message */
m_writer.Write(new byte[]
{
0xDF, 0xDF, 0x00, 0x01, 0x00, 0x01, 0x00,
0x00, /* msg size - 2 byte */
0x00, 0x00, 0x00, 0x01, 0x00, 0x00,
0xff, 0xff,
0x00, 0x00, 0x00,
0x15 /* answer 0x15, char_id */
});
m_writer.Write(IPAddress.HostToNetworkOrder(charid));
m_writer.Flush();
m_stream.Capacity = (int)m_stream.Length;
byte[] reply = m_stream.GetBuffer();
m_writer.Close();
m_stream.Dispose();
/* insert size */
byte[] packetlength = BitConverter.GetBytes(reply.Length);
reply[7] = packetlength[0];
/* send response */
client.Send(ref reply);
}
/// <summary>
/// The execute command.
/// </summary>
/// <param name="client">
/// The client.
/// </param>
/// <param name="target">
/// The target.
/// </param>
/// <param name="args">
/// The args.
/// </param>
public override void ExecuteCommand(Client client, Identity target, string[] args)
{
if (args.Length >= 2)
{
if (args[1].ToLower() == "list")
{
string filter = string.Empty;
if (args.Length > 2)
{
for (int i = 2; i < args.Length; i++)
{
if (filter.Length > 0)
{
filter = filter + " AND ";
}
if (filter.Length == 0)
{
filter = "WHERE ";
}
filter = filter + "name like '%" + args[i] + "%' ";
}
}
SqlWrapper sql = new SqlWrapper();
DataTable dt =
sql.ReadDatatable("SELECT Hash, Name FROM mobtemplate " + filter + " order by Name ASC");
client.SendChatText("List of mobtemplates: ");
foreach (DataRow row in dt.Rows)
{
client.SendChatText(row[0] + " " + row[1]);
}
return;
}
}
if (args.Length == 3)
{
NonPlayerCharacterHandler.SpawnMonster(client, args[1], uint.Parse(args[2]));
return;
}
this.CommandHelp(client);
}
/// <summary>
///
/// </summary>
/// <param name="client"></param>
/// <param name="start_in_sl"></param>
/// <param name="charid"></param>
public void SendNameToStartPF(Client client, bool start_in_sl, Int32 charid)
{
MemoryStream m_stream = new MemoryStream();
BinaryWriter m_writer = new BinaryWriter(m_stream);
SqlWrapper ms = new SqlWrapper();
/* set startplayfield */
string SqlUpdate = "UPDATE `characters` set ";
if (start_in_sl)
{
SqlUpdate += "`playfield`=4001,`X`=850,`Y`=43,`Z`=565 ";
}
else
{
SqlUpdate += "`playfield`=4582,`X`=939,`Y`=20,`Z`=732 ";
}
SqlUpdate += " where `ID` = " + charid;
ms.SqlUpdate(SqlUpdate);
m_writer.Write(new byte[]
{
0xDF, 0xDF, 0x00, 0x01, 0x00, 0x01, 0x00,
0x00, /* msg size - 2 byte */
0x00, 0x00, 0x00, 0x01, 0x00, 0x00,
0xff, 0xff, /* possible timecode */
0x00, 0x00, 0x00,
0x11 /* answer 0x11, char_id */
});
m_writer.Write(IPAddress.HostToNetworkOrder(charid));
m_writer.Write(new byte[] { 0xb0, 0xd2, 0xff, 0xff }); /* unknown */
m_writer.Flush();
m_stream.Capacity = (int)m_stream.Length;
byte[] reply = m_stream.GetBuffer();
m_writer.Close();
m_stream.Dispose();
/* insert size */
byte[] packetlength = BitConverter.GetBytes(reply.Length);
reply[7] = packetlength[0];
/* send response */
client.Send(ref reply);
}
/// <summary>
/// Select
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql打包对象</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Select(NewExpression expression, SqlWrapper sqlWrapper)
{
if (expression.Members != null)
{
for (var i = 0; i < expression.Members.Count; i++)
{
var argument = expression.Arguments[i];
var member = expression.Members[i];
SqlExpressionProvider.Select(argument, sqlWrapper);
//添加字段别名
if (argument is MemberExpression memberExpression && memberExpression.Member.Name != member.Name)
{
sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(member.Name)}";
}
else if (argument is ConstantExpression constantExpression && constantExpression.Value?.ToString() != member.Name)
{
sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(member.Name)}";
}
public void Update(JobUpdateRequest req)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand cmd = new SqlWrapper().Wrapper("Jobs_update", con);
cmd.Parameters.AddWithValue("@id", req.Id);
cmd.Parameters.AddWithValue("@job_id", req.JobId);
cmd.Parameters.AddWithValue("@title", req.Title);
cmd.Parameters.AddWithValue("@url", req.Url);
cmd.Parameters.AddWithValue("@company", req.Company);
cmd.Parameters.AddWithValue("@description", req.Description);
cmd.Parameters.AddWithValue("@location", req.Location);
cmd.Parameters.AddWithValue("@date_applied", req.DateApplied);
cmd.Parameters.AddWithValue("@archived", req.Archived);
cmd.Parameters.AddWithValue("@quick_apply", req.QuickApply);
cmd.ExecuteNonQuery();
}
}
public static void CacheAllFromDB()
{
SqlWrapper wrapper = new SqlWrapper();
DataTable dt = wrapper.ReadDatatable("SELECT * FROM mobdroptable");
DataRowCollection drc = dt.Rows;
foreach (DataRow row in drc)
{
FullDropList.Add(
new LootItem(
row[0].ToString(),
row[1].ToString(),
row[2].ToString(),
row[3].ToString(),
row[4].ToString(),
row[5].ToString()));
}
}
/// <summary>
/// Select
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql打包对象</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Select(MemberInitExpression expression, SqlWrapper sqlWrapper)
{
if (expression.Bindings?.Count > 0)
{
foreach (MemberAssignment memberAssignment in expression.Bindings)
{
var type = expression.Type != memberAssignment.Member.DeclaringType ?
expression.Type :
memberAssignment.Member.DeclaringType;
var aliasName = memberAssignment.Member.Name;
var tableName = sqlWrapper.GetTableName(type);
if ((memberAssignment.Expression as MemberExpression)?.Expression is ParameterExpression parameterExpr)
{
var tableAlias = sqlWrapper.GetTableAlias(tableName, parameterExpr.Name);
if (!tableAlias.IsNullOrEmpty())
{
tableAlias += ".";
}
var fieldName = tableAlias + sqlWrapper.GetColumnInfo(type, memberAssignment.Member).columnName;
sqlWrapper.AddField(fieldName);
}
else
{
var fieldName = memberAssignment.Expression.ToObject().ToString();
sqlWrapper.AddField(fieldName);
}
sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(aliasName)}";
}
}
else
{
sqlWrapper.AddField("*");
}
return(sqlWrapper);
}
/// <summary>
/// Insert
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Insert(ListInitExpression expression, SqlWrapper sqlWrapper)
{
if (expression.ToObject() is IEnumerable collection)
{
var i = 0;
var fields = new List <string>();
foreach (var item in collection)
{
if (sqlWrapper.DatabaseType != DatabaseType.Oracle)
{
sqlWrapper.Append("(");
}
if (i > 0 && sqlWrapper.DatabaseType == DatabaseType.Oracle)
{
sqlWrapper.Append(" UNION ALL SELECT ");
}
var properties = item?.GetType().GetProperties();
foreach (var p in properties)
{
var type = p.DeclaringType.IsAnonymousType() ?
sqlWrapper.DefaultType :
p.DeclaringType;
var columnInfo = sqlWrapper.GetColumnInfo(type, p);
if (columnInfo.IsInsert)
{
var value = p.GetValue(item, null);
if (value != null || (sqlWrapper.IsEnableNullValue && value == null))
{
sqlWrapper.AddDbParameter(value, columnInfo.DataType);
if (!fields.Contains(columnInfo.ColumnName))
{
fields.Add(columnInfo.ColumnName);
}
sqlWrapper += ",";
}
}
}
if (sqlWrapper[^ 1] == ',')
/// <summary>
/// Read meshs from database
/// TODO: catch exceptions
/// </summary>
public void readMeshsfromSql()
{
SqlWrapper Sql = new SqlWrapper();
this.Meshs.Clear();
AOMeshs m_m;
DataTable dt =
Sql.ReadDatatable(
"SELECT * from " + this.GetSqlTablefromDynelType() + "meshs WHERE ID=" + this.Id.ToString()
+ " AND playfield=" + this.PlayField.ToString());
foreach (DataRow row in dt.Rows)
{
m_m = new AOMeshs();
m_m.Position = (Int32)row["meshvalue1"];
m_m.Mesh = (Int32)row["meshvalue2"];
m_m.OverrideTexture = (Int32)row["meshvalue3"];
}
}
/// <summary>
/// Join
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Join(MemberExpression expression, SqlWrapper sqlWrapper)
{
var type = expression.Expression.Type != expression.Member.DeclaringType ?
expression.Expression.Type :
expression.Member.DeclaringType;
var tableName = sqlWrapper.GetTableName(type);
var parameter = expression.Expression as ParameterExpression;
var tableAlias = sqlWrapper.GetTableAlias(tableName, parameter?.Name);
if (tableAlias.IsNotNullOrEmpty())
{
tableAlias += ".";
}
sqlWrapper += tableAlias + sqlWrapper.GetColumnInfo(expression.Member.DeclaringType, expression.Member).ColumnName;
return(sqlWrapper);
}
/// <summary>
/// Select
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper Select(MemberInitExpression expression, SqlWrapper sqlWrapper)
{
if (expression.Bindings?.Count > 0)
{
foreach (MemberAssignment memberAssignment in expression.Bindings)
{
if (memberAssignment.Expression is MemberExpression memberExpr && memberExpr.Expression is ParameterExpression parameterExpr)
{
var type = parameterExpr.Type;
var tableName = sqlWrapper.GetTableName(type);
var tableAlias = sqlWrapper.GetTableAlias(tableName, parameterExpr.Name);
if (tableAlias.IsNotNullOrEmpty())
{
tableAlias += ".";
}
var fieldName = tableAlias + sqlWrapper.GetColumnInfo(type, memberExpr.Member).ColumnName;
sqlWrapper.AddField(fieldName);
}
else
{
var fieldName = memberAssignment.Expression.ToObject().ToString();
sqlWrapper.AddField(fieldName);
}
var aliasName = sqlWrapper.GetColumnName(memberAssignment.Member.Name);
var field = sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1];
if (field.IsNotNullOrEmpty() && field.Contains(".") && !field.Contains("(", ")"))
{
field = field.Split('.').LastOrDefault();
}
if (!field.Equals(!sqlWrapper.IsEnableFormat, aliasName))
{
sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {aliasName}";
}
}
/// <summary>
/// OrderBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlWrapper">sql包装器</param>
/// <param name="orders">排序方式</param>
/// <returns>SqlWrapper</returns>
public override SqlWrapper OrderBy(NewExpression expression, SqlWrapper sqlWrapper, params OrderType[] orders)
{
for (var i = 0; i < expression.Arguments.Count; i++)
{
SqlExpressionProvider.OrderBy(expression.Arguments[i], sqlWrapper);
if (i <= orders.Length - 1)
{
sqlWrapper += $" { (orders[i] == OrderType.Descending ? "DESC" : "ASC")},";
}
else
{
sqlWrapper += " ASC,";
}
}
sqlWrapper.RemoveLast(',');
return(sqlWrapper);
}
/// <summary>
/// Read Coordinates from Sql Table
/// </summary>
public void ReadCoordsFromSql()
{
SqlWrapper ms = new SqlWrapper();
if (this.Type == 0)
{
return;
}
string SqlTable = this.GetSqlTablefromDynelType();
DataTable dt =
ms.ReadDatatable("SELECT Playfield, X,Y,Z from " + SqlTable + " WHERE ID=" + this.Id.ToString() + ";");
if (dt.Rows.Count > 0)
{
this.PlayField = (Int32)dt.Rows[0][0];
this.Coordinates.x = (Single)dt.Rows[0][1];
this.Coordinates.y = (Single)dt.Rows[0][2];
this.Coordinates.z = (Single)dt.Rows[0][3];
}
}
