• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

HicServices / RDMP / 11913691445

19 Nov 2024 01:06PM UTC coverage: 57.376% (-0.007%) from 57.383%
11913691445

Pull #2006

github

JFriel
add safe ref
Pull Request #2006: Release: 8.4.0

11203 of 21044 branches covered (53.24%)

Branch coverage included in aggregate %.

25 of 32 new or added lines in 8 files covered. (78.13%)

351 existing lines in 9 files now uncovered.

31733 of 53789 relevant lines covered (59.0%)

4101.19 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

83.89
/Rdmp.Core/MapsDirectlyToDatabaseTable/TableRepository.cs
1
// Copyright (c) The University of Dundee 2018-2019
2
// This file is part of the Research Data Management Platform (RDMP).
3
// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
4
// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
5
// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.
6

7
using System;
8
using System.Collections.Generic;
9
using System.Data;
10
using System.Data.Common;
11
using System.Diagnostics;
12
using System.Globalization;
13
using System.Linq;
14
using System.Linq.Expressions;
15
using System.Reflection;
16
using System.Threading;
17
using FAnsi;
18
using FAnsi.Connections;
19
using FAnsi.Discovery;
20
using NLog;
21
using Rdmp.Core.MapsDirectlyToDatabaseTable.Injection;
22
using Rdmp.Core.MapsDirectlyToDatabaseTable.Revertable;
23
using Rdmp.Core.MapsDirectlyToDatabaseTable.Versioning;
24
using Rdmp.Core.ReusableLibraryCode;
25
using Rdmp.Core.ReusableLibraryCode.DataAccess;
26

27
namespace Rdmp.Core.MapsDirectlyToDatabaseTable;
28

