Data Center reconstruction-SqlHelper application and data center reconstruction-sqlhelper

Source: Internet
Author: User

Data Center reconstruction-SqlHelper application and data center reconstruction-sqlhelper

Since the implementation of each function in the data center reconstruction process involves database link access, the Code repetition rate is very high, which consumes a lot of time and makes the system redundant code, which is a very annoying task. Then, the repeated code is abstracted into a class and can be directly called to meet the requirements.

SqlHelper is a database operation component based on · NET Framework, which contains database operation methods. SqlHelper is used to simplify 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.

This is the first time to use the latest knowledge. The summary is relatively simple. Thank you for your patience.

SqlHelper has two methods: Query and add, delete, and modify databases. Each method has two categories: parameter and no parameter:

The following code uses SqlHelper during data center reconstruction:


Imports System. dataImports System. data. sqlClientImports System. configuration 'Add a reference to Public Class sqlhelper' to define the variable 'to obtain the Database link String Private ReadOnly strConnection As String = ("server = 192.168.24.68; Database = Data Center reconstruction. NET; User = sa; Password = 123456; ") 'sets the link Dim conn As SqlConnection = New SqlConnection (strConnection) 'define Command Dim cmd As New sqlcommand' <summary> 'perform add, delete, and modify operations. (with parameters) the return value is of the Boolean type. Check whether the operation is successful.' </summ Ary> '<param name = "plain text"> you need to run the statement, which 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"> 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. command Type = plain Type '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 add, delete, modify, and Catch ex As Exception throw'. If an error occurs, Finally cmd is thrown. parameters. clear () 'clear the Call CloseConn (conn) Call CloseCmd (cmd) End Try End function' <summary> 'to add, delete, and modify three 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 = "Explain type"> judge the type of an SQL statement, which is generally not a stored procedure </param> '<returns> Interger, affected rows </returns> '<remarks> February 2, 2013 8:19:59 </remarks> Public Function ExecAddDelUpdateNo (ByVal delayed text As String, ByVal delayed 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 Exc Eption Return 0 Finally Call CloseConn (conn) Call CloseCmd (cmd) End Try End Function '<summary>' queries (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 struct 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) 'input parameter sqlAdpter = New SqlDataAdapter (cmd) 'instantiate the adapter Try sqlAdpter. fill (ds) 'Use the adapter to Fill the dataSet with d T = 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 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.