Imports MySql.Data.MySqlClient
Public Class Form1
Public myTableName As String
Public mySqlQuery As String
Public StringaConnessioneMysql As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myDatabase As String
myDatabase = "test"
StringaConnessioneMysql = CreaStringaConnessioneMySql(3306, "localhost", myDatabase, "root")
Dim VerificaConnessione As Boolean
VerificaConnessione = VerificaConnessioneMySql(StringaConnessioneMysql)
If VerificaConnessione = True Then
'Sono connesso correttamente al Database MySql
''Variabile che contine il nome della Tabella
myTableName = "table"
'Query che crea la tabella "table" nel Database "test"
mySqlQuery = "CREATE TABLE `" & myDatabase & "`.`" & myTableName & "` (ID INT NOT NULL AUTO_INCREMENT, Nome TEXT, PRIMARY KEY (ID))"
'Verifica scrittura corretta Table
'Console.Write(mySqlQuery)
'Crea la tabella di nome table - con dentro i parametri ID(INT) e Nome(TEXT)
If EseguiQueryMySql(StringaConnessioneMysql, mySqlQuery) = False Then
'MsgBox("Errore la tabella = [" & myTableName & "] è già presente nel Database ", vbCritical, "Errore")
End If
''Varibile che contine la Query
'mySqlQuery = "SELECT * FROM `" & myTableName & "` WHERE 1"
'Scrivi nella consol i risultati della tabella Colonna 0 e Colonna 1
'Scrivi in Consol i risultati della tabella
'CreateMySqlDataReader(mySqlQuery, StringaConnessioneMysql)
'Agiorno il Datagrid con i nuovi parametri
'AggiornaDataGrid(myTableName)
'Inserisci un valore predefinito ID = 1 , Nome = Marco
QueryInsertMySql(StringaConnessioneMysql, myDatabase, myTableName)
'Inserisci Usando la QuerySQL
mySqlQuery = "INSERT INTO `" & myDatabase & "`.`" & myTableName & "`(Nome) VALUES ('Davide')"
If EseguiQueryMySql(StringaConnessioneMysql, mySqlQuery) = False Then
MsgBox("Erore sintassi Query ", vbCritical, "Errore Query SQL")
End If
AggiornaDataGrid(myTableName)
End If
'Call ConnessioneMySql()
End Sub
Private Sub AggiornaDataGrid(ByVal myTable As String)
''Varibile che contine la Query
mySqlQuery = "SELECT * FROM `" & myTable & "` WHERE 1"
PopolaTabellaDataSet(mySqlQuery)
End Sub
Private Function CreaStringaConnessioneMySql(ByVal Port As Integer, ByVal Server As String _
, ByVal Database As String, ByVal Username As String, Optional ByVal Password As String = "") As String
Try
'Settaggi della connessione MySql - Costruisco la Stringa di Connessione
Dim myCSB As MySqlConnectionStringBuilder = New MySqlConnectionStringBuilder
myCSB.Port = Port 'Porta del Server - Standard è 3306
myCSB.Server = Server 'IP Server o Nome del Serer - localhost = 127.0.0.1
myCSB.UserID = Username 'Nome Utente
myCSB.Password = Password 'Password
myCSB.UseCompression = True 'Attiva la compressione
myCSB.Database = Database 'Nome del Database
myCSB.MaximumPoolSize = 50
Return myCSB.ConnectionString
Catch ex As Exception
Return Nothing
End Try
End Function
Private Function VerificaConnessioneMySql(ByVal StringaConnessioneMysql As String)
Try
If StringaConnessioneMysql = "" Then
Return False
End If
'Connessione al Server MySql
Dim myConnection As MySqlConnection = New MySqlConnection(StringaConnessioneMysql)
myConnection.Open()
'Stampa la versione dei Driver MySql
'Console.WriteLine(myConnection.ServerVersion)
'Chiudo la Conessione
myConnection.Close()
Return True
Catch ex As Exception
MsgBox("Elenco errori : " & ex.ToString, vbCritical, "Errore Connessione")
Return False
End Try
End Function
Private Sub PopolaTabellaDataSet(ByVal mySqlQuerys As String)
Try
'DataSet
Dim myDataSet As DataSet = New DataSet
myDataSet = SelectMySqlSrvRows(StringaConnessioneMysql, mySqlQuerys, myTableName)
'Popolo il Griglia con le informazioni del Database MySql
dataGrid1.DataSource = myDataSet.Tables(myTableName)
'Afatta grandezza del Display
dataGrid1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
dataGrid1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCells
dataGrid1.MultiSelect = False 'Disabilito la multi selezione di Record
dataGrid1.AllowUserToAddRows = False 'Sparisce l'ultima riga per l'inserimento nuovo Record
Catch ex As Exception
MsgBox("Elenco errori : " & ex.ToString, vbCritical, "Errore Connessione")
End Try
End Sub
Public Function SelectMySqlSrvRows(ByVal myConnection As String, ByVal mySelectQuery As String, ByVal myTableName As String) As DataSet
Try
Dim myConn As New MySqlConnection(myConnection)
Dim myDataAdapter As New MySqlDataAdapter()
myDataAdapter.SelectCommand = New MySqlCommand(mySelectQuery, myConn)
Dim myCommandBuilder As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)
myConn.Open()
Dim myDataSet As DataSet = New DataSet
myDataAdapter.Fill(myDataSet, myTableName)
' Code to modify data in DataSet here
' Without the MySqlCommandBuilder this line would fail.
myDataAdapter.Update(myDataSet, myTableName)
myConn.Close()
SelectMySqlSrvRows = myDataSet
Catch ex As Exception
MsgBox("Elenco errori : " & ex.ToString, vbCritical, "Errore Connessione")
Return New DataSet
End Try
End Function
Public Sub CreateMySqlDataReader(ByVal mySelectQuery As String, ByVal myConnectionString As String)
Try
Dim myConnection As New MySqlConnection(myConnectionString)
Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)
myCommand.Connection.Open()
Dim myReader As MySqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
While myReader.Read()
Console.WriteLine(myReader.GetString(0) & " - " & myReader.GetString(1))
End While
myReader.Close()
Catch ex As Exception
MsgBox("Elenco errori : " & ex.ToString, vbCritical, "Errore Connessione")
End Try
End Sub
Private Sub QueryInsertMySql(ByVal myConnectionString As String, ByVal myDatabase As String, ByVal myTable As String)
Dim myConnection As New MySqlConnection(myConnectionString)
'INSERT INTO `test`.`table` (`ID`, `Nome`) VALUES ('3', 'Prova');
Dim sqlCommand As New MySqlCommand
With sqlCommand
' .CommandText = "INSERT INTO `" & myDatabase & "`.`" & myTable & "`(`ID`, `Nome`)VALUES (@id,@nome)"
.CommandText = "INSERT INTO `" & myDatabase & "`.`" & myTable & "`( `Nome`)VALUES (@nome)"
.CommandType = CommandType.Text
.Connection = myConnection
' .Parameters.AddWithValue("@id", 10)
.Parameters.AddWithValue("@nome", "Marco")
End With
Try
myConnection.Open()
sqlCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As MySqlException
MsgBox(ex.Message.ToString)
End Try
End Sub
Private Function EseguiQueryMySql(ByVal myConnectionString As String, ByVal myQueryTable As String) As Boolean
If myQueryTable = "" Then
Return False
End If
Try
Dim myConnection As New MySqlConnection(myConnectionString)
Dim sqlCommand As New MySqlCommand
myConnection.Open()
sqlCommand.CommandText = myQueryTable
sqlCommand.CommandType = CommandType.Text
sqlCommand.Connection = myConnection
sqlCommand.ExecuteNonQuery()
myConnection.Close()
Return True
Catch ex As MySqlException
'MsgBox(ex.Message.ToString)
'La tabella esiste
Return False
End Try
End Function
Private Sub btnChiudi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChiudi.Click
End
End Sub
Private Sub btnEseguiQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEseguiQuery.Click
Try
Dim myQuerySTR As String
myQuerySTR = InputBox("Inserisci la Query Stringa = ", "QUERY INSERT TO ")
If myQuerySTR <> "" Then
If EseguiQueryMySql(StringaConnessioneMysql, myQuerySTR) = False Then
MsgBox("Attenzione Query Scritta Male Riprova ", vbExclamation, "ATTENZIONE!!!")
End If
End If
Catch ex As Exception
MsgBox("Errore : " & ex.ToString, vbCritical, "Errore")
End Try
End Sub
End Class