Sqlhlper's understanding

Three layer of learning to knock through the log in the example of a line, to knock and delete to change the example of four lines, so will be multiple access to the database, which to the database link, open are the same, but for different tables for different operations, since the beginning to learn object-oriented thinking, Improve code reuse when you want content to be packaged for these unchanging needs. So I got to know the sqlhlper. This is the encapsulation of database connection and operation, we can open the database and manipulate the corresponding table when we use it only by passing in parameters.

Their sqlhlper borrowed from the "giant" experience, but also made their own improvements. Here's a look at my sqlhlper.

The first link to the database requires the use of connection strings and command commands

<span style= "color: #009900;" > ' parametric non-query operation where the parameter CommandType represents how to interpret the command string </span>public Shared Function executenoquery (ByVal cmdtxt As String, B Yval Cmdtype as CommandType, ByVal paras as SqlParameter ()) as Integer <span style= "color: #33ff33;" > </span><span style= "color: #009900;" > ' defines a linked string and configures ConnStr with a configuration file </span> Dim strconnstr As String = System.Configuration.ConfigurationManage R.appsettings ("ConnStr") <span style= "color: #009900;" > ' defines a link whose parameters are established by the link string </span> Dim conn as SqlConnection = New SqlConnection (strconnstr) <span sty le= "COLOR: #009900;"        > ' defines a command, cmdtxt parameter is an SQL statement, Conn is a link string to the database </span> Dim cmd as sqlcommand= New SqlCommand (cmdtxt, conn) Cmd.commandtype = Cmdtype <span style= "color: #009900;" > ' defines the type </span> cmd for command commands. Parameters.addrange (paras) <span style= "color: #009900;" > ' Add parameter </span>return noselectresult (conn, cmd) <span style= "BackgrounD-color:rgb (255, 255, 255); " ><span style= "color: #009900;" > ' This part is called abstract function </span></span>end function<span style= "color: #009900;" > ' Execute parameter query </span> public Shared Function getdatatable (ByVal cmdtxt as String, Cmdtype as CommandType, ByVal PA RAS as SqlParameter ()) as DataTable

<pre name= "code" class= "VB" >        <span style= "color: #009900;" > ' Define a String that uses the program configuration, connect to the database </span>

Dim strconnstr as String = System.Configuration.ConfigurationManager.AppSettings ("ConnStr")

Dim conn as SqlConnection = New SqlConnection (strconnstr) <span style= "color: #009900;" > ' defines a new SQL connection string </span> Dim cmd as SqlCommand = new SqlCommand (cmdtxt, conn) <span style= "COLOR: #009 900; " > ' defines a new SqlCommand command </span> Dim adataset as New DataSet <span style= "color: #009900;" > ' defines a dataset that can be used to store the results found </span> Dim adaptor as SqlDataAdapter <span style= "color: #009900;" > ' Fill in the dataset with the results you have found </span> Dim adatatable as New DataTable <span style= "color: #009900;" > ' defines a new table that can hold multiple tables in a DataSet </span> adaptor = new SqlDataAdapter (cmd) <span style= "color: #009900;" > ' Get cmd execution result </span> Cmd.commandtype = cmdtype <span style= "color: #009900;" > ' defines the type of CommandType </span> cmd. Parameters.addrange (paras) <span style= "color: #009900;" > ' Add the parameters to be used to command commands </span> Return selectresult (conn, adaptor, Adataset) End Function

The above is a parameter of non-query and query of the Sqlhlper function, corresponding to no parameter non-query and query only need to remove the definition of the parameter part of the good.

The following is the part of their own improvement, abstract to determine the database link situation and execute command commands, because four cases will be used and no change, so you can put the invariant part of the abstract.

<span style= "color: #009900;" > ' query can be called </span> public Shared Function Selectresult (ByVal conn as SqlConnection, ByVal adaptor as Sqldataadap ter, ByVal Adataset as DataSet) as DataTable Try If Conn. state = connectionstate.closed then <span style= "color: #009900;" > ' Determines the status of the link, if it is turned off, then open </span> Conn. Open () adaptor. Fill (adataset) <span style= "color: #009900;" > ' then fill in the result of the query into the defined dataset </span> End If Catch ex as Exception MsgBox (ex. Message, "database operation") Finally If Conn. state = ConnectionState.Open Then <span style= "color: #009900;" > ' finally determine the status of the link, and if it is open, close </span> Conn. Close () End If End Try Return adataset.tables (0) <span style= "color: #009900;" > ' Returns the table in the dataset </span>end Function <span style= "color: #009900;" > ' non-queried can call </span> public Shared Function Noselectresult (ByVal conn as SqlConnection, byVal cmd as SqlCommand) As Integer Dim res as Integer Try If Conn. State = Connectionstate.closed Then Conn. Open () End If res = cmd. ExecuteNonQuery () <span style= "color: #009900;" > ' performs a non-query action that returns the number of rows affected </span> Catch ex as Exception MsgBox (ex. Message, "database operation") Finally If Conn. State = ConnectionState.Open Then Conn. Close () End If End Try Return Resend Function

Summary

For the sqlhlper itself the code part of the understanding is not difficult, through Sqlhlper can further realize the benefits of object-oriented, and oneself also try to abstract something simple, sqlhlper seems to be a part of their own good, when it is necessary to take directly to use, And it can be made up of other parts, not only improve the reusability of the Code, and in the modification of the time can be modified to modify the "parts", decoupling and the benefits ah, but also need more experience in practice!!!

Sqlhlper's understanding