static void WriteSql(string name, SqlInfo content)
{
Console.WriteLine();
Console.WriteLine("".PadRight(80, '-'));
Console.WriteLine($"{name}.sql content:".ToUpperInvariant());
Console.WriteLine(content);
}
public void Operator_SqlInfo_String()
{
SqlInfo sqlInfo = "SELECT * FROM TEST";
string actual = sqlInfo;
Assert.AreEqual("SELECT * FROM TEST", actual);
}
public void Operator_String_SqlInfo()
{
SqlInfo actual = "SELECT * FROM TEST";
Assert.AreEqual("", actual.Cte);
Assert.AreEqual("SELECT * FROM TEST", actual.Content);
}
//public static void CheckStringPara(string SQL,string tableName, IDataParameter[] paras,string parameterChar)
//{
// for (int index = 0; index < paras.Length; index++)
// {
// //为字符串类型的参数指定长度 edit at 2012.4.23
// if (paras[index].Value != null && paras[index].Value.GetType() == typeof(string))
// {
// string field = FindFieldNameInSql(SQL, paras[index].ParameterName, parameterChar);
// ((IDbDataParameter)paras[index]).Size = EntityBase.GetStringFieldSize(tableName, field);
// }
// }
//}
/// <summary>
/// 使用SQL查询命令信息 执行数据阅读器查询。注意，查询单行数据不支持存储过程。
/// </summary>
/// <param name="si">SQL查询命令信息</param>
/// <param name="db">数据访问对象</param>
/// <param name="single">是否仅查询单行数据</param>
/// <returns>数据阅读器</returns>
public static IDataReader ExecuteDataReader(SqlInfo si, AdoHelper db, bool single)
{
IDataReader reader = null;
if (si.Parameters != null && si.Parameters.Count > 0)
{
IDataParameter[] paras = GetParameters(si.Parameters, db);
if (single)
{
reader = db.ExecuteDataReaderWithSingleRow(si.SQL, paras);
}
else
{
reader = db.ExecuteDataReader(si.SQL, si.CommandType, paras);
}
}
else
{
if (single)
{
reader = db.ExecuteDataReaderWithSingleRow(si.SQL);
}
else
{
reader = db.ExecuteDataReader(si.SQL, si.CommandType, null);
}
}
return(reader);
}
/// <summary>
/// 根据OQL查询数据获得DataReader。如果指定single=真，将执行优化的查询以获取单条记录
/// </summary>
/// <param name="oql">OQL表达式</param>
/// <param name="db">当前数据库访问对象</param>
/// <param name="factEntityType">实体类类型</param>
/// <param name="single">是否只查询一条记录</param>
/// <returns>DataReader</returns>
public static IDataReader ExecuteDataReader(OQL oql, AdoHelper db, Type factEntityType, bool single)
{
SqlInfo si = GetSqlInfoFromOQL(oql, db, factEntityType, single);
oql.Dispose();
return(ExecuteDataReader(si, db, single));
}
internal static SqlInfo ReadSqlInfo(IMsgPackReader reader, IMsgPackConverter <Key> keyConverter, IMsgPackConverter <int> intConverter)
{
var length = reader.ReadMapLength();
if (length == null)
{
return(null);
}
var result = default(SqlInfo);
for (var i = 0; i < length; i++)
{
switch (keyConverter.Read(reader))
{
case Key.SqlRowCount:
result = new SqlInfo(intConverter.Read(reader));
break;
default:
reader.SkipToken();
break;
}
}
return(result);
}
public string GenerateSql()
{
var result = sqlInfo.GetSql();
sqlInfo = new SqlInfo(this.sqlInfo.DatabaseType);
return(result);
}
private void Savetimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
foreach (var conn in _connections.Values)
{
if (ConnectedToSQL)
{
try { SqlInfo.UpdateUser(_connections[conn.UCID].UName, StringHelper.StripColors(SqlInfo.RemoveStupidCharacters(_connections[conn.UCID].PName)), false, _connections[conn.UCID].cash, _connections[conn.UCID].bankbalance, _connections[conn.UCID].TotalDistance, _connections[conn.UCID].cars, _connections[conn.UCID].totaljobsdone, _connections[conn.UCID].totalearnedfromjobs, _connections[conn.UCID].Timezone, _connections[conn.UCID].KMHorMPH, _connections[conn.UCID].TotalConnectionTime); }
catch (Exception EX)
{
if (!SqlInfo.IsConnectionStillAlive())
{
SQL_label.Text = "MySQL : NOT CONNECTED!";
ConnectedToSQL = false;
SQLReconnectTimer.Start();
}
LogTextToFile("sqlerror", "[" + conn.UCID + "] " + StringHelper.StripColors(_connections[conn.UCID].PName) + "(" + _connections[conn.UCID].UName + ") conn - Exception: " + EX.Message, false);
}
}
}
}
catch (Exception f)
{
MessageBox.Show("" + f.Message, "AN ERROR OCCURED");
}
}
public override SqlInfo Query(string sql)
{
SqlInfo info = new SqlInfo();
info.sqlite3_stmt = Sql_Query(dataBase, sql);
return(info);
}
private void Savetimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
foreach (var conn in _connections.Values)
{
if (ConnectedToSQL)
{
try
{
SqlInfo.UpdateUser(_connections[conn.UCID].UName, _connections[conn.UCID].PName, _connections[conn.UCID].TotalDistance, _connections[conn.UCID].points);
}
catch (Exception EX)
{
if (!SqlInfo.IsConnectionStillAlive())
{
ConnectedToSQL = false;
SQLReconnectTimer.Start();
}
LogTextToFile("sqlerror", "[" + conn.UCID + "] " + (_connections[conn.UCID].PName) + "(" + _connections[conn.UCID].UName + ") conn - Exception: " + EX.Message, false);
}
}
}
}
catch (Exception f)
{
MessageBox.Show("" + f.Message, "AN ERROR OCCURED");
}
}
private void btnConnect_Click(object sender, EventArgs e)
{
SqlInfo sqlinfo = new SqlInfo(SqlType.MYSQL);
sqlinfo.Server = tbServer.Text.Trim();
sqlinfo.Port = Convert.ToInt32(tbPort.Text);
sqlinfo.UserId = tbName.Text.Trim();
sqlinfo.Password = tbPwd.Text;
sqlinfo.Database = tbDb.Text;
ISqlHelper mangos = SqlHelperCreator.Create(sqlinfo);
try
{
if (mangos.TestConnection())
{
MessageBox.Show("连接成功!");
}
setConnection(mangos);
Close();
Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public override SqlInfo ExecuteQuery(string sqlQuery, bool isStep)//执行查询
{
Debug.Log("ExecuteQuery::" + sqlQuery);
SqlInfo qr = db.Query(sqlQuery);
return(qr);
}
public override bool MoveToNext(SqlInfo info)
{
if (info.obj == null)
{
Debug.Log("MoveToNext obj is null");
return(false);
}
return(info.obj.Call <bool>("moveToNext"));
}
/// <summary>
/// Add a match of two orders to begin the negociation process.
/// </summary>
/// <param name="workingOrderId">The WorkingOrderId of the first order.</param>
/// <param name="contraOrderId">The WorkingOrderId of the second order.</param>
private static void SetWorkingOrderStatus(object[] key, params object[] parameters)
{
int workingOrderId = (int)key[0];
int statusCode = (int)parameters[0];
// Create a transaction for adding the Match.
Transaction transaction = new Transaction(Core.WorkingOrder.PersistentStore);
try
{
// These tables are needed for the transaction.
transaction.AdoTransaction.LockRequests.AddWriterLock(ServerMarketData.DestinationOrderLock);
transaction.AdoTransaction.LockRequests.AddWriterLock(ServerMarketData.ExecutionLock);
transaction.AdoTransaction.LockRequests.AddReaderLock(ServerMarketData.MatchLock);
transaction.AdoTransaction.LockRequests.AddWriterLock(ServerMarketData.SourceOrderLock);
transaction.AdoTransaction.LockRequests.AddWriterLock(ServerMarketData.WorkingOrderLock);
// Start the Transaction.
transaction.Begin();
// The SqlInfo contains information about the SQL transaction which must be passed on to the method during a
// transaction. There is only one ADO.NET store, but there can be one or more persistent stores. In this case,
// the ‘Core.Match’ class specifies where to store the data permanently.
SqlInfo sqlInfo = transaction.SqlInfoList[Core.WorkingOrder.PersistentStore];
// Add the match of the primary order to the contra order.
ServerMarketData.WorkingOrderRow workingOrderRow = ServerMarketData.WorkingOrder.FindByWorkingOrderId(workingOrderId);
if (workingOrderRow != null)
{
long rowVersion = workingOrderRow.RowVersion;
object submittedTime = statusCode == Status.Submitted ? (object)DateTime.Now : (object)DBNull.Value;
Core.WorkingOrder.Update(transaction.AdoTransaction, sqlInfo.Transaction, ref rowVersion, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, statusCode,
null, null, null, null, submittedTime, null, null, null, workingOrderId);
}
// These two methods can now be committed to the ADO.NET and SQL data stores. They will be added as a unit or
// rolled back as a unit.
transaction.Commit();
}
catch (Exception exception)
{
// Log the error.
EventLog.Error("{0}: {1}", exception.Message, exception.StackTrace);
// Any errors will cause the transaction to be rolled back.
transaction.Rollback();
}
finally
{
// Whether successful or not, this will close out the resources for the transaction and end it.
transaction.EndTransaction();
}
}
/// <summary>
/// 初始化SQL语句
/// </summary>
/// <param name="className"></param>
/// <param name="propertyInfos"></param>
/// <returns></returns>
public List <SqlInfo> InitSqlInfos <T>(T t, string tableName, string className, PropertyInfo[] propertyInfos) where T : class
{
List <SqlInfo> result = new List <SqlInfo>();
List <string> columns = new List <string>();
Dictionary <string, object> paramtersDic = new Dictionary <string, object>();
foreach (PropertyInfo propertyInfo in propertyInfos)
{
string name = GetPropertyInfoName(propertyInfo);
if (name == null)
{
continue;
}
object value = GetPropertyInfoValue <T>(t, propertyInfo);
columns.Add(name);
paramtersDic.Add("@" + name, value);
}
string columnContactString = string.Join(",", columns);
string columnParamterNameString = string.Join(",", paramtersDic.Keys.ToList());
SqlInfo selectSqlInfo = new SqlInfo()
{
Type = 4
};
selectSqlInfo.SqlString = string.Format("select {0} from {1}", columnContactString, tableName);
result.Add(selectSqlInfo);
SqlInfo insertSqlInfo = new SqlInfo();
insertSqlInfo.SqlString = string.Format("insert into {0}({1}) values({2})", tableName, columnContactString, columnParamterNameString);
insertSqlInfo.Parameters = GetMySqlParameters(paramtersDic);
insertSqlInfo.Type = 1;
result.Add(insertSqlInfo);
SqlInfo updateSqlInfo = new SqlInfo();
result.Add(updateSqlInfo);
return(result);
}
public override SqlInfo Query(string sql)
{
// if(javaClass==null)
// {
// javaClass = new AndroidJavaClass(JAVA_CLASS);
// }
Debug.Log("SqlAndroidWrapper:Query sql=" + sql);
SqlInfo info = new SqlInfo();
{
//return Cursor
AndroidJavaObject obj = javaClass.CallStatic <AndroidJavaObject>("Query", objDb, sql);
string strtest = javaClass.CallStatic <string>("QueryTest", sql);
Debug.Log("Query AndroidJavaObject strtest=" + strtest);
//Call 不执行java
string strtest2 = javaClass.Call <string>("QueryTest2", sql);
Debug.Log("Query AndroidJavaObject strtest2=" + strtest2);
info.obj = obj;
if (info.obj == null)
{
Debug.Log("Query AndroidJavaObject obj is null");
}
else
{
Debug.Log("SqlAndroidWrapper:Query end");
}
// if(obj.Call<bool>("moveToFirst"))
// {
// }
/*
* if (cr.moveToFirst()) {
* for (int i = 0; i < cr.getCount(); i++) {
* //cr.getString();
* FaceInfo info = new FaceInfo();
* info.id = cr.getString(cr.getColumnIndex("id"));
* info.title = cr.getString(cr.getColumnIndex("title"));
* Log.d(TAG, "Seach result: title="+info.title+" id="+info.id);
* listItem.add(info);
* cr.moveToNext();
* }
* }
*/
}
return(info);
}
/// <summary>
/// 根据OQL查询数据获得DataReader。如果指定single=真，将执行优化的查询以获取单条记录
/// </summary>
/// <param name="oql">OQL表达式</param>
/// <param name="db">当前数据库访问对象</param>
/// <param name="factEntityType">实体类类型</param>
/// <param name="single">是否只查询一条记录</param>
/// <returns>DataReader</returns>
public static IDataReader ExecuteDataReader(OQL oql, AdoHelper db, Type factEntityType, bool single)
{
SqlInfo si = GetSqlInfoFromOQL(oql, db, factEntityType, single);
oql.Dispose();
if (si.SQL == string.Empty) //可能是分页但是没有记录
{
return(null);
}
else
{
return(ExecuteDataReader(si, db, single));
}
}
//执行查询
public SqlInfo Query(string sql)
{
Debug.Log("Sql:Query sql=" + sql);
SqlInfo info = null;
#if UNITY_ANDROID && !UNITY_EDITOR
info = this.platform.Query(sql);
#elif UNITY_IPHONE && !UNITY_EDITOR
info = this.platform.Query(sql);
#else
this.platform.Query(sql);
#endif
return(info);
}
internal static SqlInfo[] Clone(this SqlInfo[] sqlInfos, MemberInfo member)
{
if (sqlInfos.Length == 0)
{
return(Array <SqlInfo> .Empty);
}
var sql = new SqlInfo[sqlInfos.Length];
for (var i = 0; i < sql.Length; i++)
{
sql[i] = sqlInfos[i].Clone(member);
}
return(sql);
}
public void Test1()
{
// 测试命令参数超过最大值的情况
FieldInfo field = typeof(SqlInfo).GetField("MaxParameterCount", BindingFlags.Static | BindingFlags.NonPublic);
field.SetValue(null, 3);
DbCommand command = CreateDbCommand();
SqlInfo info = SqlInfo.Create(command);
Assert.AreEqual(4, info.Parameters.Count);
Assert.AreEqual("#####", info.Parameters[3].Name);
}
static SqlInfo GetSqlInfoInstance()
{
var info = new SqlInfo()
{
InfoID = Guid.NewGuid().ToString("N"),
username = "******",
time = DateTime.Now.ToLongTimeString(),
model = "Sky",
sql = "select * from fms.fms_user_info",
spendtime = 10,
callstack = ""
};
return info;
}
private void SQLReconnectTimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
{
SQLRetries++;
ConnectedToSQL = SqlInfo.StartUp(SQLIPAddress, SQLDatabase, SQLUsername, SQLPassword);
if (!ConnectedToSQL)
{
insim.Send(255, "SQL connect attempt failed! Attempting to reconnect in ^310 ^8seconds!");
}
else
{
insim.Send(255, "SQL connected after ^3" + SQLRetries + " ^8times!");
SQLRetries = 0;
SQLReconnectTimer.Stop();
}
}
}
public void Form1_Load(object sender, EventArgs e)
{
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");
#region ' Timer '
SuperFast.Elapsed += new System.Timers.ElapsedEventHandler(SuperFast_Elapsed);
SuperFast.Interval = 1;
SuperFast.Enabled = true;
System.Timers.Timer Payout = new System.Timers.Timer();
Payout.Elapsed += new System.Timers.ElapsedEventHandler(Payout_Timer);
Payout.Interval = 3000;
Payout.Enabled = true;
// SQL timer
SQLReconnectTimer.Interval = 10000;
SQLReconnectTimer.Elapsed += new System.Timers.ElapsedEventHandler(SQLReconnectTimer_Elapsed);
ConnectedToSQL = SqlInfo.StartUp(SQLIPAddress, SQLDatabase, SQLUsername, SQLPassword);
if (!ConnectedToSQL)
{
insim.Send(255, "SQL connect attempt failed! Attempting to reconnect in 10 seconds!");
SQLReconnectTimer.Start();
}
else
{
insim.Send(255, "SQL Connected!");
}
#endregion
}
catch (Exception error)
{
{
MessageBox.Show("" + error.Message, "AN ERROR OCCURED");
}
}
}
public static SqlInfo GetSqlText(uint devId, byte[] data)
{
SqlInfo res = new SqlInfo();
Dictionary <string, object> param;
bool c = ParseSourceData(devId, data, out param);
if (c == true)
{
res.Command = CmdText;
res.Param = param;
res.IsValid = true;
return(res);
}
else
{
res.IsValid = false;
return(res);
}
}
private void SQLReconnectTimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
{
SQLRetries++;
ConnectedToSQL = SqlInfo.StartUp(SQLIPAddress, SQLDatabase, SQLUsername, SQLPassword);
if (!ConnectedToSQL)
{
SQL_label.Text = "MySQL : NOT CONNECTED!";
MessageToAdmins("SQL connect attempt failed! Attempting to reconnect in ^310 ^8seconds!");
}
else
{
SQL_label.Text = "MySQL : CONNECTED!";
MessageToAdmins("SQL connected after ^3" + SQLRetries + " ^8times!");
SQLRetries = 0;
SQLReconnectTimer.Stop();
}
}
}
public override SqlInfo ExecuteQuery(string sqlQuery, bool isStep)//执行查询
{
Debug.Log("ExecuteQuery::" + sqlQuery);
// dbCommand = dbConnection.CreateCommand();
// dbCommand.CommandText = sqlQuery;
// dbReader = dbCommand.ExecuteReader();
// return dbReader;
SqlInfo info = new SqlInfo();
SQLiteQuery qr = new SQLiteQuery(db, sqlQuery);
if (isStep)
{
qr.Step();
}
info.sq = qr;
//qr.Release();
return(info);
}
public static string GetSqlScript(this SqlInfo sqlinfo)
{
if (sqlinfo == null || string.IsNullOrEmpty(sqlinfo.SqlText))
{
return(string.Empty);
}
StringBuilder sb = new StringBuilder();
if (sqlinfo.Parameters != null)
{
foreach (NameValue nv in sqlinfo.Parameters)
{
// 由于日志中没有记录参数的类型，所以这里只能申明为字符串类型，让SQLSERVER自动转换
sb.Append($"declare {nv.Name} as nvarchar(max);\r\n");
sb.Append($"set {nv.Name} = '{nv.Value}';\r\n\r\n");
}
sb.AppendLine("\r\n");
}
sb.Append(sqlinfo.SqlText);
return(sb.ToString());
}
public void Test2()
{
// 测试特殊参数场景
DbCommand command = CreateDbCommand();
SqlInfo info = SqlInfo.Create(command);
Assert.AreEqual(command.CommandText, info.SqlText.ToString());
SqlInfo info2 = SqlInfo.Create(null);
Assert.IsNull(info2);
command.Parameters.Clear();
SqlInfo info3 = SqlInfo.Create(command);
Assert.IsNull(info3.Parameters);
}
//select count(*) from sqlite_master where type='table' and name = 'yourtablename';
public override bool IsExitTable(string name)//创建表
{
bool ret = false;
//string query = "select count(*) from sqlite_master where type='table' and name = '" + name + "'";
string query = "select * from sqlite_master where type='table' and name = '" + name + "'";
SqlInfo rd = ExecuteQuery(query, false);
//int count = rd.GetCount();//rd.GetInteger("0");
int count = 0;
while (db.MoveToNext(rd))
{
count++;
}
Debug.Log("IsExitTable:count=" + count);
if (count > 0)
{
ret = true;
}
return(ret);
}
public static SqlInfo LoadSqlInfo(DataRow row)
{
string sql = row.GetString("SqlText");
if (string.IsNullOrEmpty(sql))
{
return(null);
}
SqlInfo info = new SqlInfo();
info.SqlText = sql;
info.InTranscation = (row.GetString("InTranscation") ?? "0") == "1";
string args = row.GetString("Parameters");
if (string.IsNullOrEmpty(args) == false)
{
info.Parameters = args.FromJson <List <NameValue> >();
}
return(info);
}
private void Savetimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
foreach (var c in _connections.Values)
{
c.totalplaytime += 1;
}
foreach (var conn in _connections.Values)
{
if (ConnectedToSQL)
{
try { SqlInfo.UpdateUser(_connections[conn.UCID].UName, StringHelper.StripColors(SqlInfo.RemoveStupidCharacters(_connections[conn.UCID].PName)),
false, _connections[conn.UCID].TotalDistance, _connections[conn.UCID].Timezone, _connections[conn.UCID].KMHorMPH, _connections[conn.UCID].Rank,
_connections[conn.UCID].Driftpoints, _connections[conn.UCID].timesCrashed, _connections[conn.UCID].timesChatted, _connections[conn.UCID].timesReset,
_connections[conn.UCID].timesJoined, _connections[conn.UCID].timesSpectated, _connections[conn.UCID].kmXRG, _connections[conn.UCID].kmLX4,
_connections[conn.UCID].kmLX6, _connections[conn.UCID].kmRB4, _connections[conn.UCID].kmFXO, _connections[conn.UCID].kmXRT, _connections[conn.UCID].kmRAC,
_connections[conn.UCID].kmFZ5, _connections[conn.UCID].totalplaytime); }
catch (Exception EX)
{
if (!SqlInfo.IsConnectionStillAlive())
{
ConnectedToSQL = false;
SQLReconnectTimer.Start();
}
LogTextToFile("sqlerror", "[" + conn.UCID + "] " + StringHelper.StripColors(_connections[conn.UCID].PName) + "(" + _connections[conn.UCID].UName + ") conn - Exception: " + EX.Message, false);
}
}
}
}
catch (Exception f)
{
MessageBox.Show("" + f.Message, "AN ERROR OCCURED");
}
}
