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); } }