29
/// <summary>
30
/// See ITableRepository
31
/// </summary>
32
public abstract class TableRepository : ITableRepository
33
{
34
    //fields
35
    protected DbConnectionStringBuilder _connectionStringBuilder;
36
    public IObscureDependencyFinder ObscureDependencyFinder { get; set; }
7,294✔
37

38
    private static object _oLockUpdateCommands = new();
4✔
39
    private UpdateCommandStore _updateCommandStore = new();
994✔
40
    public bool SupportsCommits => true;
4✔
41

42
    //'accessors'
43
    public string ConnectionString => _connectionStringBuilder.ConnectionString;
3,226✔
44
    public DbConnectionStringBuilder ConnectionStringBuilder => _connectionStringBuilder;
60✔
45

46
    public DiscoveredServer DiscoveredServer { get; protected set; }
1,543,006✔
47

48
    /// <summary>
49
    /// Constructors for quickly resolving <see cref="ConstructEntity"/> calls rather than relying on reflection e.g. ObjectConstructor
50
    /// </summary>
51
    protected Dictionary<Type, Func<IRepository, DbDataReader, IMapsDirectlyToDatabaseTable>> Constructors = new();
994✔
52

53
    private readonly Logger _logger = LogManager.GetCurrentClassLogger();
994✔
54

55
    private Lazy<DiscoveredTable[]> _tables;
56

57
    //If you are calling this constructor then make sure to set the connection strings in your derived class constructor
58
    public TableRepository()
994✔
59
    {
60
        _tables = new Lazy<DiscoveredTable[]>(() => DiscoveredServer.GetCurrentDatabase().DiscoverTables(false));
1,002✔
61
    }
994✔
62

63
    public TableRepository(IObscureDependencyFinder obscureDependencyFinder,
64
        DbConnectionStringBuilder connectionStringBuilder) : this()
838✔
65
    {
66
        ObscureDependencyFinder = obscureDependencyFinder;
838✔
67
        _connectionStringBuilder = connectionStringBuilder;
838✔
68
        DiscoveredServer = new DiscoveredServer(connectionStringBuilder);
838✔
69
    }
838✔
70

71
    /// <inheritdoc/>
72
    public void DeleteFromDatabase(IMapsDirectlyToDatabaseTable oTableWrapperObject)
73
    {
74
        //do not log information about access credentials
75
        if (oTableWrapperObject is not IDataAccessCredentials)
2,434✔
76
            _logger.Debug(
2,282✔
77
                $"Deleted,{oTableWrapperObject.GetType().Name},{oTableWrapperObject.ID},{oTableWrapperObject}");
2,282✔
78

79
        lock (_oLockUpdateCommands)
2,434✔
80
        {
81
            //if the repository has obscure dependencies
82
            ObscureDependencyFinder
2,434✔
83
                ?.ThrowIfDeleteDisallowed(
2,434✔
84
                    oTableWrapperObject); //confirm that deleting the object is allowed by the dependencies
2,434✔
85

86
            using var con = GetConnection();
2,426✔
87
            using (var cmd = DatabaseCommandHelper.GetCommand(
2,426✔
88
                       $"DELETE FROM {Wrap(oTableWrapperObject.GetType().Name)} WHERE ID =@ID", con.Connection,
2,426✔
89
                       con.Transaction))
2,426✔
90
            {
91
                DatabaseCommandHelper.AddParameterWithValueToCommand("@ID", cmd, oTableWrapperObject.ID);
2,426✔
92
                cmd.ExecuteNonQuery();
2,426✔
93
            }
2,422✔
94

95
            //likewise if there are obscure dependency handlers let them handle cascading this delete into the mists of their obscure functionality (e.g. deleting a Catalogue in CatalogueRepository would delete all Evaluations of that Catalogue in the DQE repository because they would then be orphans)
96
            ObscureDependencyFinder?.HandleCascadeDeletesForDeletedObject(oTableWrapperObject);
2,422✔
97
        }
98

99
        Deleting?.Invoke(this, new IMapsDirectlyToDatabaseTableEventArgs(oTableWrapperObject));
2,422!
UNCOV
100
    }
×
101

102
    /// <inheritdoc/>
103
    public T[] GetAllObjectsWithParent<T, T2>(T2 parent) where T : IMapsDirectlyToDatabaseTable, IInjectKnown<T2>
104
        where T2 : IMapsDirectlyToDatabaseTable
105
    {
106
        var toReturn = GetAllObjectsWithParent<T>(parent);
13,735✔
107
        foreach (var v in toReturn)
427,024✔
108
            v.InjectKnown(parent);
199,777✔
109

110
        return toReturn;
13,735✔
111
    }
112

113

114
    /// <inheritdoc/>
115
    public void SaveToDatabase(IMapsDirectlyToDatabaseTable oTableWrapperObject)
116
    {
117
        var r = (IRevertable)oTableWrapperObject;
38,912✔
118
        var changes = r.HasLocalChanges();
38,912✔
119

120
        if (changes.Evaluation == ChangeDescription.NoChanges)
38,912✔
121
            return;
17,047✔
122

123
        var e = new SaveEventArgs(oTableWrapperObject);
21,865✔
124
        Saving?.Invoke(this, e);
21,865!
125

126
        if (e.Cancel)
21,865!
UNCOV
127
            return;
×
128

129
        foreach (var c in changes.Differences)
114,778✔
130
            _logger.Debug(
35,524✔
131
                $"Save,{oTableWrapperObject.GetType().Name},{oTableWrapperObject.ID},{c.Property},{c.DatabaseValue},{c.LocalValue}");
35,524✔
132

133
        lock (_oLockUpdateCommands)
21,865✔
134
        {
135
            using var managedConnection = GetConnection();
21,865✔
136
            var cmd = GetUpdateCommandFromStore(oTableWrapperObject.GetType(), managedConnection);
21,865✔
137

138
            PopulateUpdateCommandValuesWithCurrentState(cmd, oTableWrapperObject);
21,865✔
139

140
            cmd.Connection = managedConnection.Connection;
21,865✔
141

142
            //change the transaction of the update comand to the specified transaction but only long enough to run it
143
            var transactionBefore = cmd.Transaction;
21,865✔
144
            cmd.Transaction = managedConnection.Transaction;
21,865✔
145

146
            int affectedRows;
147
            try
148
            {
149
                //run the save
150
                affectedRows = cmd.ExecuteNonQuery();
21,865✔
151
            }
21,861✔
152
            finally
153
            {
154
                //reset the transaction to whatever it was before
155
                cmd.Transaction = transactionBefore;
21,865✔
156
            }
21,865✔
157

158

159
            if (affectedRows != 1)
21,861!
UNCOV
160
                throw new Exception(
×
161
                    $"Attempted to update {oTableWrapperObject.GetType().Name} with ID {oTableWrapperObject.ID} but the UPDATE command resulted in {affectedRows} affected rows");
×
162
        }
163
    }
21,861✔
164

165
    protected static void PopulateUpdateCommandValuesWithCurrentState(DbCommand cmd,
166
        IMapsDirectlyToDatabaseTable oTableWrapperObject)
167
    {
168
        foreach (DbParameter p in cmd.Parameters)
669,236✔
169
        {
170
            var prop = oTableWrapperObject.GetType().GetProperty(p.ParameterName.Trim('@'));
312,753✔
171

172
            var propValue = prop.GetValue(oTableWrapperObject, null);
312,753✔
173

174
            //if it is a complex type but IConvertible e.g. CatalogueFolder
175
            if (!prop.PropertyType.IsValueType && propValue is IConvertible c && c.GetTypeCode() == TypeCode.String)
312,753✔
176
                propValue = c.ToString(CultureInfo.CurrentCulture);
61,437✔
177

178
            SetParameterToValue(p, propValue);
312,753✔
179
        }
180

181
        cmd.Parameters["@ID"].Value = oTableWrapperObject.ID;
21,865✔
182
    }
21,865✔
183

184
    private static void SetParameterToValue(DbParameter p, object propValue)
185
    {
186
        p.Value = propValue switch
727,224!
187
        {
727,224✔
188
            null => DBNull.Value,
144,606✔
189
            string value when string.IsNullOrWhiteSpace(value) => DBNull.Value,
156,041✔
190
            Uri => propValue.ToString(),
4✔
191
            TimeSpan => propValue.ToString(),
26✔
UNCOV
192
            Version => propValue.ToString(),
×
193
            _ => propValue
581,086✔
194
        };
727,224✔
195
    }
727,224✔
196

197
    public bool StillExists<T>(int id) where T : IMapsDirectlyToDatabaseTable => StillExists(typeof(T), id);
8✔
198

199
    public bool StillExists(IMapsDirectlyToDatabaseTable o) => StillExists(o.GetType(), o.ID);
172✔
200

201
    public bool StillExists(Type type, int id)
202
    {
203
        //go to database to see if it exists
204
        using var connection = GetConnection();
338✔
205
        using var selectCommand = DatabaseCommandHelper.GetCommand(
338✔
206
            $"SELECT case when exists(select * FROM {Wrap(type.Name)} WHERE ID= {id}) then 1 else 0 end",
338✔
207
            connection.Connection, connection.Transaction);
338✔
208
        return Convert.ToBoolean(selectCommand.ExecuteScalar());
338✔
209
    }
338✔
210

211
    /// <summary>
212
    /// Get's all the objects of type T that have the parent 'parent' (which will be interrogated by its ID).  Note that for this to work the type T must have a property which is EXACTLY the Parent objects name with _ID afterwards
213
    /// </summary>
214
    /// <typeparam name="T"></typeparam>
215
    /// <param name="parent"></param>
216
    /// <returns></returns>
217
    public T[] GetAllObjectsWithParent<T>(IMapsDirectlyToDatabaseTable parent) where T : IMapsDirectlyToDatabaseTable =>
218
        //no cached result so fallback on regular method
219
        GetAllObjectsWhere<T>($"{parent.GetType().Name}_ID", parent.ID);
211,779✔
220

221
    public T GetObjectByID<T>(int id) where T : IMapsDirectlyToDatabaseTable =>
222
        typeof(T).IsInterface
128,371!
223
            ? throw new Exception(
128,371✔
224
                "GetObjectByID<T> requires a proper class not an interface so that it can access the correct table")
128,371✔
225
            : (T)GetObjectByID(typeof(T), id);
128,371✔
226

227
    public IMapsDirectlyToDatabaseTable GetObjectByID(Type type, int id)
228
    {
229
        if (id == 0)
169,185✔
230
            return null;
3,348✔
231

232
        var typename = Wrap(type.Name);
165,837✔
233

234
        using var connection = GetConnection();
165,837✔
235
        using var selectCommand = DatabaseCommandHelper.GetCommand($"SELECT * FROM {typename} WHERE ID={id}",
165,837✔
236
            connection.Connection, connection.Transaction);
165,837✔
237
        using var r = selectCommand.ExecuteReader();
165,837✔
238
        if (!r.HasRows)
165,837✔
239
            throw new KeyNotFoundException($"Could not find {type.Name} with ID {id}");
30✔
240
        r.Read();
165,807✔
241
        return ConstructEntity(type, r);
165,807✔
242
    }
165,807✔
243

244
    public string Wrap(string name) => DiscoveredServer.GetQuerySyntaxHelper().EnsureWrapped(name);
745,786✔
245

246
    protected abstract IMapsDirectlyToDatabaseTable ConstructEntity(Type t, DbDataReader reader);
247

248
    private T ConstructEntity<T>(DbDataReader reader) where T : IMapsDirectlyToDatabaseTable
249
    {
250
        ArgumentNullException.ThrowIfNull(reader);
257,692✔
251

252
        try
253
        {
254
            return (T)ConstructEntity(typeof(T), reader);
257,692✔
255
        }
UNCOV
256
        catch (Exception e)
×
257
        {
UNCOV
258
            throw new Exception($"Could not construct '{typeof(T).Name}' with ID={reader["ID"]}", e);
×
259
        }
260
    }
257,692✔
261

262
    public virtual T[] GetAllObjects<T>() where T : IMapsDirectlyToDatabaseTable => GetAllObjects<T>(null);
35,390✔
263

264
    public T[] GetAllObjects<T>(string whereSQL) where T : IMapsDirectlyToDatabaseTable
265
    {
266
        var typename = Wrap(typeof(T).Name);
78,320✔
267

268
        //if there is whereSQL make sure it is a legit SQL where
269
        if (!string.IsNullOrWhiteSpace(whereSQL))
78,320✔
270
            if (!whereSQL.Trim().ToUpper().StartsWith("WHERE"))
42,930!
UNCOV
271
                throw new ArgumentException($"whereSQL did not start with the word 'WHERE', it was:{whereSQL}");
×
272

273
        var toReturn = new List<T>();
78,320✔
274

275
        using var opener = GetConnection();
78,320✔
276
        var selectCommand = DatabaseCommandHelper.GetCommand($"SELECT * FROM {typename} {whereSQL ?? ""}",
78,320✔
277
            opener.Connection, opener.Transaction);
78,320✔
278

279
        using var r = selectCommand.ExecuteReader();
78,320✔
280
        while (r.Read())
336,012✔
281
            toReturn.Add(ConstructEntity<T>(r));
257,692✔
282
        return toReturn.ToArray();
78,320✔
283
    }
78,320✔
284

285
    public T[] GetAllObjectsWhere<T>(string whereSQL, Dictionary<string, object> parameters = null)
286
        where T : IMapsDirectlyToDatabaseTable => GetAllObjects(typeof(T), whereSQL, parameters).Cast<T>().ToArray();
219,511✔
287

288
    public T[] GetAllObjectsWhere<T>(string property, object value1) where T : IMapsDirectlyToDatabaseTable =>
289
        GetAllObjectsWhere<T>($"WHERE {property} = @val", new Dictionary<string, object> { { "@val", value1 } });
217,765✔
290

291
    public T[] GetAllObjectsWhere<T>(string property1, object value1, ExpressionType operand, string property2,
292
        object value2) where T : IMapsDirectlyToDatabaseTable
293
    {
294
        var @operator = operand switch
1,746!
295
        {
1,746✔
UNCOV
296
            ExpressionType.AndAlso => "AND",
×
297
            ExpressionType.OrElse => "OR",
1,746✔
UNCOV
298
            _ => throw new NotSupportedException("operand")
×
299
        };
1,746✔
300

301
        return GetAllObjectsWhere<T>(
1,746✔
302
            $"WHERE {property1}=@val1 {@operator} {property2}=@val2", new Dictionary<string, object>
1,746✔
303
            {
1,746✔
304
                { "@val1", value1 },
1,746✔
305
                { "@val2", value2 }
1,746✔
306
            });
1,746✔
307
    }
308

309
    public IEnumerable<IMapsDirectlyToDatabaseTable> GetAllObjects(Type t, string whereSQL,
310
        Dictionary<string, object> parameters = null)
311
    {
312
        var typename = Wrap(t.Name);
220,731✔
313

314
        // if there is whereSQL make sure it is a legit SQL where
315
        if (!whereSQL.Trim().ToUpper().StartsWith("WHERE"))
220,731!
UNCOV
316
            throw new ArgumentException($"whereSQL did not start with the word 'WHERE', it was:{whereSQL}");
×
317

318
        var toReturn = new List<IMapsDirectlyToDatabaseTable>();
220,731✔
319
        using var opener = GetConnection();
220,731✔
320
        var selectCommand = PrepareCommand($"SELECT * FROM {typename} {whereSQL}", parameters, opener.Connection,
220,731✔
321
            opener.Transaction);
220,731✔
322

323
        using var r = selectCommand.ExecuteReader();
220,731✔
324
        while (r.Read())
630,620✔
325
            toReturn.Add(ConstructEntity(t, r));
409,889✔
326

327
        return toReturn.ToArray();
220,731✔
328
    }
220,731✔
329

330
    public IEnumerable<IMapsDirectlyToDatabaseTable> GetAllObjects(Type t)
331
    {
UNCOV
332
        var typename = Wrap(t.Name);
×
333

UNCOV
334
        var toReturn = new List<IMapsDirectlyToDatabaseTable>();
×
335

UNCOV
336
        using var opener = GetConnection();
×
337
        var selectCommand =
×
338
            DatabaseCommandHelper.GetCommand($"SELECT * FROM {typename}", opener.Connection, opener.Transaction);
×
339

UNCOV
340
        using var r = selectCommand.ExecuteReader();
×
341
        while (r.Read())
×
342
            toReturn.Add(ConstructEntity(t, r));
×
343

UNCOV
344
        return toReturn;
×
345
    }
×
346

347
    private DbCommand GetUpdateCommandFromStore(Type type, IManagedConnection managedConnection)
348
    {
349
        if (!_updateCommandStore.ContainsKey(type))
21,865✔
350
            _updateCommandStore.Add(type, _connectionStringBuilder, managedConnection.Connection,
1,636✔
351
                managedConnection.Transaction);
1,636✔
352

353
        return _updateCommandStore[type];
21,865✔
354
    }
355

356
    public Version GetVersion() =>
UNCOV
357
        DatabaseVersionProvider.GetVersionFromDatabase(DiscoveredServer.GetCurrentDatabase());
×
358

359
    public IEnumerable<T> GetAllObjectsInIDList<T>(IEnumerable<int> ids) where T : IMapsDirectlyToDatabaseTable =>
360
        GetAllObjectsInIDList(typeof(T), ids).Cast<T>();
1,616✔
361

362
    public IEnumerable<IMapsDirectlyToDatabaseTable> GetAllObjectsInIDList(Type elementType, IEnumerable<int> ids)
363
    {
364
        var inList = string.Join(",", ids);
1,616✔
365

366
        return string.IsNullOrWhiteSpace(inList)
1,616✔
367
            ? Enumerable.Empty<IMapsDirectlyToDatabaseTable>()
1,616✔
368
            : GetAllObjects(elementType, $" WHERE ID in ({inList})");
1,616✔
369
    }
370

371
    /// <inheritdoc/>
372
    public bool AreEqual(IMapsDirectlyToDatabaseTable obj1, object obj2)
373
    {
374
        if (obj1 == null && obj2 != null)
559,177!
UNCOV
375
            return false;
×
376

377
        if (obj2 == null && obj1 != null)
559,177✔
378
            return false;
562✔
379

380
        if (obj1 == null && obj2 == null)
558,615!
UNCOV
381
            throw new NotSupportedException(
×
382
                "Why are you comparing two null things against one another with this method?");
×
383

384
        return obj1.GetType() == obj2.GetType() && obj1.ID == ((IMapsDirectlyToDatabaseTable)obj2).ID &&
558,615✔
385
               obj1.Repository == ((IMapsDirectlyToDatabaseTable)obj2).Repository;
558,615✔
386
    }
387

388
    /// <inheritdoc/>
389
    public int GetHashCode(IMapsDirectlyToDatabaseTable obj1) => obj1.GetType().GetHashCode() * obj1.ID;
1,869,383✔
390

391
    /// <summary>
392
    /// Gets all public properties of the class that are not decorated with [<see cref="NoMappingToDatabase"/>]
393
    /// </summary>
394
    /// <param name="type"></param>
395
    /// <returns></returns>
396
    public static PropertyInfo[] GetPropertyInfos(Type type)
397
    {
398
        return type.GetProperties().Where(prop => !Attribute.IsDefined(prop, typeof(NoMappingToDatabase))).ToArray();
2,084,416✔
399
    }
400

401
    /// <inheritdoc/>
402
    public void RevertToDatabaseState(IMapsDirectlyToDatabaseTable localCopy)
403
    {
404
        //get new copy out of database
405
        var databaseState = GetObjectByID(localCopy.GetType(), localCopy.ID);
96✔
406

407
        Debug.Assert(localCopy.GetType() == databaseState.GetType());
408

409
        //set all properties on the passed in one to the database state
410
        foreach (var propertyInfo in GetPropertyInfos(localCopy.GetType()))
2,008✔
411
        {
412
            if (!propertyInfo.CanWrite)
908!
UNCOV
413
                throw new InvalidOperationException(
×
414
                    $"The property {propertyInfo.Name} has no setter for type {databaseState.GetType().Name}");
×
415

416
            propertyInfo.SetValue(localCopy, propertyInfo.GetValue(databaseState));
908✔
417
        }
418

419
        //Mark any cached data as out of date
420
        if (localCopy is IInjectKnown inject)
96✔
421
            inject.ClearAllInjections();
36✔
422
    }
96✔
423

424
    /// <inheritdoc/>
425
    public RevertableObjectReport HasLocalChanges(IMapsDirectlyToDatabaseTable localCopy)
426
    {
427
        IMapsDirectlyToDatabaseTable dbCopy;
428

429
        var toReturn = new RevertableObjectReport
40,444✔
430
        {
40,444✔
431
            Evaluation = ChangeDescription.NoChanges
40,444✔
432
        };
40,444✔
433

434
        try
435
        {
436
            dbCopy = GetObjectByID(localCopy.GetType(), localCopy.ID);
40,444✔
437
        }
40,438✔
438
        catch (KeyNotFoundException)
6✔
439
        {
440
            toReturn.Evaluation = ChangeDescription.DatabaseCopyWasDeleted;
6✔
441
            return toReturn;
6✔
442
        }
443

444
        foreach (var propertyInfo in GetPropertyInfos(localCopy.GetType()))
1,254,520✔
445
        {
446
            var local = propertyInfo.GetValue(localCopy);
586,822✔
447
            var db = propertyInfo.GetValue(dbCopy);
586,822✔
448

449
            //don't decided that "" vs null is a legit change
450
            if (local is string local1 && string.IsNullOrWhiteSpace(local1))
586,822✔
451
                local = null;
1,480✔
452

453
            if (db is string s && string.IsNullOrWhiteSpace(s))
586,822✔
454
                db = null;
2,498✔
455

456
            if (!Equals(local, db))
586,822✔
457
            {
458
                toReturn.Differences.Add(new RevertablePropertyDifference(propertyInfo, local, db));
36,256✔
459
                toReturn.Evaluation = ChangeDescription.DatabaseCopyDifferent;
36,256✔
460
            }
461
        }
462

463
        return toReturn;
40,438✔
464
    }
6✔
465

466

467
    #region new
468

469
    public void TestConnection()
470
    {
471
        try
472
        {
473
            using var con = GetConnection();
60✔
474
            if (con.Connection.State != ConnectionState.Open)
56!
UNCOV
475
                throw new Exception($"State of connection was {con.Connection.State}");
×
476
        }
56✔
477
        catch (Exception e)
4✔
478
        {
479
            var msg = _connectionStringBuilder.ConnectionString;
4✔
480

481
            var pass = DiscoveredServer.Helper.GetExplicitPasswordIfAny(_connectionStringBuilder);
4✔
482

483
            if (!string.IsNullOrWhiteSpace(pass))
4✔
484
                msg = msg.Replace(pass, "****");
2✔
485

486
            throw new Exception($"Testing connection failed, connection string was '{msg}'", e);
4✔
487
        }
488
    }
56✔
489

490
    public IEnumerable<T> SelectAll<T>(string selectQuery, string columnWithObjectID = null)
491
        where T : IMapsDirectlyToDatabaseTable
492
    {
493
        columnWithObjectID ??= $"{typeof(T).Name}_ID";
2,514✔
494

495
        using var opener = GetConnection();
2,514✔
496
        var idsToReturn = new List<int>();
2,514✔
497
        using (var cmd = DatabaseCommandHelper.GetCommand(selectQuery, opener.Connection, opener.Transaction))
2,514✔
498
        {
499
            using var r = cmd.ExecuteReader();
2,514✔
500
            while (r.Read()) idsToReturn.Add(Convert.ToInt32(r[columnWithObjectID]));
4,602✔
501
        }
502

503
        return !idsToReturn.Any()
2,514✔
504
            ? Enumerable.Empty<T>()
2,514✔
505
            : GetAllObjects<T>($"WHERE ID in ({string.Join(",", idsToReturn)})");
2,514✔
506
    }
2,514✔
507

508
    /// <summary>
509
    /// Runs the selectQuery (which must be a FULL QUERY) and uses @parameters for each of the kvps in the dictionary.  It expects the query result set to include
510
    /// a field which is named whatever your value in parameter columnWithObjectID is.  If you hate life you can pass a dbNullSubstition (which must also be of type
511
    /// T) in which case whenever a record in the result set is found with a DBNull in it, the substitute appears in the returned list instead.
512
    /// 
513
    /// <para>IMPORTANT: Order is NOT PERSERVED by this method so don't bother trying to sneak an Order by command into your select query </para>
514
    /// </summary>
515
    /// <typeparam name="T"></typeparam>
516
    /// <param name="selectQuery"></param>
517
    /// <param name="parameters"></param>
518
    /// <param name="columnWithObjectID"></param>
519
    /// <param name="dbNullSubstition"></param>
520
    /// <returns></returns>
521
    public IEnumerable<T> SelectAllWhere<T>(string selectQuery, string columnWithObjectID = null,
522
        Dictionary<string, object> parameters = null, T dbNullSubstition = default)
523
        where T : IMapsDirectlyToDatabaseTable
524
    {
525
        columnWithObjectID ??= $"{typeof(T).Name}_ID";
2,682!
526

527
        if (selectQuery.ToLower().Contains("order by "))
2,682!
UNCOV
528
            throw new Exception("Select Query contained an ORDER BY statement in it!");
×
529

530
        var nullsFound = 0;
2,682✔
531

532
        using var opener = GetConnection();
2,682✔
533
        var idsToReturn = new List<int>();
2,682✔
534
        var cmd = PrepareCommand(selectQuery, parameters, opener.Connection, opener.Transaction);
2,682✔
535
        using (var r = cmd.ExecuteReader())
2,682✔
536
        {
537
            while (r.Read())
3,164✔
538
            {
539
                if (r[columnWithObjectID] == DBNull.Value)
482!
540
                {
UNCOV
541
                    nullsFound++;
×
542
                    continue;
×
543
                }
544

545
                idsToReturn.Add(Convert.ToInt32(r[columnWithObjectID]));
482✔
546
            }
547
        }
2,682✔
548

549
        if (!idsToReturn.Any())
2,682✔
550
            return Enumerable.Empty<T>();
2,226✔
551

552

553
        var toReturn = GetAllObjects<T>($"WHERE ID in ({string.Join(",", idsToReturn)})").ToList();
456✔
554

555
        //this bit of hackery is if you're a crazy person who hates transparency and wants something like ColumnInfo.Missing to appear in the return list instead of an empty return list
556
        if (dbNullSubstition != null)
456!
UNCOV
557
            for (var i = 0; i < nullsFound; i++)
×
558
                toReturn.Add(dbNullSubstition);
×
559

560
        return toReturn;
456✔
561
    }
2,682✔
562

563

564
    private int InsertAndReturnID<T>(Dictionary<string, object> parameters = null)
565
        where T : IMapsDirectlyToDatabaseTable
566
    {
567
        using var opener = GetConnection();
54,232✔
568
        var query = CreateInsertStatement<T>(parameters);
54,232✔
569

570
        query += ";SELECT @@IDENTITY;";
54,232✔
571

572
        var cmd = PrepareCommand(query, parameters, opener.Connection, opener.Transaction);
54,232✔
573
        return int.Parse(cmd.ExecuteScalar().ToString());
54,232✔
574
    }
54,228✔
575

576
    private string CreateInsertStatement<T>(Dictionary<string, object> parameters)
577
        where T : IMapsDirectlyToDatabaseTable
578
    {
579
        _logger.Info($"Created New,{typeof(T).Name}");
54,232✔
580

581
        var query = $"INSERT INTO {Wrap(typeof(T).Name)}";
54,232✔
582
        if (parameters != null && parameters.Any())
54,232!
583
        {
584
            if (parameters.Any(kvp => kvp.Key.StartsWith("@")))
230,244!
UNCOV
585
                throw new InvalidOperationException(
×
586
                    $"Invalid parameters for {typeof(T).Name} INSERT. Do not use @ when specifying parameter names, this is SQL-specific and will be added when required: {string.Join(", ", parameters.Where(kvp => kvp.Key.StartsWith("@")))}");
×
587

588
            var columnString = string.Join(", ", parameters.Select(kvp => Wrap(kvp.Key)));
230,244✔
589
            var parameterString = string.Join(", ", parameters.Select(kvp => $"@{kvp.Key}"));
230,244✔
590
            query += $"({columnString}) VALUES ({parameterString})";
54,232✔
591
        }
592
        else
593
        {
UNCOV
594
            query += " DEFAULT VALUES";
×
595
        }
596

597
        return query;
54,232✔
598
    }
599

600

601
    public int Delete(string deleteQuery, Dictionary<string, object> parameters = null,
602
        bool throwOnZeroAffectedRows = true)
603
    {
604
        using var opener = GetConnection();
222✔
605
        var cmd = PrepareCommand(deleteQuery, parameters, opener.Connection, opener.Transaction);
222✔
606
        var affectedRows = cmd.ExecuteNonQuery();
222✔
607

608
        return affectedRows == 0 && throwOnZeroAffectedRows
222!
609
            ? throw new Exception($"Deleted failed, resulted in {affectedRows} affected rows")
222✔
610
            : affectedRows;
222✔
611
    }
222✔
612

613
    public int Update(string updateQuery, Dictionary<string, object> parameters)
614
    {
615
        using var opener = GetConnection();
11,536✔
616
        var cmd = PrepareCommand(updateQuery, parameters, opener.Connection, opener.Transaction);
11,536✔
617
        return cmd.ExecuteNonQuery();
11,536✔
618
    }
11,536✔
619

620
    public static DbCommand PrepareCommand(string sql, Dictionary<string, object> parameters, DbConnection con,
621
        DbTransaction transaction = null)
622
    {
623
        var cmd = DatabaseCommandHelper.GetCommand(sql, con, transaction);
290,871✔
624
        return parameters == null ? cmd : PrepareCommand(cmd, parameters);
290,871✔
625
    }
626

627
    public static DbCommand PrepareCommand(DbCommand cmd, Dictionary<string, object> parameters)
628
    {
629
        foreach (var kvp in parameters)
1,407,008✔
630
        {
631
            var paramName = kvp.Key.StartsWith("@") ? kvp.Key : $"@{kvp.Key}";
414,471✔
632

633
            // Check that this parameter name actually exists in the sql
634
            if (!cmd.CommandText.Contains(paramName))
414,471!
UNCOV
635
                throw new InvalidOperationException(
×
636
                    $"Parameter '{paramName}' does not exist in the SQL command ({cmd.CommandText})");
×
637

638
            //if it isn't yet in the command add it
639
            if (!cmd.Parameters.Contains(paramName))
414,471✔
640
                cmd.Parameters.Add(DatabaseCommandHelper.GetParameter(paramName, cmd));
414,471✔
641

642
            //set its value
643
            SetParameterToValue(cmd.Parameters[paramName], kvp.Value);
414,471✔
644
        }
645

646
        return cmd;
289,033✔
647
    }
648

649
    #endregion
650

651
    public void InsertAndHydrate<T>(T toCreate, Dictionary<string, object> constructorParameters)
652
        where T : IMapsDirectlyToDatabaseTable
653
    {
654
        var id = InsertAndReturnID<T>(constructorParameters);
54,232✔
655

656
        var actual = GetObjectByID<T>(id);
54,228✔
657

658
        //.Repository does not get included in this list because it is [NoMappingToDatabase]
659
        foreach (var prop in GetPropertyInfos(typeof(T)))
1,521,472✔
660
            prop.SetValue(toCreate, prop.GetValue(actual));
706,508✔
661

662
        toCreate.Repository = actual.Repository;
54,228✔
663

664
        NewObjectPool.Add(toCreate);
54,228✔
665

666
        Inserting?.Invoke(this, new IMapsDirectlyToDatabaseTableEventArgs(toCreate));
54,228✔
667
    }
24✔
668

669
    private object ongoingConnectionsLock = new();
994✔
670
    private readonly Dictionary<Thread, IManagedConnection> ongoingConnections = new();
994✔
671
    private readonly Dictionary<Thread, IManagedTransaction> ongoingTransactions = new();
994✔
672

673

674
    public IManagedConnection GetConnection()
675
    {
676
        //any existing ongoing connection found on this Thread
677
        GetOngoingActivitiesFromThreadsDictionary(out var ongoingConnection, out var ongoingTransaction);
664,636✔
678

679
        //if we are in the middle of doing stuff we can just reuse the ongoing one
680
        if (ongoingConnection != null &&
664,636✔
681
            ongoingConnection.Connection.State ==
664,636✔
682
            ConnectionState.Open) //as long as it hasn't timed out or been disposed etc
664,636✔
683
            if (ongoingConnection.CloseOnDispose)
47,525!
684
            {
685
                var clone = ongoingConnection.Clone();
47,525✔
686
                clone.CloseOnDispose = false;
47,525✔
687
                return clone;
47,525✔
688
            }
689
            else
690
            {
UNCOV
691
                return ongoingConnection;
×
692
            }
693

694
        ongoingConnection = DiscoveredServer.GetManagedConnection(ongoingTransaction);
617,111✔
695

696
        //record as the active connection on this thread
697
        ongoingConnections[Thread.CurrentThread] = ongoingConnection;
617,107✔
698

699
        return ongoingConnection;
617,107✔
700
    }
701

702
    private void GetOngoingActivitiesFromThreadsDictionary(out IManagedConnection ongoingConnection,
703
        out IManagedTransaction ongoingTransaction)
704
    {
705
        lock (ongoingConnectionsLock)
664,824✔
706
        {
707
            //see if Thread dictionary has it
708
            if (!ongoingConnections.TryGetValue(Thread.CurrentThread, out ongoingConnection))
664,824✔
709
                ongoingConnections.Add(Thread.CurrentThread, null);
1,114✔
710

711
            //see if Thread dictionary has it
712
            if (!ongoingTransactions.TryGetValue(Thread.CurrentThread, out ongoingTransaction))
664,824✔
713
                ongoingTransactions.Add(Thread.CurrentThread, null);
1,114✔
714
        }
664,824✔
715
    }
664,824✔
716

717
    public IManagedConnection BeginNewTransactedConnection()
718
    {
719
        GetOngoingActivitiesFromThreadsDictionary(out _, out var ongoingTransaction);
94✔
720

721
        if (ongoingTransaction != null)
94!
UNCOV
722
            throw new NotSupportedException(
×
723
                "There is already an ongoing transaction on this Thread! Call EndTransactedConnection on the last one first");
×
724

725
        var toReturn = DiscoveredServer.BeginNewTransactedConnection();
94✔
726
        ongoingTransaction = toReturn.ManagedTransaction;
94✔
727
        ongoingTransactions[Thread.CurrentThread] = ongoingTransaction;
94✔
728

729
        ongoingConnections[Thread.CurrentThread] = toReturn;
94✔
730
        if (DiscoveredServer.DatabaseType == DatabaseType.MicrosoftSQLServer)
94✔
731
        {
732
            using var cmd = toReturn.Connection.CreateCommand();
92✔
733
            cmd.Transaction = toReturn.Transaction;
92✔
734
            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
92✔
735
            cmd.ExecuteNonQuery();
92✔
736
        }
737

738
        return toReturn;
94✔
739
    }
740

741
    /// <summary>
742
    /// True to commit, false to abandon
743
    /// </summary>
744
    /// <param name="commit"></param>
745
    public void EndTransactedConnection(bool commit)
746
    {
747
        GetOngoingActivitiesFromThreadsDictionary(out _, out var ongoingTransaction);
94✔
748

749
        if (ongoingTransaction == null)
94!
UNCOV
750
            throw new NotSupportedException(
×
751
                "There is no ongoing transaction on this Thread, did you try to close the Transaction from another Thread? or did you maybe never start one in the first place?");
×
752

753
        if (commit)
94✔
754
            ongoingTransaction.CommitAndCloseConnection();
78✔
755
        else
756
            ongoingTransaction.AbandonAndCloseConnection();
16✔
757

758
        ongoingConnections[Thread.CurrentThread] = null;
94✔
759
        ongoingTransactions[Thread.CurrentThread] = null;
94✔
760
    }
94✔
761

762

763
    public void ClearUpdateCommandCache()
764
    {
UNCOV
765
        lock (_oLockUpdateCommands)
×
766
        {
UNCOV
767
            _updateCommandStore.Clear();
×
768
        }
×
769
    }
×
770

771
    public int? ObjectToNullableInt(object o) => o == null || o == DBNull.Value ? null : int.Parse(o.ToString());
41,608✔
772

773
    public DateTime? ObjectToNullableDateTime(object o) => o == null || o == DBNull.Value ? null : (DateTime)o;
40,000!
774

775
    private Dictionary<Type, bool> _knownSupportedTypes = new();
994✔
776
    private object oLockKnownTypes = new();
994✔
777

778
    public bool SupportsObjectType(Type type)
779
    {
780
        if (!typeof(IMapsDirectlyToDatabaseTable).IsAssignableFrom(type))
214!
UNCOV
781
            throw new NotSupportedException(
×
782
                "This method can only be passed Types derived from IMapsDirectlyToDatabaseTable");
×
783

784
        lock (oLockKnownTypes)
214✔
785
        {
786
            if (!_knownSupportedTypes.ContainsKey(type))
214✔
787
                _knownSupportedTypes.Add(type, DiscoveredServer.GetCurrentDatabase().ExpectTable(type.Name).Exists());
42✔
788

789
            return _knownSupportedTypes[type];
214✔
790
        }
791
    }
214✔
792

793

794
    public void SaveSpecificPropertyOnlyToDatabase(IMapsDirectlyToDatabaseTable entity, string propertyName,
795
        object propertyValue)
796
    {
797
        var prop = entity.GetType().GetProperty(propertyName);
11,490✔
798
        prop.SetValue(entity, propertyValue);
11,490✔
799

800
        //don't put in the enum number put in the free text enum value (that's what we do everywhere else)
801
        if (prop.PropertyType.IsEnum)
11,490!
UNCOV
802
            propertyValue = propertyValue.ToString();
×
803

804
        Update($"UPDATE {Wrap(entity.GetType().Name)} SET {propertyName}=@val WHERE ID = {entity.ID}",
11,490✔
805
            new Dictionary<string, object>
11,490✔
806
            {
11,490✔
807
                { "@val", propertyValue ?? DBNull.Value }
11,490✔
808
            });
11,490✔
809
    }
11,490✔
810

811
    public int Insert(string sql, Dictionary<string, object> parameters)
812
    {
813
        using var opener = GetConnection();
1,468✔
814
        using var cmd = PrepareCommand(sql, parameters, opener.Connection, opener.Transaction);
1,468✔
815
        return cmd.ExecuteNonQuery();
1,468✔
816
    }
1,468✔
817

818
    private Type[] _compatibleTypes;
819

820
    public event EventHandler<SaveEventArgs> Saving;
821
    public event EventHandler<IMapsDirectlyToDatabaseTableEventArgs> Inserting;
822
    public event EventHandler<IMapsDirectlyToDatabaseTableEventArgs> Deleting;
823

824
    public IMapsDirectlyToDatabaseTable[] GetAllObjectsInDatabase()
825
    {
UNCOV
826
        _compatibleTypes ??= GetCompatibleTypes();
×
827

828
        try
829
        {
UNCOV
830
            return _compatibleTypes.SelectMany(GetAllObjects).ToArray();
×
831
        }
UNCOV
832
        catch (Exception e)
×
833
        {
834
            throw new Exception(
×
UNCOV
835
                $"Failed to GetAllObjects of Type '{string.Join(',', _compatibleTypes.Select(t => t.FullName))}'", e);
×
836
        }
UNCOV
837
    }
×
838

839

840
    /// <inheritdoc/>
841
    public Type[] GetCompatibleTypes()
842
    {
843
        return
16✔
844
            GetType().Assembly.GetTypes()
16✔
845
                .Where(
16✔
846
                    t =>
16✔
847
                        typeof(IMapsDirectlyToDatabaseTable).IsAssignableFrom(t)
37,952✔
848
                        && !t.IsAbstract
37,952✔
849
                        && !t.IsInterface
37,952✔
850

37,952✔
851
                        //nothing called spontaneous
37,952✔
852
                        && !t.Name.Contains("Spontaneous")
37,952✔
853

37,952✔
854
                        //or with a spontaneous base class
37,952✔
855
                        && (t.BaseType == null || !t.BaseType.Name.Contains("Spontaneous"))
37,952✔
856
                        && IsCompatibleType(t)
37,952✔
857
                ).ToArray();
16✔
858
    }
859

860
    /// <summary>
861
    /// Returns True if the type is one for objects that are held in the database.  Types will come from your repository assembly
862
    /// and will include only <see cref="IMapsDirectlyToDatabaseTable"/> Types that are not abstract/interfaces.  Types are only
863
    /// compatible if an accompanying <see cref="DiscoveredTable"/> exists in the database to store the objects.
864
    /// </summary>
865
    /// <param name="type"></param>
866
    /// <returns></returns>
867
    protected virtual bool IsCompatibleType(Type type)
868
    {
869
        return _tables.Value.Any(t => t.GetRuntimeName().Equals(type.Name));
45,848✔
870
    }
871

UNCOV
872
    public virtual T[] GetAllObjectsNoCache<T>() where T : IMapsDirectlyToDatabaseTable => GetAllObjects<T>();
×
873

874
    public IDisposable BeginNewTransaction() => BeginNewTransactedConnection();
76✔
875

876
    public void EndTransaction(bool commit)
877
    {
878
        EndTransactedConnection(commit);
76✔
879
    }
76✔
880
}
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2025 Coveralls, Inc