Transformers of the Four Kings (SqlHelper)

</pre>        1, Basic introduction <p></p><p><span style= "Color:rgb (51,51,51); Font-family:arial, song body, Sans-serif; line-height:24px; text-indent:28px "><span style=" font-size:18px ">        SqlHelper is a net-based The database operations component of the framework. The component contains a database action method. SqlHelper is used to simplify the way you repeatedly write those database connections (SqlConnection), Sqlcommand,sqldatareader, and so on. SqlHelper package is usually only needed to pass some parameters such as database connection string, SQL parameters, etc., you can access the database, very convenient. </span></span></p><p><span style= "Color:rgb (51,51,51); Font-family:arial, song body, Sans-serif; line-height:24px; text-indent:28px "><span style=" font-size:18px ">        Simply speaking, is the" Four kings "in the common things, packaged up, This makes our code simple and easy to scale. </span></span></p><p><span style= "Color:rgb (51,51,51); Font-family:arial, song body, Sans-serif; line-height:24px; text-indent:28px "><span style=" font-size:18px ">       <strong>2, implementation method (VB.net version) </strong></span></span></p><p><spaN style= "Color:rgb (51,51,51); Font-family:arial, song body, Sans-serif; line-height:24px; text-indent:28px "><span style=" font-size:18px "><strong>          (1) </ strong> Preparation file (U-layer) </span></span></p><p><span style= "Color:rgb (51,51,51); Font-family:arial, song body, Sans-serif; line-height:24px; text-indent:28px "><span style=" font-size:18px ">                  Under the Created App form project, there is an app. config file open, and then add the following code behind </startup> </span></span></p><p>< Span style= "Color:rgb (51,51,51); Font-family:arial, song body, Sans-serif; line-height:24px; text-indent:28px "><span style=" font-size:18px "></span></span></p><pre name=" code " Class= "VB" > <appSettings> <clear/> <add key= "connstr" value= "server=localhost;database=login; User=sa; password=123456; " /> </appSettings>

When you are finished adding, such as

<?xml version= "1.0" encoding= "Utf-8"?><configuration>    <startup>        <supportedruntime version= "v4.0" sku= ". netframework,version=v4.5 "/>    </startup>    <appSettings>    <clear/>    <add key = "ConnStr" value= "server=localhost;database=login; User=sa; password=123456; " />     </appSettings>  </configuration>

Other methods refer to the vb.net-configuration file separately

(2) Realization of SqlHelper class (D-layer)

