Easy to handle data access layer [continued]

Access to | data database tables

Tbl_teacher

data field Name
Type
Description

Teaid
Int
Automatic numbering

Teacode
Char (20)
Teacher Employee Number

Teaname
Nchar (10)
?

Teagender
Bit
?

Teanation
Nchar (6)
?

Teaage
TinyInt
?




Tbl_student

data field Name
Type
Description

Stuid
Int
Automatic numbering

Stucode
Char (20)
Student ID Number

Stuteachercode
Char (20)
The employee number of the class teacher

Stuname
Nchar (10)
Name

Stugender
Bit
Gender

Stunation
Nchar (6)
National

Stuage
TinyInt
Age

Stuclassid
Int
ID of Class




The XML file that describes the two tables, which holds the structure information for both tables

Suppose the name of the file is bit? Db. Xml

Tbl_teacher

Name
dbname
Type
Seed
Key

Id
Teaid
Integer
1
1

Code
Teacode
String
0
0

Name
Teaname
String
0
0

Gender
Teagender
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
Stugender
Boolean
0
0

Nation
Stunation
String
0
0

Age
Stuage
Integer
0
0

ClassID
Stuclassid
Integer
0
0




Required Classes

Clssqlhelper: Microsoft's Data Access helper class

Clssqlbuilder: Classes that generate the corresponding SQL statements based on the configuration file easy to handle data access layer [continued 1]

Clsdataaccessoper: So the parent class of the Operation class, provides common data manipulation methods see easy to handle data access layer [continued 2]

Clsdataaccess: No use for the moment.



Data classes and Access classes

Entity class Clsteacher property, corresponding to database field one by one

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 class 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: The specific code for the above two classes is not written.




Access class Clsteacheroper inherit from Clsdataaccessoper

public class Clsteacheroper

Public Function GetAll () as ArrayList

Return Clsdataaccessoper.select (New clsteacher). GetType)

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 methods are similar and implement some queries such as getxxx by YYY

' According to operations such as Add/remove/Modify all inherit from Clsdataaccessoper

' The Query method here can be used Clsdataaccessoper.selectkeys ("") = ... Realize

' Just to provide a friendlier interface, if you're pressed for time can put

' Clsdataaccessoper.selectkeys/select method submitted to a logical layer or presentation level programmer

End Class

Access class Clsstudentoper inherit from Clsdataaccessoper

(Same Clsteacheroper Class)




Previous << easy to handle data access Layer >> Article someone said I have too little code to read. In fact, I just want to explain the truth.

Now I'm going to post all the code for the Clssqlbuilder clsdataaccessoper these two core classes. This is only to make it more clear that the truth. These two classes are the first version, the code quality is not very high, some exceptions are not thrown. Just implemented some functionality.

By the way, entity classes can be generated from database scripts, and this tool is certainly written by itself. The tool is very simple, two hundred or three hundred lines can be done. The XML file that has the database structure information is also cumbersome to write itself, so you can write a tool that automatically generates it.

That's not the point of this article, it's not about how to generate code that looks almost the same.




The advantage of this method of data access is that:

If your database changes, such as Tbl_teahcer table with a field title (Teatitle), then you only need to add a property title to the Clsteacher table, then in DB. Add a record to the Tblteacher in the XML file title|teatitle| String|0|0 is OK.

Fast coding speed, in addition to these two core classes, data entity classes can be automatically generated, access classes are simply write some query methods.

You do not have to write SQL statements.





Examples of how to use:

Add a teacher

Dim Newteacher as New clsteacher

With Newteahcer

' Here if there is an assignment of the ID, it will be ignored because the value is automatically added. See Clssqlbuilder

. 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 (here is not an example).

Now it's time to add a teacher to the program flow about.

After executing clsteacheroper.add (newteancher), Clsdataaccessoper.add will continue to pass Newteacher to the Clssqlbuilder.add () method, in which Clssqlbuilder first gets the type of the object

Here is the "Clsteacher" type string and DB. The tblteacher in the XML file has one by one corresponding relationships, which is to remove the prefix cls, plus the prefix tbl. You can also use other, more flexible methods, such as: record mapping relationships in a file.

Then iterate through all the rows of the Tblteacher table in the Db.xml file, where the reflection method is used to get the value of the property by knowing the name of the object's property (which I also twists and turns, starting with the InvokeMember call, good trouble.) finally found a CallByName method, it is very simple to use. In fact it is also encapsulated InvokeMember,). The resulting SQL statement returns.

Insert into Tblteacher (Name,code,gender ...) VALUES (' Haha ', ' 2001 ', 1 ...)

(There are several points to note that in add/delete/update different methods of operation, there are different requirements for data table fields. For example, when adding, you cannot assign a value to an AutoNumber ID, so the SQL statement above does not have an ID field. Here I only use the field properties of seek and key. You can actually add other properties, such as the maximum number type, and the regular expression for character validation. To complete the data validation operation in Clssqlbuilder

Finally, the Clsdataaccessoper.add method uses this SQL statement to connect to the database for operation.

Clsdataaccessoper and Clssqlbuilder I will not explain the details. The code is posted, see for yourself