Connect to mysql in visual studio 2008

visual basic , mysql , .net Metairie, United States
  • 11 years ago

    I want to connect to a remote(or local) mysql database and retrieve individual fields for single uses. Can anyone provide me with a connection code as well as code to retrieve the fields please. Thanks C-Ryan

  • 11 years ago

    what you need is mysql connector from mysql.com mysql-connector-net-5.0.7 then install this connector after that the code you need to connect and manipulate database is as under

    @author: ashwani rawal @location: india @website: www.ashwanisoft.com

    Imports System.Data Imports MySql.Data.MySqlClient

    Public Enum AMYSQLERROR InvalidValue = -154 End Enum

    Class TableSchema Public colName As String Public colType As String End Class

    Public Class AMySqlData ' Fields Private adapter1 As MySqlDataAdapter Private cmd As MySqlCommand Private conn As MySqlConnection Private rsData As DataSet Private debugflag As Boolean = False Private traceflag As Boolean = False

    ' IF DEBUG MODE IS ON, EXCEPTION WILL BE THROWS
    ' OTHERWISE SILENT EXCEPTION HANDLING
    Public Property DebugMode() As Boolean
        Get
            Return debug_flag
        End Get
        Set(ByVal value As Boolean)
            debug_flag = value
        End Set
    End Property
    
    ' if trace mode is on each and every query
    ' results and query is shown
    Public Property TraceMode() As Boolean
        Get
            Return trace_flag
        End Get
        Set(ByVal value As Boolean)
            trace_flag = value
        End Set
    End Property
    
    
    Public Function about() As String
        Return "Ashwani Rawal : MySql Client Library"
    End Function
    ' returns current date as mysql format
    Public Function getNowDate() As String
        Return Now().Year & "/" & Now().Month & "/" & Now().Day
    End Function
    ' returns current time as mysql format
    Public Function getNowTime() As String
        Return Now().Hour & ":" & Now().Minute & ":" & Now().Second
    End Function
    ' close connection
    Public Sub closeConnection()
        Try
            conn.Close()
        Catch ex As MySqlException
            If debug_flag = True Then
                Throw ex
            End If
        End Try
    End Sub
    
    
    
    
    
    
    Public Function executeQuery(ByVal query As String, ByVal dataset_name As String) As DataSet
        Dim ds As DataSet
        Try
            If trace_flag = True Then
                MsgBox("Query: " & query)
            End If
            cmd = New MySqlCommand(query, conn)
            adapter1 = New MySqlDataAdapter
            adapter1.SelectCommand = cmd
            ds = New DataSet
            adapter1.Fill(ds, dataset_name)
            Return ds
        Catch ex As Exception
            If debug_flag = True Then
                Throw ex
            End If
        End Try
        Return Nothing
    End Function
    
    Public Sub executeUpdate(ByVal query As String)
        Try
            If trace_flag = True Then
                MsgBox("Query: " & query)
            End If
            cmd = New MySqlCommand
            cmd.Connection = conn
            cmd.CommandText = query
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
        Catch ex As MySqlException
            If debug_flag = True Then
                Throw ex
            End If
        End Try
    End Sub
    
    Public Sub callProcedureWithoutArgs(ByVal proc_name As String)
        Try
            cmd = New MySqlCommand
            cmd.Connection = conn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = proc_name
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            If debug_flag = True Then
                Throw ex
            End If
        End Try
    End Sub
    
    Public Function getScalerValue(ByVal query_string As String, ByVal field_name As String) As Integer
        Try
            Dim ds As DataSet = executeQuery(query_string, "gsv")
            Return ds.Tables(0).Rows(0).Item(field_name)
        Catch ex As Exception
            Return AMYSQL_ERROR.Invalid_Value
        End Try
    End Function
    
    Public Sub openConnection(ByVal source_ip As String, ByVal databaseName As String, ByVal userName As String, ByVal password As String)
        Dim constr As String = "Database=" & databaseName & ";Data Source=" & source_ip & ";User Id=" & userName & ";Password=" & password
        Try
            conn = New MySqlConnection(constr)
            conn.Open()
        Catch ex As MySqlException
            If debug_flag = True Then
                Throw ex
            End If
        End Try
    End Sub
    
    Public Sub openLocalRootConnection(ByVal databaseName As String, ByVal password As String)
        Dim constr As String = "Database=" & databaseName & ";Data Source=localhost;User Id=root;Password=" & password
        Try
            conn = New MySqlConnection(constr)
            conn.Open()
        Catch ex As MySqlException
            If debug_flag = True Then
                Throw ex
            End If
        End Try
    End Sub
    
    
    
    Public Function getTableSchema(ByVal query As String) As String
        Dim list As New System.Text.StringBuilder
        Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(query, conn)
        Dim reader As MySqlDataReader = cmd.ExecuteReader()
    
        Dim schema As DataTable = reader.GetSchemaTable()
    
        Dim row As DataRow
        Dim total As Integer = schema.Columns.Count
        For Each row In schema.Rows
            list.AppendLine(row("ColumnName") & ", " & row("DataType").ToString())
        Next
        reader.Close()
        Return list.ToString()
    End Function
    

    End Class

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Linux is only free if your time has no value” - Jamie Zawinski