Transformers (SqlHelper)

Pre1. Basic Introduction: pppspanstylecolor: rgb (, 51); font-family: arial, Al, sans-serif; line-height: 24px; text-indent: 28pxspanstylefont-size: 18pxSqlHelper is a NETFramework-based database operation component. The component contains the database operation method. SqlHelper for simplicity

/Pre 1. Basic Introduction p/ppspan style = color: rgb (51,51, 51); font-family: arial,, sans-serif; line-height: 24px; text-indent: 28 pxspan style = font-size: 18px SqlHelper is a NET Framework-based database operation component. The component contains the database operation method. SqlHelper for simplicity

1. Basic Introduction

SqlHelper is a database operation component based on · NET Framework. The component contains the database operation method. SqlHelper is used to simplify the repeated Writing of database connections (SqlConnection), SqlCommand, SqlDataReader, and so on. After SqlHelper is encapsulated, you only need to input some parameters to the method, such as the database connection string and SQL parameters, to access the database, which is very convenient.

To put it simply, we encapsulate the common things in the "Four Kings", so that our code becomes simple and easy to expand.

2. Implementation Method (Vb. Net)

(1)Preparation file (U layer)

In the created Application Form project, there is an App. Config file, open it, and thenAdd the following code

After adding, as shown in figure

For other methods, refer to the VB. Net-configuration file.

(2) Implement the SqlHelper class (layer D)

Imports System. dataImports System. data. sqlClientImports System. configuration 'add necessary reference Public Class sqlhelper' definition variable 'to obtain database link String Private ReadOnly strConnection As String = ("ConnStr")' set link Dim conn As SqlConnection = New SqlConnection (strConnection) 'define Command Dim cmd As New sqlcommand''Add, delete, modify, and modify operations. (If any parameter exists, the return value is of the Boolean type. Check whether the operation is successful''You need to execute the statement, which is generally an SQL statement and also a stored procedure.'Determining the type of an SQL statement is generally not a stored procedure'Parameter array. The number of parameters cannot be determined.'
 '
 Public Function ExecAddDelUpdate (ByVal plain text As String, ByVal primitive type As CommandType, ByVal paras As SqlParameter () As Integer 'will assign the passed values to the cmd attributes respectively. parameters. addRange (paras) 'transmits the parameter to cmd. commandType = commantype' sets a value to explain plain text cmd. connection = conn' sets the Connection. The global variable is cmd. commandText = plain text 'sets the SQL statement Try conn. open () 'Open link Return cmd. executeNonQuery () ': Execute the add, delete, modify, and delete operation cmd. parameters. clear () 'clear the Catch ex As Exception Return 0' if an error occurs, Return 0 Finally Call CloseConn (conn) Call CloseCmd (cmd) End Try End function''Add, delete, and modify operations (No parameter )''You need to execute the statement, which is generally an SQL statement and also a stored procedure.'Determining the type of an SQL statement is generally not a stored procedure'
 
  
Interger, number of affected rows
 '
 
  
February 2, 2013 8:19:59
 Public Function ExecAddDelUpdateNo (ByVal plain text As String, ByVal primitive type As CommandType) As Integer 'assigns cmd to the command cmd to be executed. commandText = plain text 'input the query statement cmd. commandType = primitive type 'sets how SQL statements explain cmd. connection = conn' set connection' to execute Try conn. open () Return cmd. executeNonQuery Catch ex As Exception Return 0 Finally Call CloseConn (conn) Call CloseCmd (cmd) End Try End function''The query operation is performed (with parameters) with unlimited parameters''You need to execute the statement, which is generally an SQL statement and also a stored procedure.'Determining the type of an SQL statement is generally not a stored procedure'Input parameters'
 '
 Public Function ExecSeletct (ByVal plain text As String, ByVal primitive type As CommandType, ByVal paras As SqlParameter () As DataTable Dim sqlAdpter As SqlDataAdapter 'is the bridge between DataSet and SQL Server, used to retrieve and save the data Dim dt As New DataTable Dim ds As New DataSet or assign cmd to cmd. commandText = plain text 'sets the query statement cmd. commandType = cmdType 'specifies the type of the Cmd object. connection = conn' database Connection statement cmd. parameters. addRange (paras) 'incoming parameter sqlAdpt Er = New SqlDataAdapter (cmd) 'instantiate the adapter Try sqlAdpter. fill (ds) 'uses the adapter to Fill the dataSet with dt = ds. tables (0) 'able able is the first table cmd in dataSet. parameters. clear () 'clear the Catch ex As Exception MsgBox ("query failed", CType (vbOKOnly + MsgBoxStyle. exclamation, MsgBoxStyle), "warning! ") Finally Call CloseCmd (cmd) 'Finally, be sure to release cmd End Try Return dt 'to Return the Retrieved Data End function''Query operation, (No parameter )''You need to execute the statement, which is generally an SQL statement and also a stored procedure.'Determining the type of an SQL statement is generally not a stored procedure'
 
  
DataTable, The queried table
 '
 Public Function ExecSelectNo (ByVal plain text As String, ByVal primitive type As CommandType) As DataTable Dim sqlAdapter As SqlDataAdapter Dim ds As New DataSet 'assign cmd to cmd. commandText = plain text cmd. commandType = commantype cmd. connection = conn sqlAdapter = New SqlDataAdapter (cmd) 'instantiate the adapter Try sqlAdapter. fill (ds) 'uses the adapter to Fill the dataSet with Return ds. tables (0) 'Return the first table Catch ex As Exception Return Nothing Finally Call CloseCmd (cmd) 'disable cmd End Try End function''Close connection''Connection to be closed'
 Public Sub CloseConn (ByVal conn As SqlConnection) If (conn. state <> ConnectionState. closed) then' if the conn is not disabled. close () 'close connection conn = Nothing 'does not point to the original object End If End sub''Close command''Command to be closed'
 Public Sub CloseCmd (ByVal cmd As SqlCommand) If Not IsNothing (cmd) then' If the cmd command still contains cmd. dispose () 'release resource cmd = noth' does not point to the original object End If End SubEnd Class