/// <summary>
/// Executes this operation
/// </summary>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
Guard.Against <ArgumentException>(rows == null, "SqlBulkInsertOperation cannot accept a null enumerator");
PrepareSchema();
PrepareMapping();
CreateInputSchema();
using (SqlConnection connection = (SqlConnection)Use.Connection(ConnectionStringSettings))
using (SqlTransaction transaction = BeginTransaction(connection))
{
sqlBulkCopy = CreateSqlBulkCopy(connection, transaction);
DictionaryEnumeratorDataReader adapter = new DictionaryEnumeratorDataReader(_inputSchema, rows);
sqlBulkCopy.WriteToServer(adapter);
if (PipelineExecuter.HasErrors)
{
Warn("Rolling back transaction in {0}", Name);
if (transaction != null)
{
transaction.Rollback();
}
Warn("Rolled back transaction in {0}", Name);
}
else
{
Debug("Committing {0}", Name);
if (transaction != null)
{
transaction.Commit();
}
Debug("Committed {0}", Name);
}
}
yield break;
}
/// <summary>
/// Executes this operation
/// </summary>
/// <param name="rows">The rows.</param>
/// <returns></returns>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
using (IDbConnection connection = Use.Connection(ConnectionStringSettings))
using (IDbTransaction transaction = BeginTransaction(connection))
{
foreach (Row row in new SingleRowEventRaisingEnumerator(this, rows))
{
using (IDbCommand cmd = connection.CreateCommand())
{
currentCommand = cmd;
currentCommand.Transaction = transaction;
PrepareCommand(currentCommand, row);
currentCommand.ExecuteNonQuery();
}
}
if (PipelineExecuter.HasErrors)
{
Warn("Rolling back transaction in {0}", Name);
transaction.Rollback();
Warn("Rolled back transaction in {0}", Name);
}
else
{
Debug("Committing {0}", Name);
if (transaction != null)
{
transaction.Commit();
}
Debug("Committed {0}", Name);
}
}
yield break;
}
private static void CreateTestDatabase(string databaseName)
{
using (var connection = Use.Connection(TestSetupConnectionString))
{
var cmd = connection.CreateCommand();
cmd.CommandText = $"IF DB_ID('{databaseName}') IS NULL CREATE DATABASE {databaseName};";
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Executes this operation
/// </summary>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
Guard.Against <ArgumentException>(rows == null, "SqlBulkInsertOperation cannot accept a null enumerator");
PrepareSchema();
PrepareMapping();
CreateInputSchema();
using (SqlConnection connection = (SqlConnection)Use.Connection(ConnectionStringSettings))
using (SqlTransaction transaction = (SqlTransaction)BeginTransaction(connection))
{
if (!string.IsNullOrEmpty(PreCommand))
{
Debug("Executing PreCommand '{0}'", PreCommand);
using (IDbCommand preCmd = connection.CreateCommand())
{
preCmd.CommandTimeout = Timeout;
preCmd.CommandText = PreCommand;
preCmd.Transaction = transaction;
preCmd.ExecuteNonQuery();
}
}
sqlBulkCopy = CreateSqlBulkCopy(connection, transaction);
DictionaryEnumeratorDataReader adapter = new DictionaryEnumeratorDataReader(_inputSchema, rows);
sqlBulkCopy.WriteToServer(adapter);
if (!string.IsNullOrEmpty(PostCommand))
{
using (IDbCommand postCmd = connection.CreateCommand())
{
postCmd.CommandTimeout = Timeout;
postCmd.CommandText = PostCommand;
postCmd.Transaction = transaction;
postCmd.ExecuteNonQuery();
}
}
if (PipelineExecuter.HasErrors)
{
Warn("Rolling back transaction in {0}", Name);
if (transaction != null)
{
transaction.Rollback();
}
Warn("Rolled back transaction in {0}", Name);
}
else
{
Debug("Committing {0}", Name);
if (transaction != null)
{
transaction.Commit();
}
Debug("Committed {0}", Name);
}
}
yield break;
}
public void SupportsProviderNameInConnectionStringSettings()
{
string connectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
ConnectionStringSettings connectionStringSettings = new ConnectionStringSettings("test2", connectionString, "System.Data.SqlClient");
using (IDbConnection connection = Use.Connection(connectionStringSettings))
{
Assert.NotNull(connection);
}
}
public void SupportsAssemblyQualifiedConnectionTypeNameAsProviderNameInConnectionStringSettings()
{
string connectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
ConnectionStringSettings connectionStringSettings = new ConnectionStringSettings("test2", connectionString, typeof(SqlConnection).AssemblyQualifiedName);
using (IDbConnection connection = Use.Connection(connectionStringSettings))
{
Assert.NotNull(connection);
}
}
public void SupportsProviderNameInConnectionStringSettings()
{
string connectionString = Use.ConnectionString(TestDatabase.ConnectionStringName).ConnectionString;
ConnectionStringSettings connectionStringSettings = new ConnectionStringSettings("test2", connectionString, "System.Data.SqlClient");
using (IDbConnection connection = Use.Connection(connectionStringSettings))
{
Assert.NotNull(connection);
}
}
private static void DropTestDatabase(string databaseName)
{
using (var connection = Use.Connection(TestSetupConnectionString))
{
var cmd = connection.CreateCommand();
cmd.CommandText = $"ALTER DATABASE {databaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
cmd.ExecuteNonQuery();
cmd.CommandText = $"IF DB_ID('{databaseName}') IS NOT NULL DROP DATABASE {databaseName};";
cmd.ExecuteNonQuery();
}
}
private void CheckConnection()
{
if (Connection == null)
{
_selfCreatedConnection = true;
Connection = Use.Connection(ConnStringName);
if (UseTransaction)
{
Transaction = Connection.BeginTransaction();
}
}
}
/// <summary>
/// Executes this operation
/// </summary>
/// <param name="rows">The rows.</param>
/// <returns></returns>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
Guard.Against <ArgumentException>(rows == null, "SqlBatchOperation cannot accept a null enumerator");
using (SqlConnection connection = (SqlConnection)Use.Connection(ConnectionStringSettings))
using (SqlTransaction transaction = (SqlTransaction)BeginTransaction(connection))
{
SqlCommandSet commandSet = null;
CreateCommandSet(connection, transaction, ref commandSet, timeout);
foreach (Row row in rows)
{
SqlCommand command = new SqlCommand();
PrepareCommand(row, command);
if (command.Parameters.Count == 0 && (RuntimeInfo.Version.Contains("2.0") || RuntimeInfo.Version.Contains("1.1"))) //workaround around a framework bug
{
Guid guid = Guid.NewGuid();
command.Parameters.AddWithValue(guid.ToString(), guid);
}
commandSet.Append(command, row);
if (commandSet.CountOfCommands >= batchSize)
{
Debug("Executing batch of {0} commands", commandSet.CountOfCommands);
Execute(commandSet);
CreateCommandSet(connection, transaction, ref commandSet, timeout);
}
}
Debug("Executing final batch of {0} commands", commandSet.CountOfCommands);
Execute(commandSet);
if (PipelineExecuter.HasErrors)
{
Warn(null, "Rolling back transaction in {0}", Name);
if (transaction != null)
{
transaction.Rollback();
}
Warn(null, "Rolled back transaction in {0}", Name);
}
else
{
Debug("Committing {0}", Name);
if (transaction != null)
{
transaction.Commit();
}
Debug("Committed {0}", Name);
}
}
yield break;
}
public void CanCopyTableWithTransformAndConnection()
{
using (var conn = Use.Connection("test"))
{
// Please note that you cannot use the connection in the query operation because
// the data reader will still be open when you will attemp to input data
// Adding a buffered input query operation should solve this but will load
// all data in memory before executing the pipeline
var result =
Input.Query("test", UsersToPeopleActions.SelectAllUsers)
.Transform(UsersToPeopleActions.SplitUserName)
.DbCommand(conn, UsersToPeopleActions.WritePeople)
.Execute();
UsersToPeopleActions.VerifyResult(result);
}
}
/// <summary>
/// Executes this operation
/// </summary>
/// <param name="rows">The rows.</param>
/// <returns></returns>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
Guard.Against <ArgumentException>(rows == null, "SqlBatchOperation cannot accept a null enumerator");
using (var cn = (SqlConnection)Use.Connection(Connection))
using (var transaction = BeginTransaction(cn)) {
SqlCommandSet commandSet = null;
CreateCommandSet(cn, transaction, ref commandSet, _timeout);
foreach (var row in rows)
{
var command = new SqlCommand();
PrepareCommand(row, command);
//if (command.Parameters.Count == 0) //workaround around a framework bug
//{
// var guid = Guid.NewGuid();
// command.Parameters.AddWithValue(guid.ToString(), guid);
//}
commandSet.Append(command);
if (commandSet.CountOfCommands >= _batchSize)
{
Debug("Executing batch of {0} commands", commandSet.CountOfCommands);
commandSet.ExecuteNonQuery();
CreateCommandSet(cn, transaction, ref commandSet, _timeout);
}
}
Debug("Executing final batch of {0} commands", commandSet.CountOfCommands);
commandSet.ExecuteNonQuery();
if (transaction != null)
{
if (PipelineExecuter.HasErrors)
{
Warn(null, "Rolling back transaction in {0}", Name);
transaction.Rollback();
Warn(null, "Rolled back transaction in {0}", Name);
}
else
{
Debug("Committing {0}", Name);
transaction.Commit();
Debug("Committed {0}", Name);
}
}
}
yield break;
}
/// <summary>
/// Executes this operation
/// </summary>
/// <param name="rows">The rows.</param>
/// <returns></returns>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
using (var cn = Use.Connection(Connection))
using (var transaction = BeginTransaction(cn)) {
using (currentCommand = cn.CreateCommand()) {
currentCommand.Transaction = transaction;
PrepareCommand(currentCommand);
using (var reader = currentCommand.ExecuteReader(CommandBehavior)) {
while (reader.Read())
{
yield return(CreateRowFromReader(reader));
}
}
}
if (transaction != null)
{
transaction.Commit();
}
}
}
/// <summary>
/// Executes this operation
/// </summary>
/// <param name="rows">The rows.</param>
/// <returns></returns>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
using (IDbConnection connection = Use.Connection(ConnectionStringSettings))
using (IDbTransaction transaction = BeginTransaction(connection))
{
using (currentCommand = connection.CreateCommand())
{
currentCommand.Transaction = transaction;
PrepareCommand(currentCommand);
using (IDataReader reader = currentCommand.ExecuteReader())
{
while (reader.Read())
{
yield return(CreateRowFromReader(reader));
}
}
}
if (transaction != null)
{
transaction.Commit();
}
}
}
/// <summary>
/// Executes this operation
/// </summary>
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
Guard.Against <ArgumentException>(rows == null, "SqlBulkInsertOperation cannot accept a null enumerator");
PrepareSchema();
PrepareMapping();
CreateInputSchema();
using (var cn = (SqlConnection)Use.Connection(Connection)) {
_sqlBulkCopy = CreateSqlBulkCopy(cn, null);
var adapter = new DictionaryEnumeratorDataReader(_inputSchema, rows);
try {
_sqlBulkCopy.WriteToServer(adapter);
} catch (SqlException ex) {
if (!ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
{
throw;
}
const string pattern = @"\d+";
var match = Regex.Match(ex.Message, pattern);
var index = Convert.ToInt32(match.Value) - 1;
var fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
if (fi == null)
{
throw;
}
var sortedColumns = fi.GetValue(_sqlBulkCopy);
if (sortedColumns == null)
{
throw;
}
var field = sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance);
if (field == null)
{
throw;
}
var items = (object[])field.GetValue(sortedColumns);
var itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
if (itemdata == null)
{
throw;
}
var metadata = itemdata.GetValue(items[index]);
if (metadata == null)
{
throw;
}
var f = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
if (f == null)
{
throw;
}
var column = f.GetValue(metadata);
var l = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
if (l == null)
{
throw;
}
var length = l.GetValue(metadata);
throw new TransformalizeException(Logger, string.Empty, "Column: {0} contains data with a length greater than: {1}", column, length);
}
}
yield break;
}