A .Net developer's guide to MySQL

I was given the opportunity forced to get to know all things MySQL so these are the observations of a Microsoft/Oracle developer’s first foray into MySQL.

The MySQL Downloads page is the one-stop for the latest MySQL goodies. As of this writing the Community Server (free) was at release 5.1. Once you install the database, you quickly realize a need for some additional tools. The MySQL GUI Tools bundle includes the essential Administrator and QueryBrowser applications that will be familiar to any Microsoft developer. Now that you can create and maintain your new RDBMS, you need some drivers to connect the .NET code to. I tested both the ODBC and the Native (Connector/Net) drivers and from what I can see, I’d go with the Native drivers for development, but I’d also install the ODBC drivers for general Microsoft bliss (Visio, Excel, etc.).

Working with the Native driver is nearly identical to any other ADO.Net provider, save that everything is prefixed with “MySql” (e.g. MySqlConnection, MySqlCommand, etc.). After adding a project reference to MySql.Data, you need only import MySql.Data and MySql.Data.MySqlClient and the rest is completely straightforward:

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports MySql.Data
Imports MySql.Data.MySqlClient



<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class ReportService
    Inherits System.Web.Services.WebService

    Dim _connString As String = "server=localhost;database=csrm;user id=root;password=****;"

    <WebMethod()> _
    Public Function ReportList(ByVal group As String) As Report()
        Dim newList As New ArrayList
        Dim newItem As Report


        Dim conn As New MySqlConnection(_connString)
        Try
            conn.Open()

            Dim cmd As New MySqlCommand("SELECT * FROM report", conn)
            Dim reader As MySqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult)

            While reader.Read
                newItem = New Report()
                newItem.Code = reader("Code")
                newItem.Name = reader("Name")
                newList.Add(newItem)
            End While

            reader.Close()

        Catch ex As Exception
            Throw New ApplicationException("Error occured: " & ex.Message, ex)
        Finally
            conn.Close()
        End Try

        Return newList.ToArray(GetType(Report))
    End Function

    <WebMethod()> _
    Public Function ReportOutput(ByVal reportCode As String) As DataTable
        Dim dt As New DataTable("Report")

        Dim conn As New MySqlConnection(_connString)
        Try
            conn.Open()

            Dim cmd As New MySqlCommand("ServiceReport", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New MySqlParameter("IN_REPORT_CODE", reportCode))

            Dim da As New MySqlDataAdapter(cmd)
            da.Fill(dt)

        Catch ex As Exception
            Throw New ApplicationException("Error occured: " & ex.Message, ex)
        Finally
            conn.Close()
        End Try

        Return dt
    End Function

End Class


<Serializable()> _
Public Class Report

    Private _code As String
    Public Property Code() As String
        Get
            Return _code
        End Get
        Set(ByVal value As String)
            _code = value
        End Set
    End Property

    Private _name As String
    Public Property Name() As String
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property

    Private _description As String
    Public Property Description() As String
        Get
            Return _description
        End Get
        Set(ByVal value As String)
            _description = value
        End Set
    End Property

End Class

The syntax for creating a stored procedure is slightly different than PL-SQL, but seems easy enough:

DELIMITER $$

DROP PROCEDURE IF EXISTS `csrm`.`ServiceReport`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `csrm`.`ServiceReport`(
    IN IN_REPORT_CODE varchar(5)
)
BEGIN

SELECT * FROM emailAddr;

END $$

DELIMITER ;