The understanding of SqlHelper

Brief introductionSqlhelper is a class used to avoid repeated input to the database code, which is encapsulated only by passing parameters such as database connection strings,SQL parameters, and so on to the methods in the class to access the database. Because we want to declare that the class cannot be inherited or instantiated, we want to encapsulate the data access functionality through a static method. Static methods are class-owned and can be used by objects or by classes. However, it is generally advocated to use the class name, as long as the static method defines the class, without having to establish an instance of the class to be used.

Mechanism

        above said sqlhelper Introduction, the next introduction of its specific operating mechanism. First, as with normal connection databases, define connection object, give it the corresponding connection string, connection string when developing a project, we typically use reflection to configure it, and then use the command object to perform operations on the database. In fact sqlhelper command The object of several methods, as required, We generally use two, one is to perform additions and deletions of the executenonquery ()

executenonquery The return value of () is type int , returning the number of rows affected

ExecuteReader () Returns a result set DataTable, which is a network virtual table.

depending on your project needs, you will sometimes use ExecuteScalar , ExecuteXmlReader and other methods.

It will also use the command Some of the parameter objects and their methods:

SqlParameter: Is A Parameter object of Sqlcommand

AddRange: Add multiple SqlParameter.

CommandText: Gets or sets the Transact-SQL statement or stored procedure to be executed against the data source

CommandType: Gets or sets a value that indicates how the CommandText property is interpreted

We usually divide these methods into two cases, one with the other and the other without reference.

Code

Imports System.Data.SqlClientImports system.configurationpublic Class sqlHelper ' parameter increment, delete, change operation public Function Execute Noquery (Cmdtext As String, Cmdtype as CommandType, paras as SqlParameter ()) as Integer Dim connstr As String = Syst Em.        Configuration.ConfigurationManager.AppSettings ("ConnStr") Dim conn as SqlConnection = New SqlConnection (connstr) ' Define a Command object Dim cmd as new SqlCommand cmd = new SqlCommand (CMDTEXT, conn) ' or with cmd =conn.createcommand ( ) ' cmd. Parameters.addrange (paras) cmd. Parameters.addrange (paras) ' Add parameter Dim res as Integer Try ' to open the database If Conn. State = Connectionstate.closed Then Conn. Open () End If res = cmd. ExecuteNonQuery () Catch ex as Exception MsgBox (ex. Message, "Database open error") Finally ' close database If Conn. State = ConnectionState.Open Then Conn.        Close () End If End TryReturn res End Function ' no parameter additions and deletions operation public Shared Function Executenoquery (cmdtext as String, Cmdtype as Commandtyp E) as Integer Dim connstr As String = System.Configuration.ConfigurationManager.AppSettings ("ConnStr") Dim c Onn as SqlConnection = new SqlConnection (connstr) Dim cmd as New SqlCommand cmd.            CommandType = cmdtype cmd = New SqlCommand (cmdtext, conn) Dim res as Integer ' do not add parameter Try If Conn. State = Connectionstate.closed Then Conn. Open () End If res = cmd. ExecuteNonQuery () Catch ex as Exception MsgBox (ex. Message, "database operation") Finally ' closes the database If Conn. State = ConnectionState.Open Then Conn. Close () End If End Try ' Returns the number of rows affected return res End Function ' parameterized query operation public Functio N getdatatable (ByVal cmdtxt as String, ByVal Cmdtype as CommandType, ByVal paras as SqlParameter ()) as DataTable        Dim strconnstr as String = System.Configuration.ConfigurationManager.AppSettings ("connstr") ' defines a database connection object Dim conn As SqlConnection = new SqlConnection (STRCONNSTR) ' defines a command object Dim cmd as New SqlCommand Di        M adataset as DataSet ' defines an adapter object Dim adaptor as SqlDataAdapter cmd = New SqlCommand (cmdtxt, conn) adaptor = new SqlDataAdapter (cmd) adataset = new DataSet cmd. Parameters.addrange (paras) Try If Conn. State = Connectionstate.closed Then Conn. Open () End If adaptor. Fill (adataset) ' Populate data set ' Return Adataset. Tables () Catch ex as Exception MsgBox (ex. Message, "Database open error") Finally ' Close database connection If Conn. State = ConnectionState.Open Then Conn. Close () End If end Try Return adataset. Tables (0) End function ' parameterless query public Function getdatatable (ByVal cmdtxt as String, BYval Cmdtype as CommandType) as DataTable Dim strconnstr as String = System.Configuration.ConfigurationManager.AppS        Ettings ("ConnStr") Dim conn As SqlConnection = new SqlConnection (strconnstr) Dim cmd As New SqlCommand Dim adataset As DataSet Dim adaptor As SqlDataAdapter cmd = New SqlCommand (cmdtxt, conn) cmd. CommandType = Cmdtype adaptor = new SqlDataAdapter (cmd) adataset = new DataSet Try If Conn . State = Connectionstate.closed Then Conn. Open () End If adaptor. Fill (Adataset) Catch ex as Exception MsgBox (ex. Message, "Database Open") Finally If Conn. State = ConnectionState.Open Then Conn. Close () End If end Try Return adataset. Tables (0) End functionend Class

Summarize

In object-oriented learning, we know that when we meet more than two requirements or use more than two methods, we need to consider encapsulating them. SqlHelper class is the embodiment of the idea of encapsulation, its proposed is to give us a train of thought, its code is based on the specific situation of specific analysis, not dogmatism.

The understanding of SqlHelper