SqlHelper and additions and deletions change

When one thing has been repeated many times, you will want to find a way to do this repetitive action instead of yourself. Writing programs are the same.

In the program, for the repeating part, if it is exactly the same, then we will think of it as a method (procedure, function), placed in a permission of the need to be able to get the ground. If the requirement is in the same project, then write the method into a class, and if the requirement is in the same class, create a separate method for writing it in this class. The abstraction of the same thing for multi-user invocation is the abstract idea.

Any system will involve the transmission and operation of data. And the operation of the data summed up is nothing more than delete and change (curd), now the system with the increase of users, more and more user operations, big data frequent operation. If the original way to write, complex system will cause a lot of redundancy of the code.

Add two references above the class:

Imports System.Data.SqlClient       ' Reference SQL database connection imports System.Configuration        ' reference configuration file

Create a SQLHelper class that operates the database,

Public Class SQLHelper ' gets the connection string in the configuration file private ReadOnly strsqlconnection As String = Configurationmanager.appsettings    ("Sqlconcectstr")    ' Define connection Dim connsql As SqlConnection = new SqlConnection (strsqlconnection) ' Define cmd command Dim cmdsql As New SqlCommand '///<summary> '///depiction:< The method is the initialization of the SqlHelper class > '///</summary> public Sub New ( ) Connsql = New SqlConnection (strsqlconnection) End Sub '///<summary> '///depiction:< the party        method is to close the database connection > '///<summary> Private Sub closesqlconnection () ' To determine if the database Connection object state is broken and if it has not yet broken If Connsql.state <> connectionstate.closed then Connsql.close () End If end Sub '///<        Summary> '///depiction:< This method is to close the database command > '///</summary> Private Sub Closesqlcommand () ' If the command is present, close if not IsNothing (Cmdsql) then Cmdsql.dispose () ' Destroy command Cmdsql = Nothing E ndIf End Sub '///<summary> '//perform additions and deletions to three operations, (with a parameter) The return value is a Boolean type, confirming whether the execution succeeds '///</summary> '///<par Am Name= "strSQL" > need to execute statements, typically SQL statements, there are stored procedures </param> '///<param name= ' cmdtype ' > Determine the type of SQL statement, generally not a stored procedure < /param> '///<returns> '///< Returns a Boolean, succeeds to true, otherwise false> '///</returns> public fun Ction executeadddelupdate (ByVal strSQL as String, ByVal Cmdtype as CommandType, ByVal Sqlparams as SqlParameter ()) as Bool            Ean ' populates this class with its own cmd object CmdSQL.Parameters.AddRange (sqlparams) ' parameter passed in cmdsql.commandtype = Cmdtype        ' cmdsql.connection = Connsql ' connection cmdsql.commandtext = strSQL ' query statement Try connsql.open () ' Open connection Return cmdsql.executenonquery () ' Execute action cmd Sql. Parameters.clear () ' Clear parameter Catch ex as Exception Return False Finally Call Clos       Esqlconnection ()   ' Close the connection call Closesqlcommand () ' End command ends Try end Function '///<summary> '// Perform additions and deletions to three operations, (no parameter) return value is a Boolean type, confirm whether the execution succeeds '///</summary> '///<param name= "strSQL" > Need to execute statement, usually SQL statement, also have stored procedure and Lt;/param> '///<returns> '///< returns a Boolean type, succeeds to true, otherwise false> '///</returns> Pub Lic Function executeadddelupdate (ByVal strSQL as String, ByVal Cmdtype as CommandType) as Boolean ' populates the class's own cmd with the arguments passed in Like Cmdsql.commandtype = Cmdtype ' will cmdsql.connection = Connsql ' Establish connection Cmdsql.commandte XT = strSQL ' Set query statement Try connsql.open () ' Open connection Return Cmdsql.execute Nonquery () ' Returns the number of rows affected after SQL execution Catch ex as Exception return False Finally call Closesqlco Nnection () ' Close the connection call Closesqlcommand () ' End command ends Try end Function '///<su Mmary> '///execute the query operation, (withThe return value for the DataTable type '///</summary> '///<param name= "strSQL" > Requires execution of statements, typically SQL statements, as well as stored procedures </param> ' <param name= "Cmdtype" > Determine the type of SQL statement, generally not stored procedure </param> '///<returns> '///< return table    ; '///</returns> public Function executeselect (ByVal strSQL as String, ByVal Cmdtype as CommandType, ByVal SQLP  Arams as SqlParameter ()) as DataTable Dim sqladapter As SqlDataAdapter Dim dtsql As New DataTable Dim The Dssql as New DataSet ' populates the class's own CMD object with the passed parameters CmdSQL.Parameters.AddRange (sqlparams) ' Incoming parameters cmdsql.com                Mandtype = Cmdtype Cmdsql.connection = Connsql ' Establish connection Cmdsql.commandtext = strSQL               ' query Statement sqladapter = New SqlDataAdapter (cmdsql) ' Instantiation of Adapter Try Sqladapter.fill (dssql) ' Populate the DataSet with Adater Dtsql = dssql.tables (0) ' DataTable is the first table of a dataset Cmdsql.param Eters.      Clear ()      ' Clear parameter Catch ex as Exception MsgBox ("Query Failed", CType (vbOKOnly + msgboxstyle.exclamation, MsgBoxStyle), "Warning") Finally call Closesqlcommand () end Try Return dtsql end Function '///<summ Ary> '//Execute query operation, (no parameter) return value for DataTable type ' </summary> '///<param name= ' strSQL ' > need to execute statement, typically SQL statement, also has storage        Process </param> '///<param name= ' cmdtype ' > Determine the type of SQL statement, generally not a stored procedure </param> '///<returns> ' < return table > '///</returns> public Function executeselect (ByVal strSQL as String, ByVal Cmdtype A S CommandType) as DataTable Dim sqladapter As SqlDataAdapter Dim dtsql As New DataTable Dim dssql As The New DataSet ' populates the class's own CMD object with the arguments passed in Cmdsql.commandtext = strSQL Cmdsql.commandtype = Cmdtype cmdsql . Connection = Connsql Sqladapter = New SqlDataAdapter (cmdsql) ' Instantiation adapter Try SQLADAPTER.F             Ill (DSSQL)         ' Populate the DataSet with Adaper Dtsql = dssql.tables (0) ' DataTable is the first table of the DataSet Catch ex            As Exception MsgBox ("Query Failed", CType (vbOKOnly + msgboxstyle.exclamation, MsgBoxStyle), "warning") Finally Call Closesqlcommand () end Try Return dtsql End functionend Class

SqlHelper and additions and deletions change