The SqlHelper of database abstraction

Personal reconstruction version of the computer room charge system has gone through the beginning of this off, because the people have been in front of many, in the beginning of the machine room before they read some of their blogs and their communication know can be in the connection, operation of the database abstract a SqlHelper class, encapsulating the various operations of the database methods. But this learning process needs to be explored, to truly appreciate the benefits of abstraction to the programming and the shortcomings of the original way.

First realize the three-tier architecture of the landing form and view the student balance function, in the D-layer code there are duplicates, database links, database query, this is also a three-tier architecture design of a disadvantage bar, reduce the coupling of the system and increase the number of programming code. The next step is to really learn and implement SqlHelper.

What is SqlHelper?

SqlHelper is a. NET framework-based database operations component. The component contains database operation methods, currently SqlHelper has many versions, mainly in the first release of Microsoft SqlHelper class, which is included in the Enterprise Library open source package. There is also a major version of DBHelper. ORG Open source SqlHelper components, the advantage is simple, high performance, not only support SQL Server, while supporting SQL Server, Oracle, Access, MySQL database, is also an open source project, providing free download.

SqlHelper is used to simplify the way you repeatedly write those database connections (SqlConnection), Sqlcommand,sqldatareader, and so on. SqlHelper package is usually only needed to pass some parameters such as database connection string, SQL parameters, etc., you can access the database, very convenient.

The SqlHelper class is used to encapsulate data access functionality through a set of static methods. The class cannot be inherited or instantiated, so it is declared as a non-inheriting class that contains a dedicated constructor. Each of the methods implemented in the SqlHelper class provides a consistent set of overloads. This provides a good pattern for executing commands using the SqlHelper class, while providing the necessary flexibility for the developer to choose how to access the data. The overloads of each method support different method parameters, so the developer can determine how the connection, transaction, and parameter information is passed.

  premise: In order for SqlHelper to perform better, we need to use a configuration file to link the data.

<?xml version= "1.0" encoding= "Utf-8"? ><configuration><appsettings><add key= "Connstr" value= " Server=lxy;database=yangjfcharge;user id=sa;password=123456 "/><add key=" DB "value =" SQL Server "/></ Appsettings></configuration>

here are some common methods used in Sqlheper

ExecuteReader. This method is used to return a SqlDataReader object that contains the result set returned by a command.

ExecuteDataset. This method returns a DataSet object that contains the result set returned by a command.

ExecuteScalar. This method returns a value. The value is always the first column of the first row returned by the command.

ExecuteXmlReader. This method returns the XML segment of the FOR XML query.

In addition to these public methods, the SqlHelper class contains specialized functions for managing parameters and preparing the commands to execute. Regardless of the method implementation that the client invokes, all commands are executed through the SqlCommand object. Before the SqlCommand object can be executed, all parameters must be added to the Parameters collection, and the Connection, CommandType, CommandText, and Transaction properties must be set correctly. Specialized functions in the SqlHelper class are primarily used to provide a consistent way to issue commands to a SQL Server database, regardless of the overloaded method implementation that is called by the client application. The specialized utility functions in the SqlHelper class include:

Attachparameters: This function is used to connect all the necessary SqlParameter objects to the running SqlCommand.

Assignparametervalues: This function is used to assign a value to the SqlParameter object.

PrepareCommand: This function is used to initialize the properties of a command, such as a connection, a transactional environment, and so on.

ExecuteReader: This dedicated ExecuteReader implementation is used to open the SqlDataReader object with the appropriate CommandBehavior to most effectively manage the validity period of the connection associated with the reader.

But the main setting at this stage is some of the necessary link parameters in SqlHelper, that is, the Sqlcommand,parameters,commandtype,commandtext property link database.

The functions in the SqlHelper class are divided into the query and additions and deletions, and the two parts are divided into the parameter and the non-parameter in detail.

After the above theory should be curious about this mysterious class, let us uncover its veil!

Code:

