Skip to content

Commit 5416c14

Browse files
ThatRendleThatRendle
committed
Optimizing BulkInsert operation
1 parent 51ccca2 commit 5416c14

21 files changed

Lines changed: 368 additions & 99 deletions

‎Simple.Data.Ado/AdoAdapter.cs‎

Lines changed: 9 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -110,10 +110,11 @@ public object FindRelated(string tableName, IDictionary<string, object> row, str
110110

111111
public IEnumerable<IDictionary<string, object>> InsertMany(string tableName,
112112
IEnumerable<IDictionary<string, object>> data,
113-
IAdapterTransaction transaction)
113+
IAdapterTransaction transaction,
114+
Func<IDictionary<string,object>,Exception,bool> onError, bool resultRequired)
114115
{
115116
return new AdoAdapterInserter(this, ((AdoAdapterTransaction) transaction).Transaction).InsertMany(
116-
tableName, data);
117+
tableName, data, onError, resultRequired);
117118
}
118119

119120
public int UpdateMany(string tableName, IEnumerable<IDictionary<string, object>> data,
@@ -176,10 +177,10 @@ public IEnumerable<IDictionary<string, object>> Find(string tableName, SimpleExp
176177
}
177178

178179
public IDictionary<string, object> Insert(string tableName, IDictionary<string, object> data,
179-
IAdapterTransaction transaction)
180+
IAdapterTransaction transaction, bool resultRequired)
180181
{
181182
return new AdoAdapterInserter(this, ((AdoAdapterTransaction) transaction).Transaction).Insert(tableName,
182-
data);
183+
data, resultRequired);
183184
}
184185

185186
public int Update(string tableName, IDictionary<string, object> data, SimpleExpression criteria,
@@ -452,16 +453,16 @@ public override IObservable<IDictionary<string, object>> RunQueryAsObservable(Si
452453
.ToObservable(connection, this);
453454
}
454455

455-
public override IDictionary<string, object> Insert(string tableName, IDictionary<string, object> data)
456+
public override IDictionary<string, object> Insert(string tableName, IDictionary<string, object> data, bool resultRequired)
456457
{
457-
return new AdoAdapterInserter(this).Insert(tableName, data);
458+
return new AdoAdapterInserter(this).Insert(tableName, data, resultRequired);
458459
}
459460

460461
public override IEnumerable<IDictionary<string, object>> InsertMany(string tableName,
461462
IEnumerable<IDictionary<string, object>>
462-
data)
463+
data, Func<IDictionary<string,object>, Exception, bool> onError, bool resultRequired)
463464
{
464-
return new AdoAdapterInserter(this).InsertMany(tableName, data);
465+
return new AdoAdapterInserter(this).InsertMany(tableName, data, onError, resultRequired);
465466
}
466467

467468
public override int Update(string tableName, IDictionary<string, object> data)

‎Simple.Data.Ado/AdoAdapterInserter.cs‎

Lines changed: 21 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@ public AdoAdapterInserter(AdoAdapter adapter, IDbTransaction transaction)
2525
_transaction = transaction;
2626
}
2727

28-
public IEnumerable<IDictionary<string, object>> InsertMany(string tableName, IEnumerable<IDictionary<string,object>> data)
28+
public IEnumerable<IDictionary<string, object>> InsertMany(string tableName, IEnumerable<IDictionary<string, object>> data, Func<IDictionary<string,object>, Exception, bool> onError, bool resultRequired)
2929
{
3030
if (data == null) throw new ArgumentNullException("data");
3131
var list = data.ToList();
@@ -36,10 +36,10 @@ public IEnumerable<IDictionary<string, object>> InsertMany(string tableName, IEn
3636
}
3737

3838
var bulkInserter = _adapter.ProviderHelper.GetCustomProvider<IBulkInserter>(_adapter.ConnectionProvider) ?? new BulkInserter();
39-
return bulkInserter.Insert(_adapter, tableName, list, _transaction);
39+
return bulkInserter.Insert(_adapter, tableName, list, _transaction, onError, resultRequired);
4040
}
4141

