Simple Query Builder using Visual Basic.Net

Submitted by joken on Tuesday, October 8, 2013 - 15:10.
In this tutorial, It covers how to create a simple MySQL query builder using Visual basic. This application allows user to select MySQL database and when the “use” button is clicked, the program will then load all the tables existing from a specific database. And every time the user select the name of a table it will generate a query and display the result on the datagridview provided. And it looks like as shown below. To start building this application, add a following object on the form such as: one textbox, two buttons, three groupbox, listbox, label and a datagridview. Then design all the objects same as shown above. This time we’re going to functionality to our application. First double click the form, and as you have observed you will be redirected into code view, and add the following declaration and one function for Mysql Connection below the public class. And here’s the code: 
  1. 'Represents an SQL statement or stored procedure to execute against a data source.
  2.     Dim cmd As New MySqlCommand
  3.     Dim da As New MySqlDataAdapter
  4.     Public total As Integer
  5.     Dim publictable As New DataTable
  6.     'declare conn as connection and it will now a new connection because 
  7.     'it is equal to Getconnection Function
  8.     Dim con As MySqlConnection = jokenconn()
  9.  
  10.     Public Function jokenconn() As MySqlConnection
  11.         Return New MySqlConnection("server=localhost;user id=root;password=;database=")
  12.     End Function
Next, on the form load. Add the following code: This code will simply load all the databases on the combobox provided. 
  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.         Dim sql As String = "SHOW DATABASES"
  3.         Dim publictable As New DataTable
  4.         Try
  5.  
  6.             'bind the connection and query
  7.             With cmd
  8.                 .Connection = con
  9.                 .CommandText = sql
  10.             End With
  11.  
  12.             da.SelectCommand = cmd
  13.             da.Fill(publictable)
  14.  
  15.             With cbdb
  16.                 .DataSource = publictable
  17.                 .DisplayMember = "Database"
  18.                 .ValueMember = "Database"
  19.             End With
  20.  
  21.             da.Dispose()
  22.  
  23.         Catch ex As Exception
  24.             MsgBox(ex.Message)
  25.  
  26.         End Try
  27.         con.Clone()
  28.  
  29.     End Sub
And it looks like as shown below. Then, on the “use” button add the following code: This code will display all the tables on a listbox based on the database selected by the user. 
  1. Private Sub btntest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntest.Click
  2.  
  3.         Dim sql As String = "SHOW TABLES FROM " & cbdb.Text
  4.         Dim publictable As New DataTable
  5.         Try
  6.  
  7.             'bind the connection and query
  8.             With cmd
  9.                 .Connection = con
  10.                 .CommandText = Sql
  11.             End With
  12.  
  13.             da.SelectCommand = cmd
  14.             da.Fill(publictable)
  15.             '  publictable.Rows.Add("Please Select...")
  16.             With lstfield
  17.                 .DataSource = publictable
  18.                 .DisplayMember = "Tables_in_" & cbdb.Text
  19.                 .ValueMember = "Tables_in_" & cbdb.Text
  20.  
  21.             End With
  22.             ' dtgrd.Columns(1).Visible = False
  23.             da.Dispose()
  24.  
  25.         Catch ex As Exception
  26.             MsgBox(ex.Message)
  27.  
  28.         End Try
  29.         con.Clone()
  30.  
  31.     End Sub
Next, we will add functionality to “Execute query” button. Add the following code: 
  1. Private Sub btngo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
  2.         Dim publictable As New DataTable
  3.         Dim sql As String
  4.         sql = txtquery.Text
  5.         Try
  6.  
  7.             'bind the connection and query
  8.             With cmd
  9.                 .Connection = con
  10.                 .CommandText = sql
  11.             End With
  12.             'Gets or sets a Transact-SQL statement or stored procedure used to select records in the data source.
  13.             da.SelectCommand = cmd
  14.             da.Fill(publictable)
  15.             'get the datasource from publictable and passed to datagridview
  16.             dtresultview.DataSource = publictable
  17.  
  18.             'dispose the dataadapter
  19.             da.Dispose()
  20.         Catch ex As Exception
  21.  
  22.             MsgBox(ex.Message)
  23.  
  24.         End Try
  25.         con.Close()
  26.         da.Dispose()
  27.     End Sub
