read & update using reader
-
I have the following to read from database: sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString); sql_connection.Open(); sql_command = new MySqlCommand("sp_send_pending_sms", sql_connection); sql_command.CommandType = CommandType.StoredProcedure; sql_reader = sql_command.ExecuteReader(); 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.. Thanks
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
-
I have the following to read from database: sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString); sql_connection.Open(); sql_command = new MySqlCommand("sp_send_pending_sms", sql_connection); sql_command.CommandType = CommandType.StoredProcedure; sql_reader = sql_command.ExecuteReader(); 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.. Thanks
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
-
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
:rolleyes: Well, DAH!! Of course you do, but how does this answer the question?
I know the language. I've read a book. - _Madmatt
-
RaviRanjankr wrote:
need to execute Transact SQL statement against connection
:rolleyes: 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?
:doh: Ok its my fault but can you explain what extra I have to mention here.
-
I have the following to read from database: sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString); sql_connection.Open(); sql_command = new MySqlCommand("sp_send_pending_sms", sql_connection); sql_command.CommandType = CommandType.StoredProcedure; sql_reader = sql_command.ExecuteReader(); 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.. Thanks
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"
-
Mark Nischalke wrote:
how does this answer the question?
:doh: Ok its my fault but can you explain what extra I have to mention here.
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
-
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
-
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. :-D
-
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
Thanks for your support and nice idea. :-D
-
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"
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..
-
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:
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
Good sot, You need to elobrate it how they achieve
-
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..
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
-
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..
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
-
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
With MySQL?
-
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.
-
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
-
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
X| Wash your mouth out with tequila; you're better than that.
-
Mycroft Holmes wrote:
grids
X| 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
-
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.