Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi,

I am sending a request with sender, multiple numbers and ID say Sender is same for each request
sender, number, ID 
A, 9999, 1
A, 8888, 2
A, 7777, 3

For this request when I get a msgID against each number. The response contains only the msgID and the ID I have send in the request say
msgID, ID
ABC, 1 (for 1st number)
DEF, 2 (for 2nd number)
GHI, 3 (for 3rd number)

Now I have a table in SQL as below
Number
msgID
ID

I would like to store the number and the corresponding msgID in this table. How can this be achieved with best possible way?
My problem is in case a issue occur say at that particular time msgID is not generated or the update was not successful and another request is fired with the same sender ID and numbers so the corresponding data should get updated not all the data.

Say

I am sending the below request
sender, number, ID 
A, 9999, 1
A, 8888, 2
A, 7777, 3

and the update fails but the msgID is generated and user sent another request say
sender, number, ID 
A, 9999, 1
A, 8888, 2
A, 6666, 3

and in this case msgID is generated, then how will it update only this record not the previous one that was sent earlier.

Which is the best possible way to achieve this?
Posted
Updated 21-Mar-14 10:39am
v3
Comments
CoderHead 21-Mar-14 8:48am    
From where does this request originate, and how is it being sent to (I assume) the database? What generates the msgID?
mayank.bhuvnesh 21-Mar-14 10:10am    
Hi i m sending req using api say

usr="9999" id="1"

I ger the response of dis req as

msgid="asd" id="1"

For every req msg id vl b generated no msg id vl miss
ZurdoDev 21-Mar-14 12:18pm    
What exactly are you stuck on?

1 solution

'dis' is not a word. nor is 'den', unless you mean where wolves live. We don't mind typos, or poor grammar, we want to help and not be grammar Nazis. But you should at least try to speak the language you're using :-)

The SQL merge[^] keyword lets you pass a set in to an insert operation and have it recognise records that already exist.

Here[^] is my article on ways to pass an arbitrary list of data to SQL Server.
 
Share this answer
 
Comments
mayank.bhuvnesh 22-Mar-14 13:04pm    
The soul purpose here is to make you understand what's the actual problem is. I agree witrh your explanation, but it's same as I asked you about the solution and without explaining it here you have provided me the link. :) :)

K Look
With merge its not possible

You have considered only one situation Now say

I have sent the req

sender, number, ID
A, 9999, 1
A, 8888, 2
A, 7777, 3

and the updation of response fails in the database and I have again sent the same req

sender, number, ID
A, 9999, 1
A, 8888, 2
A, 7777, 3

This time while updating database 1st req will be updated or second???
My major concern is

How to store correct msgId with that corresponding number only??? It means if in second case data is updating, it should get updated for second request only and should not affect the fist one....
Christian Graus 22-Mar-14 19:19pm    
The data is the same for your two data sets. So if something was missed the first time, it gets added the second. Ultimately, if your system is fragile and data can be lost, then it is lost. I don't see what you can hope to do to respond to situations you know nothing about ( how does your DB reflect the data it did not receive ) ? Merge allows you to resend data, and it will only be inserted if it's missing. It's the best possible way I can see to deal with what I think you're trying to say ( that you send data, some of it is there already, and you want to do a set based insert or update of the rest ).
mayank.bhuvnesh 24-Mar-14 1:59am    
The data is same but these are two different entries in the system. There is no link between these two sets. Both of these act will act individually

Say for first set
(Web Request)
sender, number, ID
A, 9999, 1
A, 8888, 2
A, 7777, 3

(Web Response of above set)

msgID, ID
abc, 1
def, 2
ghi, 3

Now for second request

(web Request)

msgID, ID
fty , 1
juy , 2
lok , 3

It doesn't mean if updation of msgID for request 1 fails MsgId of second request will update...

For 1st request response for thge first vl b updated and for second response of second vl b updated

Now I have a table in database as

Number
msgID
ID

Now how I can store number with is corresponding msgId with the best possible way,

Like
Number, msgID, ID
999, abc, 1
999, fty, 1
and so on


It can b done by other means also but I am looking for the best solution here...
Christian Graus 24-Mar-14 3:29am    
If no primary key exists, then it's a question of if you add an 'id' column to act as one, or if it has no value to you. Beyond that, I'm not sure I understand what the issue is. You can't store records you didn't get, and if different records have the same id, but are not the same record, then the id is meaningless. If you control the clients, can you make the id a GUID, so it's meaningful ?
mayank.bhuvnesh 24-Mar-14 3:56am    
The format of the ID can only be integer as per the API rules.
Id column cannot be defined as primary coz it can have many similar Id as I have explained in the example.

The web response I get is in XML format which contains only this data.

I can do it by other means also but it vl be a tricky task and for long term it will not be a good Idea.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900