manage remote MySQL transactions
Posted on 2011-09-22
New to MySQL backend.
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*
Dim conn As New MySqlConnection(strMySQLConnectString)
Dim cmd As New MySqlCommand
Dim params As MySqlParameterCollection = cmd.Parameters
cmd.Connection = conn
cmd.CommandType = CommandType.Text
'updating user values with default values
cmd.CommandText = "update users set name = 'Player' where id = " & strUserId & ""
'here we enter into the transaction_history table
cmd.CommandText = "insert into transaction_history (user_id) values (" & CInt(Me.txtUserID.Text) & ");"
Dim strConnect As String = strMySQLConnectString
Dim FileSQL As String
FileSQL = "select users.* from users where id = " & CInt(Me.txtUserID.Text) & ";"
Dim conReader As New MySqlConnection(strConnect)
Dim conString As New MySqlCommand(FileSQL, conReader)
Dim drReader As MySqlDataReader
drReader = conString.ExecuteReader(CommandBehavior.CloseConnection)
Do While drReader.Read()
strUserId = drReader.Item("id").ToString
strUserName = drReader.Item("name").ToString
strUserEmail = drReader.Item("email").ToString
strUserCreateDate = drReader.Item("created").ToString
strUserExperience = drReader.Item("exp").ToString
strUserLevel = drReader.Item("level").ToString
strUserLosses = drReader.Item("losses").ToString
strUserRating = drReader.Item("rating").ToString
strUserRatingMu = drReader.Item("rating_mu").ToString
strUserRatingSigma = drReader.Item("rating_sigma").ToString
strUserGold = drReader.Item("gold").ToString
strUserCrystals = drReader.Item("crystals").ToString
strUserReferer = drReader.Item("referer").ToString