|
jrahma wrote: after reading I want to update every record when send to set the field is_sent to True?
how can i do this? do I have to create another reader in the middle? can you please guide..
A little bit confusing about your question whatever,
In case of save,update and delete
you need to execute Transact SQL statement against connection.
modified on Sunday, December 12, 2010 10:17 AM
|
|
|
|
|
RaviRanjankr wrote: need to execute Transact SQL statement against connection
 Well, DAH!! Of course you do, but how does this answer the question?
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Mark Nischalke wrote: how does this answer the question?
Ok its my fault but can you explain what extra I have to mention here.
|
|
|
|
|
RaviRanjankr wrote: what extra I have to mention here
 You provide enough to answer the question that was asked.
Take some time to read other responses around the forums and learn.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Thanks for your support and nice idea. 
|
|
|
|
|
|
Good sot, You need to elobrate it how they achieve
|
|
|
|
|
You have been around here long enough to know to format code snippets properly. Edit your post to correct this.
How do you expect the database to know what operations have occurred after retrieving the data? You have to tell it, with a SQL statement of some type.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
If you use stored procedures (and you shouldn't), don't prefix the names with "sp"[^].
When I implement a system like that, sending email or whatever, I put a Status field in the table to indicate ( 0=New , 1=Sending , 2=Sent ). When I select the New rows, I also set the Status to Sending, then when I'm done I set the Sending rows to Sent.
"UPDATE Email SET Status=1 WHERE Status=0 ; SELECT * FROM Email WHERE Status=1"
Send the emails.
"UPDATE Email SET Status=2 WHERE Status=1"
|
|
|
|
|
PIEBALDconsult wrote: If you use stored procedures (and you shouldn't),
Do you want to clarify that statement.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Nah, not so soon after the last time. I'll just opine that that particular poster probably shouldn't. 
|
|
|
|
|
That article is from 2003.
And yeah, i'm wondering too, why?
|
|
|
|
|
Nothing fundamental has changed in databases since 2003 so the premise of the article is still very valid, I just don't agree with it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When I implement a system like that, sending email or whatever, I put a Status field in the table to indicate ( 0=New , 1=Sending , 2=Sent ). When I select the New rows, I also set the Status to Sending, then when I'm done I set the Sending rows to Sent.
"UPDATE Email SET Status=1 WHERE Status=0 ; SELECT * FROM Email WHERE Status=1"
Send the emails.
"UPDATE Email SET Status=2 WHERE Status=1"
yup but my question, is I am reading the pending SMSs using sql_reader1 then how can I execute the update status code? can I do it using the same sql_reader? it won't allow braces the sql reader is still open! should I have sql_reader1 for reading the pending sms and within the while (sql_reader1.Read()) I will have sql_reader2?
please advise..
|
|
|
|
|
You can do it that way (you have a unique ID, yes?), but you'll need a second Connection as well. There are times you need to do that.
|
|
|
|
|
jrahma wrote: using sql_reader1
I have never understood the reason so many devs use the reader, sure it is he most economic way to process data but the limitations are irritating.
I ALWAYS get the data, all the data into a datatable/List<>, this frees up the connection, then I process the datatable. If the data set is too large then I get the data to process in chunks.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have never understood the reason so many devs use the DataAdapter and load a whole butt-load of data at once when they don't use it all at once.
Certainly, when I want to load a ListBox or something, I use a DataTable (loaded straight from a DataReader), but mostly I use only one record at a time -- to export or copy somewhere or update something.
For what I do, I doubt the connection stays open significantly longer, but it sure saves on RAM and class instantiations.
Bear in mind that I generally write Windows Services that cycle every ten to fifteen seconds, and not generally GUI apps that spend a lot of time just sitting there displaying a bunch of out-dated data. And when I do write a WinForms app that displays a butt-load of data, I often use a Service to create an (XML) export that the clients read, rather than have all the clients constantly clambering for database connections.
|
|
|
|
|
PIEBALDconsult wrote: I generally write Windows Services that cycle every ten to fifteen seconds
Well I do write user apps that have the (outdated) data sitting in front of the user, with move to WCF we use observablecollections serviced to the UI. I suppose we could use the reader instead of the adapter, it is just a habit from the winforms when we used to datatable as the bindingsource to the grids.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: grids
Wash your mouth out with tequila; you're better than that.
|
|
|
|
|
In this context a grid is a generic term for a collection control and with winforms I was always happy to bind a datatable to a datagirdview, the datatable is disconnected afterall. The cost of the datatable has always been irrelevant to the functionality that it brings.
Then we were told to use services and I decided the UI should be silverlight so everything changed, now it's Oracle databases, WCF, observablecollections, viewmodels and xaml. Did I say I hate (relearning) Oracle.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: (relearning) Oracle.
But not in C with Pro*C I bet.
|
|
|
|
|
PIEBALDconsult wrote: I have never understood the reason so many devs use the DataAdapter and load a whole butt-load of data at once when they don't use it all at once.
When you know there will never be too much data and that it will not have impact on system resources and when you want to work with data in a disconected state.
There are good uses for both approaches, that's why they exist. Beeing wise to use them where they fit is another story, can't speak for anyone.
|
|
|
|
|
If you are trying to update a value and you are already using the connection for reading data, you need to look at using a MARS connection (that's Multiple Active Result Set). Add MultipleActiveResultSets=true; to your connection string.
|
|
|
|
|
|
Tell us why one should NOT use stored procedures, as you state.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
