Solved

manage remote MySQL transactions

Posted on 2011-09-22
7
366 Views
Last Modified: 2012-05-12
Experts -

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*

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

    cmd.CommandText = "insert into transaction_history (user_id) values (" & CInt(Me.txtUserID.Text) & ");"
    cmd.ExecuteNonQuery()
    conn.Close()

Select example:

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)
conReader.Open()

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
   
Loop

conReader.Close()
drReader.Close()

-----------------

TIA!

c.
0
Comment
Question by:crafuse
  • 4
  • 3
7 Comments
 

Author Comment

by:crafuse
ID: 36581991
bump

;-P
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36582171
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
 

Author Comment

by:crafuse
ID: 36582286

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36582305
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
 

Author Comment

by:crafuse
ID: 36582360
it is already done, as a winforms app. should it be ported? would it be that much faster? why?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 36582393
>would it be that much faster? why?
Because it would not be transporting a lot of data over network.
0
 

Author Closing Comment

by:crafuse
ID: 36711785
feeble.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help parsing JSON in my VB.Net application 4 39
VB.Net - Oracle BulkCopy from CSV Date Format 7 44
Get list of word ducuments in a folder 10 35
Trigger usage 2 59
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now