Using Dal with sqlhelper

This is the code record for the third data center charging system. The Dal layer is configured with sqlhelper to make the code more optimized. The following code records the user table (userinfo) Dal layer of the data center charging system.

The code for the Dal layer is as follows:

'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
''Daluserinfosql. VB
''Implementation of the class daluserinfosql
''Generated by enterprise effecect
''Created on: 06-August 15-2011 15:39:54
''Original Author: Huang aigang
''
'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
''Modification history:
''
'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''

Option explicit on
Option strict on

Imports entity
Imports system. Data. sqlclient
Imports dalinterface

'Namespace computersys. dal
''' <Summary>
''' User information
''' </Summary>
Public class daluserinfosql
'Herits computersys. Dal. sqlhelper
Implements dalinterface. iuserinfo

''' <Summary>
''' Add a user
''' </Summary>
''' <Param name = "entityuserinfo"> User Information </param>
Public Function adduser (byval entityuserinfo as entityuserinfo) as Boolean implements iuserinfo. adduser
Dim sqlstr as string = "insert into userinfo (userid, userpwd, userlevel, userregister, username, usestate) values (@ userid, @ userpwd, @ userlevel, @ userregister, @ username, @ usestate )"
Dim sqlparams (5) as sqlparameter 'defines an array
Dim sqlhelper as new sqlhelper instantiate sqlhelper

'Add data to the array
Sqlparams (0) = new sqlparameter ("@ userid", entityuserinfo. userid)
Sqlparams (1) = new sqlparameter ("@ userpwd", entityuserinfo. userpwd)
Sqlparams (2) = new sqlparameter ("@ userlevel", entityuserinfo. userlevel)
Sqlparams (3) = new sqlparameter ("@ userregister", entityuserinfo. userregister)
Sqlparams (4) = new sqlparameter ("@ username", entityuserinfo. username)
Sqlparams (5) = new sqlparameter ("@ usestate", entityuserinfo. usestate)

'Call the add method and return a Boolean Value
Return sqlhelper. executeadddeleteupdate (sqlstr, sqlparams)

End Function

''' <Summary>
''' Updates the user's usage status based on the user ID.
''' </Summary>
''' <Param name = "entityuserinfo"> User Information </param>
Public Function modifyuserstate (byval entityuserinfo as entityuserinfo) as Boolean implements iuserinfo. modifyuserstate
Dim sqlstr as string = "Update userinfo set usestate = @ usestate where userid = @ userid"
Dim sqlparams (1) as sqlparameter 'defines an array
Dim sqlhelper as new sqlhelper instantiate sqlhelper

'Add data to the array
Sqlparams (0) = new sqlparameter ("@ userid", entityuserinfo. userid)
Sqlparams (1) = new sqlparameter ("@ usestate", entityuserinfo. usestate)

'Call the update method and return a Boolean Value
Return sqlhelper. executeadddeleteupdate (sqlstr, sqlparams)

End Function

''' <Summary>
''' Query all user information based on the user level
''' </Summary>
''' <Param name = "entityuserlevel"> user level in user information </param>
Public Function queryalluser (byval entityuserlevel as entityuserinfo) as dataset implements iuserinfo. queryalluser
Dim sqlstr as string = "select * From userinfo where userlevel = @ userlevel"
Dim sqlparams (0) as sqlparameter 'defines an array
Dim sqlhelper as new sqlhelper 'defines and instantiates sqlhelper

'Add an array element
Sqlparams (0) = new sqlparameter ("@ userlevel", entityuserlevel. userlevel)

'Call the query method and return Dataset
Return sqlhelper. executequery (sqlstr, sqlparams)

End Function

''' <Summary>
''' Obtain user information based on the user ID
''' </Summary>
''' <Param name = "entityuserinfo"> User ID in user information </param>
Public Function querybyid (byval entityuserinfo as entityuserinfo) as dataset implements iuserinfo. querybyid
Dim sqlstr as string = "select * From userinfo where userid = @ userid"
Dim sqlparams (0) as sqlparameter 'defines an array
Dim sqlhelper as new sqlhelper 'defines and instantiates sqlhelper

'Add an array element
Sqlparams (0) = new sqlparameter ("@ userid", entityuserinfo. userid)

'Call the query method and return Dataset
Return sqlhelper. executequery (sqlstr, sqlparams)

End Function

''' <Summary>
''' Change the User Password based on the user ID
''' </Summary>
''' <Param name = "entityuserinfo"> User Information </param>
Public Function modifyuserpwd (byval entityuserinfo as entity. entityuserinfo) as Boolean implements iuserinfo. modifyuserpwd
Dim sqlstr as string = "Update userinfo set userpwd = @ userpwd where userid = @ userid"
Dim sqlparams (1) as sqlparameter 'defines an array
Dim sqlhelper as new sqlhelper instantiate sqlhelper

'Add data to the array
Sqlparams (0) = new sqlparameter ("@ userid", entityuserinfo. userid)
Sqlparams (1) = new sqlparameter ("@ userpwd", entityuserinfo. userpwd)

'Call the update method and return a Boolean Value
Return sqlhelper. executeadddeleteupdate (sqlstr, sqlparams)

End Function

End Class

The sqlhelper code is as follows:

'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
''Sqlhelper. VB
''Implementation of the class sqlhelper
''Generated by enterprise effecect
''Created on: 06-August 15-2011 15:40:07
''Original Author: Huang aigang
''
'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
''Modification history:
''
'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''

Option explicit on
Option strict on

Imports system. Data. sqlclient
Imports system. Reflection

'Namespace computersys. dal
''' <Summary>
''' Extracts the code repeatedly used during query for calling by all Dal layer modules.
''' </Summary>
Public class sqlhelper

