|
Thanks to all for the feedback. I am going to reexamine the amount of data involved and the expected frequency of recording .vs. playback to see if it warrants a database approach or another approach.
Thanks to all.
|
|
|
|
|
Hi,
We have a C# windows forms application and I need to work with MS Word files to replace some text, table rows content, or add a new rows to an existed table, we need to know the best way.
I tried using Microsoft.Office.Interop.Word but this way may be old and must use the same office on the all pc's you install the application on it, if you use the MS Office 2007 then you must install it on any PC will use the application.
Please let me know the best way, tools to do that.
Thank You
|
|
|
|
|
Basically, interop is pretty much your only real way - but using Word as a storage medium is the real problem!
There are various libraries that claim to do it - Google found this which lists a few: Reading doc and docx files using C# without having MS Office installed on server - Stack Overflow[^] - but I've not tested any.
Personally, I'd look for a better file format - there is no guarantee that what you read today will stay the same format tomorrow if MS decide to "add features".
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
If you can use the newer Office formats, you can use the OpenXML SDK to work with the files without having Office installed.
|
|
|
|
|
Interop is the only real method of updating Word. We had to make sure ALL users were on EXACTLY the same version of Word to use the application. It was updating legal documents that had to read exactly correctly and turned out to be one of the most painful operations I have ever had to do.
If you cannot control the version of Word on all clients then you have a serious problem.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