42-
public IDictionary<string, object> Insert(string tableName, IEnumerable<KeyValuePair<string, object>> data)
42+
public IDictionary<string, object> Insert(string tableName, IEnumerable<KeyValuePair<string, object>> data, bool resultRequired)
4343
{
4444
var table = _adapter.GetSchema().FindTable(tableName);
4545

@@ -59,23 +59,27 @@ public IDictionary<string, object> Insert(string tableName, IEnumerable<KeyValue
5959

6060
string insertSql = "insert into " + table.QualifiedName + " (" + columnList + ") values (" + valueList + ")";
6161

62-
var identityFunction = _adapter.GetIdentityFunction();
63-
if (!string.IsNullOrWhiteSpace(identityFunction))
62+
if (resultRequired)
6463
{
65-
var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
66-
67-
if (identityColumn != null)
64+
var identityFunction = _adapter.GetIdentityFunction();
65+
if (!string.IsNullOrWhiteSpace(identityFunction))
6866
{
69-
var selectSql = "select * from " + table.QualifiedName + " where " + identityColumn.QuotedName +
70-
" = " + identityFunction;
71-
if (_adapter.ProviderSupportsCompoundStatements)
72-
{
73-
insertSql += "; " + selectSql;
74-
return ExecuteSingletonQuery(insertSql, dataDictionary.Keys, dataDictionary.Values);
75-
}
76-
else
67+
var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
68+
69+
if (identityColumn != null)
7770
{
78-
return ExecuteSingletonQuery(insertSql, selectSql, dataDictionary.Keys, dataDictionary.Values);
71+
var selectSql = "select * from " + table.QualifiedName + " where " + identityColumn.QuotedName +
72+
" = " + identityFunction;
73+
if (_adapter.ProviderSupportsCompoundStatements)
74+
{
75+
insertSql += "; " + selectSql;
76+
return ExecuteSingletonQuery(insertSql, dataDictionary.Keys, dataDictionary.Values);
77+
}
78+
else
79+
{
80+
return ExecuteSingletonQuery(insertSql, selectSql, dataDictionary.Keys,
81+
dataDictionary.Values);
82+
}
7983
}
8084
}
8185
}

‎Simple.Data.Ado/BulkInserter.cs‎

Lines changed: 14 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,14 @@
11
namespace Simple.Data.Ado
22
{
3+
using System;
34
using System.Collections.Generic;
45
using System.Linq;
56
using System.Data;
67
using Schema;
78

89
class BulkInserter : IBulkInserter
910
{
10-
public IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, string tableName, IEnumerable<IDictionary<string, object>> data, IDbTransaction transaction)
11+
public IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, string tableName, IEnumerable<IDictionary<string, object>> data, IDbTransaction transaction, Func<IDictionary<string,object>, Exception, bool> onError, bool resultRequired)
1112
{
1213
var table = adapter.GetSchema().FindTable(tableName);
1314
var columns = table.Columns.Where(c => !c.IsIdentity).ToList();
@@ -21,23 +22,25 @@ public IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, strin
2122
? new BulkInserterHelper(adapter, data, table, columns)
2223
: new BulkInserterTransactionHelper(adapter, data, table, columns, transaction);
2324

24-
var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
25-
if (identityColumn != null)
25+
if (resultRequired)
2626
{
27-
var identityFunction = adapter.GetIdentityFunction();
28-
if (!string.IsNullOrWhiteSpace(identityFunction))
27+
var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
28+
if (identityColumn != null)
2929
{
30-
return InsertRowsAndReturn(adapter, identityFunction, helper, insertSql, table);
30+
var identityFunction = adapter.GetIdentityFunction();
31+
if (!string.IsNullOrWhiteSpace(identityFunction))
32+
{
33+
return InsertRowsAndReturn(adapter, identityFunction, helper, insertSql, table, onError);
34+
}
3135
}
3236
}
3337

34-
helper.InsertRowsWithoutFetchBack(insertSql);
38+
helper.InsertRowsWithoutFetchBack(insertSql, onError);
3539

3640
return null;
3741
}
3842

39-
private static IEnumerable<IDictionary<string, object>> InsertRowsAndReturn(AdoAdapter adapter, string identityFunction, BulkInserterHelper helper,
40-
string insertSql, Table table)
43+
private static IEnumerable<IDictionary<string, object>> InsertRowsAndReturn(AdoAdapter adapter, string identityFunction, BulkInserterHelper helper, string insertSql, Table table, Func<IDictionary<string, object>, Exception, bool> onError)
4144
{
4245
var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
4346

@@ -47,9 +50,9 @@ private static IEnumerable<IDictionary<string, object>> InsertRowsAndReturn(AdoA
4750
" = " + identityFunction;
4851
if (adapter.ProviderSupportsCompoundStatements)
4952
{
50-
return helper.InsertRowsWithCompoundStatement(insertSql, selectSql);
53+
return helper.InsertRowsWithCompoundStatement(insertSql, selectSql, onError);
5154
}
52-
return helper.InsertRowsWithSeparateStatements(insertSql, selectSql);
55+
return helper.InsertRowsWithSeparateStatements(insertSql, selectSql, onError);
5356
}
5457

5558
return null;

‎Simple.Data.Ado/BulkInserterHelper.cs‎

Lines changed: 38 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ public BulkInserterHelper(AdoAdapter adapter, IEnumerable<IDictionary<string, ob
2323
_columns = columns;
2424
}
2525

26-
public virtual void InsertRowsWithoutFetchBack(string insertSql)
26+
public virtual void InsertRowsWithoutFetchBack(string insertSql, Func<IDictionary<string, object>, Exception, bool> onError)
2727
{
2828
var connection = Adapter.CreateConnection();
2929
using (connection.MaybeDisposable())
@@ -35,13 +35,13 @@ public virtual void InsertRowsWithoutFetchBack(string insertSql)
3535
insertCommand.Prepare();
3636
foreach (var row in Data)
3737
{
38-
InsertRow(row, insertCommand);
38+
InsertRow(row, insertCommand, onError);
3939
}
4040
}
4141
}
4242
}
4343

44-
public virtual IEnumerable<IDictionary<string, object>> InsertRowsWithSeparateStatements(string insertSql, string selectSql)
44+
public virtual IEnumerable<IDictionary<string, object>> InsertRowsWithSeparateStatements(string insertSql, string selectSql, Func<IDictionary<string, object>, Exception, bool> onError)
4545
{
4646
var connection = Adapter.CreateConnection();
4747
using (connection.MaybeDisposable())
@@ -52,12 +52,12 @@ public virtual IEnumerable<IDictionary<string, object>> InsertRowsWithSeparateSt
5252
selectCommand.CommandText = selectSql;
5353
connection.OpenIfClosed();
5454
TryPrepare(insertCommand, selectCommand);
55-
return Data.Select(row => InsertRow(row, insertCommand, selectCommand)).ToList();
55+
return Data.Select(row => InsertRow(row, insertCommand, selectCommand, onError)).Where(r => r != null).ToList();
5656
}
5757
}
5858
}
5959