Imports system.dataimports system.configurationimports System.Data.SqlClientPublic Class Sqlhelper ' Get database connection string Priva  Te ReadOnly strconn as String = Configurationmanager.appsettings ("connstr") ' New link Dim connsql as SqlConnection = new SqlConnection (strconn) ' Definition command Dim cmdsql as New SqlCommand ' <summary> ' ' perform additions and deletions, have a parameter, confirm success ' &L T;/summary> "<param name=" Cmdsqltext "> Database statement to execute </param>" <param name= "Cmdsqltype" > Data Statement Class Type, which may be SQL types, or stored procedures such as </param> "' <param name=" sqlparams "> Parameter array, unable to determine how many parameters </param > ' &LT;RETURNS&G t; Returns the number of rows affected </returns> ' <remarks></remarks> public Function executeadddelupdate (ByVal cmdsqltext A S String, ByVal Cmdsqltype as CommandType, ByVal Sqlparams as SqlParameter ()) as Integer CmdSql.Parameters.AddRange (Sqlparams) ' Pass arguments to Cmdsql.commandtype = Cmdsqltype ' To determine the statement type, whether SQL or Oracle or other type Cmdsql.commandtext = Cmdsqltext ' to pass SQL statements to      Cmdsql  Cmdsql.connection = Connsql ' Set connection, global variable ' start execution query Try connsql.open () ' Open link Return cmdsq        L.executenonquery () ' Execute Query cmdSql.Parameters.Clear () ' Clear parameter Catch ex as Exception Return 0        Finally call CloseConnection (connsql) ' calls the function to close the data connection called Closesqlcommand (cmdsql) ' Invoke command Undo function End Try End Function ' <summary> ' to perform additions and deletions, no parameters, confirmation of successful execution ' ' </summary> ' ' <param name= ' cmd Text "> Database statement to execute </param>" ' <param name= "cmdtype" > Data statement type, typically SQL statement, not stored procedure </param> ' <ret Urns> returns the amount of rows affected </returns> ' <remarks></remarks> public Function executeadddelupdate (ByVal cmdt        Ext as String, ByVal Cmdtype as CommandType) as Integer Cmdsql.commandtype = Cmdtype ' Determine statement type, SQL or Oracle or other type            Cmdsql.commandtext = Cmdtext ' Pass SQL statement to Cmdsql cmdsql.connection = Connsql ' Set connection, global variable ' execute query Try Connsql.open () ' HitOpen link Return cmdsql.executenonquery () ' Execute Query cmdSql.Parameters.Clear () ' Clear parameter Catch ex as Excepti        On Return 0-Finally call CloseConnection (connsql) call Closesqlcommand (Cmdsql)  End Try End Function ' <summary> ' performs a query operation, has a parameter, returns a DataTable type ' ' </summary > ' <param Name= "Cmdtext" > Database statements to execute, general SQL statements, and stored procedures </param> "<param name=" Cmdtype "> Data Statement types, typically SQL statements, not stored procedures </param> ' <param name= "sqlparams" > Parameter array, unsure of how many parameter arrays </param> ' <returns> return DataTable type & Lt;/returns> ' <remarks></remarks> public Function executeselect (ByVal cmdtext as String, Byva L Cmdtype as CommandType, ByVal Sqlparams as SqlParameter ()) as DataTable Dim Sqladapter as SqlDataAdapter D Im Dtsql as new DataTable Dim Dssql as new DataSet Cmdsql.commandtext = Cmdtext Cmdsql.commandtype = Cmdtype Cmdsql.connectIon = Connsql CmdSql.Parameters.AddRange (sqlparams) Sqladapter = New SqlDataAdapter (cmdsql) Try Sqladapter.fill (dssql) dtsql = dssql.tables (0) ' DataTable is the first table of a dataset cmdSql.Parameters.Clear        () Catch ex as Exception MsgBox ("Query Failed", CType (vbOKOnly + msgboxstyle.exclamation, MsgBoxStyle), "warning") Finally call Closesqlcommand (cmdsql) End Try Return dtsql End Function ' <sum Mary> ' Execute query operation, no parameter, return DataTable type ' ' </summary> ' ' <param name= ' cmdtext ' > database statement to be executed, general SQL statement, There are also stored procedures </param> ' <param name= ' cmdtype ' > Data Statement types, typically SQL statements, not stored procedures </param> ' <returns> return dat  atable epidemic </returns> ' <remarks></remarks> public Function executeselect (ByVal cmdtext as String,        ByVal Cmdtype as CommandType) as DataTable Dim sqladapter As SqlDataAdapter Dim dtsql as New DataTable Dim Dssql as New DataSet Cmdsql.commandtext = Cmdtext Cmdsql.commandtype = Cmdtype cmdsql.connection = Connsql Sqla            Dapter = New SqlDataAdapter (cmdsql) ' Instantiation adapter Try sqladapter.fill (dssql) ' Fill dssql with Sqladapater ' Dtsql = dssql.tables (0) Catch ex as Exception Closesqlcommand (cmdsql) End Try Return Dtsql End Function ' <summary> ' close database link ' </summary> ' ' <param name= ' connsql ' > Database        Links </param> ' <remarks></remarks> public Sub closeconnection (ByVal connsql as SqlConnection) if (connsql.state <> connectionstate.closed) Then ' if not close connsql.close () ' Close connection Connsql = N Othing ' Do not point to the original object end If end Sub ' ' <summary> ' command undo ' </summary> ' ' <param name= ' Cmdsql "> A Transact-SQL statement or stored procedure executed by the database. This class cannot be inherited. </param> ' <remarks></remarks> public Sub closesqlcommand (ByVal cmdsql As SqlCommand) if not IsNothing (cmdsql) Then ' if there is a command cmdsql.dispose () ' command destroys cmdsql = Nothing End If End SubEnd Class

SqlHelper prevents direct database operation, increases the security of the database, and reduces the code workload of the program.I personally think it is the same as the previous VB module function is the same, abstract out the public method, can be used for various functions call!

Although the introduction of SqlHelper on the internet due to its own classic features have been numerous, in the study and consolidation and later review, hereby published this blog, purely beginner's point of view, there is the wrong place is elegant!