How to post a complete List to database after verification of duplicate records.

Question

0.00/5 (No votes)

See more:

, +

Below mentioned code is working absolutely fine. But the problem is I have thousands of list in my dailyTransactionPosting List. So it takes plenty of time in verifying and posting that.

I want it to be something like, in its first shot it will verify all the duplicate records by comparing Date and CustomerID and remove those duplicate records.
And in its second shot it will post the complete list to the database.

This is a CoreBanking Project and my code has to be very accurate. Please Help!!! I need this urgently.

C#

public string DailyTransactionPosting(List<DailyTransactionPosting> dailyTransactionPosting)
        {
            DailyTransactionPosting dPosted = new DailyTransactionPosting();
            foreach (DailyTransactionPosting dList in dailyTransactionPosting)
            {
                dPosted = null;
                dPosted = db.Where<DailyTransactionPosting>(a => a.Date == dList.Date && a.CustId == dList.CustId).SingleOrDefault();
                if(dPosted == null)
                {
                    db.Save<DailyTransactionPosting>(aList);
                }
            }
		return Message;
	}

Posted 21-Dec-15 6:42am

Member 11579819

Add a Solution

Comments

Member 11579819 21-Dec-15 12:43pm

ORMLite is used for the database connection...

[no name] 21-Dec-15 12:55pm

And why not let the DB decide about the duplicates?

Member 11579819 21-Dec-15 12:59pm

This module would run on daily basis. So there will be customers with same Customer ID, however the transaction date will be different. So how to do this. I am not that good with Stored Procedures

[no name] 21-Dec-15 13:11pm

Sorry I was very unprecise. Give me a Chance again. I see two Option:
a.) Check before insert whether unique ID is given. Not that easy in a "parallel environment"
b.) Insert without test and check the result. In case insert failed because of duplicate ID take the Action needed.

Member 11579819 21-Dec-15 13:16pm

There can't be unique ID for the same Customers because everyday their data will be posted based on their transactions made. And i think db cannot decide this because both Customer ID and date has to be compared.
If it was only one parameter then I could have managed it on database level,but since I have to take care of two parameters,i.e, Transaction date, and Customer ID,that got me stuck.

What say??

[no name] 21-Dec-15 13:54pm

I think we are talking past each other :(

Member 11579819 21-Dec-15 14:09pm

Hey.. I thought for an answer. I will create an another instance of List(DailyTransactionPosting) and in the foreach if dPosted is null then I will add that object to the above created instance. And after the loop finishes, I will post that to database. I am not sure how it would work, but i will update you as soon as I am done with it. After all I got this idea only after getting into a conversation with you.
Thanks buddy :(

Sergey Alexandrovich Kryukov 21-Dec-15 14:21pm

Parallel environment? And what the atomicity was invented for?
—SA

Sergey Alexandrovich Kryukov 21-Dec-15 14:19pm

Instead of checking for duplicates, why not blocking creation of duplicates in first place?
—SA

Member 11579819 21-Dec-15 14:23pm

This cannot be done. The data I am talking about is supposed to be given by other banks. And there are cases that sometimes they send duplicate records which are supposed to be handled by the application.
That is why I cannot block the creation of duplicate records.

Sergey Alexandrovich Kryukov 21-Dec-15 14:36pm

Not quite so. Please see my other comment below.
—SA

[no name] 21-Dec-15 14:28pm

I think one can't blocking creation of duplicates in advance. In this case I would give the responsibility completely to DB and check the result of an insert/update Statement.
Bruno

Sergey Alexandrovich Kryukov 21-Dec-15 14:34pm

What makes you think so? Or maybe I don't know what would you mean by "in advance".
In my understanding, this is equivalent to "what if" approach: in atomic approach, you can see that your update to the database would violate uniqueness and refuse to do it. Better yet, database schema itself could prevent duplication. You cannot duplicate unique key.

But of course, if you have to work unconnected from the database for a while, you can violate the uniqueness in the data set created, but it is also reduced to enforcement of uniqueness at the level of SQL statement. Dealing with uniqueness at "validation" level is the utterly bad approach.

—SA

Member 11579819 21-Dec-15 14:40pm

I suppose by now you must have understood my problem. I would appreciate any sort of help in refining my approach.

Sergey Alexandrovich Kryukov 21-Dec-15 14:48pm

I don't know your uniqueness criteria and the purpose. If you are using relational database, I would start at thinking on the database schema where uniqueness is enforced by using a unique key. If you think it's impossible, I would like to hear why. If you cannot even modify the database schema, it would be a pity, but then you still should better enforce the uniqueness at the SQL statement level. That is is, each update should be performed with the statement which would either preserve uniqueness or change nothing, in transactional/atomic way. I would even suggest that it's the only safe way. With multi-processing, it may happen that one user tries to preserve uniqueness and another one destroy it. That's why transactional/atomic quality of your code is critically important.
—SA

Member 11579819 21-Dec-15 15:03pm

I got you.. I can use the concept of Compound keys. But since I never implemented this before I would like to know how to tackle with the error if any duplicate record is found/being inserted. Like what should be my next step.

Sergey Alexandrovich Kryukov 21-Dec-15 15:13pm

That's it. Great. I hope you will produce your solution based on this idea, because this is the safest approach, and not too complicated, which would make code less error-prone.
—SA

Member 11579819 22-Dec-15 11:08am

Thank you so much... Actually it's not enough to thank you just with words. My module is running much better and faster than ever before. I don't have to do any work with code(no logic no sh***y work). The exception I was talking about yesterday was handled by database.
Hats off to you

Sergey Alexandrovich Kryukov 22-Dec-15 11:46am

You are very welcome.
Good luck, call again.
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sergey Alexandrovich Kryukov · Accepted Answer · 2015-12-21T08:51:00

Please see my comment to the question for the discussion of the valid approaches and some rationale behind them. For some introduction, please see https://en.wikipedia.org/wiki/Unique_key[^].

As I explained in my last comment, it's not all. But further discussion requires more detail on your problem, database schema and your requirements, as well as ultimate goals of your uniqueness criteria, as well as criteria themselves.

—SA