 |
|
VB.Net Tutorial #8
And now we get to finish databases. So now we cab create databases, create tables, insert data and get data that we have put in back out again. Now we need to be able to update exisiting information with new information (perhaps the name we have in the database is wrong. Here's how we update that information:-
Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class DBTest
Public Shared Sub Main()
Dim answer As String = Nothing
Dim strSQL As String = "UPDATE fone Set strName='Fred', strNumb='567890123'" & _
" WHERE strName='Dave'"
Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source = newmdb.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connectionString)
Try
conn.Open
Console.WriteLine("Connected to data source")
Dim cmd As OleDbCommand = conn.CreateCommand
cmd.CommandText = strSQL
cmd.ExecuteNonQuery
Catch e As Exception
Console.WriteLine("Failed to connect to data source")
Console.WriteLine(e.Message)
Finally
conn.Close
Console.WriteLine("Data source closed")
End Try
Console.WriteLine("Press a key to continue...")
answer = System.Console.ReadLine
End Sub
End Class
There isn't anything really new here just a modified SQL statement where we update the fields where we find the name Dave. You might like to try some changes to test for various things. Is the where case sensitive? What happens if there are two (or more) Daves in the database. Now how about if we want to delete an entry from the database entirely. We do this:-
Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class DBTest
Public Shared Sub Main()
Dim answer As String = Nothing
Dim strSQL As String = Nothing
Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source = newmdb.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connectionString)
Try
conn.Open
Console.WriteLine("Connected to data source")
strSQL = "DELETE FROM fone WHERE strName = 'Dave'"
Dim cmd As OleDbCommand = conn.CreateCommand
cmd.CommandText = strSQL
cmd.ExecuteNonQuery
Catch e As Exception
Console.WriteLine("Failed to connect to data source")
Console.WriteLine(e.Message)
Finally
conn.Close
Console.WriteLine("Data source closed")
End Try
Console.WriteLine("Press a key to continue...")
answer = System.Console.ReadLine
End Sub
End Class
Again, the only thing that has really changed is the SQL statement this time deleting where we find Dave (poor Dave!). For completeness, here is what we do to remove the table:-
Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class DBTest
Public Shared Sub Main()
Dim answer As String = Nothing
Dim strSQL As String = Nothing
Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source = newmdb.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connectionString)
Try
conn.Open
Console.WriteLine("Connected to data source")
strSQL = "DROP TABLE fone"
Dim cmd As OleDbCommand = conn.CreateCommand
cmd.CommandText = strSQL
cmd.ExecuteNonQuery
Catch e As Exception
Console.WriteLine("Failed to connect to data source")
Console.WriteLine(e.Message)
Finally
conn.Close
Console.WriteLine("Data source closed")
End Try
Console.WriteLine("Press a key to continue...")
answer = System.Console.ReadLine
End Sub
End Class
Again, all that has really changed is the SQL statement. Beware that this will remove the table and all its data without giving a warning - that will be up to you to add. And that just about wraps up databases in VB.Net. Of course, there are still problems to be overcome. The main one is duplication of data. When daa is first entered, you need to check that the database doesn't already have that entry in it. How? For this particualr example where we have only two fields then read the database into a dataset (using the SELECT statement) and loop through all the entires looking for both name (case insensitive) and number and if they both match then chances are it is already in the database and you warn the user that thiese entires already exist. You maybe have to be aware that the phone number has been entered with a space in it so you either have to scan for spaces or insist that the user only enter phone numbers with (or without) spaces so you can be sure what format the data is in when searching. In databases with rather more fields in it, you need to single out a single field as being a primary key. A primary key should be wherever possible unique (Post/Zip Code or Car Registration number for example) so again you can be reasonably sure that data duplication doesn't happen. Then again, what do you do when two people live at the same address or both use the same car (husband and wife for example). You will have to make a decision based on experience or what your client prefers before writing the system and make sure you make it clear in the user manual for the system (ALWAYS provide a user manual either on paper or electronically). You might also have to consider what will happen when the database is being used across a network by multiple users. How you deal with these things are beyond the remit of this tutorial but they are real world problems that need to be considered and solved.
Thats it for databases. The final tutorial will focus on creating Windows GUI programming. In the meantime, enjoy messing around with databases. Catch you later...