public static List <MoneyRecord> FindByUid(int uid)
{
if (uid <= 0)
{
return(null);
}
string sql = String.Format("SELECT moneyrecordtable.recordid,moneyrecordtable.uid,moneyrecordtable.changedmoney,moneyrecordtable.changetime,userinfotable.username,userinfotable.usernumber FROM {0} left join userinfotable on moneyrecordtable.uid =userinfotable.uid WHERE userinfotable.uid = {1};", table_name, uid);
MySqlCommand cmd = new MySqlCommand(sql, SQLEngine.Instance.Connection);
MySqlDataReader rs = cmd.ExecuteReader();
List <MoneyRecord> moneyrecords = new List <MoneyRecord>();
while (rs.Read())
{
MoneyRecord moneyrecord = new MoneyRecord(
rs.GetInt32("uid"),
rs.GetInt32("changedmoney"),
rs.GetDateTime("changetime"),
rs.GetString("username"),
rs.GetString("usernumber"));
moneyrecord.recordid = rs.GetInt32("recordid");
moneyrecords.Add(moneyrecord);
}
rs.Close();
return(moneyrecords);
}
public static bool InitDB()
{
try
{
if (SQLEngine.Instance.Connection == null)
{
return(false);
}
//创建数据库
string sqlstr = "CREATE DATABASE IF NOT EXISTS " + DATABASE_NAME + ";"
+ "use " + DATABASE_NAME + ";";
MySqlCommand cmd = new MySqlCommand(sqlstr, SQLEngine.Instance.Connection);
cmd.ExecuteNonQuery();
//创建表
FaceInfoBase.MakeTable();
UserInfo.MakeTable();
MoneyRecord.MakeTable();
return(true);
}catch (Exception ex)
{
return(false);
}
}
public static void Test()
{
MoneyRecord.MakeTable();
MoneyRecord.Create(1, 233, DateTime.Now);
MoneyRecord.Create(1, 666, DateTime.UtcNow);
MoneyRecord.Create(1, 5, DateTime.Today);
foreach (var mr in FindByUid(1))
{
Console.WriteLine(mr);
}
Console.WriteLine("Test Completed [MoneyRecord]!");
}
public static MoneyRecord Create(int uid, int changedmoney, DateTime changetime)
{
MoneyRecord moneyrecord = new MoneyRecord(uid, changedmoney, changetime, null, null);
string sql = String.Format("select (case when money >= {0} then 1 when money < {0} THEN 0 else '-1'end) from userinfotable where uid = '{1}';", -changedmoney, uid);
//new MySqlCommand(sql, SQLEngine.Instance.Connection);
MySqlCommand cmd0 = new MySqlCommand(sql, SQLEngine.Instance.Connection);
MySqlDataReader rs = cmd0.ExecuteReader();
while (rs.Read())
{
switch (rs.GetInt32(0))
{
case 0: Console.WriteLine("余额不足"); return(null); break;
case -1: Console.WriteLine("错误"); return(null); break;
case 1: Console.WriteLine("余额不足"); break;
}
;
}
rs.Close();
sql = String.Format("update userinfotable set money = money+{0} where uid = '{1}'; ", changedmoney, uid);
MySqlCommand cmd1 = new MySqlCommand(sql, SQLEngine.Instance.Connection);
cmd1.ExecuteNonQuery();
sql = String.Format("INSERT INTO {0}" +
"(uid, changedmoney, changetime)" +
" VALUES({1}, {2}, NOW());", table_name,
uid, changedmoney);
MySqlCommand cmd = new MySqlCommand(sql, SQLEngine.Instance.Connection);
if (cmd.ExecuteNonQuery() != 1 && cmd1.ExecuteNonQuery() != 1)
{
Console.WriteLine("Error UPDATE model {0}", moneyrecord);
return(null);
}
else
{
sql = String.Format("SELECT MAX(uid) FROM {0};", table_name);
cmd = new MySqlCommand(sql, SQLEngine.Instance.Connection);
moneyrecord.recordid = (Int32)cmd.ExecuteScalar();
return(moneyrecord);
}
}
