Vb. NET Edition computer room charge system---SqlHelper

SqlHelper, the earliest contact with this word, as if it was the summer of 13, that summer than in previous years to a little later, hehe, SqlHelper, translated into Chinese is the database assistant, helper. Baidu Encyclopedia this to her to elaborate:

SqlHelper is a. NET framework-based database operations component. A database operation method is included in the component. SqlHelper is used to simplify the way we repeatedly write those database connections (SqlConnection), Sqlcommand,sqldatareader, and so on. SqlHelper package After the general is only need to give the method to pass some parameters such as database connection string, SQL parameters, you can access the database, very convenient!

But, well, why should we use Sqlhlper? I think the explanation of Baidu Encyclopedia is very clear, very clear, Sqlhlper is in the D layer of code abstraction out, that d layer of what kind of code ability abstract out? It turns out that the same code is abstracted in the same way that the database is being hacked and checked, stored procedures and assemblies.

The methods implemented in the SqlHelper class include:
ExecuteNonQuery. This method is used to run commands (with or without a number of parameters) that do not return no matter what the line or value is. These commands are often used to run the database (add \ Delete \ Change) updates, but can also be used to return the output parameters of the stored procedure.
ExecuteReader. This method is used to return a SqlDataReader object that includes the result set returned by a command.
ExecuteDataset. This method returns a DataSet object that includes the result set returned by a command.

The following is a detailed implementation of SqlHelper:

