VB.NET Tutorials

Welcome to Dream.In.Code
	Become an Expert! Join 307,109 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,015 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

Access and SQL Database Handler for Newbies and the Lazy

Access and SQL Database Handler for Newbies and the Lazy

Options

olibenu

5 Oct, 2009 - 02:30 AM

Post #1

The laziness begins with the insertion of ~~this~~ (check new file below) as a new class that will be used to access the database.

Instructions:

1. declare it like this

CODE

dim myDB as new DB

or this

CODE

dim myDB as new DB(DbType.SQL)

2. specify db type

CODE

myDB.type = DBType.SQL

other types are DBType.ACCESS ~~and DBType.ACCESS07~~
if you dont specify a type, it uses a default which is SQL

3. specify details
for 2007 access
dim myDB as new DB
myDB.Type = DBType.ACCESS07
myDB.Path = "db.accdb"
for 2003 access
dim myDB as new DB
myDB.Type = DBType.ACCESS
myDB.Path = "db.mdb"

for ms access (note that it chooses 2k7 or 2k3 based on extension)

CODE

dim myDB as new DB
myDB.Type = DBType.ACCESS
myDB.Path = "db.mdb" 'or myDB.Path = "db.accdb"

for ms sql

CODE

dim myDB as new DB
myDB.Type = DBType.SQL
myDB.name = "db"

4. reading data

CODE

With myDB
If (.Connect And .Open) Then
Dim alRows As New ArrayList, alColumns As New ArrayList
alRows = .Read("atable")
dim iRowIndex as integer, iColIndex as integer
for iRowIndex= 0 to alRows.count - 1
'access your rows
for iColIndex = 0 to alRows(iRowIndex).count -1
'access your columns
next
next

note, you might want to use your own select statement:

CODE

myDB.Command="select *"
myDB.Read

5. Write data
you have to use your own sql statements

CODE

myDB.command = "insert into ......"
dim rowsaffected=myDB.Write

CODE

dim rowsaffected=myDB.Write("insert into ...")

6. close

CODE

myDB.close

if anyone can add xls & xlsx support ~~or combine mdb & accdb into one dbtype~~ please do. the same lazy spirit which made me avoid conn strings, readers and stuff has stopped me from adding those for now.

also, i have not tested the username password usage

This post has been edited by olibenu: 9 Oct, 2009 - 08:52 AM

Register to Make This Ad Go Away!

olibenu

7 Oct, 2009 - 01:52 AM

Post #2

no one has used it? review

olibenu

9 Oct, 2009 - 08:55 AM

Post #3

modified the class and compressed access 2k3 and 2k7 into one dbtype
also added documentation.
if u can, add xls&xlsx support and mysql

Attached File(s)

DB.vb.txt ( 24.44k ) Number of downloads: 17

olibenu

9 Oct, 2009 - 12:13 PM

Post #4

licked the error that Read() throws when a table does not exist or command not successfully executed
this was due to no test for an empty DataReader

Attached File(s)

DB.new.vb.txt ( 25.56k ) Number of downloads: 24

olibenu

20 Oct, 2009 - 03:57 AM

Post #5

Access and SQL (also Excel) Database Handler for Newbies and the Lazy

I've rushed the earlier part of this tutorial so I've decided to slow down and explain things a little.
Access and SQL (also Excel) Database Handler for Newbies and the Lazy is what I use in my databse applications

because of the following reasons:
-I'm lazy
-I dont line importing stuff for every small database operation
-I don't want to remember connection strings
It has worked so far for me that's why I've been extending it to other areas: It started with MS SQL and MS Access

2003, 2007 and now can manage Excel 2003 and 2007. I have an upcoming project to do with mySQL and hopefully, I'll

extended this handler to cover that. I also intended to add features such as Record Locking and Database

Transactions.

Enough of the small talk, how is this better than doing stuff straight?
To connect to a database, you have to
1. first of all declare a connector/connection
2. define a connection string to connect to a database
3. declare a command to use in queries and
4. declare a datareader/dataset to get information in and out of the database.

These steps are simplified in DB Handler.
(before you read on, you might want to download a copy attached below this writeup so that you can go along)

Connection
Declaring a connection is based o what database you want to access. You can use the OleDb, ADODB, SQL or other

connections. As a newbie, this might be confusing to you because all you know is you have a database which you

want to write to and read from, you don't give a damn about connections, just show me how to display data from my

database. Now, I'll try to explain things in three levels: Newbie, Intermediate and Advanced. Therefore, the first

step in database I/O with this handler is to define your database:

Newbie:

CODE