Then on creating a query based on tables selected by the user. Here’s the following code: 
  1. Private Sub lstfield_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstfield.Click
  2.         txtquery.Text = "SELECT * FROM " & cbdb.Text & "." & lstfield.SelectedValue.ToString
  3.         Call btngo_Click(sender, e)
  4.     End Sub
And here’s all the codes for this tutorial: 
  1. Imports MySql.Data.MySqlClient
  2. Public Class Form1
  3.     'Represents an SQL statement or stored procedure to execute against a data source.
  4.     Dim cmd As New MySqlCommand
  5.     Dim da As New MySqlDataAdapter
  6.     Public total As Integer
  7.     Dim publictable As New DataTable
  8.     'declare conn as connection and it will now a new connection because 
  9.     'it is equal to Getconnection Function
  10.     Dim con As MySqlConnection = jokenconn()
  11.  
  12.     Public Function jokenconn() As MySqlConnection
  13.         Return New MySqlConnection("server=localhost;user id=root;password=;database=")
  14.     End Function
  15.  
  16.     Private Sub btngo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
  17.         Dim publictable As New DataTable
  18.         Dim sql As String
  19.         sql = txtquery.Text
  20.         Try
  21.             'bind the connection and query
  22.             With cmd
  23.                 .Connection = con
  24.                 .CommandText = sql
  25.             End With
  26.             'Gets or sets a Transact-SQL statement or stored procedure used to select records in the data source.
  27.             da.SelectCommand = cmd
  28.             da.Fill(publictable)
  29.             'get the datasource from publictable and passed to datagridview
  30.             dtresultview.DataSource = publictable
  31.  
  32.             'dispose the dataadapter
  33.             da.Dispose()
  34.         Catch ex As Exception
  35.  
  36.             MsgBox(ex.Message)
  37.  
  38.         End Try
  39.         con.Close()
  40.         da.Dispose()
  41.     End Sub
  42.  
  43.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  44.         Dim sql As String = "SHOW DATABASES"
  45.         Dim publictable As New DataTable
  46.         Try
  47.  
  48.             'bind the connection and query
  49.             With cmd
  50.                 .Connection = con
  51.                 .CommandText = sql
  52.             End With
  53.  
  54.             da.SelectCommand = cmd
  55.             da.Fill(publictable)
  56.  
  57.             With cbdb
  58.                 .DataSource = publictable
  59.                 .DisplayMember = "Database"
  60.                 .ValueMember = "Database"
  61.             End With
  62.  
  63.             da.Dispose()
  64.  
  65.         Catch ex As Exception
  66.             MsgBox(ex.Message)
  67.  
  68.         End Try
  69.         con.Clone()
  70.  
  71.     End Sub
  72.  
  73.  
  74.     Private Sub btntest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntest.Click
  75.  
  76.         Dim sql As String = "SHOW TABLES FROM " & cbdb.Text
  77.         Dim publictable As New DataTable
  78.         Try
  79.  
  80.             'bind the connection and query
  81.             With cmd
  82.                 .Connection = con
  83.                 .CommandText = Sql
  84.             End With
  85.  
  86.             da.SelectCommand = cmd
  87.             da.Fill(publictable)
  88.             '  publictable.Rows.Add("Please Select...")
  89.             With lstfield
  90.                 .DataSource = publictable
  91.                 .DisplayMember = "Tables_in_" & cbdb.Text
  92.                 .ValueMember = "Tables_in_" & cbdb.Text
  93.  
  94.             End With
  95.             ' dtgrd.Columns(1).Visible = False
  96.             da.Dispose()
  97.  
  98.         Catch ex As Exception
  99.             MsgBox(ex.Message)
  100.  
  101.         End Try
  102.         con.Clone()
  103.  
  104.     End Sub
  105.  
  106.     Private Sub lstfield_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstfield.Click
  107.         txtquery.Text = "SELECT * FROM " & cbdb.Text & "." & lstfield.SelectedValue.ToString
  108.         Call btngo_Click(sender, e)
  109.     End Sub
  110.  
  111. End Class
And this time, you can test your program by pressing “F5”.

Tags

Comments

Add new comment

About text formats