Transformers (SqlHelper) and transformers sqlhelper

Transformers (SqlHelper) and transformers sqlhelper

</Pre> 1. Basic Introduction <p> </p> <span style = "color: rgb (51,51, 51); font-family: arial,, sans-serif; line-height: 24px; text-indent: 28px "> <span style =" font-size: 18px "> 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. </Span> </p> <span style = "color: rgb (51,51, 51); font-family: arial,, sans-serif; line-height: 24px; text-indent: 28px "> <span style =" font-size: 18px "> to put it simply, it is to encapsulate the common things in the "Four Kings", so that our code becomes simple and easy to expand. </Span> </p> <span style = "color: rgb (51,51, 51); font-family: arial,, sans-serif; line-height: 24px; text-indent: 28px "> <span style =" font-size: 18px "> <strong> 2. Implementation Method (Vb. </strong> </span> </p> <span style = "color: rgb (, 51); font-family: arial,, sans-serif; line-height: 24px; text-indent: 28px "> <span style =" font-size: 18px "> <strong> (1) </strong> preparation file (U layer) </span> </p> <span style = "color: rgb (, 51 ); font-family: arial,, sans-serif; line-height: 24px; text-indent: 28px "> <span style =" font-size: 18px "> under the created Application Form project, there is an App. open the Config file, and add the following code </span> </p> <span style = "color: rgb (51, 51); font-family: arial,, sans-serif; line-height: 24px; text-indent: 28px "> <span style =" font-size: 18px "> </span> </p> <pre name =" code "class =" vb "> <etettings> <clear/> <add key =" connStr "value =" server = localhost; database = Login; User = sa; Password = 123456; "/> </appSettings>

After adding, as shown in figure

<?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>

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' <summary> 'to add, delete, and modify three operations. (with parameters) the return value is of the Boolean type, confirm whether execution is successful '</summary>' <param name = "plain text"> you need to execute the statement, which is generally an SQL statement or a stored procedure </par Am> '<param name = "partition type"> determines the type of an SQL statement, which is generally not a stored procedure </param>' <param name = "paras"> parameter array, you cannot determine the number of parameters </param> '<returns> </returns>' <remarks> </remarks> Public Function ExecAddDelUpdate (ByVal plain text As String, ByVal primitive type As CommandType, byVal paras As SqlParameter () As Integer values are assigned to the cmd attributes. parameters. addRange (paras) 'transmits the parameter to cmd. commandType = commantype' sets a value to explain plain text cmd. connection = conn' Set connection, global variable 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 '<summary>' perform add, delete, modify, and delete operations. (No parameter) '</summary>' <param name = "plain text"> the statement must be executed, generally, it is an SQL statement and a stored procedure </param> '<param name = "primitive type"> determines the type of an SQL statement. Generally, it is not a stored procedure. </param>' <retu Rns> Interger, number of affected rows </returns> '<remarks> February 2, 2013 8:19:59 </remarks> Public Function ExecAddDelUpdateNo (ByVal plain text As String, ByVal primitive type As CommandType) as Integer 'assigns cmd to the command 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 Cl OseCmd (cmd) End Try End Function '<summary>' performs the query operation (with parameters ), the parameter is not limited to '</summary>' <param name = "plain text"> the statement to be executed is generally an SQL statement, there are also stored procedures </param> '<param name = "primitive type"> to determine the type of SQL statement, generally, it is not a stored procedure </param> '<param name = "paras"> input parameter </param>' <returns> </returns> '<remarks> </remarks> Public Function ExecSeletct (ByVal plain text As String, byVal parameter type As CommandType, ByVal paras As SqlParameter () As DataTable Dim SQL Adpter As SqlDataAdapter is the bridge between DataSet and SQL Server. It is 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) 'input parameter sqlAdpter = 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. paramet Ers. 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 queried data End function' <summary>' to perform the query operation (No parameter) '</summary>' <param name = "plain text"> the statement must be executed. Generally, it is an SQL statement, there are also stored procedures </param> '<param name = "partition type"> to determine the type of SQL statements, which are generally not stored procedures </param>' <returns> dataTable, the queried table </returns> '<remarks> </remarks> Public Function ExecSelectNo (ByVal plain text As String, ByVal primitive type As CommandType) as DataTable Dim sqlAdapter As SqlDataAdapter Dim ds As New DataSet 'assigns cmd a value. 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) 'returns the first table Catch ex As Exception Return Nothing Finally Call CloseCmd (cmd) of dataSet) 'Close cmd End Try End function' <summary> 'close connection' </summary> '<param name = "conn"> connection to be closed </param>' <remarks> </remarks> 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 endsub' <summary> 'close command' </summary> '<param name = "cmd"> command to be closed </param> '<remarks> </remarks> Public Sub CloseCmd (ByVal cmd As SqlCommand) if Not IsNothing (cmd) then', If the cmd command still has cmd. dispose () 'release resource cmd = noth' does not point to the original object End If End SubEnd Class

C #: SqlConnection does not have the name SqlHelper in the current context

Check whether SqlHelper CS is in your App_Code.

SqlHelper is a database operation class that can be downloaded online.

What is sqlhelper?

SqlHelper is A. NET Framework-based database operation component. The component contains the database operation method. Currently, SqlHelper has many versions, mainly the SqlHelper class released at the beginning of Microsoft, which is later included in the Enterprise Library open-source package. Another major version is dbhelper.org's open-source sqlhelper component, which features simplicity and high performance. It not only supports sqlserver, but also supports sqlserver, oracle, access, and Mysql databases. It is also an open-source project, free Download is provided. 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. The SqlHelper class is used to encapsulate data access through a set of static methods. This class cannot be inherited or instantiated, so it is declared as a non-inherited class that contains a dedicated constructor. Each method implemented in the SqlHelper class provides a set of consistent overloading. This provides a good way to use the SqlHelper class to execute commands. It also provides necessary flexibility for developers to select a way to access data. Each method overload supports different method parameters, so developers can determine how to pass connection, transaction, and parameter information.