'clsqlbuilder
'By Yuhonglai
'Www.hahait.com
'Hahasoft@msn.com
'Note: Provide an overload method to specify the name of the table. By default, it is a type "TBL_" of the Parameter O. At this point, the class name must be in the form of CLSxxx.
' Such as:
'Dim Rooms As New CLSROOMS
'Sqlbuilder.add (rooms)
'This program will convert CLSROOMS to TBL_ROOMS to operate database table TBL_ROOMS
'If the class name, the database table name does not have the above-described corresponding relationship, use the form of the Add (O, "Tablename") to display the name of the database table to be operated.
Public Class Sqlbuilder
'When the WHERE condition statement of the SQL statement to be generated is complicated, this constant is used as a SELECT method.
'Key, for example: to generate WHERE BIRTH <'
2000-4-4
'and birth>'
1980-1-1
'Complicated condition, use the following methods:
'DIM H AS New Hashtable
'H.Add (ComplexSql, "_ Birth <'
2000-4-4
'and _bpter>'
1980-1-1
'")
'Note that Birth is the name of the physical class, and there must be a next line "_" in front.
The program will replace the corresponding _birth when processing, the program will use the actual database field name to replace the corresponding _birth
Public const complexsql as string = "@complexsql"
'Rootware physical classes Generate the corresponding INSERT ... SQL statement
'If the key field is related to the attribute of the database table, it is automatically added to the value (in the db.xml file, the value is 1)
'Then the genus will ignore, and will not appear in the return INSERT ... SQL statement
Public overloads Shared Function Add (Byval O As Object) AS String
DIM TYPESTRING AS STRING = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return Add (O, TypeString)
END FUNCTION
Public overloads Shared Function Add (Byval O As Object, Byval Tablename As String) AS String
Try
Dim outsql as string
Dim tmpstring as string
Outsql = "INSERT INTO [" & TableName & "] ("
TMPSTRING = "" "
DIM DSDB AS New DataSet
DSDB.ReadXML (Clspersistant.dbconfigPath)
Dim row as data.datarow
For Each Row In DSDB.Tables (TableName ).Rowsif Row.Item ("Seed") & "=" 0 "THEN
Outsql = Outsql & Row.Item ("DBNAME") & ","
Tmpstring = Tmpstring & Gets (Row.Item ("Type") & "DBName") & Get (Row.Item ("Type") & ","
END IF
NEXT
Outsql = outsql.substring (0, Outsql.Length - 1)
Tmpstring = tmpstring.substring (0, tmpstring.length - 1)
Outsql = Outsql & ") Values (" & Tmpstring & ")"
For each row in dsdb.tables (TableName) .rows
If Row.Item ("SEED") & "<>" 1 "THEN
'tmpstring = o.gettype.invokemember (row.item ("name"), reflection.bindingflags.getfield or reflection.bindingflags.getProperty, Nothing, O, New Object () {}) & ""
Tmpstring = CallbyName (O, CType (Row.Item ("Name", String) .Trim, CallType.get) & ""
If tmpstring = "true" then
Tmpstring = "1"
Elseif Tmpstring = "false" THEN
Tmpstring = "0"
END IF
Outsql = outsql.replace ("@" & rotion.Item ("dbname"), TMPSTRING)
END IF
NEXT
Return Outsql.Trim
Catch exception
Throw EX
END TRY
END FUNCTION
'Such as add methods, key fields do not update
'And the key field will appear as update .... where .... WHERE condition
Public overloads Shared Function Update (Byval O as Object) AS STRING
DIM TYPESTRING AS STRING = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return Update (O, TypeString)
END FUNCTION
Public overloads Shared Function Update (Byval O As Object, Byval Tablename As String) AS StringTry
Dim outstring as string = "" "
Dim tmpstring as string
Outstring = "Update [" & TableName & "] SET"
TMPSTRING = "" "
Dim wherestring as string = "" "
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
WHERESTRING = WHERESTRING & ROW.ITEM ("DBNAME") & Get (Row.Item ("@")) & "@" & rotion ("dbname") & gets (Row.Item ("Type ")) &" AND "
Else
Tmpstring = tmpstring & row.item ("dbname") & Gets (row.item ("@") & "dbname") & gets (Row.Item ("Type ")) &"
END IF
NEXT
If WHERESTRING.TRIM = "" "" ""
Throw new Exception ("must specify more than one primary key!")
END IF
Tmpstring = tmpstring.substring (0, tmpstring.length - 1)
WHERESTRING = wherestring.substring (0, WHERESTRING.LENGTH - 4)
Outstring = Outstring & Tmpstring & "Where" & WhereString
For each row in dsdb.tables (TableName) .rows
'tmpstring = o.gettype.invokemember (row.item ("name"), reflection.bindingflags.getfield or reflection.bindingflags.getProperty, Nothing, O, New Object () {}) & ""
Tmpstring = CallbyName (O, CType (Row.Item ("Name", String) .Trim, CallType.get) & ""
If tmpstring = "true" then
Tmpstring = "1"
Elseif Tmpstring = "false" Tentmpstring = "0"
END IF
Outstring = outstring.replace ("@" & row.item ("dbname"), tmpstring
NEXT
Return Outstring.trim
Catch exception
Throw EX
END TRY
END FUNCTION
'More objects of key properties (corresponding to the key fields of the database table) Delete the specified record
The other properties of the object will be ignored
Public overloads Shared Function Delete (Byval O As Object) AS String
DIM TYPESTRING AS STRING = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return Delete (O, TypeString)
END FUNCTION
Public overloads Shared Function Delete (Byval O as Object, Byval TableName As String) AS String
Try
Dim outstring as string = "" "
Dim tmpstring as string
Outstring = "delete from [" & Tablename & "] Where"
Dim wherestring as string = "" "
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
WHERESTRING = WHERESTRING & ROW.ITEM ("DBNAME") & Get (Row.Item ("@")) & "@" & rotion ("dbname") & gets (Row.Item ("Type ")) &" AND "
END IF
NEXT
If WHERESTRING.TRIM = "" "" ""
Throw new Exception ("must specify more than one primary key!")
END IF
WHERESTRING = wherestring.substring (0, WHERESTRING.LENGTH - 4)
Outstring = Outstring & Tmpstring & WhereString
For each row in dsdb.tables (TableName) .rows
If Row.Item ("Key") & "=" 1 "THEN
'TmpString = o.GetType.InvokeMember (row.Item ( "name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () {}) & "" tmpString = CallByName (o, CType (Row.Item ("Name", String) .trim, CallType.get) & ""
Outstring = outstring.replace ("@" & row.item ("dbname"), tmpstring
END IF
NEXT
Return Outstring.trim
Catch exception
Throw EX
END TRY
END FUNCTION
'More object-oriented key properties (corresponding to the key field of the database table) determine if the object exists in the database
The other properties of the object will be ignored
Public overloads Shared Function Exists (Byval O As Object) AS String
DIM TYPESTRING AS STRING = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return EXISTS (O, TypeString)
END FUNCTION
Public overloads Shared Function Exists (Byval O As Object, Byval TableName As String) AS String
Try
Dim outstring as string
Outstring = "SELECT Count (*) from [" & Tablename & "] Where"
Dim tmpstring as string
Dim wherestring as string = "" "
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
WHERESTRING = WHERESTRING & ROW.ITEM ("DBNAME") & Get (Row.Item ("@")) & "@" & rotion ("dbname") & gets (Row.Item ("Type ")) &" AND "
END IF
NEXT
If WHERESTRING.TRIM = "" "" ""
Throw new Exception ("must specify more than one primary key!")
END IF
WHERESTRING = WhereString.Substring (0, WHERESTRING.LENGTH - 4) Outstring = Outstring & Tmpstring & WhereString
For each row in dsdb.tables (TableName) .rows
If Row.Item ("Key") & "=" 1 "THEN
'tmpstring = o.gettype.invokemember (row.item ("name"), reflection.bindingflags.getfield or reflection.bindingflags.getProperty, Nothing, O, New Object () {}) & ""
Tmpstring = CallbyName (O, CType (Row.Item ("Name", String) .Trim, CallType.get) & ""
Outstring = outstring.replace ("@" & row.item ("dbname"), tmpstring
END IF
NEXT
Return Outstring.trim
Catch exception
Throw EX
END TRY
END FUNCTION
'Generate a first SQL statement
Public overloads Shared Function First (Byval O As Object) AS String
DIM TYPESTRING AS STRING = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return First (TypeString)
END FUNCTION
Public overloads Shared Function First (Byval Tablename As String) AS String
Dim moudlesql as string
Moudlesql = "SELECT * from" "WHERE [% key%] in (SELECT MIN ([% key%]) from [% TableName%]"
Dim key as string
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
Key = ctype (row.item ("dbname"), string .trim
EXIT for
END IF
NEXT
Moudlesql = moudlesql.replace ("% TableName%", TableName)
Moudlesql = moudlesql.replace ("% key%", key)
Return Moudlesql
END FUNCTION
Public overloads Shared Function Last (Byval O As Object) AS Stringdim TypeString As String = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return Last (TypeString)
END FUNCTION
Public overloads Shared Function Last (Byval TableName As String) AS String
Dim moudlesql as string
Moudlesql = "Select * from [% Tablename%] where [% key%] in (SELECT MAX ([% key%]) from [% tablename%])
Dim key as string
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
Key = ctype (row.item ("dbname"), string .trim
EXIT for
END IF
NEXT
Moudlesql = moudlesql.replace ("% TableName%", TableName)
Moudlesql = moudlesql.replace ("% key%", key)
Return Moudlesql
END FUNCTION
Public overloads Shared Function Previous (byval o as object) AS String
DIM TYPESTRING AS STRING = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return Previous (O, TypeString)
END FUNCTION
Public overloads Shared Function Previous (Byval O as Object, Byval Tablename As String) AS String
Dim moudlesql as string
Moudlesql = "Select * from [% Tablename%] where [% key%] in (SELECT MAX ([% key%]) from [% tablename%] where [% key%] <% keyvalue%)
Dim key as string
Dim PropertyName As String
Dim PropertyValue As String
DIM DSDB AS New DataSet
DSDB.ReadXML (Clspersistant.dbconfigPath)
Dim row as data.datarow
For Each Row in DSDB.Tables (TableName) .rowsif Row.Item ("Key") = "1" THEN
Key = ctype (row.item ("dbname"), string .trim
PropertyName = CType (Row.Item ("Name"), String) .trim
EXIT for
END IF
NEXT
'PropertyValue = O.gettype.invokemember (PropertyName, Reflection.BindingFlags.Getfield or Reflection.bindingflags.getProperty, Nothing, O, New Object () {}) & "
PropertyValue = CallbyName (O, PropertyName, CALLTYPE.GET) & ""
Moudlesql = moudlesql.replace ("% TableName%", TableName)
Moudlesql = moudlesql.replace ("% key%", key)
Moudlesql = moudlesql.replace ("% keyvalue%", PropertyValue)
Return Moudlesql
END FUNCTION
Public overloads shared function [next] (byval o as object) AS STRING
DIM TYPESTRING AS STRING = O.GETTYPE.TOSTRING
DIM I as INT16
I = TypeString.indexof ("CLS") 3
TypeString = "TBL_" & TypeString.Substring (i, TypeString.Length - i)
Return [Next] (O, TypeString)
END FUNCTION
Public overloads shared function [next] (Byval O as object, byval Tablename as string) AS String
Dim moudlesql as string
Moudlesql = "SELECT * from" "WHERE [% key%] in (SELECT MIN ([% key%]) from [% tablename%] where [% key%]>% keyvalue%)
Dim key as string
Dim PropertyName As String
Dim PropertyValue As String
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
Key = ctype (row.item ("dbname"), string .trim
PropertyName = CType (Row.Item ("Name"), String) .trim
EXIT for
END IF
NEXT
'PropertyValue = o.GetType.InvokeMember (propertyName, Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object () {}) & "" propertyValue = CallByName (o, propertyName, CallType.Get) & ""
Moudlesql = moudlesql.replace ("% TableName%", TableName)
Moudlesql = moudlesql.replace ("% key%", key)
Moudlesql = moudlesql.replace ("% keyvalue%", PropertyValue)
Return Moudlesql
END FUNCTION
'Instructions for see Public Const ComplexSQL
Public Shared Function [SELECT] (Byval Findcondition As Hashtable, Byval TableName As String) AS String
Dim outsql as string
If Findcondition.Contains (ComplexSql) THEN 'Handling complex type Where clause
Outsql = "SELECT *" & TABLENAME & "] Where" & FindCondition (ComplexSQL)
Dim row as data.datarow
DIM DSDB AS New DataSet
DSDB.ReadXML (Clspersistant.dbconfigPath)
For each row in dsdb.tables (TableName) .rows
Outsql = outsql.replace ("_" & ctype ("name"), string) .trim, "[" & ctype (row.item ("dbname"), string) .trim & "]")
NEXT
Else
Outsql = "SELECT *" & TABLENAME & "] Where"
Dim wherestring as string = "" "
DIM Eachkey as Object
For Each Eachkey in Findcondition.keys
whereString = whereString & CType (eachKey, String) & "=" & getS (getTypeByName (TableName, CType (eachKey, String))) & FindCondition (eachKey) & getS (getTypeByName (TableName, CType (eachKey, String))) & "and"
NEXT
If WhereString.length = 0 THEN
WHERESTRING = "0 = 0"
Else
WHERESTRING = wherestring.substring (0, WHERESTRING.LENGTH - 5) Endiff
Outsql = Outsql & WhereString
END IF
Return Outsql
END FUNCTION
'Returns the data type name of the specified field (the name of the database table field) (VB data type)
Private shared function gettypebyname (Byval N as String) AS String
Dim outstr as string
DIM DSDB As New Data.Dataset
DSDB.ReadXML (Clspersistant.dbconfigPath)
Dim Eachrow As DataRow
For Each Eachrow in DSDB.Tables (TableName) .rows
IF CType ("DBNAME"), String) .trim.tolower = n.trim.tolower then
Outstr = ctype (Eachrow.Item ("Type"), string) .trim.tolower
EXIT for
END IF
NEXT
Return Outstr
END FUNCTION
'Retal data type name, return empty or' sql statement in the number and character types in 'SQL statements
Private Shared Function Get (Byval T AS String) AS String
Dim outstring as string
T = T.Tolower.Trim
IF T = "SINGLE" OR T = "INT16" OR T = "int32" OR T = "Double" OR T = "byte" THEN
Outstring = ""
Return Outstring
Elseif T = "Date" OR T = "String" then
Outstring = "'"
Return Outstring
END IF
END FUNCTION
END CLASS
