Code : MS SQL vs MYSQL through adapters

It has been a very long time since I changed from Access to using MS SQL, so I decided to go for some thing new for my new apps and sites (none public so far), my choice went to MYSQL, not because it is just free with all features, but because when we go for small hosting plans for mid size sites and apps the chances are that you get more number of databases, bigger database size with MYSQL rather than MS SQL.

MS SQL connectors come by default with Visual Studios however MYSQL Connectors need to be installed and they are freely available at MySQL.

If we are used to using the MS SQL (SQLCE) data adapters style programming then MYSQL is also the same way of programming. Following is a small code portion from my project to show the difference between the SQLCE Adapter and the MYSQL adapter based programming:

Code with MS SQL CE adapter:

Dim Con As SqlCeConnection = New SqlCeConnection(DataSource)
Dim Cmd As SqlCeCommand
Cmd = New SqlCeCommand("INSERT INTO [commentstable]([PageID],[ParentID],[CommentID],[Comment],[CommentTime],[UserName],[UserEmail],[IsApproved]) VALUES (@PageID,@ParentID,@CommentID,@Comment,@CommentTime,@UserName,@UserEmail,@IsApproved)", Con)
Cmd.Parameters.Add(New SqlCeParameter("@PageID", SqlDbType.int))
Cmd.Parameters.Add(New SqlCeParameter("@ParentID", SqlDbType.int))
Cmd.Parameters.Add(New SqlCeParameter("@CommentID", SqlDbType.int))
Cmd.Parameters.Add(New SqlCeParameter("@Comment", SqlDbType.string))
Cmd.Parameters.Add(New SqlCeParameter("@CommentTime", SqlDbType.DateTime2))
Cmd.Parameters.Add(New SqlCeParameter("@UserName", SqlDbType.string))
Cmd.Parameters.Add(New SqlCeParameter("@UserEmail", SqlDbType.string))
Cmd.Parameters.Add(New SqlCeParameter("@IsApproved", SqlDbType.Boolean))
With SomeInsertObject
     Cmd.Parameters("@PageID").Value = .PageID
     Cmd.Parameters("@ParentID").Value = .ParentID
     Cmd.Parameters("@CommentID").Value = .CommentID
     Cmd.Parameters("@Comment").Value = .Comment
     Cmd.Parameters("@CommentTime").Value = .CommentTime
     Cmd.Parameters("@UserName").Value = .UserName
     Cmd.Parameters("@UserEmail").Value = .UserEmail
     Cmd.Parameters("@IsApproved").Value = .IsApproved
End With
If Con.State = System.Data.ConnectionState.Closed Then Con.Open()
Cmd.ExecuteNonQuery()
Con.Close()

Code with the MYSQL CE Adapter:

Dim Con As MySqlConnection = New MySqlConnection(DataSource)

Dim Cmd As MySqlCommand

Cmd = New MySqlCommand("INSERT INTO [commentstable]([PageID],[ParentID],[CommentID],[Comment],[CommentTime],[UserName],[UserEmail],[IsApproved]) VALUES (@PageID,@ParentID,@CommentID,@Comment,@CommentTime,@UserName,@UserEmail,@IsApproved)", Con)

Cmd.Parameters.Add(New MySqlParameter("@PageID", SqlDbType.Int))
Cmd.Parameters.Add(New MySqlParameter("@ParentID", SqlDbType.Int))
Cmd.Parameters.Add(New MySqlParameter("@CommentID", SqlDbType.Int))
Cmd.Parameters.Add(New MySqlParameter("@Comment", SqlDbType.VarChar))
Cmd.Parameters.Add(New MySqlParameter("@CommentTime", SqlDbType.DateTime2))
Cmd.Parameters.Add(New MySqlParameter("@UserName", SqlDbType.VarChar))
Cmd.Parameters.Add(New MySqlParameter("@UserEmail", SqlDbType.VarChar))
Cmd.Parameters.Add(New MySqlParameter("@IsApproved", SqlDbType.TinyInt))
            With SomeInsertObject
                Cmd.Parameters("@PageID").Value = .PageID
                Cmd.Parameters("@ParentID").Value = .ParentID
                Cmd.Parameters("@CommentID").Value = .CommentID
                Cmd.Parameters("@Comment").Value = .Comment
                Cmd.Parameters("@CommentTime").Value = .CommentTime
                Cmd.Parameters("@UserName").Value = .UserName
                Cmd.Parameters("@UserEmail").Value = .UserEmail
                Cmd.Parameters("@IsApproved").Value = .IsApproved
            End With
If Con.State = System.Data.ConnectionState.Closed Then Con.Open()
Cmd.ExecuteNonQuery()
Con.Close()

The only observable difference is that we need to change the name from SQLCE to MYSQL for code portion and some data types like string to varchar etc.