I am just trying to write a simple login appliation using Visual
Basic
2008 and MS Access 2007. What I have been looking for is an example
of
the most basic way to connect to the database named "example" and
select a value from the "username" and "password" fields in a table
called example.
I have found plenty of examples for Visual Basic 6, but they don't
seem to work 100% in 2008. I have also found plenty of long winded,
complicated scripts that try to do more with the database connection
than simply open it, pull two values out and store those two values
to
variables.
Any help would be appreciated, even if it is just a link to where I
can get the right information. This is a learning excercise for me,
so
I want to start with the most simplified database connection and work
my way up from there.
Hmmm... Well, I don't have 2007 Access 2007 to try this out with - but, I
suspect that major difference would be the connection string. So here is a
simple example using access 2003
Option Strict On
Option Explicit On
Option Infer Off
Imports System
Imports System.Data
Imports System.Data.OleDb
Module Module1
Sub Main()
Dim builder As New OleDbConnectionStringBuilder()
builder.Provider = "Microsoft.Jet.OLEDB.4.0"
builder.DataSource = "example.mdb"
Using connection As New OleDbConnection(builder.ConnectionString), command As New OleDbCommand("SELECT * FROM Example WHERE UserId = ?", connection)
command.Parameters.Add(New OleDbParameter("UserId", 2))
command.Connection.Open()
Using reader As OleDbDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
Console.WriteLine("User: {0} Password: {1}", reader("UserName"), reader("Password"))
End While
End Using
End Using
End Sub
End Module
That's using a reader. Now, you can also do this with a dataset and and a
dataadapter. Here is what that would look like:
Option Strict On
Option Explicit On
Option Infer Off
Imports System
Imports System.Data
Imports System.Data.OleDb
Module Module1
Sub Main()
Dim builder As New OleDbConnectionStringBuilder()
builder.Provider = "Microsoft.Jet.OLEDB.4.0"
builder.DataSource = "example.mdb"
Dim dt As New DataTable
Using _
connection As New OleDbConnection(builder.ConnectionString), _
command As New OleDbCommand("SELECT * FROM Example WHERE UserId = ?", connection), _
adapter As New OleDbDataAdapter(command)
command.Parameters.Add(New OleDbParameter("UserId", 2))
adapter.Fill(dt)
End Using
For Each row As DataRow In dt.Rows
Console.WriteLine("User: {0} Password: {1}", row("UserName"), row("Password"))
Next
End Sub
End Module
The main difference between the two is that the reader is a forward only,
readonly cursor. It maintains a persistant connection to the database while
it is open - and in some cases can be faster. But, because it maintains a
connection you can have locking issues, connection issues, etc.
The second method using a DataTable (or dataset) represents a disconnected
query. Basically, all the data from the query is pulled into memory and the
connection is closed to the database. This is usually the prefered method of
working with data in larger multiuser applications - because it tends to
scale better. Of course, it introduces other considerations - such as memory
usage and concurrency considerations.
There are also other methods, such as typed datasets, etc - that some people
seem to like, and there is nothing wrong with that, but my preference is to
layer my applications into multiple layers - Presentation, BLL (buisness logic
layer), DAL (Data Access Layer), and DTO (Data Transport Object) layer. The
DTO's are basically just classes with no real functionality. They simply
provide a common layer to transport information accross the various layers of
the application. The BLL contains all of the application buisness logic, and
the DAL does all of the dataaccess.
I basically have a base dal library with a DataAccess class that encapsulates
a lot of common functionality - then each application creates it's own classes
by inheriting from this base class. The DataAccess class uses reflection and
custom mapping attributes on the DTO's to populate them from the query...
There is a similar arrangement with the BAL with a base BuinessLayer class.
The presentaion layer only deals with the DTO's and the BAL and knows nothing
of the DAL.
The above would look something like this in one of my apps (air-code!)
' User DTO
Public Class UserInfo
<DataColumnNameMapping("UserId")> _
Public Property Id() As Integer
...
End Property
<DataColumnNameMapping("UserName")> _
Public Property UserName() As String
...
End Property
<DataColumnNameMapping("Password")> _
Public Property Password() As String
...
End Property
End Class
Public Class UserBal
Inherits BuisnessLayer
Public Sub New (ByVal connectionString As String)
Me.ConnectionString = connectionString
End Sub
....
Public Function UserInfo GetUser(ByVal id As Integer)
Return dal.GetUser(id)
End Function
End
Friend Class UserDal
Inherits DataAccess
Public UserInfo GetUser(ByVal id As Integer)
Using reader As IDataReader = ExecuteReader("SELECT * FROM Example WHERE UserId = ?", id)
Dim info As List(Of UserInfo = ToList(Of UserInfo)(reader)
Return If info.Count > 0, info(0), Nothing
End Using
End Function
End Class
Then the console app would look like:
Public Sub Main ()
Dim builder As New OleDbConnectionStringBuilder()
builder.Provider = "Microsoft.Jet.OLEDB.4.0"
builder.DataSource = "example.mdb
' this would probably really come from a config file
Dim bal As New UserBal (builder.ConnectionString)
Dim info As UserInfo = bal.GetUser(2)
If info IsNot Nothing Then
Console.WriteLine("User: {0} Password: {1}", info.UserName, info.Password)
Else
Console.WriteLine ("User Not Found!")
End If
End Sub
Anyway, this is a simple example - and all of the top. It is only meant to
illustrate that there are numerous ways to access data...
