Member class contains its services and types collection:
public record Member
{
public int id { get; set; }
public string name { get; set; }
public ICollection<Service>? Services { get; set; }
public ICollection<Type>? Types { get; set; }
}
Calling stored procedure through Dapper:
public async Task<List<Member>> BulkSave(List<Member> items)
{
var dt = ToDataTable(items);
string sp = "SP_Member_BulkSave";
using SqlConnection conn = new SqlConnection(_context.ConnectionString);
using SqlCommand command = new SqlCommand(sp, conn);
try
{
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@tbl", dt);
command.ExecuteNonQuery();
return items;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
Insert/Update using MERGE statement in SQL and passing the List<Member>
as DataTable to the stored procedure.
CREATE PROCEDURE [dbo].[SP_Member_BulkSave]
@tbl udtMember READONLY
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
MERGE dbo.[Member] AS TARGET
USING @tbl source ON AND LOWER(source.name) = LOWER(TARGET.name)
WHEN NOT MATCHED BY TARGET
THEN
INSERT INTO (NAME) VALUES (source.name)
WHEN MATCHED
THEN
UPDATE
SET TARGET.name = source.name;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
This is working fine while inserting/updating the Member. but how to insert/update member's related services and types?