public void SaveExpToDataBase()
{
DateTime current = DateTime.UtcNow.AddHours(6);
DateTime lastday = new DateTime(current.Year, current.Month, 1, 15, 0, 0).AddMonths(1).AddDays(-1);
if (lastday == _date)
{
string _sql = @"INSERT INTO Exp VALUES (@DateID, @PlayerID, @Exp)";
using (var DataBaseConnection = KanColleSenkaManager.NewSQLiteConnection()) {
DataBaseConnection.Open();
using (var transaction = DataBaseConnection.BeginTransaction()) {
using (var cmd = new SQLiteCommand(_sql, DataBaseConnection)) {
SQLiteParameter[] paras = new SQLiteParameter[3] {
new SQLiteParameter("@DateID", DbType.Int64),
new SQLiteParameter("@PlayerID", DbType.Int64),
new SQLiteParameter("@Exp", DbType.Int32)
};
cmd.Parameters.AddRange(paras);
foreach (var data in _dataSet)
{
cmd.Parameters["@DateID"].Value = _dateID;
cmd.Parameters["@PlayerID"].Value = data.PlayerID;
cmd.Parameters["@Exp"].Value = data.Exp;
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
}
}
public void CheckDataBase()
{
int dataCount = 0;
string _sqlSelect = @"SELECT COUNT(*) FROM Senka WHERE DateID = @DateID AND ServerID = @ServerID";
string _sqlDelete = @"DELETE FROM Senka WHERE DateID = @DateID AND ServerID = @ServerID";
using (var DataBaseConnection = KanColleSenkaManager.NewSQLiteConnection()) {
DataBaseConnection.Open();
using (var transaction = DataBaseConnection.BeginTransaction()) {
using (var cmd = DataBaseConnection.CreateCommand()) {
SQLiteParameter dateID = new SQLiteParameter("@DateID", DbType.Int64);
SQLiteParameter serverID = new SQLiteParameter("@ServerID", DbType.Int32);
dateID.Value = _dateID;
serverID.Value = _id;
cmd.Parameters.Add(dateID);
cmd.Parameters.Add(serverID);
cmd.CommandText = _sqlSelect;
dataCount = Convert.ToInt32(cmd.ExecuteScalar());
if (dataCount > 0)
{
cmd.CommandText = _sqlDelete;
cmd.ExecuteNonQuery();
log.Info(string.Format("[ServerID {0}] {1} datas were deleted and have been re-requested.", _id, dataCount));
}
}
transaction.Commit();
}
}
}
public void SaveToDataBase()
{
int count = _dataSet.Count;
if (count != 990)
{
log.Warn(string.Format("[ServerID {0}] Data count is {1}", _id, count));
}
CheckDataBase();
string _sql = @"INSERT INTO Senka VALUES " +
@"(@DateID, @ServerID, @Ranking, @Level, @PlayerName, @PlayerID, @Comment, @RankPoint, @RankType, @Medals)";
using (var DataBaseConnection = KanColleSenkaManager.NewSQLiteConnection()) {
DataBaseConnection.Open();
using (var transaction = DataBaseConnection.BeginTransaction()) {
using (var cmd = new SQLiteCommand(_sql, DataBaseConnection)) {
SQLiteParameter[] paras = new SQLiteParameter[10] {
new SQLiteParameter("@DateID", DbType.Int64),
new SQLiteParameter("@ServerID", DbType.Int32),
new SQLiteParameter("@Ranking", DbType.Int32),
new SQLiteParameter("@Level", DbType.Int32),
new SQLiteParameter("@PlayerName", DbType.String),
new SQLiteParameter("@PlayerID", DbType.Int64),
new SQLiteParameter("@Comment", DbType.String),
new SQLiteParameter("@RankPoint", DbType.Int32),
new SQLiteParameter("@RankType", DbType.Int32),
new SQLiteParameter("@Medals", DbType.Int32)
};
cmd.Parameters.AddRange(paras);
foreach (var data in _dataSet)
{
cmd.Parameters["@DateID"].Value = _dateID;
cmd.Parameters["@ServerID"].Value = _id;
cmd.Parameters["@Ranking"].Value = data.Ranking;
cmd.Parameters["@Level"].Value = data.Level;
cmd.Parameters["@PlayerName"].Value = data.PlayerName;
cmd.Parameters["@PlayerID"].Value = data.PlayerID;
cmd.Parameters["@Comment"].Value = data.Comment;
cmd.Parameters["@RankPoint"].Value = data.RankPoint;
cmd.Parameters["@RankType"].Value = data.RankType;
cmd.Parameters["@Medals"].Value = data.Medals;
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
}
public void UpdateTimeToDataBase()
{
string _sql = @"UPDATE Servers SET LastUpdate = @DateID WHERE ID = @ServerID";
using (var DataBaseConnection = KanColleSenkaManager.NewSQLiteConnection())
using (var cmd = new SQLiteCommand(_sql, DataBaseConnection)) {
DataBaseConnection.Open();
SQLiteParameter[] paras = new SQLiteParameter[2] {
new SQLiteParameter("@DateID", DbType.Int64),
new SQLiteParameter("@ServerID", DbType.Int32)
};
cmd.Parameters.AddRange(paras);
cmd.Parameters["@DateID"].Value = _dateID;
cmd.Parameters["@ServerID"].Value = _id;
cmd.ExecuteNonQuery();
}
}