Dim MyDataBase as New DB
MyDataBase.Type = DBType.ACCESS 'or DBType.SQL or DBType.EXCEL

Intermediate:

CODE

Dim MyDataBase as New DB(DBType.ACCESS)

Advanced:

CODE

Dim MyDataBase as New DB(DBType.ACCESS, "Provider=Microsoft.ACE.OLEDB.12.0; bla bla bla")

That is it. For us newbies (yes, I am one), I declare my database first of all and then set the kind of database

I'm using (either MS Access, MS SQL Server or MS Excel). For the intermediate, they'll like to do things just a

little bit above newbies: declare the database and assign it's type immediately. For the tough guys, they're

always a step ahead and doing things the hard way, they have declared the database, specified its type and defined

its connection string (that's why not everyone is a toughie).

Connection String
For the newbies and intermediates, you'll have to define some of your database properties so that the DB Handler

can build (or guess) a connection string for the connection. Advanced users can just use the ConnectionString

property to just set it. The following properties should be defined:
Note: Y-Yes, N-No, O-Optional

CODE

Property          MS-SQL      MS-Access     MS-Excel
-Type                  Y             Y             Y        'already specified above
-Provider             O             O             O
-DataSource         Y         use Path       use Path
-Path          clear Name 1st     Y             Y
-Name                 Y         use Path       use Path
-UseWindowsID     Y         Invalid        Invalid
-Username     set WinId false    Y           Ignored
-Password     set WinId false    Y           Ignored

That's it, you don't have to mind some of them because, to connect to the access database on my desktop, i just

use this

CODE

Dim MyDataBase As New DB
MyDataBase.Type = DBType.ACCESS 'i told you im a newbie
MyDataBase.Path = "C:\Users\olibenu\Desktop\pagoda.accdb"

This post has been edited by olibenu: 20 Oct, 2009 - 04:05 AM

olibenu

20 Oct, 2009 - 04:21 AM

Post #6

Input & Output
Before performing read or write operations, you will have to Connect and Open the database. These operations

return Boolean values of theie success so it'll be better if you test if Connect returns True (If it doesn't,

check your connection string, MyDataBase.ConnectionString will give you the string). Anytime I use this handler

(which is everytime) I normally code it like this

CODE

If MyDatabase.Connect And MyDataBase.Open Then
'do the database operations
Else
MsgBox ("Error in connecting to database")
End If

For Reading, there are two ways of doing this (you have to know a little SQL for the SELECT, INSERT etc commands).

For a regular SELECT * statements (without the WHERE clause) just use this

CODE

Dim MyData as New ArrayList
MyData = MyDataBase.Read("MyTableName")
If MyData.Count = 0 Then
   'no records found or table does not exist
Else
   Dim Row as Integer, Column as Integer
   For Row = 0 To MyData.Count - 1
       Dim PresentRow as New ArrayList
       PresentRow = MyData(Row)
       For Column = 0 To PresentRow.Count - 1
           'all columns in present row: PresentRow(Column)
       Next
   Next
End If

For more complex SQL statements:

CODE

MyDataBase.Command = "SELECT * FROM TableName WHERE ID='User123', Age=23"
Dim MyData as New ArrayList
MyData = MyDataBase.Read

Read returns an ArrayList of ArrayLists which you can use to display your data wherever you want: Grid, File, ListView etc.

For Writing, you'll have to set the command first of all with the Command property and then Write

CODE

MyDataBase.Command = "INSERT INTO TableName WHERE ID='User123', Age=23 bla bla bla"
Dim RowsAffected as Integer
RowsAffected = MyDataBase.Write

Or, to do it like a tough guy

CODE

Dim RowsAffected as Integer
RowsAffected = MyDataBase.Write("INSERT INTO TableName WHERE ID='User123', Age=23 bla bla bla")

Note that Write returns the number of records affected, if it returns -1 then an error has occured and your SQL command might be faulty, if it returns 0, your SQL statement is good but the rows affected where, yes you guessed it, none.

olibenu

20 Oct, 2009 - 04:28 AM

Post #7

Cleaning Up
When all is said and done and you don't want to use the database anymore, just use the Close command to

close the database.

CODE

MyDataBase.Close

Also know that Close returns a boolean value for its success so you might want to know if it really

closed.

The file for the DB Handler class is attached, just rename it to .vb by removing the .txt and then add to your project and get LAZY!!

nb: If you want to suggest something or you feel this whole thing is unnecessary (you must be a toughie then), just post it!

Attached File(s)

DB.vb.txt ( 26.42k ) Number of downloads: 23