private ExecuteBatch ( DataFeeder feed, int maxBatchSize = Int32.MaxValue ) : int | ||
feed | DataFeeder | |
maxBatchSize | int | |
return | int |
public void TestBulkLoadOnStoredProcedureCommand() { using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString)) { connection.Open(); new NuoDbCommand("drop table temp if exists", connection).ExecuteNonQuery(); new NuoDbCommand("create table temp (col string)", connection).ExecuteNonQuery(); new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery(); new NuoDbCommand("create procedure nunit_test(input_data string) " + " as " + " insert into temp values (input_data); " + " end_procedure", connection).ExecuteNonQuery(); DataTable metadata = new DataTable("dummy"); metadata.Columns.Add("xyz", typeof(string)); DataRow[] rows = new DataRow[10]; for (int i = 0; i < rows.Length; i++) { rows[i] = metadata.NewRow(); rows[i][0] = Convert.ToString(i); } NuoDbCommand loader = new NuoDbCommand(connection); loader.CommandType = CommandType.StoredProcedure; loader.CommandText = "nunit_test"; loader.ExecuteBatch(rows); DbCommand command = new NuoDbCommand("select count(*) from temp", connection); object val = command.ExecuteScalar(); Assert.AreEqual(10, val); command = new NuoDbCommand("select col from temp", connection); val = command.ExecuteScalar(); Assert.AreEqual("0", val); } }
private void WriteToServer(DataFeeder feeder) { if (this.tableName.Length == 0) { throw new ArgumentException("The name of the destination table hasn't been specified", "DestinationTableName"); } StringBuilder builder = new StringBuilder(); builder.Append("INSERT INTO "); if (this.tableName.Contains(".")) { string[] parts = this.tableName.Split(new char[] { '.' }); bool first = true; foreach (string part in parts) { if (first) { first = false; } else { builder.Append("."); } builder.Append("`"); builder.Append(part.Replace("`", "``")); builder.Append("`"); } } else { builder.Append("`"); builder.Append(this.tableName.Replace("`", "``")); builder.Append("`"); } builder.Append(" "); if (mappings.Count == 0) { // the target table has the same number and names of the columns as in the specified input rows builder.Append("VALUES ("); for (int i = 0; i < feeder.FieldCount; i++) { if (i != 0) { builder.Append(", "); } builder.Append("?"); } builder.Append(")"); } else { DataRowCollection targetColumns = null; builder.Append(" ("); for (int i = 0; i < mappings.Count; i++) { NuoDbBulkLoaderColumnMapping mapping = mappings[i]; if (i != 0) { builder.Append(", "); } builder.Append("`"); if (mapping.DestinationColumn == null) { // we are requested to map to a target column that is identified with its ordinal number, so // fetch the schema of the target table to find out what is its name if (targetColumns == null) { // split the destination table into its different parts string[] parts = this.tableName.Split(new char[] { '.' }); DataTable targetSchema = this.connection.GetSchema("Columns", new string[] { null, // catalog parts.Length == 2 ? parts[0] : null, // schema parts.Length == 2 ? parts[1] : parts[0] // table }); targetColumns = targetSchema.Rows; } if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal > targetColumns.Count) { throw new IndexOutOfRangeException(String.Format("The specified ordinal of the target column ({0}) is outside the range of the column count ({1}) of table {2}", new object[] { mapping.DestinationOrdinal, targetColumns.Count, this.tableName })); } string columnName = (string)(targetColumns[mapping.DestinationOrdinal]["COLUMN_NAME"]); builder.Append(columnName.Replace("`", "``")); } else { builder.Append(mapping.DestinationColumn.Replace("`", "``")); } builder.Append("`"); } builder.Append(") VALUES ("); for (int i = 0; i < mappings.Count; i++) { if (i != 0) { builder.Append(", "); } builder.Append("?"); } builder.Append(")"); } string sqlString = builder.ToString(); #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: " + sqlString); #endif if (this.connection.State != ConnectionState.Open) { this.connection.Open(); } using (NuoDbCommand command = new NuoDbCommand(sqlString, this.connection)) { if (mappings.Count > 0) { // do the check for out-of-range values just once foreach (NuoDbBulkLoaderColumnMapping mapping in mappings) { if (mapping.SourceColumn == null && mapping.SourceOrdinal < 0 || mapping.SourceOrdinal > feeder.FieldCount) { throw new IndexOutOfRangeException(String.Format("The specified ordinal of the source column ({0}) is outside the range of the column count ({1})", mapping.SourceOrdinal, feeder.FieldCount)); } } feeder = new FeederOrderer(feeder, mappings); } int batchCount = 0; int totalSize = 0; while ((batchCount = command.ExecuteBatch(feeder, this.batchSize)) > 0) { totalSize += batchCount; #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: sent a batch of " + batchCount + " rows"); #endif if (handlers.Count != 0) { BatchProcessedEventHandler[] tmpArray = new BatchProcessedEventHandler[handlers.Count]; handlers.CopyTo(tmpArray); BatchProcessedEventArgs args = new BatchProcessedEventArgs(); args.BatchSize = batchCount; args.TotalSize = totalSize; args.HasErrors = false; foreach (BatchProcessedEventHandler h in tmpArray) { h.Invoke(this, args); } } } } }
private void WriteToServer(DataFeeder feeder) { if (this.tableName.Length == 0) throw new ArgumentException("The name of the destination table hasn't been specified", "DestinationTableName"); StringBuilder builder = new StringBuilder(); builder.Append("INSERT INTO "); if (this.tableName.Contains(".")) { string[] parts = this.tableName.Split(new char[] { '.' }); bool first = true; foreach (string part in parts) { if (first) first = false; else builder.Append("."); builder.Append("`"); builder.Append(part.Replace("`", "``")); builder.Append("`"); } } else { builder.Append("`"); builder.Append(this.tableName.Replace("`", "``")); builder.Append("`"); } builder.Append(" "); if (mappings.Count == 0) { // the target table has the same number and names of the columns as in the specified input rows builder.Append("VALUES ("); for (int i = 0; i < feeder.FieldCount; i++) { if (i != 0) builder.Append(", "); builder.Append("?"); } builder.Append(")"); } else { DataRowCollection targetColumns = null; builder.Append(" ("); for (int i = 0; i < mappings.Count; i++) { NuoDbBulkLoaderColumnMapping mapping = mappings[i]; if (i != 0) builder.Append(", "); builder.Append("`"); if (mapping.DestinationColumn == null) { // we are requested to map to a target column that is identified with its ordinal number, so // fetch the schema of the target table to find out what is its name if (targetColumns == null) { // split the destination table into its different parts string[] parts = this.tableName.Split(new char[] { '.' }); DataTable targetSchema = this.connection.GetSchema("Columns", new string[] { null, // catalog parts.Length == 2 ? parts[0] : null, // schema parts.Length == 2 ? parts[1] : parts[0] // table }); targetColumns = targetSchema.Rows; } if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal > targetColumns.Count) throw new IndexOutOfRangeException(String.Format("The specified ordinal of the target column ({0}) is outside the range of the column count ({1}) of table {2}", new object[] { mapping.DestinationOrdinal, targetColumns.Count, this.tableName })); string columnName = (string)(targetColumns[mapping.DestinationOrdinal]["COLUMN_NAME"]); builder.Append(columnName.Replace("`", "``")); } else builder.Append(mapping.DestinationColumn.Replace("`", "``")); builder.Append("`"); } builder.Append(") VALUES ("); for (int i = 0; i < mappings.Count; i++) { if (i != 0) builder.Append(", "); builder.Append("?"); } builder.Append(")"); } string sqlString = builder.ToString(); #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: " + sqlString); #endif if (this.connection.State != ConnectionState.Open) this.connection.Open(); using (NuoDbCommand command = new NuoDbCommand(sqlString, this.connection)) { if (mappings.Count > 0) { // do the check for out-of-range values just once foreach (NuoDbBulkLoaderColumnMapping mapping in mappings) { if (mapping.SourceColumn == null && mapping.SourceOrdinal < 0 || mapping.SourceOrdinal > feeder.FieldCount) throw new IndexOutOfRangeException(String.Format("The specified ordinal of the source column ({0}) is outside the range of the column count ({1})", mapping.SourceOrdinal, feeder.FieldCount)); } feeder = new FeederOrderer(feeder, mappings); } int batchCount = 0; int totalSize = 0; while ((batchCount = command.ExecuteBatch(feeder, this.batchSize)) > 0) { totalSize += batchCount; #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: sent a batch of " + batchCount + " rows"); #endif if (handlers.Count != 0) { BatchProcessedEventHandler[] tmpArray = new BatchProcessedEventHandler[handlers.Count]; handlers.CopyTo(tmpArray); BatchProcessedEventArgs args = new BatchProcessedEventArgs(); args.BatchSize = batchCount; args.TotalSize = totalSize; args.HasErrors = false; foreach (BatchProcessedEventHandler h in tmpArray) { h.Invoke(this, args); } } } } }