The following is the method you can free from SQL's Add, Delete, Update, and SELECT. Entity class 2. Access classes are now tblperson (Perid, Perne, Pergender, PEROLD, Perid, Pernation) physical classes --------- iDNameGenderoldNation Access base class DataOper ---------- Shared deleteshared Addshared Updateshared SELECT Access Class Personoper: DataOper -------------------- Shared getAllshared getPersonbyidshared getPersonBynameshared get ... imports the information of the database table to DB.xml files In accordance with DB.XML files, generate physical classes such as the top of the TBLPerson table corresponding to a table name Name DBNAME KEY SEED TYPE ------------------- ----------------- iD Perid 1 1 IntegerName Perid 0 0 Stringgender Pergender 0 0 Booleanold Perold 0 0 IntegerNation Pernation 0 0 String will convert DataSet returned from the database into physical classes, if Write in general way: DS = cmd.execute ("Select * from tblperson where id = 1") DIM P as new personp.id = ctype (DS.TABLES (0) .ROWS (0) .Item ("Perid" ), Integer) p.Name = ... p.gender = ... p.old = ... p.NATION = ... a table conversion is also good, then if there is dozens of patch efficiency Underground, there is another generic method, using the reflection function provided by .NET! The reflection function provided by .NET can dynamically access the classes in the case of a known method or attribute.
Do you know how to do it? In front, we have a DB.xml database architecture file from the database and the field of the entity class. Therefore, we will easily realize the above assignment, let us look at the above assignment. Class DataOper -------------- Delete (o as object) Update (o as object) Select Add (o as object) as an example of public sub add (o as Object) DIM TYPE AS STRING = O.GETTYPE.TOSTRING 'Get the type of passed object ...' Object Type String and Name of the Table of the XML Schema File One Map Dim Schematablename As String = GetSchematAmename (Type ) 'Name of the table that needs to operate and opens the database architecture file from Person-> TBLPERSON' DIM DSSCHEMA AS NEW DATASET ("DB.XML") DIM SQL AS STRING = "INSERT INTO" & SchematableName Dim R AS DataRow for Each R In dsschema.tables (SCHEMATABLENAME) 'Here, the SQL statement above, the value of the value is taken from O, it is recommended to write a class of SQL statements that specifically operate specific object specific operations, such as primary keys, Auto Add value fields, etc. If you store more architecture information in the database architecture file, such as maximum, you can complete some data verification, etc. ... Next SQLHELPER.EXCUTE (Connectionstring, ... Text, SQL The generic method of End Sub delete / update is similar. 'From DataOper inheritance, you can also write: DataOper.add (p) If you want to add a book, you can do this Dim b as new bookb.isbn = B.Name = ... BOOKOPER.ADD (b) How is it very common? This is written as a common class, and you can complete all the physical classes of the add / update / delete operation. To note that the DB.xml database architecture file is very important. Universal physical class operation is complete. Here is the universal SELECT method before 9CBS In this article: Personoper.keys ("name") = "Hahasoft" Personoper.keys ("gender") = 1dim p as new person = personoperty.select () This can return the corresponding Person's entity class can also Make some ways to complete: such as getPersonbyId (ID) getPersonByName () ... later, I thought it was finally realized. (Renew ...) Note: (Single meter of the single table), if If you associate a table, you can declare a member of ALADDRESS AS ARRAYLIST, and of course, some data is filled with advanced filling to improve efficiency, and it will not be detailed here) Database table.
TBL_TEACHER data field name
Types of
Description
Teai
Int
auto numbering
Teacode
Char (20)
Teacher employee
TEANAME
Nchar (10)
?
Teainder
Bit
?
TEANATION
Nchar (6)
?
Teaage
Tinyint
?
TBL_STUDENT
Data field name
Types of
Description
Stuid
Int
auto numbering
STUCODE
Char (20)
Student card number
STUTEACHERCODE
Char (20)
Class teacher employee
Stuname
Nchar (10)
Name
Stugen
Bit
gender
Stunation
Nchar (6)
Nation
Stuage
Tinyint
age
StuclassID
Int
Class ID
To describe the XML files of these two tables, the XML file is stored in the structural information of these two tables
Assume the name of the file? Db.xml
TBL_TEACHER
Name
DBNAME
Type
SEED
Key
Id
Teai
Integer
1
1
Code
Teacode
String
0
0
Name
TEANAME
String
0
0
Gender
Teainder
Boolean
0
0
Nation
TEANATION
String
0
0
AGE
Teaage
Integer
0
0
TBL_STUDENT
Name
DBNAME
Type
SEED
Key
Id
Stuid
Integer
1
1
Code
STUCODE
String
0
0
Teachercode
STUTEACHERCODE
String
0
0
Name
Stuname
String
0
0
Gender
Stugen
Boolean
0
0
Nation
Stunation
String
0
0
AGE
Stuage
Integer
0
0
ClassID
StuclassID
Integer
0
0
Must-have
Clssqlhelper: Microsoft's Data Access Auxiliary Class
Clssqlbuilder: Easily get the data access layer by generating the corresponding SQL statement according to the configuration file [Continued 1]
CLSDataAccessOper: So the parent class of the operation class, provide a universal data operation method to see easily getting the data access layer [continued 2]
CLSDataAccess: There is no use for the time being.
Data classes and access classes
Entity class CLSTEACHER's property (Property), corresponds to the database field
Property ID As INTEGER Property Code As String
Property Name As String
Property Gender As Boolean
Property nation as string
Property Age As INTEGER
Entity CLSStudent
Property ID As INTEGER
Property Code As String
Property Teachercode As String
Property Name As String
Property Gender As Boolean
Property nation as string
Property Age As INTEGER
Property ClassID AS INTEGER
Note: If the specific code of the two classes above is not written.
Access CLSTEacheroper inherits from CLSDataAccessoper
Public Class ClsteacherOper
Public function getAll () as arraylist
Return CLSDataAccessoper.select (NEW CLSTEACHER) END FUNCTION
Public Function Getteacherbycode (Code As String) AS CLSTEACHER
SDataAccessoper.selectKeys ("code") = CODE
Return CLSDataAccessOper.select (new clsteacher) .gettype) .ITEM (0)
END FUNCTION
'The following method is similar, and it is some inquiry such as getxxx by YYY.
'According to the operation, such as add / delete / modify all from CLSDataAccessoper
'The query method here can be implemented with clsdataaccessoper.selectKeys ("") = ...
'Just to provide a more friendly interface, if you are tight, you can submit the' clsdataAccessoper.selectkeys / select method to the logical layer or a programmer
END CLASS
Access CLSStudentoper inherits from CLSDataAccessoper
(With CLSTEacheroper class)
Previous article << Easy to get data access layer >> The article said that my code is too small, I don't understand. In fact, I just want to explain the truth.
Now, I posted all CLSSQLBUILDER CLSDataAccessoper all code for these two core classes. This just wants to make more understanding. These two classes are also the first version, the quality of code is not very high, some exceptions have not thrown. Just achieve some functions.
By the way, the physical classes can be generated through the database script, and this tool is of course written by itself. The tool is simple, and the two or three hundred lines can be done. There is also the XML file of the database structure information, it is also very troublesome, so you can also write a tool that automatically generates it.
This is not the focus of this article discussed, this will not introduce how to generate almost the same code.
This advantage of this data access method is:
If your database has changed, for example, the TBL_TEAHCER table adds a field title (TEATITLE), then you only need to add a property Property Title in the clsteacher table, then add a record title | TEATITLE | STRING | 0 | 0 is OK.
The coding speed is fast, in addition to these two core classes, the data entity class can be automatically generated, and the access class is simply written to some query methods.
Don't write SQL statements.
Usage method example:
Add a teacher
Dim Newteacher As New CLSTEACHER
With newteahcer
'If there is an ID assignment here, it will be ignored because it is an automatic value. See clsqlbuilder
.Name = "haha"
.Code = "
2001 "
.Gender = TRUE
...
End with
CLSTEacherOper.Add (Newteacher)
Add a student
Dim Newstudent As New Clsstudent
WITH NewStudent
.Name = "Tom"
.Gender = TRUE
.TEachercode = "
2001 "
...
End with
Clsstudentoper.Add (NewStudent)
Update deletion is similar (not exemplified here).
Now, add a program process to the program process.
When CLSTEACHEROPER.ADD (NewTeancher), clsdataAccessoper.add will continue to pass NEWTEACHER to the clsqlbuilder.add () method. In this method, Clssqlbuilder first acquires the type of this object here is "clsteacher". String and DB TBLTEacher has a corresponding relationship in the .xml file, here is the prefix CLS, plus the prefix TBL. You can also use other more flexible methods such as record the mapping relationship into the file.
Then traverse all rows of the TBLTEacher table in the db.xml file, where the reflection method is used to obtain the value of this attribute in the case of knowing the object attribute name (this is also twists and turns, start calling with the invokememember, so troublesome Finally, there is a way to have a CallbyName method, which is simple. In fact, it also encapsulates invokemember, which finally generates such a SQL statement returns.
INSERT INTO TBLTEACHER (Name, Code, Gender ...) VALUES ('Haha', '
2001 '
,1…)
(A few things to note that in different ways of add / delete / update, there are different requirements for the data table field. For example, when adding, the automatic number ID cannot be assigned, so the above SQL statement does not have an ID field. Here I only use the field properties of Seek and Key, in fact, other properties, such as the maximum value of the digital, the regular expression of characters. To complete the data verification operation in Clssqlbuilder)
Final CLSDataAccessOper.Add method uses this SQL statement to connect to the database for operation.
I will not explain in detail. The code is posted, I look at it.
'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 instead
Public const complexsql as string = "@complexsql"
'Rootware physical classes Generate the corresponding INSERT ... SQL statement
'If the key field is related to the database table name and is automatically added to the value (the value of SEED in the db.xml file is 1)' Then the genus will ignore, and there will be no Insert ... SQL In the 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) .rows
If 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" thentmpstring = "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 String
Try
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 = "" "Thenthrow 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" THEN
Tmpstring = "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 ).Rowsif 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 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 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.TOSTRINGDIM 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) .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
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 Stringdim 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)
END IF
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 Outstringelseif T = "Date" OR T = "String" THEN
Outstring = "'"
Return Outstring
END IF
END FUNCTION
END CLASS
'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.Messagereturn 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 you want to operate with Returntype, Eg: returntype = "clsrooms", SaipleName = "TBL_ROOMS"
'This query method adds the query condition into the keys (HashTable), then call the select method to return to the object.
'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 STRINGFOR 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 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
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) .trimexit for
END IF
NEXT
Return Keynum
END FUNCTION
'Provide general functions for first, previous, next, limited
Private Shared Function Move (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