|
If you wanted to write a query that contains 'IF' clause, how would you do it?
Please revise this query:
mycommand.CommandText = "IF EXISTS(SELECT * FROM [MyData] WHERE NOT Wo = 'ABC' OR Code = 1200)";
If above statement satisfied:
INSERT INTO[MyData] VALUES('New001', 768353)";
|
|
|
|
|
It's a 2 part act: One query to select; then an Insert based on the result of the first query.
You're using C# without stored procs; the "if" check is done on the client.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
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 though it would be easier for you. You know ... partitioning the problem.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
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]).
IF EXISTS
(
SELECT *
FROM [MyData]
WHERE NOT EXISTS [Wo] = 'ABC'
OR [Code] = 1200
)
INSERT INTO [MyData]
VALUES('New001', 768353)
END;
|
|
|
|
|
Ok, How would you use it with
mycommand.CommandText = "write your sql statements";
|
|
|
|
|
Can anyone please check this SQL CE query and revise its syntax for me?
insert into MyData values('test01', '1122/035', '666','ty01', 'tt01') where not exists (select * from MyData where 'test01', '1122/035', '666','ty01', 'tt01')
In query above, I add a new row to SQL data but it checks for duplicates before adding.
modified 5-Jun-21 2:45am.
|
|
|
|
|
There is no such thing as a WHERE clause on an INSERT statement.
You have to write this as two queries. The first is the SELECT to see if anything is returned. If there isn't, then you can execute the INSERT query.
|
|
|
|
|
Quote:
I found a solution on the internet which fixes it. You can use a command of the form:
INSERT INTO TableName (ColumnName)
SELECT '" + value + "' WHERE NOT EXISTS (
SELECT ColumnName from TableName WHERE Name = '" + value + "')";
Followed by an ExecuteNonQuery().
SQL Server Equivalent of MySQL INSERT IGNORE
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
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?
|
|
|
|
|
No, there is no query statement that will do that for you.
You could write up a stored procedure to do that in the database and call that stored procedure from your app with the appropriate data.
|
|
|
|
|
I added my method below. Please read it.
|
|
|
|
|
I never used SQL CE (compact edition)
Dave Kreskowiak wrote: You could write up a stored procedure to do that in the database and call that stored procedure from your app with the appropriate data.
, however, I always thought the stored procedures are not available there... Was I wrong? 
|
|
|
|
|
I really don't know. I don't use CE at all.
After looking it up, SP's aren't supported, and neither is multi-user. awesome...
|
|
|
|
|
SQL CE is the same as SQL Lite. It's a "local" db for an app. Allows you to use SQL / EF without a "server". It's not meant to be shared though you can easily copy it (the single db file).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
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.
modified 2-Jun-21 13:19pm.
|
|
|
|
|
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.
DO IT IN THE DATABASE WITH A STORED PROCEDURE!
|
|
|
|
|
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.
Thanks.
|
|
|
|
|
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.
|
|
|
|
|
Thanks, My application is single-user.
|
|
|
|
|
CE doesn't support SP's, nor is it multi-user.
So, you can query the database from your app for the record you're trying to add.
|
|
|
|
|
He's anti-SQL. Doesn't know it; doesn't want to learn it. Every solution is C# only; even if it's a database app.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Well then, he's really screwing himself, isn't he? 
EDIT:
Why did I just get the feeling he's going to be coming back bitching about more performance problems because he doesn't have any indexing setup?
|
|
|
|
|
He won't accept that without "SQL" everything has to be downloaded to the client; that's why he'll always have performance issues.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
No, I'm serious about my work. I just want to learn.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
