public void ComplexTypeReader()
{
var user = new TestUser
{
Contact = new Contact {
Address = new Address {
City = "Tallinn", Country = "Estonia"
}, PhoneNumber = "1234567"
},
FirstName = "Max",
LastName = "Lego",
Id = Guid.NewGuid()
};
var emptyUser = new TestUser();
using (var ctx = new TestBaseContext())
{
using (var reader = new MappedDataReader <TestUser>(new[] { user, emptyUser }, GetDummyProvider(ctx)))
{
Assert.AreEqual(11, reader.FieldCount);
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; ++i)
{
Console.WriteLine("{0}: {1}", i, reader.GetValue(i));
}
}
}
}
}
public void Performance()
{
var sw = new Stopwatch();
var swv = new Stopwatch();
using (var ctx = new TestBaseContext())
{
ctx.Database.Initialize(false);
sw.Restart();
using (var reader = new MappedDataReader <Page>(CreatePages(1000000), GetDummyProvider(ctx)))
{
Console.WriteLine("Construct {0}ms", sw.Elapsed.TotalMilliseconds);
while (reader.Read())
{
foreach (var col in reader.Cols)
{
swv.Start();
var value = reader.GetValue(col.Key);
swv.Stop();
}
}
}
sw.Stop();
Console.WriteLine("Elapsed {0}ms. Getting values took {1}ms", sw.Elapsed.TotalMilliseconds, swv.Elapsed.TotalMilliseconds);
}
}
public void ComplexTypeReader()
{
var user = new TestUser
{
Contact = new Contact { Address = new Address { City = "Tallinn", Country = "Estonia"}, PhoneNumber = "1234567"},
FirstName = "Max",
LastName = "Lego",
Id = Guid.NewGuid()
};
var emptyUser = new TestUser();
using (var ctx = new TestContext())
{
var tableMapping = ctx.Db<TestUser>();
var tableMappings = new Dictionary<Type, IEntityMap>
{
{typeof (TestUser), tableMapping}
};
using (var reader = new MappedDataReader<TestUser>(new[] { user, emptyUser }, tableMappings))
{
Assert.AreEqual(9, reader.FieldCount);
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; ++i)
{
Console.WriteLine("{0}: {1}", i, reader.GetValue(i));
}
}
}
}
}
public void ComplexTypeReader()
{
var user = new TestUser
{
Contact = new Contact {
Address = new Address {
City = "Tallinn", Country = "Estonia"
}, PhoneNumber = "1234567"
},
FirstName = "Max",
LastName = "Lego",
Id = Guid.NewGuid()
};
var emptyUser = new TestUser();
using (var ctx = new TestContext())
{
var tableMapping = ctx.Db <TestUser>();
var tableMappings = new Dictionary <Type, IEntityMap>
{
{ typeof(TestUser), tableMapping }
};
using (var reader = new MappedDataReader <TestUser>(new[] { user, emptyUser }, tableMappings))
{
Assert.AreEqual(9, reader.FieldCount);
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; ++i)
{
Console.WriteLine("{0}: {1}", i, reader.GetValue(i));
}
}
}
}
}
private void Run <T>(IEnumerable <T> entities, MySqlConnection connection, MySqlTransaction transaction)
{
bool keepIdentity = (BulkCopyOptions.KeepIdentity & Options.BulkCopyOptions) > 0;
bool keepNulls = (BulkCopyOptions.KeepNulls & Options.BulkCopyOptions) > 0;
using (var reader = new MappedDataReader <T>(entities, this))
{
var tableEngine = GetTableEngine(connection.Database, reader.TableName, connection);
var columns = reader.Cols
.Where(x => !x.Value.Computed && (!x.Value.IsIdentity || keepIdentity))
.ToArray();
// INSERT INTO [TableName] (column list)
var insert = new StringBuilder()
.Append($" INSERT INTO {reader.TableName} ")
.Append("(")
.Append(string.Join(",", columns.Select(col => col.Value.ColumnName)))
.Append(")")
.Append(" VALUES")
.ToString();
int i = 0;
long rowsCopied = 0;
var rows = new List <string>();
while (reader.Read())
{
var values = new List <string>();
foreach (var col in columns)
{
var value = reader.GetValue(col.Key);
var type = col.Value.Type;
AddParameter(type, value, values);
}
rows.Add("(" + string.Join(",", values) + ")");
i++;
if (i == Options.BatchSize || i == Options.NotifyAfter)
{
using (var cmd = CreateCommand(CreateInsertBatchText(insert, rows, tableEngine), connection, transaction))
cmd.ExecuteNonQueryAsync();
if (Options.Callback != null)
{
int batches = Options.BatchSize / Options.NotifyAfter;
rowsCopied += i;
Options.Callback(this, new RowsCopiedEventArgs(rowsCopied));
}
i = 0;
rows.Clear();
}
}
if (rows.Any())
{
using (var cmd = CreateCommand(CreateInsertBatchText(insert, rows, tableEngine), connection, transaction))
cmd.ExecuteNonQuery();
}
}
}
private void Run <T>(IEnumerable <T> entities, SqlCeConnection connection, SqlCeTransaction transaction)
{
bool runIdentityScripts;
bool keepIdentity = runIdentityScripts = (SqlBulkCopyOptions.KeepIdentity & Options.SqlBulkCopyOptions) > 0;
var keepNulls = (SqlBulkCopyOptions.KeepNulls & Options.SqlBulkCopyOptions) > 0;
using (var reader = new MappedDataReader <T>(entities, this))
{
var identityCols = reader.Cols.Values.Where(x => x.IsIdentity).ToArray();
if (identityCols.Length != 1 || !IsValidIdentityType(identityCols[0].Type))
{
runIdentityScripts = false;
}
if (keepIdentity && runIdentityScripts)
{
SetIdentityInsert(connection, transaction, reader.TableName, true);
}
var colInfos = ColInfos(connection, reader)
.Values
.Where(x => !x.IsIdentity || keepIdentity)
.ToArray();
using (var cmd = CreateCommand(reader.TableName, connection, transaction))
{
cmd.CommandType = CommandType.TableDirect;
using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
var rec = rs.CreateRecord();
int i = 0;
long rowsCopied = 0;
while (reader.Read())
{
foreach (var colInfo in colInfos)
{
var value = reader.GetValue(colInfo.ReaderKey);
if (value == null && keepNulls)
{
rec.SetValue(colInfo.OrdinalPosition, DBNull.Value);
}
else
{
rec.SetValue(colInfo.OrdinalPosition, value);
}
}
rs.Insert(rec);
++i;
if (i == Options.NotifyAfter && Options.Callback != null)
{
rowsCopied += i;
Options.Callback(this, new SqlRowsCopiedEventArgs(rowsCopied));
i = 0;
}
}
}
}
if (keepIdentity && runIdentityScripts)
{
SetIdentityInsert(connection, transaction, reader.TableName, false);
}
}
}
public override void Run <T>(IEnumerable <T> entities, MySqlTransaction transaction, BulkInsertOptions options)
{
var keepIdentity = (SqlBulkCopyOptions.KeepIdentity & options.SqlBulkCopyOptions) > 0;
using (var reader = new MappedDataReader <T>(entities, this))
{
var csvPath = AppDomain.CurrentDomain.BaseDirectory + System.Guid.NewGuid().ToString() + ".csv";
Dictionary <int, IPropertyMap> propertyMaps = reader.Cols
.Where(x => !x.Value.IsIdentity || keepIdentity)
.ToDictionary(x => x.Key, x => x.Value);
var csv = new StringBuilder();
while (reader.Read())
{
foreach (var kvp in propertyMaps)
{
var value = reader.GetValue(kvp.Key);
if (value == null)
{
csv
.Append("null")
.Append(FieldTerminator);
}
else
{
// todo - escape "'"
csv
.AppendFormat("{0}", value)
.Append(FieldTerminator);
}
}
csv.Append(LineTerminator);
}
// todo - save csv
File.WriteAllText(csvPath, csv.ToString());
// upload csv
var mySqlBulkLoader = new MySqlBulkLoader(transaction.Connection);
//mySqlBulkLoader.TableName = string.Format("[{0}].[{1}]", reader.SchemaName, reader.TableName);
mySqlBulkLoader.TableName = reader.TableName;
mySqlBulkLoader.FieldTerminator = FieldTerminator;
mySqlBulkLoader.LineTerminator = LineTerminator;
mySqlBulkLoader.FileName = csvPath;
foreach (var kvp in propertyMaps)
{
mySqlBulkLoader.Columns.Add(kvp.Value.ColumnName);
}
int count = mySqlBulkLoader.Load();
try
{
File.Delete(csvPath);
}
catch (Exception exception)
{
}
}
}
private void Run<T>(IEnumerable<T> entities, MySqlConnection connection, MySqlTransaction transaction)
{
if (!entities.Any())
return;
bool keepIdentity = (BulkCopyOptions.KeepIdentity & Options.BulkCopyOptions) > 0;
bool keepNulls = (BulkCopyOptions.KeepNulls & Options.BulkCopyOptions) > 0;
using (var reader = new MappedDataReader<T>(entities, this))
{
var tableEngine = GetTableEngine(connection.Database, reader.TableName, connection);
var keyColumns = reader.Cols
.Where(x => !x.Value.Computed && x.Value.IsPk)
.ToArray();
var columns = reader.Cols
.Where(x => !x.Value.Computed && !x.Value.IsPk && (!x.Value.IsIdentity || keepIdentity))
.ToArray();
// UPDATE [TableName] (column list)
var updateHeader = $"UPDATE `{reader.TableName}` SET ";
var updateBuilder = new StringBuilder();
int i = 0;
long rowsCopied = 0;
while (reader.Read())
{
updateBuilder.Append(updateHeader);
foreach (var col in columns)
{
var value = reader.GetValue(col.Key);
var type = col.Value.Type;
updateBuilder.AppendFormat("{0}={1},", col.Value.ColumnName, AddParameter(type, value));
}
updateBuilder.Remove(updateBuilder.Length - 1, 1);//remove last ,
updateBuilder.Append(" WHERE ");
foreach(var col in keyColumns)
{
var value = reader.GetValue(col.Key);
var type = col.Value.Type;
updateBuilder.AppendFormat("{0}={1} AND ", col.Value.ColumnName, AddParameter(type, value));
}
updateBuilder.Remove(updateBuilder.Length - 5, 5);//remove last AND
updateBuilder.Append(";");
i++;
if (i == Options.BatchSize || i == Options.NotifyAfter)
{
using (var cmd = CreateCommand(CreateUpdateBatchText(updateBuilder, tableEngine), connection, transaction))
cmd.ExecuteNonQueryAsync();
if (Options.Callback != null)
{
int batches = Options.BatchSize / Options.NotifyAfter;
rowsCopied += i;
Options.Callback(this, new RowsCopiedEventArgs(rowsCopied));
}
i = 0;
}
}
if (i>0)
{
using (var cmd = CreateCommand(CreateUpdateBatchText(updateBuilder, tableEngine), connection, transaction))
cmd.ExecuteNonQuery();
}
}
}