A very smart piece of code for databases... :) 
Author Message
 A very smart piece of code for databases... :)

Hi, I wanted to start using ADO.NET but I didn't want to rewrite all my old
code where I used RS.MoveNext, DB.OpenRecordset, DB.Execute,
RS.Field("test").value etc. You probably know what I mean :)

So I thought I a class could be created to do all this, so I could use the
old commands, but with ADO.NET. And it works. It works very well actually.
It doesn't have all the same functions, but the most basic ones.

Although I tried to create a function like the RS.Update, I never succeeded,
and I thought that someone out there was capable of doing this?

I have uncommented my last try in the bottom of the code below, it says that
I need to create a correct InsertCommand, UpdateCommand and so on.
I read a little about it, but I do not understand how to use it completely.
So I just rewrote my code that updates the database, to use the DB.Execute
"UPDATE....." instead.

/Michael

Imports System

Imports System.Data

Imports System.Data.OleDb

Public Class clsDatabase

Private Database As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\xxx.mdb;Jet
OLEDB:Database Password=""xxxx""") ' User ID=Admin;

Private Cmd As OleDbCommand = New OleDbCommand()

Private Paramter As New OleDbParameter()

ReadOnly Property Connection() As OleDbConnection

Get

Return Database

End Get

End Property

Public Sub Open()

Try

Database.Open()

Catch e As System.Exception

MsgBox("Error opening Database, returned following error:" & vbCrLf &
e.Message)

End

End Try

Cmd.Connection = Database

End Sub

Public Function Format(ByVal str As String) As String

Return Replace(str, "'", "''")

End Function

Public Function FormatNumber(ByVal v As Double) As String

Return Format(Replace(v, ",", "."))

End Function

Public Function Execute(ByVal SQL As String) As Long

Cmd.CommandText = SQL

Try

Return Cmd.ExecuteNonQuery()

Catch e As System.Exception

MsgBox("Error receiving information form Database, returned following
error:" & vbCrLf & e.Message & vbCrLf & vbCrLf & "Tried to execute: '" & SQL
& "'")

DB.Close()

DB.Open()

Execute(SQL)

End Try

End Function

Public Function OpenRecordset(ByVal SQL As String) As clsDatabase.RecordSet

Dim RS As New RecordSet(SQL)

Return RS

End Function

Public Function Close()

Try

Database.Close()

Catch e As System.Exception

MsgBox("Error closing Database, returned following error:" & vbCrLf &
e.Message)

'End

End Try

End Function

Public Class RecordSet

Private DS As New DataSet()

Private DA As OleDbDataAdapter

Private i As Long

Private strSQL As String

Public Sub New(ByVal SQL As String)

DA = New OleDbDataAdapter(SQL, DB.Connection)

Try

DA.Fill(DS)

Catch e As Exception

If Not isExeFile Then MsgBox("Error while filling data from database:" &
vbCrLf & vbCrLf & e.Message)

DB.Close()

DB.Open()

DA = New OleDbDataAdapter(SQL, DB.Connection)

DA.Fill(DS)

End Try

End Sub

Public Property AbsolutePosition() As Long

Get

Return i

End Get

Set(ByVal Value As Long)

i = Value

End Set

End Property

Public Sub Delete()

DS.Tables(0).Rows(i).Delete()

End Sub

Public ReadOnly Property Rows() As Data.DataRowCollection

Get

Return DS.Tables(0).Rows()

End Get

End Property

Public Sub MoveNext()

If i = DS.Tables(0).Rows.Count Then

MsgBox("Error: Table returned EOF" & vbCrLf & vbCrLf & "(" & strSQL & ")")

Else

i += 1

End If

End Sub

Public ReadOnly Property BOF() As Boolean

Get

Return (i = 1)

End Get

End Property

Public ReadOnly Property EOF() As Boolean

Get

Return (i = DS.Tables(0).Rows.Count)

End Get

End Property

Public ReadOnly Property Count() As Integer

Get

Return DS.Tables(0).Rows.Count

End Get

End Property

Public Sub MovePrevious()

If i = 1 Then

MsgBox("Error: Table returned BOF" & vbCrLf & vbCrLf & "(" & strSQL & ")")

Else

i -= 1

End If

End Sub

Public Sub MoveFirst()

i = 0

End Sub

Public Sub MoveLast()

i = DS.Tables(0).Rows.Count

End Sub

Default ReadOnly Property Fields(ByVal ColumnName As String) As Field

Get

Return New Field(DS, i, ColumnName)

End Get

End Property

'Public Sub Update()

' DA.Update(DS.Tables(0))

'End Sub

Public Sub Close()

DA.Dispose()

DS.Dispose()

End Sub

Public Class Field

Dim intRow As Integer, strCol As String, DS As DataSet

Public Sub New(ByRef theDS As DataSet, ByVal Row As Integer, ByVal Col As
String)

DS = theDS

intRow = Row

strCol = Col

End Sub

Public ReadOnly Property Value() As Object

Get

Try

Return ReplaceNull(DS.Tables(0).Rows(intRow).Item(strCol), "")

Catch e As Exception

MsgBox(e.Message)

End Try

End Get

End Property

End Class

End Class

End Class



Sun, 05 Jun 2005 15:43:04 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. help with this piece of code

2. Why does this piece of code work with Word 97 but not Word 2000

3. Help with this piece of code ...

4. what is wrong with this piece of code ?

5. Can someone debug this small piece of code?

6. Help with piece of SQL code

7. Help with this piece of coding

8. VBX OCX or any else piece of code send email using SMTP

9. OVER 150 PIECES OF CODE FREE

10. Can someone debug this small piece of code?

11. Easy way to include piece of code.

12. HELP:This piece of code works in win95 but not in wfw311

 

 
Powered by phpBB® Forum Software