I have small, very straightforeward VB.NET Windows Forms app that reads and writes to a remote MySQL instance. Only a few users, but a couple are in Asia, and the db resides in the Amazon Cloud.
Anyways, none of the writes are db intensive - in almost every case I'm hitting well organized indexes, mostly PKs. Still, I'm new to this type of architecture (and perhaps it's not perfect, it maybe screams out for a WEB-based app, but I'm not an ASP guy...). I'm mostly concerned about maintaining absolute transactional integrity. I would like some advice - code examples - regarding the best way to programmatically manage the sorts of inserts and updates and selects I'm performing. Any pointers to best practices in these various regards would be greatly appreciated.
Mostly, I'd like error handling code that helps to confirm connection status and manages rollbacks & commits.
*No points awarded if answers don't have code, links to code*
Insert\Update example:
Dim conn As New MySqlConnection(strMySQLConnectString)
Dim cmd As New MySqlCommand
Dim params As MySqlParameterCollection = cmd.Parameters
cmd.Connection = conn
cmd.CommandType = CommandType.Text
conn.Open()
'updating user values with default values
cmd.CommandText = "update users set name = 'Player' where id = " & strUserId & ""
cmd.ExecuteNonQuery()
'here we enter into the transaction_history table
You comment above had an opposite effect!!! EE sends out email notifications automatically if no comment is posted to a question after a given number of hours but you cancelled that.
Now to the point. Where are transactions? I dont see any. Is your concern referential integrity of data or concurrency violations?
0
crafuseAuthor Commented:
mostly concurrency issues, thought i was clear. you don't agree writes are transactions? anyways, i guess i'm mostly concerned that connections to the db are opened and closed successfully, and if there's any interruption within, that i can somehow flag this. i only write to a few key tables now and then, but it's important that data and transactional 'integrity' is maintained. i can't really afford any screw-ups...ideas?
(and no more bumps...)
c.
0
Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
I thought you are talking about explicit transactions. As I said, if you have not gone much far in the development, consider doing this as a web. It will be very slow as a winforms app.
0
crafuseAuthor Commented:
it is already done, as a winforms app. should it be ported? would it be that much faster? why?
;-P