read & update using reader

[PIEBALDconsult]

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"

[Not Active]

RaviRanjankr wrote:

what extra I have to mention here

:wtf: You provide enough to answer the question that was asked. :rolleyes: Take some time to read other responses around the forums and learn.

---

I know the language. I've read a book. - _Madmatt

[Mycroft Holmes]

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

[PIEBALDconsult]

Nah, not so soon after the last time. I'll just opine that that particular poster probably shouldn't. :-D

[RaviRanjanKr]

Thanks for your support and nice idea. :-D

[Jassim Rahma]

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..

[PIEBALDconsult]

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.

[ShilpaKumari]

Good sot, You need to elobrate it how they achieve

[Mycroft Holmes]

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

[Pete OHanlon]

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.

I'm not a stalker, I just know things. Oh by the way, you're out of milk.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

[PIEBALDconsult]

With MySQL?

[PIEBALDconsult]

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.

[Mycroft Holmes]

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

[PIEBALDconsult]

Mycroft Holmes wrote:

grids

X| Wash your mouth out with tequila; you're better than that.

[Mycroft Holmes]

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

[PIEBALDconsult]

Mycroft Holmes wrote:

(relearning) Oracle.

But not in C with Pro*C I bet.

[MDNadeemAkhter]

:-D

[Brady Kelly]

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);

    // blah blah blah. do your work.
    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.

[Gary Huck]

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!

[Gary Huck]

Tell us why one should NOT use stored procedures, as you state.