forked from ThatRendle/Simple.Data
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSqlCustomInserter.cs
More file actions
129 lines (118 loc) · 5.46 KB
/
SqlCustomInserter.cs
File metadata and controls
129 lines (118 loc) · 5.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
using System;
using System.Collections.Generic;
using System.ComponentModel.Composition;
using System.Data;
using System.Linq;
using System.Text;
using Simple.Data.Ado;
using Simple.Data.Ado.Schema;
namespace Simple.Data.SqlServer
{
[Export(typeof(ICustomInserter))]
public class SqlCustomInserter : ICustomInserter
{
public IDictionary<string, object> Insert(AdoAdapter adapter, string tableName, IDictionary<string, object> data, IDbTransaction transaction = null, bool resultRequired = false)
{
var table = adapter.GetSchema().FindTable(tableName);
var dataDictionary = BuildDataDictionary(adapter, data, table);
string columnList = dataDictionary.Keys.Select(c => c.QuotedName).Aggregate((agg, next) => agg + "," + next);
string valueList = dataDictionary.Keys.Select(s => "?").Aggregate((agg, next) => agg + "," + next);
var insertSql = new StringBuilder();
bool identityInsert = adapter.AdoOptions != null && adapter.AdoOptions.IdentityInsert;
if (identityInsert)
{
insertSql.AppendFormat("SET IDENTITY_INSERT {0} ON; ", table.QualifiedName);
}
insertSql.AppendFormat("INSERT INTO {0} ({1})", table.QualifiedName, columnList);
insertSql.AppendFormat(" VALUES ({0})", valueList);
if (identityInsert)
{
insertSql.AppendFormat("; SET IDENTITY_INSERT {0} OFF; ", table.QualifiedName);
}
if (resultRequired)
{
var identityColumn = table.Columns.FirstOrDefault(c => c.IsIdentity);
if (identityColumn != null)
{
insertSql.AppendFormat(" SELECT * FROM {0} WHERE {1} = SCOPE_IDENTITY()", table.QualifiedName,
identityColumn.QuotedName);
return ExecuteSingletonQuery(adapter, insertSql.ToString(), dataDictionary.Keys,
dataDictionary.Values, transaction);
}
}
Execute(adapter, insertSql.ToString(), dataDictionary.Keys, dataDictionary.Values, transaction);
return null;
}
private static Dictionary<Column, object> BuildDataDictionary(AdoAdapter adapter, IDictionary<string, object> data, Table table)
{
Func<string, bool> columnFilter;
if (adapter.AdoOptions != null && adapter.AdoOptions.IdentityInsert)
{
columnFilter =
key =>
{
Column column;
if (table.TryFindColumn(key, out column))
{
return column.IsWriteable || column.IsIdentity;
}
return false;
};
}
else
{
columnFilter = key => table.HasColumn(key) && table.FindColumn(key).IsWriteable;
}
var dataDictionary = data.Where(kvp => columnFilter(kvp.Key))
.ToDictionary(kvp => table.FindColumn(kvp.Key), kvp => kvp.Value);
return dataDictionary;
}
internal IDictionary<string, object> ExecuteSingletonQuery(AdoAdapter adapter, string sql, IEnumerable<Column> columns, IEnumerable<Object> values, IDbTransaction transaction)
{
if (transaction != null)
{
var command = new CommandHelper(adapter).CreateInsert(transaction.Connection, sql, columns, values.ToArray());
command.Transaction = transaction;
return TryExecuteSingletonQuery(command);
}
var connection = adapter.CreateConnection();
using (connection.MaybeDisposable())
{
using (var command = new CommandHelper(adapter).CreateInsert(connection, sql, columns, values.ToArray()))
{
connection.OpenIfClosed();
return TryExecuteSingletonQuery(command);
}
}
}
private static IDictionary<string, object> TryExecuteSingletonQuery(IDbCommand command)
{
using (var reader = command.TryExecuteReader())
{
if (reader.Read())
{
return reader.ToDictionary();
}
}
return null;
}
internal int Execute(AdoAdapter adapter, string sql, IEnumerable<Column> columns, IEnumerable<Object> values, IDbTransaction transaction)
{
if (transaction != null)
{
var command = new CommandHelper(adapter).CreateInsert(transaction.Connection, sql, columns, values.ToArray());
command.Transaction = transaction;
return command.TryExecuteNonQuery();
}
var connection = adapter.CreateConnection();
using (connection.MaybeDisposable())
{
using (var command = new CommandHelper(adapter).CreateInsert(connection, sql, columns, values.ToArray()))
{
connection.OpenIfClosed();
return command.TryExecuteNonQuery();
}
}
}
}
}