60-
public virtual IEnumerable<IDictionary<string, object>> InsertRowsWithCompoundStatement(string insertSql, string selectSql)
60+
public virtual IEnumerable<IDictionary<string, object>> InsertRowsWithCompoundStatement(string insertSql, string selectSql, Func<IDictionary<string, object>, Exception, bool> onError)
6161
{
6262
insertSql += "; " + selectSql;
6363

@@ -68,12 +68,12 @@ public virtual IEnumerable<IDictionary<string, object>> InsertRowsWithCompoundSt
6868
{
6969
connection.OpenIfClosed();
7070
TryPrepare(command);
71-
return Data.Select(row => InsertRowAndSelect(row, command)).ToList();
71+
return Data.Select(row => InsertRowAndSelect(row, command, onError)).Where(r => r != null).ToList();
7272
}
7373
}
7474
}
7575

76-
protected IDictionary<string, object> InsertRowAndSelect(IDictionary<string, object> row, IDbCommand command)
76+
protected IDictionary<string, object> InsertRowAndSelect(IDictionary<string, object> row, IDbCommand command, Func<IDictionary<string,object>, Exception, bool> onError)
7777
{
7878
var values = new object[command.Parameters.Count];
7979
foreach (var kvp in row)
@@ -86,11 +86,19 @@ protected IDictionary<string, object> InsertRowAndSelect(IDictionary<string, obj
8686
}
8787

8888
CommandHelper.SetParameterValues(command, values);
89-
var insertedRow = TryExecuteSingletonQuery(command);
90-
return insertedRow;
89+
try
90+
{
91+
var insertedRow = TryExecuteSingletonQuery(command);
92+
return insertedRow;
93+
}
94+
catch (Exception ex)
95+
{
96+
if (onError(row, ex)) return null;
97+
throw;
98+
}
9199
}
92100

93-
protected int InsertRow(IDictionary<string, object> row, IDbCommand command)
101+
protected int InsertRow(IDictionary<string, object> row, IDbCommand command, Func<IDictionary<string, object>, Exception, bool> onError)
94102
{
95103
var values = new object[command.Parameters.Count];
96104
foreach (var kvp in row)
@@ -103,10 +111,18 @@ protected int InsertRow(IDictionary<string, object> row, IDbCommand command)
103111
}
104112

