Public Class Daxecutor, PUBLIC CLASS DAXECUTOR
Private connStr as string
Private conn as sqlclient.sqlconnection
Private rs as sqlclclient.sqldatareader
Private Sqlstr as String
Private rcmd as sqlclient.sqlcommand
Private sb as new system.text.stringbuilder
Private sqlpar as new sqlclient.sqlparameter
Friend Sub New ()
End Sub
'********************************************************** ******************
'Do not return SQL
Friend Sub Execute ()
Rcmd.connection.open ()
Rcmd.comMandText = SQLSTR
Rcmd.executenonQuery ()
End Sub
'********************************************************** ******************
'Perform a single return value SQL
Friend Function GetsingLas () As Object
Rcmd.connection.open ()
Rcmd.comMandText = SQLSTR
Return rcmd.executescalar ()
END FUNCTION
'********************************************************** ******************
'Execute result set SQL
Friend function getQueryds () as sqlclient.sqldatareader
Rcmd.connection.open ()
Rcmd.comMandText = SQLSTR
RS = rcmd.executeReader (Commandbehavior.singleResult)
Return RS
END FUNCTION
'********************************************************** ******************
'Construction parameters
Friend overloads sub addpar (byval sqlpar as string, byval valpar as string)
Rcmd.Parameters.Add (Sqlpar, Valpar)
End Sub
'********************************************************** ******************
'SQL statement attribute
Public Writeonly Property Istext () AS BYTE
Set (Byval value as byte)
IF value = 1 THEN
Rcmd.commandtype = commandtype.text
Else
Rcmd.commandtype = commandtype.storedProcedure
END IF
End set
End Property
'********************************************************** ******************
'SQL statement
Public property SQL () AS STRING
Get
Return SQLSTR
END GET
Set (byval value as string)
SQLSTR = Value
End setnd Property
'********************************************************** ******************
'Start database object
Friend Sub open ()
Connstr = configurationSettings.appsettings ("sqlconnstring")
CONN = New Sqlclient.sqlConnection (Conntr)
Rcmd = new sqlclient.sqlcommand (SQLSTR, CONN)
End Sub
'********************************************************** ******************
'Terminate Database Objects
Friend Sub Terminate ()
CONN.CLOSE ()
Rcmd.connection.close ()
CONN = Nothing
RCMD = Nothing
End Sub
END CLASS
Usage 1: Return to a single value
DIM SQL AS New Daxecutor
DIM User_T, Pass_t As String
DIM CK As Object
User_t = username.text
Pass_t = password.text
'SQLSTR = "SELECT TOP 1 ID from DATADMIN_USER WHERE AUSER ='" & User_t & "'and apass ='" & pass_t & "'"
With sql
.Open ()
. SQL = "SELECT TOP 1 id from dataMin_user where auser = @ user_t and apass = @ pass_t"
.Istext = 1
.Addpar ("@ User_t", user_t)
.Addpar ("@ Pass_t", pass_t)
CK = .GetsingLEValue ()
.TERMINATE ()
End with
SQL = Nothing
Usage 2: Insert operation
DIM SQL AS New Daxecutor
With sql
.Open ()
. SQL = "INSERT INTO NPAPER_ARTICLES (ClassType, Titles, Content, IStop, Keywords, Keyperson) Values (" _ _
& "@Classtypes," _
& "@Title," _
& "@Contents,"
& "@Istops," _
& "@Keyw," _
& "@KeyP)"
.Istext = 1
.Addpar ("@ ClasStypes", ClassTypes)
.Addpar ("@ title", Title)
.Addpar ("@ Contents", Contents)
.Addpar ("@ iStops", iStops)
.Addpar ("@ Keyw", Keyw)
.Addpar ("@ Keyp", keyp) .execute ()
.TERMINATE ()
End with
SQL = Nothing
Usage 3: Data set operation
DIM RS As Sqlclient.sqldataReader
DIM SQL AS New Daxecutor
With sql
.Open ()
. SQL = "SELECT ID, MNAME, CLASSTYPE from NPAPER_SUBMENU"
.Istext = 1
RS = .GetQueryds ()
End with
'SQLSTR = "SELECT ID, MNAME, CLASSTYPE from NPAPER_SUBMENU"
'Rcmd = new sqlclient.sqlcommand (SQLSTR, CONN)
'Rcmd.connection.open ()
'Rs = rcmd.executeReader (Commandbehavior.CloseConnection)
Do while rread = true
ClassType.Items.Add (NEW ListItem (1), RS.GetByte (0) .tostring))
Loop
Rs.close ()
Sql.terminate ()
SQL = Nothing
