[olibenu]

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

dim myDB as new DB

or this

dim myDB as new DB(DbType.SQL)

2. specify db type

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)

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

for ms sql

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

4. reading data

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:

myDB.Command="select *"
myDB.Read

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

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

or

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

6. close

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: 09 October 2009 - 09:52 AM

[olibenu]

no one has used it? review

[olibenu]

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: 267

[olibenu]

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: 177

[olibenu]

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:

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

Intermediate:

Dim MyDataBase as New DB(DBType.ACCESS)

Advanced:

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

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

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 October 2009 - 05:05 AM

[olibenu]

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

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

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:

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

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

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]

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.

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: 258

[olibenu]

added examples in comments and a little tweak in read function

[olibenu]

Here's the file

Attached File(s)

•  DB.vb.txt (25.31K)
  Number of downloads: 186

[olibenu]

edit: sorry, double post

This post has been edited by olibenu: 13 April 2010 - 07:27 PM

[luchador]

thank you

[olibenu]

i've put something related and more recent at sourceforge called DataBox.