Skip to content

Commit dca487a

Browse files
ThatRendleThatRendle
committed
Optimised bulk insert code
1 parent 2d18f17 commit dca487a

10 files changed

Lines changed: 84 additions & 53 deletions

File tree

‎CommonAssemblyInfo.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,6 @@
1919
// COM, set the ComVisible attribute to true on that type.
2020
[assembly: ComVisible(false)]
2121

22-
[assembly: AssemblyVersion("0.12.2.1")]
23-
[assembly: AssemblyFileVersion("0.12.2.1")]
22+
[assembly: AssemblyVersion("0.12.2.2")]
23+
[assembly: AssemblyFileVersion("0.12.2.2")]
2424

‎Simple.Data.Ado/BulkInserterHelper.cs‎

Lines changed: 29 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@ class BulkInserterHelper
1414
protected readonly IEnumerable<IDictionary<string, object>> Data;
1515
private readonly Table _table;
1616
private readonly List<Column> _columns;
17+
private Action<IDictionary<string, object>, IDbCommand> _parameterSetter;
1718

1819
public BulkInserterHelper(AdoAdapter adapter, IEnumerable<IDictionary<string, object>> data, Table table, List<Column> columns)
1920
{
@@ -74,17 +75,9 @@ public virtual IEnumerable<IDictionary<string, object>> InsertRowsWithCompoundSt
7475

7576
protected IDictionary<string, object> InsertRowAndSelect(IDictionary<string, object> row, IDbCommand command, Func<IDictionary<string,object>, Exception, bool> onError)
7677
{
77-
var values = new object[command.Parameters.Count];
78-
foreach (var kvp in row)
79-
{
80-
int index = _columns.IndexOf(_table.FindColumn(kvp.Key));
81-
if (index > -1)
82-
{
83-
values[index] = kvp.Value;
84-
}
85-
}
78+
if (_parameterSetter == null) _parameterSetter = BuildParameterSettingAction(row);
79+
_parameterSetter(row, command);
8680

87-
CommandHelper.SetParameterValues(command, values);
8881
try
8982
{
9083
var insertedRow = TryExecuteSingletonQuery(command);
@@ -99,17 +92,9 @@ protected IDictionary<string, object> InsertRowAndSelect(IDictionary<string, obj
9992

10093
protected int InsertRow(IDictionary<string, object> row, IDbCommand command, Func<IDictionary<string, object>, Exception, bool> onError)
10194
{
102-
var values = new object[command.Parameters.Count];
103-
foreach (var kvp in row)
104-
{
105-
int index = _columns.IndexOf(_table.FindColumn(kvp.Key));
106-
if (index > -1)
107-
{
108-
values[index] = kvp.Value;
109-
}
110-
}
95+
if (_parameterSetter == null) _parameterSetter = BuildParameterSettingAction(row);
96+
_parameterSetter(row, command);
11197

112-
CommandHelper.SetParameterValues(command, values);
11398
try
11499
{
115100
return TryExecute(command);
@@ -123,17 +108,9 @@ protected int InsertRow(IDictionary<string, object> row, IDbCommand command, Fun
123108

124109
protected IDictionary<string, object> InsertRow(IDictionary<string, object> row, IDbCommand insertCommand, IDbCommand selectCommand, Func<IDictionary<string, object>, Exception, bool> onError)
125110
{
126-
var values = new object[insertCommand.Parameters.Count];
127-
foreach (var kvp in row)
128-
{
129-
int index = _columns.IndexOf(_table.FindColumn(kvp.Key));
130-
if (index > -1)
131-
{
132-
values[index] = kvp.Value;
133-
}
134-
}
111+
if (_parameterSetter == null) _parameterSetter = BuildParameterSettingAction(row);
112+
_parameterSetter(row, insertCommand);
135113

136-
CommandHelper.SetParameterValues(insertCommand, values);
137114
try
138115
{
139116
if (TryExecute(insertCommand) == 1)
@@ -195,5 +172,27 @@ private static void TryPrepare(params IDbCommand[] commands)
195172
}
196173
}
197174
}
175+
176+
private Action<IDictionary<string,object>, IDbCommand> BuildParameterSettingAction(IDictionary<string,object> sample)
177+
{
178+
var actions =
179+
_columns.Select<Column, Action<IDictionary<string,object>, IDbCommand>>((c, i) => (row, cmd) => cmd.SetParameterValue(i, null)).ToArray();
180+
181+
foreach (var key in sample.Keys)
182+
{
183+
int index = _columns.IndexOf(_table.FindColumn(key));
184+
if (index >= 0)
185+
actions[index] = BuildIndividualFunction(key, index);
186+
187+
++index;
188+
}
189+
190+
return actions.Aggregate((working, next) => working + next) ?? ((row,cmd) => { });
191+
}
192+
193+
private Action<IDictionary<string, object>, IDbCommand> BuildIndividualFunction(string key, int index)
194+
{
195+
return (dict, command) => command.SetParameterValue(index, dict[key]);
196+
}
198197
}
199198
}

‎Simple.Data.Ado/CommandHelper.cs‎

Lines changed: 5 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,8 @@ internal IDbCommand Create(IDbConnection connection, string sql, IList<object> v
2626
var command = connection.CreateCommand();
2727

2828
command.CommandText = PrepareCommand(sql, command);
29-
SetParameterValues(command, values);
29+
command.ClearParameterValues();
30+
command.SetParameterValues(values);
3031

3132
return command;
3233
}
@@ -96,16 +97,6 @@ private string PrepareInsertCommand(string sql, IDbCommand command, IEnumerable<
9697
return sqlBuilder.ToString();
9798
}
9899

99-
public static void SetParameterValues(IDbCommand command, IList<object> values)
100-
{
101-
int index = 0;
102-
foreach (var parameter in command.Parameters.Cast<IDbDataParameter>())
103-
{
104-
parameter.Value = FixObjectType(values[index]);
105-
index++;
106-
}
107-
}
108-
109100
private static void PrepareCommand(CommandBuilder commandBuilder, IDbCommand command)
110101
{
111102
foreach (var pair in commandBuilder.Parameters)
@@ -141,6 +132,7 @@ public IDbCommand CreateInsert(IDbConnection connection, string insertSql, IEnum
141132
{
142133
var command = connection.CreateCommand();
143134
command.CommandText = PrepareInsertCommand(insertSql, command, columns);
135+
command.ClearParameterValues();
144136
return command;
145137

146138
}
@@ -150,7 +142,8 @@ internal IDbCommand CreateInsert(IDbConnection connection, string sql, IEnumerab
150142
var command = connection.CreateCommand();
151143

152144
command.CommandText = PrepareInsertCommand(sql, command, columns);
153-
SetParameterValues(command, values);
145+
command.ClearParameterValues();
146+
command.SetParameterValues(values);
154147

155148
return command;
156149
}

‎Simple.Data.Ado/DbCommandExtensions.cs‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -79,6 +79,29 @@ internal static void DisposeCommandAndReader(IDbConnection connection, IDbComman
7979
using (reader)
8080
{ /* NoOp */ }
8181
}
82+
83+
public static void SetParameterValues(this IDbCommand command, IList<object> values)
84+
{
85+
int index = 0;
86+
foreach (var parameter in command.Parameters.Cast<IDbDataParameter>())
87+
{
88+
parameter.Value = CommandHelper.FixObjectType(values[index]);
89+
index++;
90+
}
91+
}
92+
93+
public static void ClearParameterValues(this IDbCommand command)
94+
{
95+
foreach (var parameter in command.Parameters.Cast<IDbDataParameter>())
96+
{
97+
parameter.Value = DBNull.Value;
98+
}
99+
}
100+
101+
public static void SetParameterValue(this IDbCommand command, int index, object value)
102+
{
103+
((IDbDataParameter) command.Parameters[index]).Value = CommandHelper.FixObjectType(value);
104+
}
82105
}
83106

84107
class EnumerableShim<T> : IEnumerable<T>
@@ -98,6 +121,7 @@ IEnumerator IEnumerable.GetEnumerator()
98121
{
99122
return GetEnumerator();
100123
}
124+
101125
}
102126

103127
static class EnumerableShim

‎Simple.Data.Ado/Simple.Data.Ado.nuspec‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
33
<metadata>
44
<id>Simple.Data.Ado</id>
5-
<version>0.12.2.1</version>
5+
<version>0.12.2.2</version>
66
<authors>Mark Rendle</authors>
77
<owners>Mark Rendle</owners>
88
<description>ADO Adapter for the Simple.Data data access library.</description>
@@ -12,7 +12,7 @@
1212
<tags>sqlserver database data ado .net40</tags>
1313
<language>en-us</language>
1414
<dependencies>
15-
<dependency id="Simple.Data.Core" version="0.12.2.1" />
15+
<dependency id="Simple.Data.Core" version="0.12.2.2" />
1616
</dependencies>
1717
</metadata>
1818
</package>

‎Simple.Data.BehaviourTest/Query/WithTest.cs‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
namespace Simple.Data.IntegrationTest.Query
2+
{
3+
using System;
4+
using Mocking.Ado;
5+
using NUnit.Framework;
6+
7+
[TestFixture]
8+
public class WithTest : DatabaseIntegrationContext
9+
{
10+
protected override void SetSchema(MockSchemaProvider schemaProvider)
11+
{
12+
throw new NotImplementedException();
13+
}
14+
}
15+
}

‎Simple.Data.Mocking/Simple.Data.Mocking.nuspec‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
<package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
33
<metadata xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
44
<id>Simple.Data.Mocking</id>
5-
<version>0.12.2.1</version>
5+
<version>0.12.2.2</version>
66
<authors>Mark Rendle</authors>
77
<owners>Mark Rendle</owners>
88
<description>XML-based Mocking adapter for the Simple.Data data access library.</description>
@@ -12,7 +12,7 @@
1212
<tags>database data .net40</tags>
1313
<language>en-us</language>
1414
<dependencies>
15-
<dependency id="Simple.Data.Ado" version="0.12.2.1" />
15+
<dependency id="Simple.Data.Ado" version="0.12.2.2" />
1616
</dependencies>
1717
</metadata>
1818
</package>

‎Simple.Data.SqlCe40/Simple.Data.SqlCe40.nuspec‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
<package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
33
<metadata xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
44
<id>Simple.Data.SqlCompact40</id>
5-
<version>0.12.2.1</version>
5+
<version>0.12.2.2</version>
66
<authors>Mark Rendle</authors>
77
<owners>Mark Rendle</owners>
88
<description>SQL Server Compact 4.0 ADO provider for the Simple.Data data access library.</description>
@@ -12,7 +12,7 @@
1212
<tags>sqlserver compact sqlce database data ado .net40</tags>
1313
<language>en-us</language>
1414
<dependencies>
15-
<dependency id="Simple.Data.Ado" version="0.12.2.1" />
15+
<dependency id="Simple.Data.Ado" version="0.12.2.2" />
1616
</dependencies>
1717
</metadata>
1818
</package>

‎Simple.Data.SqlServer/Simple.Data.SqlServer.nuspec‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
<package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
33
<metadata xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
44
<id>Simple.Data.SqlServer</id>
5-
<version>0.12.2.1</version>
5+
<version>0.12.2.2</version>
66
<authors>Mark Rendle</authors>
77
<owners>Mark Rendle</owners>
88
<description>SQL Server ADO provider for the Simple.Data data access library.</description>
@@ -12,7 +12,7 @@
1212
<tags>sqlserver database data ado .net40</tags>
1313
<language>en-us</language>
1414
<dependencies>
15-
<dependency id="Simple.Data.Ado" version="0.12.2.1" />
15+
<dependency id="Simple.Data.Ado" version="0.12.2.2" />
1616
</dependencies>
1717
</metadata>
1818
</package>

‎Simple.Data/Simple.Data.nuspec‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
33
<metadata>
44
<id>Simple.Data.Core</id>
5-
<version>0.12.2.1</version>
5+
<version>0.12.2.2</version>
66
<authors>Mark Rendle</authors>
77
<owners>Mark Rendle</owners>
88
<licenseUrl>http://www.opensource.org/licenses/mit-license.php</licenseUrl>

0 commit comments

Comments
 (0)