1_data_type_usage.cs

private void SaveWhiteBlackListDataImportFile(IEnumerable<PartInfoImportFile> parts, int userID)
{
        using (var sqlConnection = new SqlConnection(_connectionString))
        {
                sqlConnection.Open();
                
                using (var multi = sqlConnection.QueryMultiple("dbo.SavePartInfoImportFileData",
                        new PartInfoDynamicParam("ParameterTable", parts, userID),
                        commandType: CommandType.StoredProcedure, commandTimeout: 120)) {
                }
                
                sqlConnection.Close();
        }
}

2_data_type_definition.cs

private class PartInfoDynamicParam : Dapper.SqlMapper.IDynamicParameters
        {
            string name;
            int userID;
            IEnumerable<PartInfoImportFile> files;

            public PartInfoDynamicParam(string name, IEnumerable<PartInfoImportFile> files, int userID)
            {
                this.name = name;
                this.files = files;
                this.userID = userID;
            }

            public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
            {
                var sqlCommand = (SqlCommand)command;
                sqlCommand.CommandType = CommandType.StoredProcedure;

                List<Microsoft.SqlServer.Server.SqlDataRecord> file_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>();
                Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { 
                                         new SqlMetaData("PartID", System.Data.SqlDbType.Int)//Max length has to be specified
                                         ,new SqlMetaData("PartNumber", System.Data.SqlDbType.NVarChar, 100)//same story
                                         ,new SqlMetaData("IsInBlackList", System.Data.SqlDbType.Bit)//same story
                                         ,new SqlMetaData("Region", System.Data.SqlDbType.NVarChar, 10)//same story
                                         ,new SqlMetaData("Enabled", System.Data.SqlDbType.Bit)//same story
                                         ,new SqlMetaData("EngineeringGroupID", System.Data.SqlDbType.Int)//same story
                                     };
                foreach (PartInfoImportFile file in files)
                {
                    Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);

                    rec.SetInt32(0, file.PartID.GetValueOrDefault(-1));
                    rec.SetString(1, file.PartNumber);
                    rec.SetBoolean(2, file.IsInBlackList);
                    rec.SetString(3, file.Region);
                    rec.SetBoolean(4, file.Enabled);
                    rec.SetInt32(5, file.EngineeringGroupID.GetValueOrDefault(-1));

                    file_list.Add(rec);
                }

                var param1 = sqlCommand.Parameters.Add(name, SqlDbType.Structured);
                param1.TypeName = "PartInfoTableType";
                param1.Value = file_list;

                var param2 = sqlCommand.Parameters.Add("userID", SqlDbType.Int);
                param2.Value = this.userID;
            }

        }

3_rules_profile_create_sample.sql

CREATE TYPE [dbo].RulesGroupTableType AS TABLE (
	 [GroupGuid] char(32) NOT NULL
	,[GroupDescription] varchar(100) NOT NULL
)

CREATE TYPE [dbo].RulesTableType AS TABLE (
	 [RuleTypeID] int NOT NULL
	,[GroupGuid] char(32) NOT NULL
	,[RuleConfiguration] xml NOT NULL
)

GO

CREATE PROCEDURE CreateProfile (
	 @ProfileName varchar(50)
	,@Groups dbo.RulesGroupTableType 
	,@Rules dbo.RulesTableType
)
AS 
BEGIN
	BEGIN TRY
		BEGIN TRAN

		INSERT INTO RuleProfile (ProfileName) 
		VALUES (@ProfileName)

		select *
		into #groups
		from @Groups -- temp aux table used to iterate over groups

		while exists (select 1 from #groups) begin
			declare @guid varchar = (select top 1 GroupGuid from #groups)

			INSERT INTO RulesGroup (GroupDescription)
			SELECT [GroupDescription]
			FROM @Groups 
			WHERE [GroupGuid] = @guid

			declare @groupID int = scope_identity()

			INSERT INTO RulesDetail (RuleTypeID, GroupID, RuleConfiguration)
			SELECT [RuleTypeID], @groupID, [RuleConfiguration]
			FROM @Rules
			WHERE [GroupGuid] = @guid

			delete #groups where [Guid] = @guid
		end

		COMMIT TRAN
	END TRY

	BEGIN CATCH
		ROLLBACK TRAN
		EXEC ThrowException
	END CATCH
END
GO

CREATE PROCEDURE ThrowException
AS BEGIN
	DECLARE @errorMessage nvarchar(4000),	@errorSeverity int
	SELECT @errorMessage = ERROR_MESSAGE(),	@errorSeverity = ERROR_SEVERITY()
	RAISERROR(@errorMessage, @errorSeverity, 1)
END
GO