Imports System.Data.SqlClientImports system.configurationpublic Class SqlHelper ' defines the connection string Dim strconnection as String    = System.Configuration.ConfigurationSettings.AppSettings ("strconnection") ' Define connection Dim conn as SqlConnection ' definition command  Dim cmd as SqlCommand ' Initialize Connection object public Sub New () conn = New SqlConnection (strconnection) End Sub '/// <summary> '///depiction:< for non-query operations > '///</summary> '//<param name= ' <strText> ' ;< Adding or deleting statements or stored procedures ></param> '///<param name= <cmdType> >< Command type text or stored procedure ></param> '// /<param name= "<sqlParameter>" >< array of Parameters ></param> '///<returns> '///< return Boolean > '// /</returns> public Function ExecuteNonQuery (ByVal strText as String, ByVal Cmdtype as CommandType, ByVal Sqlpara Meter as SqlParameter ()) as Boolean Dim cmd as New SqlCommand ' defines command cmd. CommandText = StrText ' SQL statement or stored procedure name cmd. CommandType = When the Cmdtype ' command type is StoredProcedure, the stored procedure is called, typically CommandText cmd. Connection = conn ' Connect to database cmd. Parameters.addrange (SqlParameter) ' passes Dim flag as Boolean = False ' To define the return value of Try Conn. Open () ' opens database connection flag = cmd. ExecuteNonQuery cmd. Parameters.clear () Catch ex as Exception flag = False Finally call CloseConnection (con n) ' Close database connection call Closecmd (cmd) end Try Return flag End Function '///<summary> '/ depiction:< get a query result with a parameter reader > '///</summary> '//<param name= ' <strText> ' >< adding or deleting statements or stored procedures and Gt;</param> '///<param name= <cmdType> >< Command type text or stored procedure ></param> '///<param name= ' &    Lt;sqlparameter> ">< parameter array ></param> '///<returns> '///< return Boolean > '///</returns> Public Function ExecuteReader (ByVal strText as String, ByVal Cmdtype as CommandType, ByVal SqlParameter as SQlparameter ()) as a Boolean Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd. CommandText = StrText ' SQL statement or stored procedure name cmd. CommandType = Cmdtype ' command type is storedprocdeure, the stored procedure is called, typically CommandText cmd. Connection = conn cmd. Parameters.addrange (SqlParameter) ' passes Dim flag as Boolean = False ' To define the return value of Try Conn. Open () reader = cmd. ExecuteReader flag = reader. Read () cmd. Parameters.clear () Catch ex as Exception flag = False Finally call CloseConnection (con N) call Closecmd (cmd) End Try Return flag End Function '///<summary> '//Depicti on:< gets a query with a parameter DataTable result set > '///</summary> '//<param name= ' <strText> ' >< adding or deleting statements or stored procedures ;</param> '///<param name= <cmdType> >< Command type text or stored procedure ></param> '///<param name= ' &lt ;sqlparameter> ">< Array of parameters ></param>"<returns> '///< return datatable> '//</returns> public Function executereadertable (ByVal strte XT as String, ByVal Cmdtype as CommandType, ByVal SqlParameter as SqlParameter ()) as DataTable Dim cmd as New Sqlco Mmand ' Definition command Dim dataAdapter As New SqlDataAdapter ' defines an adapter object Dim dst As New DataSet Dim dt As New Da tatable cmd. CommandText = StrText ' SQL statement or stored procedure name cmd. CommandType = Cmdtype ' command type is storedprocdeure, the stored procedure is called, typically CommandText cmd. Connection = conn ' Connect to database cmd. Parameters.addrange (SqlParameter) ' Pass the Try Conn. Open () Dataadapter.selectcommand = cmd DataAdapter.Fill (DST) dt = DST.        Tables (0) Catch ex as Exception call CloseConnection (conn) call Closecmd (cmd) End Try  Return DT End Function '///<summary> '///depiction:< Get on-machine number > '///</summary> '// <param name= "<strText>" &Gt;< Adding or deleting statements or stored procedures ></param> '///<param name= <cmdType> >< Command type text or stored procedure ></param> '/ <param name= "<sqlParameter>" >< array of Parameters ></param> '///<returns> '///< return int > '// /</returns> public Function executescalar (ByVal cmdtext as String, ByVal Cmdtype as CommandType, ByVal Sqlparame ter as SqlParameter ()) As Integer Dim cmd As New SqlCommand Dim count As Integer cmd. CommandText = Cmdtext cmd. CommandType = Cmdtype cmd. Connection = conn cmd. Parameters.addrange (SqlParameter) Try Conn. Open () Count = cmd. ExecuteScalar Catch ex as Exception Throw New Exception (ex.        Message.tostring ()) Finally call Closecounection (conn) call Closecmd (cmd) End Try Return count End Function '///<summary> '//depiction:< amount > '///</summary> '//&L T;param name= "<strtext> ">< Delete or change statements or stored procedures ></param> '//<param name= <cmdType>" >< command type text or stored procedures ></ Param> '///<param name= <sqlParameter> >< array of parameters ></param> '///<returns> '//< Returns integer> '///</returns> public Function Executescalarcash (ByVal cmdtext as String, ByVal Cmdtype as Comm        Andtype, ByVal SqlParameter as SqlParameter ()) As decimal Dim cmd As New SqlCommand Dim cash As Decimal Cmd.commandtext = Cmdtext Cmd.commandtype = cmdtype cmd. Connection = conn cmd. Parameters.addrange (SqlParameter) Dim I as String Try Conn. Open () i = cmd.            Executescalar.tostring () If i = "" Then Cash = 0.0 Else cash = i End If Catch ex as Exception Throw New Exception (ex.        Message.tostring ()) Finally call CloseConnection (conn) call Closecmd (CMD)End Try Return Cash end Function public Sub CloseConnection (ByVal conn as SqlConnection) If not Isnoth ING (conn. State <> connectionstate.closed) then Conn. Close () ' Closed Connection conn = Nothing End If End Sub Private Sub closecmd (cmd as SqlCommand) If not IsNothing (cmd) Then ' infers whether it is an empty cmd. Dispose () cmd = Nothing End If End SubEnd Class

SqlHelperencapsulated into a class provides the flexibility for developers to choose how to access a database, and the overloads of each method support different method parameters, so developers can determine how to pass connections, transactions, and parameter information. Like packing, the idea of encapsulation is perfectly interpreted, pulling out the same content and making the code reusable!