105113
CommandHelper.SetParameterValues(command, values);
106-
return TryExecute(command);
114+
try
115+
{
116+
return TryExecute(command);
117+
}
118+
catch (Exception ex)
119+
{
120+
if (onError(row, ex)) return 0;
121+
throw;
122+
}
107123
}
108124

109-
protected IDictionary<string, object> InsertRow(IDictionary<string, object> row, IDbCommand insertCommand, IDbCommand selectCommand)
125+
protected IDictionary<string, object> InsertRow(IDictionary<string, object> row, IDbCommand insertCommand, IDbCommand selectCommand, Func<IDictionary<string, object>, Exception, bool> onError)
110126
{
111127
var values = new object[insertCommand.Parameters.Count];
112128
foreach (var kvp in row)
@@ -119,8 +135,16 @@ protected IDictionary<string, object> InsertRow(IDictionary<string, object> row,
119135
}
120136

121137
CommandHelper.SetParameterValues(insertCommand, values);
122-
if (TryExecute(insertCommand) == 1)
123-
return TryExecuteSingletonQuery(selectCommand);
138+
try
139+
{
140+
if (TryExecute(insertCommand) == 1)
141+
return TryExecuteSingletonQuery(selectCommand);
142+
}
143+
catch (Exception ex)
144+
{
145+
if (onError(row, ex)) return null;
146+
throw;
147+
}
124148
return null;
125149
}
126150

‎Simple.Data.Ado/BulkInserterTransactionHelper.cs‎

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,6 @@
11
namespace Simple.Data.Ado
22
{
3+
using System;
34
using System.Collections.Generic;
45
using System.Data;
56
using System.Linq;
@@ -15,32 +16,32 @@ public BulkInserterTransactionHelper(AdoAdapter adapter, IEnumerable<IDictionary
1516
_transaction = transaction;
1617
}
1718

18-
public override IEnumerable<IDictionary<string, object>> InsertRowsWithSeparateStatements(string insertSql, string selectSql)
19+
public override IEnumerable<IDictionary<string, object>> InsertRowsWithSeparateStatements(string insertSql, string selectSql, Func<IDictionary<string, object>, Exception, bool> onError)
1920
{
2021
var insertCommand = new CommandHelper(Adapter).Create(_transaction.Connection, insertSql);
2122
var selectCommand = _transaction.Connection.CreateCommand();
2223
selectCommand.CommandText = selectSql;
2324
insertCommand.Transaction = _transaction;
2425
selectCommand.Transaction = _transaction;
25-
return Data.Select(row => InsertRow(row, insertCommand, selectCommand)).ToList();
26+
return Data.Select(row => InsertRow(row, insertCommand, selectCommand, onError)).ToList();
2627
}
2728

28-
public override IEnumerable<IDictionary<string, object>> InsertRowsWithCompoundStatement(string insertSql, string selectSql)
29+
public override IEnumerable<IDictionary<string, object>> InsertRowsWithCompoundStatement(string insertSql, string selectSql, Func<IDictionary<string, object>, Exception, bool> onError)
2930
{
3031
insertSql += "; " + selectSql;
3132
var command = new CommandHelper(Adapter).Create(_transaction.Connection, insertSql);
3233
command.Transaction = _transaction;
33-
return Data.Select(row => InsertRowAndSelect(row, command)).ToList();
34+
return Data.Select(row => InsertRowAndSelect(row, command, onError)).ToList();
3435
}
3536

36-
public override void InsertRowsWithoutFetchBack(string insertSql)
37+
public override void InsertRowsWithoutFetchBack(string insertSql, Func<IDictionary<string, object>, Exception, bool> onError)
3738
{
3839
using (var insertCommand = new CommandHelper(Adapter).Create(_transaction.Connection, insertSql))
3940
{
4041
insertCommand.Transaction = _transaction;
4142
foreach (var row in Data)
4243
{
43-
InsertRow(row, insertCommand);
44+
InsertRow(row, insertCommand, onError);
4445
}
4546
}
4647

‎Simple.Data.Ado/IBulkInserter.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,11 @@
11
namespace Simple.Data.Ado
22
{
3+
using System;
34
using System.Collections.Generic;
45
using System.Data;
56

67
public interface IBulkInserter
78
{
8-
IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, string tableName, IEnumerable<IDictionary<string, object>> data, IDbTransaction transaction);
9+
IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, string tableName, IEnumerable<IDictionary<string, object>> data, IDbTransaction transaction, Func<IDictionary<string,object>, Exception, bool> onError, bool resultRequired);
910
}
1011
}

0 commit comments

Comments
 (0)