Results 1 to 1 of 1

Thread: Load/Edit/Save all tables on a MySql Server

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Load/Edit/Save all tables on a MySql Server

    Here's a small project with which you can load any table from a MySql server (locally or remotely) by simply selecting its name and then do any operation on its data; Insert, delete, update. All you need is to provide the hostname of the server and the username/password. I have included a test database from freedb.tech

    How to use the code:
    Create a new empty Windows Forms Project, Add two ListBoxes to the form, one DataGridView, one TextBox and one Button, then copy/paste the whole code. And then add a reference to MySql.Data.dll. The Form is shown in the attached image.

    Please note that the code has no error trapping whatsoever.

    Forum Gurus, please feel free to correct/revise the code. Thanks

    Code:
    Option Explicit On
    Option Strict On
    Imports MySql.Data.MySqlClient
    
    Public Class Form1
        Dim host As String = "freedb.tech"
        Dim user As String = "freedbtech_cpubix"
        Dim pass As String = "vbnfghrty456"
        Dim DS As New DataSet
        Dim BS As New BindingSource
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            TextBox1.Text = "show databases"
            ListBox1.DisplayMember = "database"
            ListBox1.DataSource = Go("GetItems")
        End Sub
    
        Private Sub ListBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
            TextBox1.Text = "SELECT table_name FROM information_schema.tables where TABLE_SCHEMA='" & ListBox1.Text & "'"
            ListBox2.DisplayMember = "table_name"
            ListBox2.DataSource = Go("GetItems")
        End Sub
    
        Private Sub ListBox2_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListBox2.SelectedIndexChanged
            DS.Clear()
            TextBox1.Text = "select * from " & ListBox2.Text
            BS.DataSource = Go("GetData")
            DataGridView1.DataSource = BS
        End Sub
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Go("SaveData")
            MessageBox.Show("Done!")
        End Sub
    
        Private Sub TextBox1_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
            If e.KeyChar = Convert.ToChar(Keys.Enter) Then
                DS.Clear()
                BS.DataSource = Go("GetData")
                DataGridView1.DataSource = BS
            End If
        End Sub
    
        Private Function Go(Operation As String) As DataTable
            Dim cns As String
            cns = "server=" & host & ";user id=" & user & ";password=" & pass & ";database=" & ListBox1.Text
            Using con As New MySqlConnection(cns)
                con.Open()
                Using cmd As New MySqlCommand(TextBox1.Text, con)
                    Using dap As New MySqlDataAdapter(cmd)
                        Select Case Operation
                            Case "GetItems"
                                Dim Items As New DataTable
                                dap.Fill(Items)
                                Return Items
                            Case "GetData"
                                dap.Fill(DS, ListBox2.Text)
                                Return DS.Tables(ListBox2.Text)
                            Case "SaveData"
                                Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(dap)
                                dap.UpdateCommand = cb.GetUpdateCommand
                                dap.InsertCommand = cb.GetInsertCommand
                                dap.DeleteCommand = cb.GetDeleteCommand
                                BS.EndEdit()
                                dap.Update(DS, ListBox2.Text)
                        End Select
                    End Using
                End Using
            End Using
        End Function
    End Class
    Attached Images Attached Images  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width