Four kings Transformers (SqlHelper)

1, on the basis of the introduction

SqlHelper is a database operations component based on the NET Framework. A database operation method is included in the component. SqlHelper is used to simplify the way you repeatedly write those database connections (SqlConnection), Sqlcommand,sqldatareader, and so on. After the SqlHelper package, it is generally only necessary to pass some parameters to the method such as the database connection string. SQL parameters, and so on. will be able to access the database. Very convenient.

Simply speaking, it is the "four kings" in the common things. encapsulation, which makes our code simple and easy to scale.

2. Implementation method (VB.net version) (1) </strong> Preparation document (U-layer)

Under the application window project that was created. Have an app. config file open, and then add the following code to the </startup>, for example

<span style= "FONT-SIZE:18PX;" >  <appSettings>    <clear/>    <add key= "connstr" value= "Server=localhost;database=login ; User=sa; password=123456; " />     </appSettings></span>

After joining, for example, with

<span style= "FONT-SIZE:18PX;" ><?
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></span>

Other methods vb.net-configuration file

(2) Realization of SqlHelper class

<span style= "FONT-SIZE:18PX;"    >imports system.dataimports System.Data.SqlClientImports System.Configuration ' Join the necessary reference to public Class SqlHelper ' Define variable ' get database link string Private ReadOnly strconnection As String = ("ConnStr") ' Set link Dim conn as SqlConnection = Ne W SqlConnection (strconnection) ' Define command command Dim cmd as New SqlCommand ' <summary> ' run additions and deletions three operations, (has participated) return        A value of Boolean that confirms whether the run succeeded ' </summary> ' <param name= ' cmdtext ' > required to run statements, usually SQL statements, and stored procedures </param> ' <param name= ' cmdtype ' > infer the type of SQL statement, which is generally not a stored procedure </param> ' <param name= ' paras ' > parameter array, unable to confirm how many of the parameters &L T;/param> ' <returns></returns> ' <remarks></remarks> public Function execaddd Elupdate (ByVal cmdtext as String, ByVal Cmdtype as CommandType, ByVal paras as SqlParameter ()) as Integer ' will pass in the value, respectively The Cmd property is assigned a value of CMD. Parameters.addrange (paras) ' passes the number of references to Cmd.commandtype = Cmdtype ' To set a value,Explains Cmdtext cmd. Connection = Conn ' Set the connection, global variable cmd. CommandText = Cmdtext ' sets the SQL statement Try Conn. Open () ' opens the link to Return cmd. ExecuteNonQuery () ' Run and delete and change the operation CMD.        Parameters.clear () ' Purge parameters Catch ex as Exception return 0 ' assuming an error returns 0 Finally call CLOSECONN (conn) call Closecmd (cmd) end Try end Function ' <summary > ' run additions and deletions to three operations, (no participation) ' </summary> ' <param name= "Cmdtext" > need to run statements, usually SQL statements, as well as stored procedures </par Am> ' <param name= ' cmdtype ' > infer the type of SQL statement, which is generally not a stored procedure </param> ' <returns>interger, the 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 be run. CommandText = Cmdtext ' incoming query statement CMd.commandtype = Cmdtype ' Sets how the SQL statement interprets CMD. Connection = conn ' Set connection ' run Operation Try Conn. Open () Return cmd.            ExecuteNonQuery Catch ex as Exception Return 0 Finally call CLOSECONN (conn) Call Closecmd (cmd) End Try End Function ' <summary> ' operation of the query, (with participation), the number of parameters is limited to ' &LT;/SUMMARY&G        T ' <param name= ' cmdtext ' > need to run statements, usually SQL statements, as well as stored procedures </param> ' <param name= ' cmdtype ' > Infer Types of SQL statements, typically not stored Process </param> ' <param Name= ' paras > Incoming references </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 ' Sets the query statement cmd. CommandType = Cmdtype ' Sets the Cmd object's type cmd. Connection = Conn ' database connection statement cmd.            Parameters.addrange (paras) ' incoming argument 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 parameters 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 The data End function ' <summary> ' operation of the query, (no participation) ' </summary> ' <param name= "Cmdtext" > need to run statements, usually SQL statements, and stored Procedures </param> ' <param name= ' cmdtype ' > infer the type of SQL statement, which is generally not a stored procedure </param> ' <returns>d atatable, check the table </returns> ' <remarks></remarks> public Function execselectno (ByVal Cmdtex T as String, ByVal Cmdtype as CommandType) as DataTable Dim sqladapter As SqlDataAdapter Dim ds as New DataS Et ' assigns a value of cmd 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) theN ' assumes that Conn is not closed.    Close () ' Close Connection conn = Nothing ' does not point to the original object End If End Sub ' <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 ' Assume that the cmd command also has cmd.     Dispose () ' Frees resource cmd = Nothing ' does not point to the original object End If End SubEnd class</span>

Copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

Four kings Transformers (SqlHelper)