Resolved Winform to capture records from an online mySQL db

[divjoy]

Hi,

I designing a winform application to capture data stored in a mySQL db on my https website.

I am not sure whether to access the mySQL db directly (if I can) or goto a webpage and read the records from it?

I have the passwords for both

It seems more logical to go straight to the db and query the tables, any suggestions of best method?

Thanking you

 

[jmcilhinney]

There's no choice to be made. Scraping a web page should be the absolute last option for getting data. Of course you should connect to the database directly if you can. The code will be the same as for any other database. The only thing that will change because the database is online is the connection string. You can add Connector/Net to your project as a NuGet package and then your code will be standard ADO.NET.

 

[divjoy]

Thanks for advice jmcilhinney.
Now have to code it! anyone done something similar I can use as a template?

 

[divjoy]

So my code so far is as follows, but doesn't return that data, I get "Cannot connect to server. Contact administrator" what am I doing wrong ?

Imports System
Imports System.Data
Imports MySql.Data.MySqlClient


Public Class FrmMySQLData
    Private ConnStr As String = ""
    Private conn As MySqlConnection()
   
    Private Sub FrmGetMySQL_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dbhost As String = "xxxxx"
        Dim dbuser As String = "cccccc"
        Dim dbpass As String = "xxxxxxx"
        Dim db As String = "xcxcxcxc"
        ConnStr = "server=" & dbhost & ";dns-srv=true;userid=" & dbuser & ";password=" & dbpass & ";database=" & db

  
    End Sub

    Private Sub btnGetData_Click(sender As Object, e As EventArgs) Handles btnGetData.Click
        Try

            MsgBox(1)
            Dim conn As New MySqlConnection(ConnStr)
            conn.Open()
            MsgBox(2)
            Dim query As String = "SELECT * FROM TblData;"
            Dim da As New MySqlDataAdapter(query, conn)
            Dim ds As New DataSet()
            da.Fill(ds, "ClientData")
            MsgBox(ds.Tables("ClientData").Rows.Count)
            Dim dt As DataTable = ds.Tables("ClientData")
            Me.DataGridView1.DataSource = dt
        
        Catch ex As MySql.Data.MySqlClient.MySqlException
            Select Case ex.Number
                Case 0
                    MessageBox.Show("Cannot connect to server. Contact administrator")
                Case 1045
                    MessageBox.Show("Invalid username/password, please try again")
            End Select
        Catch ex As Exception
            Console.WriteLine("Error: {0}", ex.ToString())
        Finally
            If conn IsNot Nothing Then
                '  conn.Close()
            End If
        End Try
        'conn.Close()
    End Sub
End Class

 

[jmcilhinney]

I don't really use MySQL much so I'm not sure exactly what it supports and what it doesn't but I suggest that you follow the patterns provided here for your connection string. I would also recommend that you use a MySqlConnectionStringBuilder rather than string concatenation. That way, you can't get the field names wrong.

 

[JohnH]

Also check that the database has been configured to allow remote connections.

 

[divjoy]

So got the remote connections allowed with my IP address and had to open the port 3306 and using the MySqlConnection string builder it all worked!

I am delighted!

Thanks for help.

 

[jmcilhinney]

Generally speaking, the preferred way to get data over the internet is via a web service. A web service works much like a web site except that it provides data rather than HTML for a browser to display. That way, you would be getting just the data you want/need, rather than that data being turned into HTML and then you extracting it from that HTML. The problem with screen-scraping is that it can be tedious if the HTML is complex and/or suboptimal and a tiny change in the HTML produced by the web site can break your code. The web service also hides the database from direct access by malicious users.