private void Init()
{
EnableSqlTrace = EnableSqlTraceDefault;
NPocoDatabaseExtensions.ConfigureNPocoBulkExtensions();
if (_mapperCollection != null)
{
Mappers.AddRange(_mapperCollection);
}
}
/// <summary>
/// Bulk-insert records using SqlCE TableDirect method.
/// </summary>
/// <typeparam name="T">The type of the records.</typeparam>
/// <param name="database">The database.</param>
/// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
/// <param name="records">The records.</param>
/// <returns>The number of records that were inserted.</returns>
private static int BulkInsertRecordsSqlCe <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
{
var columns = pocoData.Columns.ToArray();
// create command against the original database.Connection
using (var command = database.CreateCommand(database.Connection, CommandType.TableDirect, string.Empty))
{
command.CommandText = pocoData.TableInfo.TableName;
command.CommandType = CommandType.TableDirect; // TODO: why repeat?
// TODO: not supporting transactions?
//cmd.Transaction = GetTypedTransaction<SqlCeTransaction>(db.Connection.);
var count = 0;
var tCommand = NPocoDatabaseExtensions.GetTypedCommand <SqlCeCommand>(command); // execute on the real command
// seems to cause problems, I think this is primarily used for retrieval, not inserting.
// see: https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.indexname%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396
//tCommand.IndexName = pd.TableInfo.PrimaryKey;
using (var resultSet = tCommand.ExecuteResultSet(ResultSetOptions.Updatable))
{
var updatableRecord = resultSet.CreateRecord();
foreach (var record in records)
{
for (var i = 0; i < columns.Length; i++)
{
// skip the index if this shouldn't be included (i.e. PK)
if (NPocoDatabaseExtensions.IncludeColumn(pocoData, columns[i]))
{
var val = columns[i].Value.GetValue(record);
if (val is byte[])
{
var bytes = val as byte[];
updatableRecord.SetSqlBinary(i, new SqlBinary(bytes));
}
else
{
updatableRecord.SetValue(i, val);
}
updatableRecord.SetValue(i, val);
}
}
resultSet.Insert(updatableRecord);
count++;
}
}
return(count);
}
}
/// <summary>
/// Bulk-insert records using SqlServer BulkCopy method.
/// </summary>
/// <typeparam name="T">The type of the records.</typeparam>
/// <param name="database">The database.</param>
/// <param name="pocoData">The PocoData object corresponding to the record's type.</param>
/// <param name="records">The records.</param>
/// <returns>The number of records that were inserted.</returns>
private int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records)
{
// TODO: The main reason this exists is because the NPoco InsertBulk method doesn't return the number of items.
// It is worth investigating the performance of this vs NPoco's because we use a custom BulkDataReader
// which in theory should be more efficient than NPocos way of building up an in-memory DataTable.
// create command against the original database.Connection
using (DbCommand command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty))
{
// use typed connection and transaction or SqlBulkCopy
SqlConnection tConnection = NPocoDatabaseExtensions.GetTypedConnection <SqlConnection>(database.Connection);
SqlTransaction tTransaction =
NPocoDatabaseExtensions.GetTypedTransaction <SqlTransaction>(command.Transaction);
var tableName = pocoData.TableInfo.TableName;
if (database.SqlContext.SqlSyntax is not SqlServerSyntaxProvider syntax)
{
throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider.");
}
using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction)
{
// 0 = no bulk copy timeout. If a timeout occurs it will be an connection/command timeout.
BulkCopyTimeout = 0,
DestinationTableName = tableName,
// be consistent with NPoco: https://github.com/schotime/NPoco/blob/5117a55fde57547e928246c044fd40bd00b2d7d1/src/NPoco.SqlServer/SqlBulkCopyHelper.cs#L50
BatchSize = 4096,
})
using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax))
{
// we need to add column mappings here because otherwise columns will be matched by their order and if the order of them are different in the DB compared
// to the order in which they are declared in the model then this will not work, so instead we will add column mappings by name so that this explicitly uses
// the names instead of their ordering.
foreach (SqlBulkCopyColumnMapping col in bulkReader.ColumnMappings)
{
copy.ColumnMappings.Add(col.DestinationColumn, col.DestinationColumn);
}
copy.WriteToServer(bulkReader);
return(bulkReader.RecordsAffected);
}
}
}
public virtual string EscapeString(string val)
{
return(NPocoDatabaseExtensions.EscapeAtSymbols(val.Replace("'", "''")));
}
public override string EscapeString(string val)
{
return(NPocoDatabaseExtensions.EscapeAtSymbols(MySql.Data.MySqlClient.MySqlHelper.EscapeString(val)));
}
