[Data center cooperation]-SqlHelper we have another appointment. Data Center cooperation sqlhelper

[Data center cooperation]-SqlHelper we have another appointment. Data Center cooperation sqlhelper
1. What is it? SqlHelper is a database operation component based on · NET Framework, which contains database operation methods.
2. Why? Why use the SqlHelper class?
1. SqlHelper is used to simplify repeated writes to database connections (such as SqlConnection), SqlCommand, and SqlDataReader.
2. After qlHelper is encapsulated, you only need to input some parameters to the method, such as database connection strings and SQL parameters, to access the database.
3. Each method overload supports different method parameters, so developers can determine how to pass connection, transaction, and parameter information.
4. It provides necessary flexibility for developers to select a way to access data.
3. How to use it? The methods implemented in the SqlHelper class include:
1. ExecuteNonQuery. This method is used to execute commands that do not return any rows or values. These commands are usually used to execute database updates, but can also be used to return output parameters of stored procedures.
2. ExecuteReader. This method is used to return the SqlDataReader object, which contains the result set returned by a command.
3. ExecuteDataset. This method returns the DataSet object, which contains the result set returned by a command.
4. ExecuteScalar. This method returns a value. This value is always the first column in the first line returned by the command.

5. ExecuteXmlReader. This method returns the XML fragment of the for xml query.

VB. NET instance (SqlHelper class ):

Imports System. data. sqlClientImports System. reflectionImports System. dataImports System. configurationImports System. collections. genericPublic Class SQLHelper defines a database connection object Dim strCon As String = ConfigurationSettings. appSettings ("ConnString") Dim conn As SqlConnection = New SqlConnection (strCon) 'define a command object Dim cmd As New SqlCommand ''' <summary> ''' close connection ''' </summary> ''' <param name = "conn"> required closed connection </param> ''' <remarks> </remarks> Private Sub CloseConn (ByVal conn As SqlConnection) 'If not, close the connection If (conn. state <> ConnectionState. closed) Then conn. close () conn = Nothing End If End Sub ''' <summary> ''' close command ''' </summary> ''' <param name = "cmd"> command to be closed </param> ''' <remarks> </remarks> Private Sub CloseCmd (ByVal cmd As SqlCommand) 'If Not IsNothing (cmd) Then cmd. dispose () 'processing 'COMMAND = Nothing End If End Sub ''' <summary> ''' added, deleted, modified, and operated ''' with parameters </summary> ''' <param name =" plain text "> command to be executed </param> ''' <param name =" plain type "> type of the command to be executed, it is generally an SQL statement or a stored procedure, or the table </param> ''' <param name = "sqlParams"> parameter array </param> ''' <returns> returns the number of rows affected by the addition, deletion, and modification statement, integer type </returns> ''' <remarks> </remarks> Public Function ExecuteNoQuery (ByVal plain text As String, ByVal primitive type As CommandType, ByVal sqlParams As SqlParameter ()) as Integer, the passed values are assigned to the cmd attributes respectively. parameters. addRange (sqlParams) 'refers to the cmd parameter. commandType = commantype cmd. connection = conn' sets the Connection cmd. commandText = plain text 'cmd = New SqlCommand (plain text, conn) Dim result As Integer' execute the Try conn operation. open () result = cmd. executeNonQuery () ': Execute add, delete, modify, and return the affected number of rows. cmd. parameters. clear () 'clear the Catch ex As Exception MsgBox (ex. message, "database operation") Finally Call CloseConn (conn) 'close connection Call CloseCmd (cmd) 'Close command End Try Return result End function''' <summary> ''' No parameter addition, deletion, modification operation ''' </summary> ''' <param name = "plain text "> type of the command to be executed </param> ''' <param name =" primitive type ">, it is generally an SQL statement, or a stored procedure, or a table </param> ''' <returns> returns the number of rows affected by the execution of the add, delete, and modify statements, integer type </returns> ''' <remarks> </remarks> Public Function ExecuteNoQuery (ByVal plain text As String, ByVal primitive type As CommandType) as Integer, the passed parameters are assigned to the cmd attribute. commandType = commantype cmd. connection = conn cmd. commandText = plain text 'sets the statement 'cmd = New SqlCommand (plain text, conn) Dim res As integer' to execute the Try conn operation. open () res = cmd. executeNonQuery () ': Execute add, delete, modify, and return the affected row cmd. parameters. clear () 'catch ex As Exception MsgBox (ex. message, "Database Operations") Finally Call CloseConn (conn) Call CloseCmd (cmd) end Try Return res End Function ''' <summary> ''' query operations with parameters ''' </summary> ''' <param name = "plain text"> need to be executed </param> ''' <param name = "primitive type"> indicates the type of the command to be executed, it is generally an SQL statement, it may also be a stored procedure or table </param> ''' <param name = "sqlParams"> parameter array </param> ''' <returns>. result, dataTable type </returns> ''' <remarks> </remarks> Public Function ExecSelect (ByVal plain text As String, ByVal primitive type As CommandType, ByVal sqlParams As SqlParameter ()) as DataTable Dim sqlAdapter As New SqlDataAdapter 'declare adapter Dim dt As New able able' declare data table Dim ds As New DataSet 'declare data cache cmd. commandType = commantype cmd. connection = conn cmd. commandText = plain text 'set query statement' the passed values are respectively paid to the cmd attribute cmd. parameters. addRange (sqlParams) 'transmits the parameter to 'cmd = New SqlCommand (plain text, conn) sqlAdapter = New SqlDataAdapter (cmd)' instantiate adapt' to execute the operation Try sqlAdapter. fill (ds) 'fills ds with the adapter dt = ds. tables (0) 'returns the first table cmd of the dataset. parameters. clear () 'catch ex As Exception MsgBox ("query failed", CType (vbOKOnly + MsgBoxStyle. exclamation, MsgBoxStyle), "warning") Finally Call CloseConn (conn) Call CloseCmd (cmd) end Try Return dt End Function ''' <summary> ''' query operations without parameters ''' </summary> ''' <param name = "plain text"> need to be executed </param> ''' <param name = "primitive type"> indicates the type of the command to be executed, it is generally an SQL statement, or a stored procedure or table </param> ''' <returns>, dataTable type </returns> ''' <remarks> </remarks> Public Function ExecSelect (ByVal plain text As String, ByVal primitive type As CommandType) as DataTable Dim sqlAdapter As SqlDataAdapter 'declare the adapter Dim dt As New able able' declare the data table Dim ds As New DataSet 'declare the data cache' assign the passed values to the cmd attribute cmd respectively. commandType = commantype' sets a value, which is then plain text cmd. connection = conn cmd. commandText = plain text sqlAdapter = New SqlDataAdapter (cmd) 'instance' execute the Try sqlAdapter operation. fill (ds) 'fills ds with the adapter dt = ds. tables (0) 'returns the first table Catch ex As Exception MsgBox ("query failed", CType (vbOKOnly + MsgBoxStyle. exclamation, MsgBoxStyle), "warning") Finally Call CloseConn (conn) Call CloseCmd (cmd) End Try Return dt End FunctionEnd Class

