|
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.
|
|
|
|
|
Using stored procedures, more of a good thing than using dynamic SQL? The article makes a good argument about changes legacy systems. The need to change the stored procedures will break code, or adding more stored procedures adds extra (duplicate) overhead. If just adding a new column to a table requires adding a new input parameter, we will need to add a new stored procedure to handle it. Of course, the old stored procedure can call the new stored procedure with a dummy argument for the new input value.
On the other hand, using dynamic SQL may require revision to add the new parameter (where it is needed). The down side is that legacy code that uses the database will need to have its SQL updated. Not only will the new application making use of the "improved" database have the changes, but all of the older applications will need to change, if the new database is used with them. And, there is the possibility of SQL injection attacks, depending on how you store your SQL in your application.
Updating Stored Procedures is akin to the old days of COM programming. There was a rule, at one time, that said we should not change a published interface, but make a new interface. This also was applied to the methods as well. And, a good programmer will do the least amount of work to get the job done efficiently.
You must decide which method is best for you.
|
|
|
|
|
It is maybe neater to not use a reader, but rather a DataTable and a TableAdapter. Then you can loop through the table rows, update the is_sent on each row as you process it, and then call Update. Something like this:
using (var ad = new SqlDataAdapter("sp_send_pending_sms", ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString))
{
ad.SelectCommand.CommandType = CommandType.StoredProcedure;
var dt = new DataTable();
ad.Fill(dt);
ad.AcceptChangesDuringUpdate = true;
ad.Update(dt);
}
If the list is long, you may want to call Update every n rows, not just at the end.
|
|
|
|
|
Yes, something like that is what you need. The ExecuteReader() method, as the name implies, is only for selecting (reading) data.
p.s. Stored procedures are a great way to go!
|
|
|
|
|
GamleKoder wrote: is only for selecting (reading) data.
No it isn't; it can execute any SQL statement (even a list of them if the particular database engine supports it) and it underlies all the other ADO.net data manipulation methody, so you're using it whether you know it or not.
|
|
|
|
|
You can not update through a DataReader. It retreives a read-only, forward-only stream of data one record at a time.
If I read your question correctly, you are doing some processing on each record after reading, then need to set a flag in the database to indicate that record has been processed.
You will need a second connection and comand object for this. You can't use the same connection/command as the DataReader because it keeps it's connection open internally throughout it's lifetime.
The best way to accomplish this will vary slightly depending on how many records you expect to process and whether the processing will run a long time (cumulative).
For a relatively short run:
1. Open SQLConnection for updating and attach to SQLCommand
2. Read next record from DataReader
3. Perform needed processing
4. Generate update SQL for current record
5. Use SQLCommand to run update SQL
6. Repeat steps 2 - 5 until last record has been processed and updated
7. Close and destroy update SQLConnection/SQLCommand
For a long run:
1. Create SQLConnection for updating and attach to SQLCommand
2. Read next record from DataReader
3. Perform needed processing
4. Generate update SQL for current record
5. Open SQLConnection for update
6. Use SQLCommand to run update SQL
7. Close SQLConnection
8. Repeat 2 - 7 until last record has been processed and updated
9. Close and destroy update SQLConnection/SQLCommand
Hope this answers your question and helps out.
Kevin Rucker, Application Programmer
QSS Group, Inc.
United States Coast Guard OSC
Kevin.D.Rucker@uscg.mil
"Programming is an art form that fights back." -- Chad Hower
|
|
|
|
|
Spectre_001 wrote: You can not update through a DataReader
No, but you can through ExecuteReader.
In your scenarios... generate a parameterized update command up front and then set the parameters for each row and execute -- no need to keep generating the same SQL statement.
|
|
|
|
|
That dosen't help if he needs to read, process, then update.
Kevin Rucker, Application Programmer
QSS Group, Inc.
United States Coast Guard OSC
Kevin.D.Rucker@uscg.mil
"Programming is an art form that fights back." -- Chad Hower
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
