0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ghazni
  • 826
  • 8
  • 16
  • 1
    **Side note**: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Jul 27 '23 at 14:18
  • Based on the SQL syntax I assume SQL Server. This means you can't have hierarchal user defined table types, but you can, however, use more than one udt as arguments to your stored procedure. The main problem is to sync what I believe is an `identity` column (of the member) with the foreign keys in the related tables - for that, You can use some version of my proposed solution in [this SO post.](https://stackoverflow.com/a/38217498/3094533) – Zohar Peled Jul 27 '23 at 14:31
  • You need to show us more of what you are trying to do, what do these tables look like and what are you inserting into them? "Calling stored procedure through Dapper:" the above code does not use Dapper, it uses SqlCommand directly. Also your procedure's error handling is broken, it will just swallow errors. Instead remove the `BEGIN TRY...BEGIN CATCH...ROLLBACK` etc and just put `SET XACT_ABORT ON;` at the top. You should also use a case-insensitive collation, rather than using `LOWER` – Charlieface Jul 27 '23 at 16:04
  • Also `catch (Exception ex) { throw ex; }` is a bad idea, it will just wipe the stack trace. And you don't need the `finally Dispose` because the `using` will do that anyway. – Charlieface Jul 27 '23 at 22:45

0 Answers0