The instance that calls the corresponding method in the SqlHelper class at Layer D:

<Span style = "font-family: KaiTi_GB2312; font-size: 18px; "> ''' call the add, delete, modify, and delete operations with parameters ''' <summary> ''' insert a row of information into the basic data settings table ''' </summary> ''' <param name = "enBasicData"> pass in the attribute value of the BasicData table </param> ''' <returns> returns a Boolean value </returns> ''' <remarks> </remarks> public Function InsertBasicData (enBasicData As BasicDataEntity) as Boolean Implements IDAL. IBasicData. insertBasicData defines the database connection String Dim SQL As String = "Insert into T_BasicData (Rate, TmpRate, UnitTime, LeastTime, PrepareTime, LimitCash, Date, Time, UserID) value (@ rate, @ tmprate, @ unittime, @ leasttime, @ reparetime, @ limitcash, @ date, @ time, @ userid) "'puts forward the string to be inserted and assigns Dim sqlparams As SqlParameter () = {New SqlParameter ("@ rate", enBasicData. rate), New SqlParameter ("@ tmprate", enBasicData. tmpRate), New SqlParameter ("@ unittime", enBasicData. unitTime), New SqlParameter ("@ leasttime", enBasicData. leastTime), New SqlParameter ("@ preparetime", enBasicData. prepareTime), New SqlParameter ("@ limitcash", enBasicData. limitCash), New SqlParameter ("@ date", enBasicData. zDate), New SqlParameter ("@ time", enBasicData. time), New SqlParameter ("@ userid", enBasicData. userID)} 'instantiate a sqlhelper Class Object Dim helper As New SQLHelper' Call the sqlhelper class method Dim InsertOK = helper. executeNoQuery (SQL, CommandType. text, sqlparams) return InsertOK End Function ''' No parameter addition, deletion, modification operation ''' <summary> ''' delete all information in the user record table using the user account ''' </summary>'' '<returns> return Boolean value </returns> ''' <remarks> </remarks> Public Function DeleteByUserID (enUser As UserEntity) as Boolean Implements IDAL. IUser. deleteByUserID 'create and delete the database connection statement Dim SQL As String = "delete from T_User" 'defines a sqlHelper Class Object Dim helper As New SQLHelper' Call the delete method Dim DeleteOK = helper in sqlHelper. executeNoQuery (SQL, CommandType. text) return DeleteOK End Function ''' call the parameter query operation ''' <summary> ''' to query information in the card table using the card number ''' </summary> ''' <param name = "enCard"> input CardID value </param> ''' <returns> returns a generic set </returns> ''' <remarks> </remarks> Public Function selectByCardID (enCard As CardEntity) as List (Of CardEntity) Implements IDAL. ICard. selectByCardID 'defines the database connection String Dim SQL As String = "Select * from T_Card where CardID = @ cardid"' to propose the query String and assign the value Dim sqlparams As SqlParameter () = {New SqlParameter ("@ cardid", enCard. cardID)} 'instantiate a temporary table Dim table As New able' instantiate a sqlhelper Class Object Dim helper As New SQLHelper 'to call the query method of the sqlhelper class, and put the queried information in the temporary table = helper. execSelect (SQL, CommandType. text, sqlparams) 'instantiate a generic set Dim mylist As New List (Of CardEntity) 'to convert a temporary table to a generic set mylist = DataTolist. converToList (Of CardEntity) (table) return mylist End Function ''' call the query operation without parameters ''' <summary> ''' to query the latest information in the basic data setting table ''' </summary> ''' <returns> returns a generic set </returns> ''' <remarks> </remarks> Public Function SelectBasicData () as List (Of BasicDataEntity) Implements IDAL. IBasicData. selectBasicData defines the database connection String Dim SQL As String = "Select top 1 * from T_BasicData order by Date desc" 'instantiate the temporary table Dim table As New able able' instantiate the sqlhelper Class Object Dim helper new SQLHelper 'calls the sqlhelper class query method, put the queried information in the temporary table = helper. execSelect (SQL, CommandType. text) 'instantiate a generic set object Dim mylist As New List (Of BasicDataEntity) 'to convert a temporary table to a generic set mylist = DataTolist. converToList (Of BasicDataEntity) (table) Return mylist End Function </span>

Iv. Summary:

SqlHelper is really useful and looks forward to a more intimate appointment with you next time.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.