What is the suitable way to insert/update bulk records (array of JSON data)?

ServiceStack.OrmLite
1

Hi,

I have written a web service which will insert single record for single request. Following is the structure of code-

POCO class

[Alias("OperatingSystem")]
public partial class OperatingSystem : IHasId<int> 
{
    public OperatingSystem()
    {
    }
    [Alias("Id")]
    [AutoIncrement]
    public int Id { get; set; }
    [References(typeof(Client))]
    [Required]
    public int ClientId { get; set; }
    [Required]
    public string Name { get; set; }
    [References(typeof(OperatingSystemType))]
    public int? OperatingSystemTypeId { get; set; }
    public string OperatingSystemVendor { get; set; }
    public string OperatingSystemVersion { get; set; }
    public string OperatingSystemPatchLevel { get; set; }
    public string Description { get; set; }
    [Required]
    public DateTime CreatedOn { get; set; }
    [Required]
    public string CreatedBy { get; set; }
    public DateTime? UpdatedOn { get; set; }
    public string UpdatedBy { get; set; }
    [Compute]        
    [Required]
    public string Display { get; set; }
}

Request DTO

[Api("CRUD for OperatingSystem")]
[Route("/OperatingSystem", "POST", Summary = "Create an OperatingSystem")]
public class CreateOperatingSystemRequestDto : IReturn<CreateOperatingSystemRequestDtoResponse>
{   
    [ApiMember(IsRequired = true)]
    public string Name { get; set; }        

    [ApiMember]
    public int? OperatingSystemTypeId { get; set; }

    [ApiMember]
    public string OperatingSystemVendor { get; set; }

    [ApiMember]
    public string OperatingSystemVersion { get; set; }

    [ApiMember]
    public string OperatingSystemPatchLevel { get; set; }

    [ApiMember]
    public string Description { get; set; }
    [IgnoreDataMember]
    public bool Bulk { get; set; }
}

Response DTO-

public class CreateOperatingSystemRequestDtoResponse : IHasResponseStatus
{
    public int Id { get; set; }
    public ResponseStatus ResponseStatus { get; set; }
}

Service Interface

[Authenticate]
public sealed class OperatingSystemService : ServiceStack.Service
{
    private readonly IOperatingSystemLogic _logic;

    public OperatingSystemService()
    {
        _logic = new OperatingSystemLogic(this.SessionAs<InventoryUserSession>());
    }

    public CreateOperatingSystemRequestDtoResponse Post(CreateOperatingSystemRequestDto requestDto)
    {            
        var result = new CreateOperatingSystemRequestDtoResponse();            
        var returnValue = _logic.CreateOperatingSystem(requestDto.ConvertTo<OperatingSystemModelBo>());

        result.Id = returnValue;
        result.ResponseStatus = returnValue > 0
            ? new ResponseStatus() { Message = "Operating System created successfully." }
            : new ResponseStatus() { Message = "Operating System creation failed.", ErrorCode = "Create failed" };

        return result;
    }

    public UpdateOperatingSystemRequestDtoResponse Put(UpdatOperatingSystemRequestDto request)
    {
        var result = new UpdateOperatingSystemRequestDtoResponse();           
        var returnValue = _logic.UpdateOperatingSystem(request.ConvertTo<OperatingSystemModelBo>());

        result.ResponseStatus = returnValue == 1
            ? new ResponseStatus() { Message = "Operating System updated successfully." }
            : new ResponseStatus() { Message = "Operating System update failed.", ErrorCode = "Update failed" };
        return result;
    }

    public DeleteOperatingSystemResponseDto Delete(DeleteOperatingSystemRequestDto request)
    {
        var result = new DeleteOperatingSystemResponseDto();            
        var returnValue = _logic.DeleteOperatingSystem(request.OperatingSystemId, request.ForceDelete);

        result.ResponseStatus = returnValue == 1
                                    ? new ResponseStatus() { Message = "Operating System deleted successfully." }
                                    : new ResponseStatus() { Message = "Operating System delete failed.", ErrorCode = "Delete failed" };

        return result;
    }
}

And at Data layer, I am opening ormlite connection, do the transaction and then closing the connection.
This code structure works fine for single request.

Now, I wanted to leverage same code for bulk data. This bulk data will be in the form of array of object as input. Hence, I created wrapper on top of single request object like-

[Authenticate]
[Api("CRUD for bulk OperatingSystems")]
[Route("/OperatingSystemBulk", "POST", Summary = "Create bulk OperatingSystems")]
public class CreateOperatingSystemBulkRequestDto : List of CreateOperatingSystemRequestDto
{
}

Also, I have created a seperate servcie layer which will map single service like-

[Authenticate]
public sealed class OperatingSystemServiceBulk : Service
{
    private readonly IOperatingSystemLogic _logic;
    private OperatingSystemService _service;

    public OperatingSystemServiceBulk()
    {
        _logic = new OperatingSystemLogic(this.SessionAs<InventoryUserSession>());
        _service = new OperatingSystemService();
    }
    public CreateOperatingSystemBulkRequestDtoResponse Post(CreateOperatingSystemBulkRequestDto requestDtos)
    {
        var results = new CreateOperatingSystemBulkRequestDtoResponse();
        requestDtos.ForEach(req => req.Bulk = true);
        results.AddRange(requestDtos.Map(x => _service.Post(x)));
        return results;
    }
    public UpdateOperatingSystemBulkRequestDtoResponse Put(UpdateOperatingSystemBulkRequestDto requests)
    {
        var results = new UpdateOperatingSystemBulkRequestDtoResponse();
        requests.ForEach(req => req.Bulk = true);
        results.AddRange(requests.Map(x => _service.Put(x)));
        return results;
    }
}

Following code will execute single service multiple times-
requestDtos.Map(x => _service.Post(x)

However, during execution it throws timeout exception for 25 rows of data-

image.png1225×460 185 KB

Note - I was following - ‘Defining a Request DTO to accept a collection of Types’ from https://github.com/ServiceStack/ServiceStack/wiki/Auto-Batched-Requests.

Can you pls. suggest me solution to overcome this exception so that it works for 5k records ?

Thanks in Advance

2

Please put all your C# code in code blocks:

```csharp
// C# code
```

And please properly format/indent your code as your posts containing code is always unreadable (as it’s time consuming having to constantly clean up after yourself), have a look at the edit source raw text for your post to see how to properly create posts with code blocks:

image.png1450×1561 230 KB

The Exception indicates you have too many concurrent connections open, so you should dispose them as soon as you’re finished with them, i.e. within a using block:

using (var db = ConnectionFactory.Open())
{
    // use db connection
}
3

Thanks for your advice. However, I am invoking ExecuteNonQuery method to impose access level/security before going for transaction. And here I am getting above exception with implementation of using statement-

image.png1034×321 54.9 KB

Before proceeding ahead, let me clear you idea, on what we are doing (logic) to achieve our goal-

  1. When bulk record (JSON array) is received from Azure function using POST web service, we are first checking whether record exists or not, one by one using get method and passing ‘Name’(Primary field) to it.
  2. If record found we are copying its id to received data and redirecting it to Update method.
  3. If record doesn’t exists, redirect to Insert method.
  4. So, these steps are followed for each record one by one.
    During execution, we are sticking to access level security.
4

This error indicates the connection instance has been closed by something else you have that used that instance.

Open and close a new connection as I’ve shown in my example:

using (var db = ConnectionFactory.Open())
{
    // use db connection
}