'How to send a list of objects, where the objects contain another list of objects to a stored procedure?

This is the C# code that sends data to a stored procedure:

using (var sqlConnection = new SqlConnection(_dbContext.GetDbConnection().ConnectionString))
{
    sqlConnection.Open();
    var sqlCommand = new SqlCommand("dbo.InsertDetails", sqlConnection)
    {
        CommandType = CommandType.StoredProcedure
    };

    var detailsDataTable = new DataTable();

    new Detail().GetType().GetProperties().ToList()
        .ForEach(x => detailsDataTable.Columns.Add(x.Name,
        Nullable.GetUnderlyingType(x.PropertyType) ?? x.PropertyType));

    var data = detailsList
        .Select(x => x.GetType().GetProperties().Select(y => y.GetValue(x, null)).ToArray()).ToList();

    data.ForEach(x => detailsDataTable.Rows.Add(x));

    SqlParameter movieAssignmentsParameter = sqlCommand.Parameters.AddWithValue("@DetailsTableType", detailsDataTable);
    movieAssignmentsParameter.SqlDbType = SqlDbType.Structured;

    sqlCommand.ExecuteReader();
}

And this works fine, there are no errors, the @DetailsTableType parameter is received by the stored procedure. However the issue here is that the Detail class should contain a List<DetailInfo> property (a one-to-many relationship), and each DetailInfo must have a DetailId which is the Id of Detail. Those Ids do not exist yet, and the detailsList should be inserted first and then the Ids should be assigned to each element in each List<DetailInfo>.

One solution that I thought of (haven't tried it out yet) is to create another table type in the database called DetailInfoTableType and send all List<DetailInfo> as an additional parameter - detailsList.SelectMany(x => x.DetailInfoList) with an additional property in both Detail and DetailInfo called TempId and TempDetailId respectively, which would just be an incrementing count. After that I would map them in the stored procedure and assign all the created DetailId's to the correct DetailInfo rows.

My question is, is there a simpler, more straightforward way to do this, without assigning temporary Ids?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source