public void Save(AppTrack track) {
track.Id = Guid.NewGuid();
string sql =
@"insert into `AppTrack` (
`Id`, `User`, `App`, `Status`, `CreateTime`, `CreatePrice`,
`BuyTime`, `BuyPrice`, `Rate`, `HasRead`
) values (
?Id, ?User, ?App, ?Status, ?CreateTime, ?CreatePrice,
?BuyTime, ?BuyPrice, ?Rate, ?HasRead
);";
MySqlCommand command = connection.CreateCommand();
command.CommandText = sql;
AddParametersForAppTrack(track, command);
command.ExecuteNonQuery();
}
public void Update(AppTrack track) {
string sql =
@"update `AppTrack`
set
`Id` = ?Id,
`User` = ?User,
`App` = ?App,
`Status` = ?Status,
`CreateTime` = ?CreateTime,
`CreatePrice` = ?CreatePrice,
`BuyTime` = ?BuyTime,
`BuyPrice` = ?BuyPrice,
`Rate` = ?Rate,
`HasRead` = ?HasRead
where `Id` = ?Id;";
MySqlCommand command = connection.CreateCommand();
command.CommandText = sql;
AddParametersForAppTrack(track, command);
command.ExecuteNonQuery();
}
public static AppTrack ToAppTrack(this IDataRecord record) {
AppTrack track = new AppTrack() {
BuyPrice = record.GetNullableFloat("BuyPrice"),
BuyTime = record.GetNullableDateTime("BuyTime"),
CreatePrice = record.GetFloat("CreatePrice"),
CreateTime = record.GetDateTime("CreateTime"),
HasRead = record.GetBoolean("HasRead"),
Id = record.GetGuid("Id"),
Rate = record.GetInt32("Rate"),
Status = (AppTrackStatus)record.GetInt32("Status"),
App = record.ToAppBrief("App."),
User = record.GetGuid("User")
};
return track;
}
private static int MigrateAppTracks(int offset, int batchSize) {
var command = souce.CreateCommand();
command.CommandType = CommandType.Text;
// 因为User的ID都改成Guid类型了，这里用Int32类型的User是对不上的
// 因此要表连接把Username拿出来，利用Username的唯一性去取
command.CommandText = "select Username, App, t.Status, CreateTime, CreatePrice, BuyTime, BuyPrice, Rate, HasRead from AppTrack t inner join User u on u.Id = t.User limit ?offset, ?batchSize;";
command.Parameters.AddWithValue("?offset", offset);
command.Parameters.AddWithValue("?batchSize", batchSize);
List<AppTrack> tracks = new List<AppTrack>();
Stopwatch watch = new Stopwatch();
watch.Start();
using (var reader = command.ExecuteReader()) {
while (reader.Read()) {
string username = reader.GetString(0);
User user = repository.User.RetrieveByUsername(username);
AppTrack track = new AppTrack() {
User = user.Id,
// App有自己的Serializer，只需要Id就行
App = new AppBrief() { Id = reader.GetInt32(1) },
Status = (AppTrackStatus)reader.GetInt32(2),
CreateTime = reader.GetDateTime(3),
CreatePrice = reader.GetFloat(4),
BuyTime = reader.IsDBNull(5) ? (DateTime?)null : reader.GetDateTime(5),
BuyPrice = reader.IsDBNull(6) ? (float?)null : reader.GetFloat(6),
Rate = reader.GetInt32(7),
HasRead = reader.GetBoolean(8)
};
tracks.Add(track);
}
}
watch.Stop();
Console.WriteLine("Retrieved {0} tracks using {1}", tracks.Count, watch.Elapsed);
watch.Reset();
watch.Start();
using (MySqlTransaction transaction = destination.BeginTransaction()) {
foreach (AppTrack track in tracks) {
repository.AppTrack.Save(track);
}
transaction.Commit();
}
watch.Stop();
Console.WriteLine("Saved to mongo using {0}", watch.Elapsed);
return tracks.Count;
}
public void Update(AppTrack track) {
appTracks.Save(track);
}
public void Save(AppTrack track) {
appTracks.Save(track);
}
public TrackingApp(AppBrief app, AppTrack track) {
App = app;
Track = track;
}
private static void AddParametersForAppTrack(AppTrack track, MySqlCommand command) {
command.Parameters.AddWithValue("?Id", track.Id.ToString("N"));
command.Parameters.AddWithValue("?User", track.User.ToString("N"));
command.Parameters.AddWithValue("?App", track.App.Id);
command.Parameters.AddWithValue("?Status", track.Status);
command.Parameters.AddWithValue("?CreateTime", track.CreateTime);
command.Parameters.AddWithValue("?CreatePrice", track.CreatePrice);
command.Parameters.AddWithValue("?BuyTime", track.BuyTime);
command.Parameters.AddWithValue("?BuyPrice", track.BuyPrice);
command.Parameters.AddWithValue("?Rate", track.Rate);
command.Parameters.AddWithValue("?HasRead", track.HasRead);
}