Collection: Data Access Layer

xiaoxiao2021-03-06  83

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

转载请注明原文地址:https://www.9cbs.com/read-106502.html

New Post(0)