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