Easy data access layer Yuhonglai [original] 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.
