/// <summary> /// Copies all rows in the supplied IDataReader to a destination table specified /// by the destinationTableName. /// </summary> /// <param name="reader"></param> /// <param name="destinationTableName"> Name of the destination table in the database.</param> /// <param name="databaseEngine"></param> public async Task WriteToServer(IDataReader reader, string destinationTableName, DatabaseEngine databaseEngine) { //TODO: needs to support mapping columns if (reader == null) { throw new ArgumentNullException(nameof(reader)); } if (reader == null) { throw new ArgumentNullException(nameof(destinationTableName)); } if (Connection.State == ConnectionState.Closed) { await Connection.OpenAsync(); } var sqlBuilder = new InsertCommandSqlBuilder(databaseEngine); var currentBatch = 0; var cmd = GetDbCommand(); while (reader.Read()) { sqlBuilder.AppendInsert(cmd, reader, destinationTableName); currentBatch++; if (currentBatch != BulkCopyOptions.BatchSize) { continue; //otherwise send to db } using (cmd) await cmd.ExecuteNonQueryAsync(); cmd = GetDbCommand(); currentBatch = 0; } // if any records remain after the read loop has completed then write them to the DB // we also need to close the command using (cmd) { if (currentBatch > 0) { await cmd.ExecuteNonQueryAsync(); } } if (CloseAndDisploseDataReader) { reader.Close(); reader.Dispose(); } }
/// <summary> /// Generates a list of concatenated parameterized MySQL INSERT statements from the given list of objects and adds it to /// the <see cref="DbCommand" />. /// </summary> public static DbCommand AppendInserts <T>(this DbCommand dbCommand, IEnumerable <T> objects, string destinationTableName, DatabaseEngine databaseEngine) { var sqlBuilder = new InsertCommandSqlBuilder(databaseEngine); foreach (var obj in objects) { sqlBuilder.AppendInsert(dbCommand, obj, destinationTableName); } return(dbCommand); }
public void Should_Not_Produce_A_Trailing_Comma_After_The_Last_Parameter_In_The_Values_List() { // Arrange var customer = new ClassWithTheLastFieldNullable { Ant = 1, Baboon = "broom" }; DbCommand dbCommand = new SqlCommand(); // Act dbCommand = new InsertCommandSqlBuilder(DatabaseEngine.SqlServer).AppendInsert(dbCommand, customer, "INSERT INTO {0} ({1}) VALUES({2});"); // Assert Assert.IsNotNull(dbCommand.CommandText); Assert.IsFalse(dbCommand.CommandText.Contains(",);")); }