Hi all, with the following free .net helper class you can easly execute SQL commands against a MySQL database, execute stored procedures, get or programmatically update tables/DataSet objects and convert to and from MySQL datetime types.
Enjoy! š
' MySqlHelper VB.NET class
' http://www.maxvergelli.com/
' Copyright (c) 2016 Max Vergelli
' Vers. 2.1.0
' Date: 21/03/2016
' Description:
' .NET helper class to easly execute SQL commands against a MySQL database,
' execute stored procedures, get or programmatically update DataSet objects,
' and convert to and from MySQL datetime types.
'
' Before use this class, You have to install and import in your project the
' last MySQL Connector/NET at
' http://dev.mysql.com/doc/refman/5.0/es/connector-net.html
' Permission is hereby granted, free of charge, to any person obtaining a copy
' of this software and associated documentation files (the "Software"), to deal
' in the Software without restriction, including without limitation the rights
' to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
' copies of the Software, and to permit persons to whom the Software is
' furnished to do so, subject to the following conditions:
' The above copyright notice and this permission notice shall be included in
' all copies or substantial portions of the Software.
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
' IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
' FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
' AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
' LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
' OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
' THE SOFTWARE.
Imports System.Data
Imports System.Globalization
Imports MySql.Data.MySqlClient
Public Class MySqlHelper
Private _cnx_string As String = Nothing
Private _cnx_obj As MySqlConnection = Nothing
Private Function IsConnectionAlive() As Boolean
Dim is_alive As Boolean = False
If _cnx_obj IsNot Nothing AndAlso _cnx_obj.State <> ConnectionState.Closed Then
is_alive = True
End If
Return is_alive
End Function
Private Sub CloseConnection()
Try
If IsConnectionAlive() Then
_cnx_obj.Close()
End If
_cnx_obj = Nothing
Catch ex As Exception
_cnx_obj = Nothing
End Try
End Sub
Public ReadOnly Property ConnectionString() As String
Get
Return _cnx_string
End Get
End Property
Public Function IsOpen() As Boolean
Return IsConnectionAlive()
End Function
Public Sub New(Optional ByVal db_connection_string As String = "", _
Optional ByVal open_connection As Boolean = False)
_cnx_string = db_connection_string
If _cnx_string.Length > 0 And open_connection = True Then
Dim is_open As Boolean = Open(_cnx_string)
End If
End Sub
Public Function Open(ByVal db_connection_string As String) As Boolean
Dim success As Boolean = False
Try
If IsConnectionAlive() = False Then
If db_connection_string.Length > 0 AndAlso _cnx_obj Is Nothing Then
_cnx_string = db_connection_string
_cnx_obj = New MySqlConnection
_cnx_obj.ConnectionString = _cnx_string
_cnx_obj.Open()
success = True
End If
End If
Catch ex As Exception
CloseConnection()
success = False
End Try
Return success
End Function
Public Sub Close()
CloseConnection()
End Sub
Protected Overrides Sub Finalize()
CloseConnection()
End Sub
'get a DataRow object with the first row of a SQL select command
Public Function SelectFirstRow(ByVal sql_select As String) As DataRow
Dim ds As DataSet = SelectRows(sql_select)
If ds Is Nothing Then
Return Nothing
End If
If ds.Tables.Count < 1 Then
Return Nothing
End If
If ds.Tables(0).Rows.Count < 1 Then
Return Nothing
End If
Return ds.Tables(0).Rows(0)
End Function
'get a DataSet object with the rows of a SQL select command
Public Function SelectRows(ByVal sql_select As String) As DataSet
Dim ds As DataSet = Nothing
Try
If IsConnectionAlive() Then
Dim cmd As MySqlCommand = New MySqlCommand(sql_select, _cnx_obj)
Dim da As MySqlDataAdapter = New MySqlDataAdapter
da.SelectCommand = cmd
ds = New DataSet
da.Fill(ds)
da = Nothing
End If
Catch ex As Exception
ds = Nothing
End Try
Return ds
End Function
'Execute a MySql Stored Procedure
Public Overloads Function ExecuteStoredProcedure(ByVal stored_procedure_name As String) As DataSet
Return ExecuteProcedure(stored_procedure_name)
End Function
'Execute a MySql Stored Procedure with parameters
'How to create the parameters
'Dim param As MySqlParameter = New MySqlParameter("?v_id", MySqlDbType.Int32)
'param.Value = 1
'param.Direction = ParameterDirection.Input
Public Overloads Function ExecuteStoredProcedure(ByVal stored_procedure_name As String, _
ByVal ParamArray parameters As MySqlParameter()) As DataSet
Return ExecuteProcedure(stored_procedure_name, parameters)
End Function
Private Function ExecuteProcedure(ByVal procedure_name As String, _
ByVal ParamArray sql_parameters As MySqlParameter()) As DataSet
Dim ds As DataSet = Nothing
Try
If IsConnectionAlive() Then
Dim cmd As New MySqlCommand(procedure_name, _cnx_obj)
cmd.CommandType = CommandType.StoredProcedure
If sql_parameters IsNot Nothing Then
For Each p As MySqlParameter In sql_parameters
cmd.Parameters.Add(p)
Next
End If
Dim da As MySqlDataAdapter = New MySqlDataAdapter
da.SelectCommand = cmd
ds = New DataSet
da.Fill(ds)
da = Nothing
cmd.Parameters.Clear()
End If
Catch ex As Exception
ds = Nothing
End Try
Return ds
End Function
'UpdateRows() function automatically generates single-table commands used to reconcile changes made to a DataSet
'with the associated table in the MySQL database.
'The function gets
' - the SQL SELECT used to populate the dataset
' - the updated dataset
' - the table name that must be updated in the database
'Note:
'The SQL SELECT must also return at least one primary key or unique column, otherwise an exception is generated;
Public Function UpdateRows(ByVal sql_select As String, ByRef input_dataset As DataSet, ByVal table_name As String) As Boolean
Dim success As Boolean = False
Try
If IsConnectionAlive() Then
Dim data_adapter As New MySqlDataAdapter(sql_select, _cnx_obj)
Dim cmd_builder As New MySqlCommandBuilder(data_adapter)
data_adapter.Update(input_dataset, table_name)
success = True
End If
Catch ex As Exception
success = False
End Try
Return success
End Function
'To use a MySqlDataReader,
'You have to use OpenReader() and CloseReader() functions like in the following example:
'
' Public Sub ReadMyData(ByVal connection_string As String)
'
' Dim mysql As MySqlHelper = New MySqlHelper()
' mysql.Open(connection_string)
'
' Dim sql As String = "SELECT OrderID, CustomerID FROM Orders"
' Dim reader As MySqlDataReader = Nothing
'
' mysql.OpenReader(reader, sql)
' While reader.Read()
' Console.WriteLine((reader.GetInt32(0) & ", " & reader.GetString(1)))
' End While
' mysql.CloseReader(reader)
'
' mysql.Close()
'
' End Sub
Public Sub OpenReader(ByRef reader As MySqlDataReader, ByVal sql_select As String)
Try
If IsConnectionAlive() Then
Dim cmd As New MySqlCommand(sql_select, _cnx_obj)
reader = cmd.ExecuteReader()
End If
Catch ex As Exception
reader = Nothing
End Try
End Sub
Public Sub CloseReader(ByRef reader As MySqlDataReader)
Try
reader.Close()
Catch ex As Exception
End Try
End Sub
'Execute SQL command and return the number of records affected
Public Function Execute(ByVal sql_command As String) As Long
Dim affected_rows As Long = 0
Try
If IsConnectionAlive() Then
Dim cmd As New MySqlCommand(sql_command, _cnx_obj)
affected_rows = cmd.ExecuteNonQuery()
End If
Catch ex As Exception
affected_rows = 0
End Try
Return affected_rows
End Function
'GetSingleValue() function executes the query, and returns the value of the first column of the first row in the
'result set returned by the query. Extra columns or rows are ignored.
'Note:
'Use the GetSingleValue() method to retrieve a single value (for example, an aggregate value)
'from a database, like "select count(*) from region"
Public Overloads Function GetSingleValue(ByVal sql_select As String) As Object
Return ExecuteScalar(sql_select, DirectCast(Nothing, MySqlParameter()))
End Function
Public Overloads Function GetSingleValue(ByVal sql_select As String, ByVal ParamArray sql_parameters As MySqlParameter()) As Object
Return ExecuteScalar(sql_select, sql_parameters)
End Function
'ExecuteScalar: Executes the query, and returns the value (integer/string) of the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
Private Function ExecuteScalar(ByVal sql_select As String, ByVal ParamArray sql_parameters As MySqlParameter()) As Object
Dim row As Object = Nothing
Try
If IsConnectionAlive() Then
Dim cmd As New MySqlCommand(sql_select, _cnx_obj)
If sql_parameters IsNot Nothing Then
For Each p As MySqlParameter In sql_parameters
cmd.Parameters.Add(p)
Next
End If
row = cmd.ExecuteScalar()
cmd.Parameters.Clear()
End If
Catch ex As Exception
row = Nothing
End Try
Return row
End Function
'25/1/12 -> 2012-01-25
Public Overloads Function ConvertToMySqlDate(ByVal input_date As Date) As String
Return input_date.ToString("yyyy-MM-dd")
End Function
'25/1/12 -> 2012-01-25
Public Overloads Function ConvertToMySqlDate(ByVal input_date As String) As String
Return ConvertDateTime(input_date, DATETIME_TYPE.MYSQL, True)
End Function
'25/1/12 13:00 -> 2012-01-25 13:00:00
Public Overloads Function ConvertToMySqlDateTime(ByVal input_datetime As DateTime) As String
Return input_datetime.ToString("yyyy-MM-dd HH:mm:ss")
End Function
'25/1/12 13:00 -> 2012-01-25 13:00:00
Public Overloads Function ConvertToMySqlDateTime(ByVal input_datetime As String) As String
Return ConvertDateTime(input_datetime, DATETIME_TYPE.MYSQL)
End Function
'2012-01-25 -> 25-01-2012
Public Overloads Function ConvertToDate(ByVal mysql_date As Date) As String
Return mysql_date.ToString("dd-MM-yyyy")
End Function
'2012-01-25 -> 25-01-2012
Public Overloads Function ConvertToDate(ByVal mysql_date As String) As String
Return ConvertDateTime(mysql_date, DATETIME_TYPE.SYSTEM, True)
End Function
'2012-01-25 13:00:00 -> 25-01-2012 13:00:00
Public Overloads Function ConvertToDateTime(ByVal mysql_datetime As DateTime) As String
Return mysql_datetime.ToString("dd-MM-yyyy HH:mm:ss")
End Function
'2012-01-25 13:00:00 -> 25-01-2012 13:00:00
Public Overloads Function ConvertToDateTime(ByVal mysql_datetime As String) As String
Return ConvertDateTime(mysql_datetime, DATETIME_TYPE.SYSTEM)
End Function
Private Enum DATETIME_TYPE As Integer
SYSTEM = 0
MYSQL = 1
End Enum
Private Function ConvertDateTime(ByVal input_datetime As String, ByVal output_datetime_type As DATETIME_TYPE, _
Optional ByVal get_only_date As Boolean = False) As String
'The function returns a datetime in a well-formatted string for regular or MySQL use.
'You have to insert a datetime string formatted by {,/,-,.,:} symbols.
'Besides, for the input date, You need to specify "day, month and year";
'for the time, only "hour and minutes", "seconds" doesn't matter.
'The function returns Nothing if an error is encountered.
'You can select one of the following datetime types as output:
'SYSTEM: "(2 digits day)-(2 digits month)-(4 digits year) hours:minutes:seconds"
'MYSQL: "(4 digits year)-(2 digits month)-(2 digits day) hours:minutes:seconds"
'examples:
'for a regular datetime in input and
'a MySql datetime as output, You 'll get...
'25-1-08 -> 2008-01-25 00:00:00
'25/1/08 -> 2008-01-25 00:00:00
'25/1/08 12:00 -> 2008-01-25 12:00:00
'25-1-08 12:00 -> 2008-01-25 12:00:00
'25.1.08 12:00 -> 2008-01-25 12:00:00
'25.1.08 12.00 -> 2008-01-25 12:00:00
'25.1.08 12.00.23 -> 2008-01-25 12:00:23
Dim dt_out As String = Nothing
Dim dt_type As String = ""
If output_datetime_type = DATETIME_TYPE.MYSQL Then
dt_type = "yyyy-MM-dd HH':'mm':'ss"
Else
dt_type = "dd-MM-yyyy HH':'mm':'ss"
End If
Try
If input_datetime.IndexOf(".", 0, CompareMethod.Binary) > 0 Then
Dim tmp() As String = input_datetime.Split(" ")
If tmp.GetUpperBound(0) > 0 Then
input_datetime = tmp(0).Replace(".", "-") & " " & tmp(1).Replace(".", ":")
End If
End If
Dim myDTFI As DateTimeFormatInfo = New CultureInfo("it-IT", False).DateTimeFormat
myDTFI.FullDateTimePattern = "dd-MM-yyyy HH':'mm':'ss"
myDTFI.DateSeparator = "-"
myDTFI.TimeSeparator = ":"
Dim myDT As New DateTime
myDT = System.Convert.ToDateTime(input_datetime, myDTFI)
dt_out = Format(myDT, dt_type)
myDT = Nothing
myDTFI = Nothing
If get_only_date = True Then
Dim s() As String = dt_out.Split(" ")
If s.GetUpperBound(0) > 0 Then
dt_out = s(0)
End If
End If
Catch ex As Exception
dt_out = Nothing
End Try
Return dt_out
End Function
End Class
Leave a Reply