Easy to handle data access layer [continued 2]

Access to | data ' Clsdataaccessoper This class is the parent class for all data access classes

' By Yujun

' Www.hahaIT.com

' Hahasoft@msn.com



Public Class Clsdataaccessoper



' Log error information when the Update,delete,add method operation fails to return False

Public Shared modifyerrorstring as String



Private Shared Keys as New Hashtable



' Database connection string

Public Shared Property ConnectionString () as String

Get

Return SqlHelper.cnnString.Trim

End Get

Set (ByVal Value as String)

sqlhelper.cnnstring = Value.trim

End Set

End Property



' Update does not renew primary key, including Union primary key

Public Shared Function Update (ByVal o as Object) as Boolean

modifyerrorstring = ""

Try

If CType (Sqlhelper.executenonquery (sqlhelper.cnnstring, CommandType.Text, Sqlbuilder.exists (o)), Int64) = 0 Then

Throw New Exception ("This record does not exist!")

End If

Catch ex as Exception

Throw EX

End Try



Try

Sqlhelper.executenonquery (sqlhelper.cnnstring, CommandType.Text, Sqlbuilder.update (o))

Catch ex as Exception

Modifyerrorstring = ex. Message

Return False

End Try

Return True

End Function



' Delete will ignore

Public Shared Function Delete (ByVal o as Object) as Boolean

modifyerrorstring = ""

Try

Sqlhelper.executenonquery (sqlhelper.cnnstring, CommandType.Text, Sqlbuilder.delete (o))

Catch ex as Exception

Modifyerrorstring = ex. Message

Return False

End Try

Return True

End Function



The ' Add method ignores the primary key that automatically adds a value

Public Shared Function Add (ByVal o as Object) as Boolean

modifyerrorstring = ""

Try

Sqlhelper.executenonquery (sqlhelper.cnnstring, CommandType.Text, Sqlbuilder.add (o))

Catch ex as Exception

Modifyerrorstring = ex. Message

Return False

End Try

Return True

End Function



' Common database Query method

' Overloaded method is used to explicitly specify the name of the database table to be manipulated

' Otherwise, the name of the database table to be manipulated will be described in ReturnType type eg:returntype= "Clsrooms", Tablename= "Tbl_rooms".



' The Query method adds the query criteria to the Keys (HashTable), and then calls the Select method to return the collection of objects

' When the keys contain special keys, you will be dealing with complex types of queries, see Sqlbuilder's complexsql description

' This method can extend the fixed query method of data access class



Public Overloads Shared Function [Select] (ByVal returntype as Type) as ArrayList

Dim TableName as String

TableName = Returntype.name

Dim I as Int16

i = Tablename.indexof ("CLS") + 3

TableName = "Tbl_" & Tablename.substring (i, tablename.length-i)

return [Select] (ReturnType, TableName)

End Function



Public Overloads Shared Function [Select] (ByVal returntype as Type, ByVal TableName as String) as ArrayList

Dim Alout as New ArrayList



Dim Dsdb as New data.dataset

Dsdb.readxml (Clspersistant.dbconfigpath)

Dim Xxxh as New Hashtable

Dim Eachrow as Data.datarow

For each eachrow in Dsdb.tables (tablename). Rows

If Keys.contains (CType (Eachrow.item ("name"), String). Tolower.trim) Then

Xxxh.add (CType (Eachrow.item ("dbname"), String). Tolower.trim, Keys (CType (Eachrow.item ("name"), String). Trim.tolower))

End If

Next



' Check the legality of the Keys

Dim Dsselect as New data.dataset

If keys.count <> Xxxh.count Then

Keys.clear ()

Dim Invalidfield as New Exception ("No fields you set up:")

Throw Invalidfield

Else

Keys.clear ()

Try

Dsselect = SqlHelper.ExecuteDataset (sqlhelper.cnnstring, CommandType.Text, Sqlbuilder.select (XxxH, TableName))

Catch ex as Exception

Throw EX

End Try

End If



Dim Eachselect as Data.datarow

Dim FieldName as String

Dim Dbfieldname as String



For each eachselect in Dsselect.tables (0). Rows

Dim newobject as Object = System.Activator.CreateInstance (returntype)

For each eachrow in Dsdb.tables (tablename). Rows

FieldName = CType (Eachrow.item ("name"), String). Trim

Dbfieldname = CType (Eachrow.item ("dbname"), String). Trim

CallByName (NewObject, FieldName, Calltype.set, CType (Eachselect.item (dbfieldname), String). Trim)

Next

Alout.add (NewObject)

NewObject = Nothing

Next

Return alout

End Function



Public Shared WriteOnly Property Selectkeys (ByVal KeyName as String)

Set (ByVal Value as Object)

Keys.add (KeyName.Trim.ToLower, Value)

End Set

End Property



' The following 4 methods are used to move a record

' Move records An in order of size, only for tables with only one primary key

' Returns nothing for the combined primary key

' Returns noting when the record is moved to the end, and First,last returns Nothing when the table is empty

Public Shared Function ByVal (o as Object) as Object

Return Move ("a", O)

End Function



The public Shared Function is last (ByVal O as Object) as Object

Return move ("Last", O)

End Function



Public Shared Function Previous (ByVal o as Object) as Object

Return move ("previous", O)

End Function



Public Shared Function [Next] (ByVal o as Object) as Object

Return Move ("Next", O)

End Function



' Returns the number of primary keys for a table, Keyname,keydbname records the last primary key

Private Shared Function Getkey (ByRef keyname As String, ByRef keydbname As String, ByVal TableName as String) as Int16

Dim keynum as Int16 = 0

Dim Dsdb as New DataSet

Dsdb.readxml (Clspersistant.dbconfigpath)

Dim Row as Data.datarow

For each row in Dsdb.tables (tablename). Rows

If row. Item ("key") = "1" Then

Keynum = keynum + 1

KeyName = CType (row. Item ("name"), String). Trim

Keydbname = CType (row. Item ("dbname"), String). Trim

Exit for

End If

Next

Return Keynum

End Function



' provides common functions for first,previous,next,last

Private Shared Function Move (ByVal Type as String, ByVal o as Object) as Object

Dim Movesql as String

Select Case Type.Trim.ToLower

Case "a"

Movesql = Sqlbuilder.first (o)

Case "Last"

Movesql = Sqlbuilder.last (o)

Case "Previous"

Movesql = sqlbuilder.previous (o)

Case "Next"

Movesql = Sqlbuilder.next (o)

End Select



Dim typestring as String = o.gettype.tostring

Dim I as Int16

i = Typestring.indexof ("CLS") + 3

typestring = "Tbl_" & Typestring.substring (i, typestring.length-i)

Dim tablename as String = typestring



Dim KeyName as String

Dim Keydbname as String

Dim tmpstring as String

If Getkey (KeyName, keydbname, tablename) = 1 Then

Keys.clear ()

Dim DS as New data.dataset

ds = SqlHelper.ExecuteDataset (sqlhelper.cnnstring, CommandType.Text, Movesql)

If ds. Tables (0). Rows.Count = 0 Then

Return Nothing

Else

tmpstring = CType (ds. Tables (0). Rows (0). Item (Keydbname), String). Trim

Keys.add (KeyName.Trim.ToLower, tmpstring)

Dim Al as New ArrayList

Al = [Select] (O.gettype)

If al. Count = 1 Then

Return al. Item (0)

Else

Return Nothing

End If

End If

Else

Return Nothing

End If

End Function



End Class