Imports system.dataimports System.Data.SqlClientImports system.configuration ' add necessary reference to public Class SqlHelper ' definition Variable ' get database link string Private ReadOnly strconnection As String = ("ConnStr") ' Set link Dim conn as SqlConnection = New Sq Lconnection (strconnection) ' Define command command Dim cmd as New SqlCommand ' <summary> ' perform additions and deletions three operations, (with parameters) return value for Bo        Olean type, verify that execution succeeds ' </summary> ' <param name= ' cmdtext ' > need to execute statements, typically SQL statements, and stored procedures </param> ' <param name= ' cmdtype ' > Determine the type of SQL statement, generally not stored procedure </param> ' <param name= ' paras ' > parameter array, unable to confirm how many parameters </p Aram> ' <returns></returns> ' <remarks></remarks> public Function Execadddelup Date (ByVal Cmdtext as String, ByVal Cmdtype as CommandType, ByVal paras as SqlParameter ()) as Integer ' will pass in values, respectively, cmd The property is assigned a value of CMD. Parameters.addrange (paras) ' parameters passed in Cmd.commandtype = Cmdtype ' Set a value explaining Cmdtext cmd. Connection = Conn ' Set connection, global variable cmd. CommandText = Cmdtext ' sets the SQL statement Try Conn. Open () ' opens the link to Return cmd. ExecuteNonQuery () ' perform additions and deletions and change operation CMD.        Parameters.clear () ' Clear parameter Catch ex as Exception return 0 ' returns 0 if an error occurs Finally call CLOSECONN (conn) call Closecmd (cmd) end Try end Function ' <summary > ' Perform additions and deletions to three operations, (no parameter) ' </summary> ' <param name= "Cmdtext" > need to execute statements, typically SQL statements, there are stored procedures </par Am> ' <param name= "Cmdtype" > Determine the type of SQL statement, generally not stored procedure </param> ' <returns>interger, number of rows affected < /returns> ' <remarks>2013 February 2 8:19:59</remarks> public Function Execadddelupdateno (ByVal cmdte XT as String, ByVal Cmdtype as CommandType) as Integer ' is the command cmd to execute cmd. CommandText = Cmdtext ' incoming query statement cmd.            CommandType = Cmdtype   ' Sets how the SQL statement interprets CMD. Connection = conn ' Set connection ' to execute action Try Conn. Open () Return cmd.            ExecuteNonQuery Catch ex as Exception Return 0 Finally call CLOSECONN (conn) Call Closecmd (cmd) end Try End Function ' <summary> ' to perform the operation of the query (with parameters), the parameter is unlimited ' &LT;/SUMMARY&G        T ' <param name= ' cmdtext ' > need to execute statements, typically SQL statements, there are stored procedures </param> ' <param name= ' cmdtype ' > Determine the type of SQL statements, generally not storage Process </param> ' <param Name= ' paras > Incoming parameters </param> ' <returns></returns> ' <remarks></remarks> public Function EXECSELETCT (ByVal cmdtext as String, ByVal Cmdtype as CommandType, Byv Al Paras as SqlParameter ()) as DataTable Dim Sqladpter as SqlDataAdapter ' is a bridge between the dataset and SQL Server for   Retrieving and saving data Dim dt As New DataTable Dim ds As New DataSet ' or cmd assignment cmd.commandtext = Cmdtext               ' Set query statement cmd.commandtype = Cmdtype ' To set the Cmd object's type cmd. Connection = Conn ' database connection statement cmd.            Parameters.addrange (paras) ' incoming parameter Sqladpter = New SqlDataAdapter (cmd) ' Instantiation adapter Try Sqladpter.fill (DS) ' fills the dataset with the adapter dt = ds. Tables (0) ' DataTable is the first table cmd of a dataset. Parameters.clear () ' purge parameter Catch ex as Exception MsgBox ("Query Failed", CType (vbOKOnly + msgboxsty Le. Exclamation, MsgBoxStyle), "Warning! ") Finally call Closecmd (CMD) ' At last, be sure to release the cmd end Try return DT ' return to find Data End Function ' <summary> ' operation of the query, (no parameter) ' </summary> ' <param name= "Cmdtext" > needs to execute statements, typically SQL statements, and stored Procedures </param> ' <param name= ' cmdtype ' > Determine the type of SQL statement, generally not a stored procedure </param> '   <returns>datatable, Enquiry Form </returns>     ' <remarks></remarks> public Function execselectno (ByVal cmdtext as String, ByVal Cmdtype as Comman DType) as DataTable Dim sqladapter As SqlDataAdapter Dim ds As New DataSet ' to cmd-value cmd. CommandText = Cmdtext cmd. CommandType = Cmdtype cmd.                      Connection = conn Sqladapter = New SqlDataAdapter (cmd) ' Instantiation adapter Try Sqladapter.fill (DS) ' Use adapter to populate the DataSet with Return DS.            Tables (0) ' Returns the first table of a DataSet Catch ex as Exception return nothing Finally        Call Closecmd (cmd) ' Close cmd end Try end Function ' <summary> ' close connection       ' </summary> ' <param name= ' conn ' > need to close the connection </param> ' <remarks></remarks> Public Sub Closeconn (ByVal conn as SqlConnection) IF (Conn. State <> connectionstate.closed) and then ' if Conn is not turned off.  Close ()                              ' Close Connection conn = Nothing ' does not point to the original object end If end Su        B ' <summary> ' Close command ' </summary> ' <param name= ' cmd ' > command to close </param>                       ' <remarks></remarks> public Sub closecmd (ByVal cmd as SqlCommand) If not isnothing (cmd) Then ' If the cmd command also exists cmd.     Dispose () ' Frees resource cmd = Nothing ' does not point to the original object End If End SubEnd Class