Computer room fee Reconstruction (iv)-sqlhelper

recently because of the school professional examinations, leading to some of the previous blog did not fill in time, so that the reader waiting, the following say about the Dal in a template bar-sqlhelper.

In the knock machine room charge, there are a lot of repeated access to the database these operations, so that we can abstract these steps, named SqlHelper. Think of abstraction and encapsulation at any time. Object-oriented thought deep into the marrow.

There are four main categories in SqlHelper, namely:
SQL additions and deletions without parameters to a statement or stored procedure (no return rows or values)
SQL additions and deletions with parameters or stored procedures (no return rows or values)
SQL query statement or stored procedure with no parameters, return DataTable result set (with return rows or values)
SQL query statement or stored procedure with parameters that returns a DataTable result set (with return rows or values)

Okay, cut the crap and go straight to the code template.

1. First to obtain a string from a configuration file

<span style= "font-size:14px" >imports System.Data.SqlClientImports system.configuration        ' Add a reference to a configuration file public Class SqlHelper    ' Gets the connection string from the configuration file and assigns it to the declared variable strconnection    Dim strconnection as String = Configurationmanager.appsettings ("Strsqlconnection")    Dim conn as SqlConnection = New SqlConnection (strconnection )                    ' Set connection     Dim cmd as New SqlCommand                                                       ' declares SqlCommand class variable cmd</span>

2. Extract the duplicated code:

<span style=" font-size:14px ">" <summary> "Close Connection" </summary> "<param name=" co        nn "> need to close the connection </param> ' <remarks></remarks> private Sub closeconn (ByVal conn as SqlConnection) ' If it is not closed, close the Connect if (conn. State <> connectionstate.closed) then Conn. Close () conn = Nothing End If end Sub ' <summary> ' Close command ' </summary> ' ' <param name= ' cmd ' > command to close </param> ' <remarks></remarks> Private Sub closecmd (ByVal cmd As SqlCommand) ' if not closed, then close the command if not IsNothing (cmd) and then cmd. Dispose () cmd = Nothing End If end Sub</span> 

    3. Specific operation. respectively, there are parameters and additions and deletions, no parameter additions and deletions, a reference query, no reference query.

