Accessing MySQL Databases

As we've been saying, working with different data sources is only a matter of changing the connection string and class names. You can access a MySQL database either using a DSN or using the database name directly in the connection string. You can use a database name directly, as shown in the following code:

 dim connectionString as string = "Driver={MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;" 

Or you can use an ODBC DSN, as you can see from the following code that uses TestDSN to connect to the data source:

 Dim conn as OdbcConnection = new OdbcConnection("DSN=TestDSN") 

To test this code, create a Windows application, add a DataGrid control to the form, and then write the code in Listing 10-2 on the Form_Load event. As you can see in Listing 10-2, this code is similar to the code you saw in Listing 10-1. It creates a Connection object, a DataAdapter, fills the DataSet from the DataAdapter, and then sets the DataSet's DefaultViewManager as the DataGrid control's DataSource property.

Listing 10-2: Accessing a MySQL Database

 Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load     Dim connectionString As String = _     "Driver={MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;"     Dim conn As OdbcConnection = New OdbcConnection(connectionString)     conn.Open()     Dim da As OdbcDataAdapter = New OdbcDataAdapter _     ("SELECT CustomerID, ContactName, ContactTitle FROM Customers", conn)     Dim ds As DataSet = New DataSet("Cust")     da.Fill(ds, "Customers")     DataGrid1.DataSource = ds.DefaultViewManager     conn.Close()     conn.Dispose()   End Sub