''' <Summary>
'''Database connection string
''' </Summary>
Private connstr as string = system. configuration. configurationsettings. deleettings ("connstr ")
Dim conn as sqlconnection = new sqlconnection (connstr) 'defines the connection

''' <Summary>
''' Connect to the database
''' </Summary>
''' <Param name = "sqlstr"> </param>
'Public function connection (byval sqlstr as string) as Boolean
'Connection = false
'End Function

''' <Summary>
'''Execute the add, delete, modify, and return the boolean type.
''' </Summary>
''' <Param name = "sqlstr"> </param>
Public Function executeadddeleteupdate (byval sqlstr as string, byval sqlparameter as sqlparameter () as Boolean
Dim cmd as sqlcommand = new sqlcommand (sqlstr, Conn)

'Add SQL statements cyclically
For I = 0 to sqlparameter. Length-1
Cmd. Parameters. Add (sqlparameter (I ))
Next
Conn. open () 'Open the connection
Return cmd. executenonquery ()> 0
Conn. Close () 'Close the connection
Conn = nothing 'clear
Cmd. Dispose () 'Close cmd
Cmd = nothing 'clear

End Function

''' <Summary>
''' Execute the query statement and return Dataset
''' </Summary>
''' <Param name = "sqlstr"> SQL statement </param>
Public Function executequery (byval sqlstr as string, byval sqlparameter as sqlparameter () as Dataset
Dim cmd as sqlcommand = new sqlcommand (sqlstr, Conn)

'Add SQL statements cyclically
For I = 0 to sqlparameter. Length-1
Cmd. Parameters. Add (sqlparameter (I ))
Next

Dim DAP as sqldataadapter = new sqldataadapter (CMD) 'defines dataadapter
Dim ds as new dataset 'defines Dataset

Try
Dap. Fill (DS) 'add data to Dataset
Return DS 'Return Dataset
Catch ex as exception
Return nothing
End try

Conn = nothing 'clear
Cmd. Dispose () 'Close cmd
Cmd = nothing 'clear

End Function

''' <Summary>
''' Execute the query statement and return Dataset
''' </Summary>
''' <Param name = "sqlstr"> SQL statement </param>
Public Function executequery (byval sqlstr as string) as Dataset
Dim cmd as sqlcommand = new sqlcommand (sqlstr, Conn) 'add execution command
Dim DAP as sqldataadapter = new sqldataadapter (CMD) 'defines dataadapter
Dim ds as new dataset 'defines Dataset

Try
Conn. open () 'Open the connection (Note: This sentence can be left blank)
Dap. Fill (DS) 'add data to Dataset
Return DS 'Return Dataset
Catch ex as exception
Return nothing
End try

Conn. Close () 'Close the connection (Note: This statement can be left blank)
Conn = nothing 'clear
Cmd. Dispose () 'Close cmd
Cmd = nothing 'clear

End Function
End Class

 

Click connection for the second Dal Layer Code