<span style= "font-size:14px" > "<summary>" "Close Command" </summary> "<param name=" cmd " > need to close command </param> ' <remarks></remarks> Private Sub closecmd (ByVal cmd as SqlCommand) ' If not, close the command if not IsNothing (cmd) and then cmd. Dispose () cmd = Nothing End If end Sub ' ' <summary> ' have parameter additions and deletions change operation ' </summar Y> "<param name=" Cmdtext "> Commands to execute </param>" <param name= "Cmdtype" > Types of commands executed, typically SQL statements, and Could be a stored procedure, or table </param> "<param name=" sqlparams "> Parameter array </param> '" <returns> returns the affected  Number of rows, for integer type </returns> public Function execadddelupdate (ByVal cmdtext as String, ByVal Cmdtype as CommandType, ByVal Sqlparams as SqlParameter ()) as Integer ' will pass in the value, respectively, to the Cmd property assignment cmd. Parameters.addrange (sqlparams) ' parameters passed in Cmd.commandtype = Cmdtype ' Set a value explaining Cmdtext cmd.connection = conn ' Set connection cmd. CommandText = Cmdtext ' Set query ' statement ' to execute action Try Conn. Open () ' Opens the connection to Return cmd. ExecuteNonQuery () ' Performs a delete and change operation and returns the number of rows affected by CMD. Parameters.clear () ' Clear parameter Catch ex as Exception Return 0 ' If an error occurs, Returns 0 Finally call CLOSECONN (conn) ' Close connection call Closecmd (cmd) ' Close connection End Try end Function ' <summary> ' no parameter additions and deletions change operation ' </summary> ' <param nam    E= "Cmdtext" > Commands to execute </param> "<param name=" Cmdtype "> The type of command executed, typically SQL statement, or possibly a stored procedure, or table </param> ' <returns> returns the number of rows affected by the execution of the delete and modify statement, for the integer type </returns> public Function execadddelupdate (ByVal cmdtext as Strin G, ByVal Cmdtype as CommandType) as Integer ' will pass in the parameters, respectively, of the Cmd property assignment cmd. CommandType = Cmdtype ' Sets a value that explainsCmdtext cmd. Connection = conn ' Set connection cmd. CommandText = Cmdtext ' Set query ' statement ' to execute action Try Conn. Open () ' Opens the connection to Return cmd. ExecuteNonQuery () ' Performs a delete and change operation and returns the number of rows affected by CMD. Parameters.clear () ' Clear parameter Catch ex as Exception Return 0 ' If an error occurs, Returns 0 Finally call CLOSECONN (conn) ' Close connection call Closecmd (cmd) ' Close connection End Try end Function ' <summary> ' parameter query operation ' ' </summary> ' ' <param name '      = "Cmdtext" > Command to execute </param> "<param name=" Cmdtype "> The type of command executed, typically SQL statement, or possibly a stored procedure, or table </param>      ' <param name= ' sqlparams ' > Parameter array </param> ' ' <returns> returns the result of executing the query for the DataTable type </returns> Public Function Execselect (ByVal cmdtext as String, ByVal Cmdtype as CommandType, ByVal Sqlparams as SQlparameter ()) as DataTable Dim sqladapter As SqlDataAdapter ' declaration adapter Dim dt As New DataTable ' Declare data table Dim DS as New DataSet ' declare data cache ' will pass in the value, respectively, to CMD for the property assignment cmd. Parameters.addrange (sqlparams) ' passes parameters to cmd. CommandType = Cmdtype ' Sets a value that interprets Cmdtext cmd. Connection = conn ' Set connection cmd.        CommandText = Cmdtext ' Set query statement sqladapter = New SqlDataAdapter (cmd) ' Instantiate adapter ' to perform action Try Sqladapter.fill (DS) ' uses the adapter to populate the DS with dt = ds. Tables (0) ' Returns the first table of data set CMD. Parameters.clear () ' clears the parameter Catch ex as Exception MsgBox ("Query Failed", CType (vbOKOnly + MsgBoxStyle. Exclamation, MsgBoxStyle), "warning") Finally call Closeconn (conn) ' Close connection call Close cmd (cmd) ' Close connection End Try Return dt End Function ' ' &LT;summary> ' parameter-free query operation, the return value is DataTable type ' </summary> ' ' <param name= ' cmdtext ' > command to execute </par Am> "<param name=" Cmdtype "> The type of command executed, typically SQL statements, or possibly a stored procedure, or table </param>" <returns> return execution query To the result, for the DataTable type </returns> public Function execselect (ByVal cmdtext as String, ByVal Cmdtype as CommandType) A        S DataTable Dim sqladapter As SqlDataAdapter ' declaration adapter Dim dt As New DataTable ' Declaration data table The Dim DS as New DataSet ' declare data cache ' will pass in the values, respectively, to the Cmd property assignment cmd. CommandType = Cmdtype ' Sets a value that interprets Cmdtext cmd. Connection = conn ' Set connection cmd.        CommandText = Cmdtext ' Set query statement sqladapter = New SqlDataAdapter (cmd) ' Instantiate adapter ' to perform action Try Sqladapter.fill (DS) ' uses the adapter to populate the DS with dt = ds. Tables (0) ' Returns the first table of a DataSet Catch ex as Exception MsgBox ("Query Failed", CType (vbOKOnly + msgboxstyle.exclamation, MsgBoxStyle), "warning") Finally call CLOSECONN (conn) ' Close connection call Closecmd (cmd) ' Close connection End Try Return dt End FunctionEnd class< /SPAN>

in the future computer room charges, I would like to use this step, we will not every time access to the database is always repeated write so query database code, from the SqlHelper we can learn the object-oriented thinking, later in the future programming to pay attention to the use of templates. This streamlines the code and improves code utilization.