using linq with mysql database in vb.net

Question

how to use linq with mysql database in vb.net,I have searched on google but did not find clear tutorial on this?

thanks

Answer 1

Here's how I solved this problem. Assume your MySQL table called myTable looks like this:

+----+-------------+--------------+--------+------------+
| id | name        | phone        | office | department |
+----+-------------+--------------+--------+------------+
|  1 | Brian Baker | 858-555-1212 | N-110  | software   |
+----+-------------+--------------+--------+------------+
|  2 | Dana Damien | 858-555-1414 | N-120  | hardware   |
+----+-------------+--------------+--------+------------+

Use the links in the above answer to get the connector (download the MySql.Data.dll and add it to your project references), and then use something similar to this code:

Imports System.Data
Imports MySql.Data.MySqlClient

Public Class MyClass
    Public Sub getData()
        Dim myDataSet As New DataSet()

        Try
            Dim connStr As String = "Database=xxx;Data Source=xxx;User Id=xxx;
                Password=xxx;Allow Zero Datetime=true;Port=####"
            Dim connection As New MySqlConnection(connStr)
            Dim query As String = "select * from myTable where department='software';"
            Dim cmd As New MySqlDataAdapter(query, connection)
            connection.Open()
            cmd.Fill(myDataSet)
            connection.Close()
            cmd.Dispose()
        Catch ex As Exception
            MsgBox("Error opening database: " & ex.Message)
            Exit Sub
        End Try

        Dim employeeData As DataTable = myDataSet.Tables(0)

        Dim myQuery = From employee In employeeData.AsEnumerable()
                      Where employee.Field(Of String)(4) = "software"
                      Select New With {
                        .name = employee.Field(Of String)(1),
                        .phone = employee.Field(Of String)(2),
                        .office = employee.Field(Of String)(3)
                      }

        For Each employee In myQuery
            Console.Write("Name: " + employee.name)
            Console.Write("Phone: " + employee.phone)
            Console.WriteLine("Office: " + employee.office)
        Next
    End Sub
End Class

Note we are accessing the columns in the Linq query by number and that they are 0-based.

Answer 2

Here's a blog post which illustrates a step by step tutorial of how you could use EF with MySQL. All you have to do is install MySQL Connector.