Easy to handle data access layer [continued 2]
Last Update:2017-02-28
Source: Internet
Author: User
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