-
Notifications
You must be signed in to change notification settings - Fork 3
/
GenericRepository.cs
210 lines (167 loc) · 6.71 KB
/
GenericRepository.cs
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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Infrastructure.Interfaces;
namespace Infrastructure.Repositories
{
public abstract class GenericRepository<T> : IGenericRepository<T> where T: class
{
private readonly string _tableName;
protected GenericRepository(string tableName)
{
_tableName = tableName;
}
/// <summary>
/// Generate new connection based on connection string
/// </summary>
/// <returns></returns>
private SqlConnection SqlConnection()
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString);
}
/// <summary>
/// Open new connection and return it for use
/// </summary>
/// <returns></returns>
private IDbConnection CreateConnection()
{
var conn = SqlConnection();
conn.Open();
return conn;
}
private IEnumerable<PropertyInfo> GetProperties => typeof(T).GetProperties();
public async Task<IEnumerable<T>> GetAllAsync()
{
using (var connection = CreateConnection())
{
//TODO: extract field names from T and use them to create query instead of using *
return await connection.QueryAsync<T>($"SELECT * FROM {_tableName}");
}
}
public async Task DeleteRowAsync(Guid id)
{
using (var connection = CreateConnection())
{
await connection.ExecuteAsync($"DELETE FROM {_tableName} WHERE Id=@Id", new { Id = id });
}
}
public async Task<T> GetAsync(Guid id)
{
using (var connection = CreateConnection())
{
//TODO: extract field names from T and use them to create query instead of using *
var result = await connection.QuerySingleOrDefaultAsync<T>($"SELECT * FROM {_tableName} WHERE Id=@Id", new { Id = id });
if (result == null)
throw new KeyNotFoundException($"{_tableName} with id [{id}] could not be found.");
return result;
}
}
public async Task<int> SaveRangeAsync(IEnumerable<T> list)
{
var inserted = 0;
var query = GenerateInsertQuery();
using (var connection = CreateConnection())
{
inserted += await connection.ExecuteAsync(query, list);
}
return inserted;
}
public async Task UpdateAsync(T t)
{
var updateQuery = GenerateUpdateQuery();
using (var connection = CreateConnection())
{
await connection.ExecuteAsync(updateQuery, t);
}
}
public async Task InsertAsync(T t)
{
var insertQuery = GenerateInsertQuery();
using (var connection = CreateConnection())
{
await connection.ExecuteAsync(insertQuery, t);
}
}
#region helper private methods
private string GenerateUpsertQuery()
{
//we assume
var upsertQuery = new StringBuilder($"IF EXISTS (select * FROM {_tableName} WITH" +
" (updlock, SERIALIZABLE) WHERE Id=@Id) " +
$"BEGIN UPDATE {_tableName} SET ");
var listOfProperties = typeof(T).GetProperties().Select(f => f.Name).ToList();
var idPropertyType = typeof(T).GetProperties().Where(x => x.Name.Equals("Id")).Select(f => f.PropertyType).First();
var insertId = idPropertyType.Name.Equals("Guid") || idPropertyType.Name.Equals("String");
//add update part
foreach (var prop in listOfProperties)
{
if (!insertId && prop.Equals("Id")) continue;
upsertQuery.Append($"{prop}=@{prop},");
}
upsertQuery.Remove(upsertQuery.Length - 1, 1); //remove last comma
upsertQuery.Append($" WHERE Id=@Id END ELSE BEGIN INSERT INTO {_tableName} ");
upsertQuery.Append("(");
foreach (var prop in listOfProperties)
{
if (!insertId && prop.Equals("Id")) continue;
upsertQuery.Append($"[{prop}],");
}
upsertQuery
.Remove(upsertQuery.Length - 1, 1)
.Append(") VALUES (");
foreach (var prop in listOfProperties)
{
if (!insertId && prop.Equals("Id")) continue;
upsertQuery.Append($"@{prop},");
}
upsertQuery
.Remove(upsertQuery.Length - 1, 1)
.Append(") END");
return upsertQuery.ToString();
}
private string GenerateUpdateQuery()
{
var updateQuery = new StringBuilder($"UPDATE {_tableName} SET ");
var properties = GenerateListOfProperties(GetProperties);
properties.ForEach(property =>
{
if (!property.Equals("Id"))
{
updateQuery.Append($"{property}=@{property},");
}
});
updateQuery.Remove(updateQuery.Length - 1, 1); //remove last comma
updateQuery.Append(" WHERE Id=@Id");
return updateQuery.ToString();
}
private static List<string> GenerateListOfProperties(IEnumerable<PropertyInfo> listOfProperties)
{
return (from prop in listOfProperties let attributes = prop.GetCustomAttributes(typeof(DescriptionAttribute), false)
where attributes.Length <= 0 || (attributes[0] as DescriptionAttribute)?.Description != "ignore" select prop.Name).ToList();
}
private string GenerateInsertQuery()
{
var insertQuery = new StringBuilder($"INSERT INTO {_tableName} ");
insertQuery.Append("(");
var properties = GenerateListOfProperties(GetProperties);
properties.ForEach(prop => { insertQuery.Append($"[{prop}],"); });
insertQuery
.Remove(insertQuery.Length - 1, 1)
.Append(") VALUES (");
properties.ForEach(prop => { insertQuery.Append($"@{prop},"); });
insertQuery
.Remove(insertQuery.Length - 1, 1)
.Append(")");
return insertQuery.ToString();
}
#endregion
}
}