SqlHelper of reconstruction of personal computer room

It was uh before touching the sqlhelper. Machine Room Reconstruction Master said first with three layers of thought to do, and so experience deep in the design mode and other. The day before yesterday moved the computer to let the teacher looked, guided the guidance, and then said can add something. I said add this plus that? Master said that want to add what you want to use to go back to learn more concrete will experience profound.

In fact, when writing a pure three layer, the code in the D layer has experienced a lot of repetition. Connect database query Database write SQL statements always use a few words.

The operation of the database in the computer room charge nothing but these four cases:

1. Adding or deleting without parameters, returning the result as a value

2. With the parameter of adding and deleting operations, return the result is a value

3. Query operation without parameters, with table as return result

4. Query operation with parameters, with table as return result

SqlHelper can encapsulate these methods and then simply invoke them when they are used. A parameter argument is passed.

Imports system.dataimports System.Data.SqlClientImports system.configurationpublic Class SqlHelper Private Shared Conn Ectionstring as String = Configurationmanager.appsettings ("strconn") "<summary>" to execute the "'" ' &lt ;/summary> "<param name=" cmdtxt "> Parameters cmdtxt for the SQL statement to be executed </param>" <param name= "Cmdtype" >sqlco Mmand execution Type </param> ' ' <returns> reshape return value res, execution succeeds Res!=0, unsuccessful then res=0</returns> ' <remarks><        /remarks> public Shared Function executenoquery (ByVal cmdtxt as String, ByVal Cmdtype as CommandType) as Integer Dim conn As SqlConnection = New SqlConnection (ConnectionString) ' Define connection variable conn, connect database Dim cmd as SqlCommand ' Define EXECUTE command        Variable Dim res as Integer ' definition return value variable res cmd = New SqlCommand (cmdtxt, conn) ' Instantiate SqlCommand command on Conn, execute statement as Cmdtxt Cmd.commandtype = Cmdtype ' command execution Type Try ' If the database connection status is off, open the If Conn.               state = Connectionstate.closed Then Conn. Open () End If ' executes the Cmdtxt statement and returns the execution result to the RES (total number of records successfully returned by execution) res = cmd. ExecuteNonQuery () Catch ex as Exception ' error handler, error prompts MsgBox (ex. Message, "database operation") Finally ' If the connection status is open, close it and release the memory if Conn. State = ConnectionState.Open Then Conn. Close () End If End Try return res ' res as result return End Function ' <summary> ' ' execute with parameter Adding and removing operations "</summary>" "<param name=" cmdtxt "> Parameter cmdtxt for the SQL statement to be executed </param> ' <param name= ' C Mdtype ">sqlcommand Execution type </param> ' <param name=" paras ">sql statement reference parameter </param> ' <returns&gt ;</returns> ' <remarks></remarks> public Shared Function executenoquery (ByVal cmdtxt as String, ByVal Cmdtype as CommandType, ByVal paras as SqlParameter ()) as Integer Dim conn as SqlConnection = New sqlconnecti On (ConnectionString) ' Define connection variable conn, connect database Dim cmd as SqlCommand ' definition of EXECUTE command variable Dim res as Integer ' definition return value variable res cmd = New SqlCommand (cmdtxt, conn) ' Instantiate Conn ' SqlCommand Command, execute the statement for cmdtxt cmd.commandtype = Cmdtype ' command to execute type cmd. Parameters.addrange (paras) ' Execute Cmdtxt statement with a parameter Try ' if the database connection status is off, open the If Conn. State = Connectionstate.closed Then Conn. Open () End If ' executes the Cmdtxt statement and returns the execution result to res (the total number of records executed successfully returned) res = cmd. ExecuteNonQuery () Catch ex as Exception ' error handler, error prompts MsgBox (ex. Message, "database operation") Finally ' If the connection status is open, close it and release the memory if Conn. State = ConnectionState.Open Then Conn. Close () End If End Try return res ' res as return value returns End Function ' <summary> ' ' execute without parameter query operation ' ' </summary> ' ' <param name= ' cmdtxt ' >cmdtxt as query SQL statement </param> ' <param name= ' C Mdtype "> Query mode </param>" <returns> query returns </retur as a tableNs> ' <remarks></remarks> public Shared Function getdatatable (ByVal cmdtxt as String, ByVal Cmdtype As CommandType) as DataTable Dim conn As SqlConnection = New SqlConnection (ConnectionString) ' Connection database Dim cm        D as New SqlCommand ' instantiation command Query variable Dim adataset As DataSet ' Define data cache variable Dim adaptor As SqlDataAdapter ' define data adapter variable cmd = new SqlCommand (cmdtxt, conn) ' Instantiate the command variable CMD above conn, execute the statement cmdtxt adaptor = new SqlDataAdapter (cmd) ' Bind the result to the data Adapter variable adaptor above Adataset = New DataSet ' instantiates data cache variable Adataset cmd. CommandType = Cmdtype ' Select command execution Type Try ' If the database connection status is off, open the If Conn. State = Connectionstate.closed Then Conn. Open () End If ' populates the adaptor object with the data adaptor of the query. Fill (Adataset) Catch ex as Exception ' Error prompts MsgBox (ex. Message, "database operation") Finally ' If the connection status is open, close it and release the memory if Conn.     state = ConnectionState.Open Then           Conn. Close () End If End Try ' Returns the result as a table return Adataset. Tables (0) End Function ' <summary> ' Execute query operation with parameters ' ' </summary> ' ' <param name= ' cmdtxt ' & gt; parameter cmdtxt to the SQL statement you want to execute </param> "<param name=" Cmdtype "> Query method </param> ' <param name= ' para S "> Command parameters at Query paras</param> '" <returns> return to table after query </returns> ' <remarks></remarks > Public Shared Function getdatatable (ByVal cmdtxt as String, ByVal Cmdtype as CommandType, ByVal paras as Sqlparame ter ()) as DataTable Dim conn As SqlConnection = New SqlConnection (ConnectionString) ' Connection database Dim cmd As Sqlco Mmand ' Define command variable cmd Dim adaptor As SqlDataAdapter ' Define data adapter variable Dim adataset As DataSet ' define data cache variable cmd = N EW SqlCommand (cmdtxt, conn) ' Executes the instantiated command variable above conn and executes the type cmd executed by the statement cmdtype cmd.commandtype = Cmdtype ' command. Parameters.addrange (paras) ' command execution parameter adaptor = NewSqlDataAdapter (CMD) ' Binds the result to the data adapter variable adaptor above Adataset = New DataSet Try ' If the data connection state is closed then open If Conn. State = Connectionstate.closed Then Conn. Open () End If adaptor. Fill (adataset) ' Populates the adaptor object with the data of the query Catch ex as Exception ' Error prompt MsgBox (ex. Message, "database operation") Finally ' If the connection status is turned off, release the memory if Conn. State = ConnectionState.Open Then Conn. Close () End If End Try ' Returns the result as a table return Adataset. Tables (0) End functionend Class

SqlHelper This helper does help us solve a lot of problems, reduce the code duplication of writing. Finally deep experience of the teacher's good intentions. There are a lot of things in the computer room that we should learn to use to practice our previous theoretical knowledge.