Pen Truth Contributor
Database Tutorial Source Code :
As promised, here is my complete code for the data base tutorial. This represents a cleaned up and tested version, so I hope it is helpful to those learning database programming. Enjoy.
' Project: My Database
' Programmer: Mike C. Trethowan
' Date: November 2, 2009
' Description:
'
'
' Program perameters:
' Creates an empty database
' Delets the database
' Checks if tables exist in the database and return the count
' Creates an empty table within the database
' Fills the data grid view with data from a table within the database
' Adds information to the database table
' Clears all data from the database table
' Refreshs the grid view - same function as fill button - provides an example of
' how one event handler can handle several button presses.
' This is usefull for a group of Radio Buttons
'
' Notes"
' Under Build Tab Change Machine Type In Configuration Manager To x86
' To Run On 64 bit or Systems Above XP
' May need to configure tool bar - Build to show Configuration Manager
' Add a COM Reference to: Microsoft ADO Ext. 2.8 for DDL and Security
'
' This project requires 8 Buttons, 2 Labels, 2 Text Boxes and 1 Data Grid View:
'
' Name Text
' btnCreateDatabase Create Database
' btnDeleteDatabase Delete Database
' btnTablesExist Tables Exist
' btnCreateTable Create Table
' btnFillGridView Fill Grid View
' btnAddData Add Data To Table
' btnClearAllEntries Clear All Table Entries
' btnRefreshGridView Refresh Grid View
'
' Label1 Enter First Name
' Label2 Enter Last Name
' txbFirstName First Name
' txbLastName Last Name
'
' DataGridView1
#Region " Imports "
Imports ADOX
Imports System.Data
Imports System.Data.OleDb
#End Region
Public Class frmMain
#Region " Dims "
Dim dbDelete As String
Dim dbInsert As String
Dim dbNameLoc As String
Dim dbFileName As String
Dim dbProvider As String
Dim dbSelectFrom As String
Dim dbTableName As String
Dim dbTableCreate As String
#End Region
#Region " Form Loading & Closing "
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dbFileName = "myDataBase.mdb"
dbTableName = "myDbTable"
dbDelete = "DELETE FROM " & dbTableName
dbSelectFrom = "SELECT * FROM " & dbTableName
dbInsert = "INSERT INTO " & dbTableName & "(First_Name, Last_Name) VALUES (@First_Name, @Last_Name)"
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = "
dbTableCreate = "CREATE TABLE " & dbTableName & "(First_Name TEXT(20), Last_Name TEXT(20))"
dbNameLoc = "C:\Visual Studio 2008\Projects\DataBases\" & dbFileName
End Sub
#End Region
#Region " Button Operations "
Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDatabase.Click
' If System.IO.File.Exists(dbNameLoc) Then ' Tests to see if the file exists and returns a boolean True/False
' MessageBox.Show(" That Database Already Exists") ' If the file exists the resulting boolean is True
' Else ' Since the test needs to also provide for a False condition we place an else statement here
MsgBox(createEmptyDatabase(dbProvider, dbNameLoc)) ' Call And Show Results
' End If ' End of If System.IO.File.Exists
End Sub ' btnCreateDatabase_Click
Private Sub btnDeleteDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteDatabase.Click
' System.IO.File.Exists result returns a Boolean
' System.IO.File.Delete does not throw an Exception
Try
If System.IO.File.Exists(dbNameLoc) Then ' Test if file exists
System.IO.File.Delete(dbNameLoc) ' File exists, delete file
MsgBox("Database Deleted")
Else ' Else file was not found
MsgBox("Database Not Found") ' File not exists
End If ' End of If System.IO.File.Exists
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub ' btnDeleteDatabase_Click
Private Sub btnTablesExist_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTablesExist.Click
' Here we remove several lines of code simplifying our project. Simple is always better.
MsgBox(checkForTables(dbProvider, dbNameLoc, dbTableName)) ' Eliminates IF statement And Calls the Function And Shows The Results
End Sub ' btnTablesExist_Click
Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTable.Click
MsgBox(createDataBaseTable(dbProvider, dbNameLoc, dbTableCreate)) ' Call And Show Results
End Sub ' btnCreateTable_Click
Private Sub btnFillGridView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles _
btnFillGridView.Click, btnRefreshGridView.Click
MsgBox(fillDataTable(dbProvider, dbNameLoc, dbTableCreate)) ' Call And Show Results
End Sub ' btnFillGridView_Click
Private Sub btnAddData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddData.Click
' Here we will reduce the amount of code to one line and simply display the results of the function
MsgBox(addData(dbProvider, dbNameLoc, txbFirstName.Text, txbLastName.Text)) ' Call And Show Results
End Sub 'btnAddData_Click
Private Sub btnClearAllEntries_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClearAllEntries.Click
MsgBox(clearDataTable(dbProvider, dbNameLoc, dbTableName)) ' Call And Show Results
End Sub ' btnClearAllEntries_Click
#End Region
#Region " Database Operations "
Private Function createEmptyDatabase(ByVal dbConn As String, ByVal dbName As String) As String
Dim sRtrn As String ' The boolean True/False that gets returned to the calling sub, btnCreateDatabase_Click
Dim cat As ADOX.Catalog = New ADOX.Catalog()
Try ' Try to run the following code and catch errors
cat.Create(dbConn & dbName)
sRtrn = "Successfully Created A Database"
Catch ex As Exception ' Catches errors
sRtrn = ex.Message ' Send Ex Message
Finally ' Once the Try statement has finished do some more stuff
cat = Nothing ' Dispose of any opened objects
End Try ' Finished Trying
Return sRtrn ' Return the results to the calling sub.
End Function ' Function createEmptyDatabase
Private Function createDataBaseTable(ByVal dbConn As String, ByVal dbName As String, ByVal dbTable As String) As String
Dim sRtrn As String ' Returns Results
Dim dbConnect As New OleDbConnection(dbConn & dbName) ' Create the database connection object
Dim dbCommand As New OleDbCommand(dbTable, dbConnect) ' Create the database command to create the table
Try
dbConnect.Open() ' Open the database connection
dbCommand.ExecuteNonQuery() ' initiate the command to create the table
sRtrn = "Successfully Created A Table"
Catch ex As Exception ' Catches errors
sRtrn = ex.Message ' Could not create a table, returns False
Finally ' Regardless of the outcome, make sure that the database has been closed
dbConnect.Close() ' Open the database connection
End Try
Return sRtrn ' Return the results to the calling sub.
End Function ' Function createDataBaseTable
Private Function checkForTables(ByVal dbConn As String, ByVal dbName As String, ByVal dbTable As String) As String
Dim sRtrn As String ' Returns number of tables found in database
Dim dbConnect As New OleDbConnection(dbConn & dbName) ' Create the database connection object
Try
dbConnect.Open()
' Can only create when a connection is open
Dim dbSchema As DataTable = dbConnect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, dbTable, "TABLE"})
' dbConnect.Close()
sRtrn = "There Are " & dbSchema.Rows.Count.ToString & " Tables In The Database"
Catch ex As Exception
sRtrn = ex.Message ' Send Ex Message
Finally ' Regardless of the outcome, make sure that the database has been closed
dbConnect.Close()
End Try
Return sRtrn ' Return the results to the calling sub.
End Function ' Function checkForTables
Private Function fillDataTable(ByVal dbConn As String, ByVal dbName As String, ByVal dbTable As String) As String
Dim sRtrn As String ' Returns Results
Dim dbConnect As New OleDbConnection(dbConn & dbName) ' Create the database connection object
Dim dataAdapter As New OleDbDataAdapter(dbSelectFrom, dbConnect) ' Adapter connects to the table we want data from
Dim dbDataSet As New DataSet() ' Creata a dataset to accept data from the table
Try
dbConnect.Open() ' Open the database connection
dataAdapter.Fill(dbDataSet, dbTable) ' Can only fill when a connection is open
DataGridView1.DataSource = dbDataSet
DataGridView1.DataMember = dbTable
sRtrn = "Successfully Retrieved Table Data"
Catch ex As Exception
sRtrn = ex.Message ' Send Ex Message
Finally ' Regardless of the outcome, make sure that the database has been closed
dbConnect.Close() ' Close the database connection
dataAdapter.Dispose() ' Cleane up
End Try
Return sRtrn ' Return the results to the calling sub.
End Function ' Function fillDataTable populates/refreshes DataGridView1 with table data
Private Function addData(ByVal dbConn As String, ByVal dbName As String, ByVal firstName As String, ByVal lastName As String) As String
Dim sRtrn As String ' Returns Results
Dim dbConnect As New OleDbConnection(dbConn & dbName) ' Create the database connection object
Try
dbConnect.Open() ' Open the database connection
Dim dbCommand As New OleDbCommand(dbInsert, dbConnect) ' Can only create when a connection is open
dbCommand.Parameters.AddWithValue("@First_Name", firstName) ' Add data to first column
dbCommand.Parameters.AddWithValue("@Last_Name", lastName) ' Add data to last column
dbCommand.ExecuteNonQuery() ' initiate the command to insert data
sRtrn = "Successfully Added Data."
Catch ex As Exception ' Catches errors
sRtrn = ex.Message ' Send Ex Message
Finally ' Regardless of the outcome, make sure that the database has been closed
dbConnect.Close() ' Close the database connection
End Try
Return sRtrn ' Return the results to the calling sub.
End Function ' Function addData inserts data into a table
Private Function clearDataTable(ByVal dbConn As String, ByVal dbName As String, ByVal dbTable As String) As String
Dim sRtrn As String ' Returns Results
Dim dbConnect As New OleDbConnection(dbConn & dbName) ' Create the database connection object
Try
dbConnect.Open() ' Open the database connection
Dim dbCommand As New OleDbCommand(dbDelete, dbConnect) ' Create the database command to create the table
dbCommand.ExecuteNonQuery() ' initiate the command to create the table
sRtrn = "Successfully Cleared Table Data"
Catch ex As Exception
sRtrn = ex.Message ' Send Ex Message
Finally ' Regardless of the outcome, make sure that the database has been closed
dbConnect.Close() ' Close the database connection
End Try
Return sRtrn ' Return the results to the calling sub.
End Function ' Sub clearDataTable()
#End Region
End Class
Return to “How To Search A Table Within A Database”
Disclaimer: The code in this tutorial is not intended as a final product or by no means the only way this project can be coded. The presented code is for educational purposes only and no warranty is granted or implied. I/we are not responsible for any damages as a result of using this code. Use at your own risk.