What do you mean by CLIENT? Do you mean that if statement must be in C# part? I have written a query in SQL Management Tool. I can use the IF statement and achieve what I want. But SQL CE gives errors in multiple sections. How can I write queries which have multiple lines in SQLCE and C#?
I know little about C# and how, once your SQL Query gets constructed as mycommand.CommandText, you'd use it from your C# frontend, but I'lll wager you're going to need some more TSQL describing the redirection that "IF EXISTS" intimates in order to complete a statement ... I assume there's a target of your INSERT, something like a TABLE (called [MyData]).
WHERE NOT EXISTS [Wo] = 'ABC'
OR [Code] = 1200
INSERT INTO [MyData]
My user needs to load some excel sheets and store some of their information into a SQL CE file. This SQL CE file contains a table with 5 columns. The rows will increase as the user adds their information into SQL. What is the best way to do this? Is there any SQL query that can check for duplicate values and if the new entry does not exist in the SQL file, adds it to the table?
I used a method that can do this work, but it gets slower as the data increases.
I have used Dictionary<string, string=""> class to detect duplicate values (Dictionary.ContainsKey()).
After that I take them into a DataTable and then into SQL CE file.
Each time I want to add new data into my SQL, I read all previous SQL data into a DataTable then take them into Dictionary<> and check new data for any duplicate values and if they were unique, adding process is continued as above.
The problem is that this method works fine and fast to some extent. As the data increase(for example more than 5000 rows in SQL), it gets some seconds to calculate the process.
And this is why I said do it in a stored procedure in the database.
For every record you want to insert, why on earth would you read all of the existing data? Yes, this get's slower and slower every time you insert a record. All that data transfer will take more and more time.
It sounds like you're not even caching the data you read and insert! If you're going to do something like this, you would normally just read the database once, keep all that data, then add new records to the cache and have the cache update the database.
This has a downside though. You really cannot use it in a multi-user environment, and there is a limit to the amount of data you can keep in memory, so it doesn't scale well at all.
Doing it in the database removes reading all that data over and over again, and removes the memory size limits.
Is this the way to DO IT IN THE DATABASE WITH A STORED PROCEDURE:
When I want to add a new entry, I should use a query to check if that entry exists in SQL. If it was unique, I would add it to SQL database.
If it was not what you meant, please explain your method for DOING IT IN THE DATABASE WITH A STORED PROCEDURE more.
You don't know what a stored procedure is, do you?
Yes, you can do it by querying the database for the record you want to insert. If it's already there, you can either update that record or ignore it and go to the next record in your Excel sheet. If it's not there, you can insert it.
Again, there's a problem with doing it from the client-side. In a multi-user environment, if someone else does the same thing at the same time, you could end up with the same record being updated by two or more clients at the same time, or inserting the same record at the same time.