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;
}
Login