'CLSDataAccessoper This class is the parent class for all data access classes
'by yujun
'Www.hahait.com
'Hahasoft@msn.com
Public Class ClsDataAccessoper
'When Update, Delete, ADD method operation fails to return false, record an error message
Public Shared ModifyErrorString As String
Private shared keys as new hashtable
'Database connection string
Public Shared Property Connectionstring () AS STRING
Get
Return SQLHELPER.CNSTRING.TRIM
END GET
Set (byval value as string)
SQLHELPER.CNNSTRING = Value.Trim
End set
End Property
'Update does not update the primary key, including the joint primary key
Public Shared Function Update (Byval O as Object) as boolean
ModifyerRorstring = "" "
Try
IF ctype (SqlHelper.Executenonury (SqlHelper.cnnstring, CommandType.Text, Sqlbuilder.exists (O)), INT64) = 0 THEN
Throw new Exception ("This record does not exist!")
END IF
Catch exception
Throw EX
END TRY
Try
SQLHELPER.EXECUTENONQUERY (SQLHELPER.CNSTRING, CommandType.Text, Sqlbuilder.Update (O))
Catch 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.CNSTRING, CommandType.text, Sqlbuilder.delete (O))
Catch exception
ModifyerRorstring = ex.Message
Return False
END TRY
Return True
END FUNCTION
'Add method will ignore the primary key of the automatic value
Public Shared Function Add (Byval O as Object) as boolean
ModifyerRorstring = "" "
Try
SQLHELPER.EXECUTENONQUERY (SQLHELPER.CNNSTRING, CommandType.Text, Sqlbuilder.Add (O)))
Catch exception
ModifyerRorstring = ex.Message
Return False
END TRY
Return True
END FUNCTION
'Universal database query method
'Overlay method is used to express the name of the database table to be operated
'Otherwise, you will describe the name of the database table to be operated by RETURNTYPE. Eg: returntype = "clsrooms", SKLNAME = "TBL_ROOMS" "The query method adds the query condition into the keytable, then call the SELECT method Returns the collection of objects
'When Keys contains special keys, the complex type query will be processed, see SQLBUILDER's ComplexSQL description
'This method can expand a fixed query method for data access classes
Public overloads shared function [select] (byval return "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 ("name"), string .tolower.trim) .tolower.trim?
XXXH.Add (CType ("DBNAME"), String) .tolower.trim (CTYPE ("Name"), String) .trim.tolower))
END IF
NEXT
'Check the legality of Keys
DIM DSSELECT As New Data.DataSet
IF keys.count <> xxxh.count then
Keys.clear ()
DIM INVALIDFIELD AS New Exception ("Does not set the field:")
Throw invalidfield
Else
Keys.clear ()
Try
DSselect = SQLHELPER.EXECUtedataSet (SqlHelper.cnnstring, CommandType.text, Sqlbuilder.select (xxxh, tablename))
Catch 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) .rowsfieldName = 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 four methods used to move records
'Moving the size of the record security key moves, only the table operation of having only one primary key
'For the combination primary key, return Nothing
'Returning NOTING when the record moves to the end or end, when the table is empty, first, Last returns Nothing
Public Shared Function First (Byval O As Object) AS Object
Return Move ("first", o)
END FUNCTION
Public Shared Function 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
'Return to a list of primary keys, keyname, keydbname records the last primary key
Private shared function getkey (byref keyname as string, byref keydbname as string) AS INT16, BYVAL TABLENAME AS S
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
'Provision of general functions for First, Previous, Next, Last provides general functions private month (byval o as object) AS Object
Dim MoveSQL As String
Select Case Type.trim.Tolower
Case "first"
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