Monthly Archives: August 2022
Dapper 1:n Relation
|
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 |
private IEnumerable<SqlDataRecord> CreateTestObjectSqlDataRecords(IEnumerable<TestObject> testObjects) { SqlMetaData[] metaData = new SqlMetaData[3]; metaData[0] = new SqlMetaData("TestObjectId", SqlDbType.Int); metaData[1] = new SqlMetaData("Name", SqlDbType.NVarChar, maxLength: 255); metaData[2] = new SqlMetaData("Something", SqlDbType.NVarChar, maxLength: 1024); SqlDataRecord record = new SqlDataRecord(metaData); foreach (var testObject in testObjects) { record.SetInt32(0, testObject.TestObjectId); record.SetString(1, testObject.Name); record.SetString(2, testObject.Something); yield return record; } } public async Task<Setting?> SaveAsync(Setting setting) { try { int settingId = await new SqlConnection(ConnectionString).ExecuteScalarAsync<int>( sql: "[dbo].[spSetting_Save]", param: new { SettingId = setting.SettingId, TestObjects = CreateTestObjectSqlDataRecords(setting.TestObjects).AsTableValuedParameter(typeName: "dbo.TestObject"), }, commandType: System.Data.CommandType.StoredProcedure); if (settingId > 0) { return await GetSettingAsync(settingId); } else { return await GetSettingAsync(setting.SettingId); } } catch (Exception ex) { this.Logger.Log(LogLevel.Error, ex); throw; } } CREATE PROCEDURE [dbo].[spSetting_Save] @SettingId INT , @TestObjects [dbo].[TestObject] READONLY AS IF(@SettingId <= 0) BEGIN INSERT INTO [dbo].[tblSetting] ( ...) OUTPUT Inserted.SettingId VALUES( ... ) SET @SettingId = SCOPE_IDENTITY(); EXEC [dbo].[spTestObjects_Save] @SettingId, @TestObjects END ELSE BEGIN UPDATE [dbo].[tblSetting] SET ... WHERE [SettingId] = @SettingId EXEC [dbo].[spTestObjects_Save] @SettingId, @Variables END CREATE PROCEDURE [dbo].[spTestObjects_Save] @SettingId INT , @Variables [dbo].[TestObject] READONLY AS MERGE [dbo].[TestObject] AS TARGET USING @TestObject AS SOURCE ON (TARGET.[SettingId] = @SettingId AND TARGET.[TestObjectId] = SOURCE.[TestObjectId]) WHEN MATCHED THEN UPDATE SET TARGET.[Name] = SOURCE.[Name], TARGET.[Name2] = SOURCE.[Name2] WHEN NOT MATCHED BY TARGET THEN INSERT ([SettingId], [Name], [Name2]) VALUES (@SettingId, SOURCE.[Name], SOURCE.[Name2]) public async Task<Setting?> GetSettingAsync(int settingId) { Setting? setting = null; await new SqlConnection(ConnectionString).QueryAsync<Setting, TestObject, Setting>( sql: "[dbo].[spSetting_Select]", param: new { SettingId = settingId }, commandType: System.Data.CommandType.StoredProcedure, splitOn: "TestObjectId", map: (qrSetting, qrTestObject) => { if (qrSetting is null) return false; if (setting is null) setting = qrSetting; if (qrTestObject is not null) { if (!setting.Variables.Any(x => x.TestObjectId == qrTestObject.TestObjectId)) setting.TestObjects.Add(qrTestObject); } if (qrTestObject2 is not null) { ... } return setting; }); return setting; } |
0 
Login