Need help to Save data in to SQL Server DB using NServiceBus


(Srikanta Swain) #1

Hi,

I am trying to save my data in to SQL Server DB, but not able to do. Tried with multiple scenarios like using Saga, NHibernate, OutBox, but still getting some confusion.

I have a class having more than 10 properties which I need to store in to the DB. Below is the sample…

public int Id { get; set; }
public string Description { get; set; }
public int Vin { get; set; }
public string VinDesc { get; set; }
public string Comments { get; set; }
public string Status { get; set; }

This entire value needs to store in to DB with a customized table called VinDetails_Table and all properties will be the column name of this table.

As soon as saved the data there will be another process which will retrieve the data from the same table and do some business manipulation and post the same to another endpoint.

Still I am not sure which is the best approach for my requirement and how can I get some sample code to achieve my requirement.

Please help me to identify the best way to achieve my requirement. Kindly advise if I need to more details about my requirement.

Thank you in advance.


(Tim Bussmann) #2

Hey @Srikanta,

NServiceBus does not directly store business data for you. This is because there are many different approaches and storage options to do this and NServiceBus is mostly a messaging and workflow framework. NServiceBus allows you to store your business data in any way you want. A simplified approach would look like this:

  • Write a message handler which will be invoked on an incoming message of a specific message type.
  • In the message handler code, store your data to your preferred database (e.g. SQL Server in your case) by using whatever approach you would like. You can write raw SQL Queries, use some lightweight SQL query libraries or full blown OR mappers like Entity Framework or NHibernate.
  • You handler can send out new messages to itself or other endpoints to kick off further processes.

Note: It sounds like you would like to share a database between different endpoints. This is a pattern we don’t recommend in most cases. Have you considered to send the data for the second endpoint as part of your message instead of sharing it via database?

NServiceBus also supports stateful workflows using Sagas. When using Sagas, NServiceBus uses a database to store the stateful workflow. NServiceBus supports different options for Saga storages like SQL Server, RavenDB and more. In the case of Sagas, the database tables used are created by NServiceBus and you won’t be able to define every detail on how the data is stored. You can read more about Sagas here: https://docs.particular.net/nservicebus/sagas/

Does that answer your question?


(Srikanta Swain) #3

Hello Tim,

Thank you so much for your support.

Coming to the point of your ‘Note’ section, we will not be sharing the entire database between endpoints. The requirement is like I need to read some data in batch wise from one source(Can be a service/API…) , then normalize/customize the data and store it to Database in a structured format. For me the database table structure is already defined, So in my case I need to use the custom SQL queries.

There may be another endpoint/service which will pull the data from the database based on some condition and process further to another source( Can be a Service/API/DB).

I tried to use saga, but will not have any control over the table structure to store the customized data.

Probably I may ask more question in future related to NServiceBus, hope it will be nice interaction. :wink:

Thank you in advance.


(Tim Bussmann) #4

For me the database table structure is already defined, So in my case I need to use the custom SQL queries.

If the database table structure is predefined, going with custom SQL queries is indeed the way I’d recommend.

As already mentioned before, consider shipping data as part of your message instead reading the data from a second endpoint directly from the database. This usually leads to a more robust design as there aren’t multiple parties dependent on the same database structure.

Probably I may ask more question in future related to NServiceBus, hope it will be nice interaction.

Great :slight_smile: Feel free to come back here any time. It’s usually best to create a new topic for a specific question.