The SqlHelper of the D-layer thin body

Today I also chatter about the three-tier architecture of the SqlHelper , in the early days of the computer room charges did not use this class, and then in n Multiple connections to the database when I do the following (to query the login user as an example):

Imports system.dataimports System.Data.SqlClientImports entity.entitypublic Class d_userdao ' query login user presence public funct Ion Selectuser (ByVal User as E_userinfo) as E_userinfo Dim conn As New SqlConnection ' Create Connection object Dim cmd A s new SqlCommand ' Create command Object conn = New SqlConnection (connstr.connectstring ()) ' Create database connection cmd. Connection = conn ' Query database cmd. CommandText = "Select * from UserInfo Where [email protected] and [email protected]" ' Database connection string CMD.P Arameters. ADD (New SqlParameter ("@UserName", User.username)) ' database connection parameter cmd. Parameters.Add (New SqlParameter ("@Password", User.password)) cmd. CommandType = CommandType.Text ' Gets the concrete type of the SQL statement, here is the Select Conn. Open () Dim reader as SqlDataReader = cmd. ExecuteReader ' executes the query statement and generates a DataReader Dim Nuser as New E_userinfo ' instantiation of the new UserInfo for saving the returned     Entity ' gets the queried data and returns it to the corresponding property   While reader.            Read () ' Determines if the user information is empty if Nuser is nothing and then Nuser = New E_userinfo End If Nuser. UserID = reader. GetInt32 (0) Nuser. UserName = reader. GetString (1) nuser. Password = reader. GetString (2) Nuser. Level = reader. GetString (3) End while return Nuser ' returns the resulting entity Conn. Close () ' Shut down database connection End functionend Class

I believe that the lazy people must exist in my shadow, so if the process of 45678 connected data, I will follow the example above, that is, easy and save the brain (because the example of those cmd, I'm trying to understand the special.Nan hour ofADO, so they are reluctant to abandon them). Suddenly one day when I knocked on my hand with the code, I got a cramp. Create a connection, close the connection ... , it's really going to freak out! Destroy nothing, well, since Microsoft is so smart, it won't play tricks like me. The result is conceivable, the Internet open a search, "wow", I was playing with myself, Microsoft sentHelperto save me.SQL, row by line about reducing the database connectionSqlHelpersee the dazzling. Let's just take a look at the above example.SqlHelperafter the result:

Imports system.dataimports System.Data.SqlClientImports entity.entitypublic Class d_userdao    ' query login user presence    Public Function Selectuser (ByVal User as E_userinfo) as DataTable        Dim mysqlhelper as New SqlHelper                ' Create database Connection object 
   
    dim Cmdtext As String = "select * from UserInfo Where [email protected] and [email protected]"  ' Create connection string        Dim paras As SqlParameter () = {new SqlParameter ("@UserID", User.userid), New SqlParameter ("@Password", User.password)}  ' Add parameter        Dim DT as New DataTable                         ' defines a DataTable to receive the results of a database query        dt = Mysqlhelper.execselect (Cmdtext, CommandType.Text , paras)       ' Execute database query operation return        DT       ' Returns query result    end FunctionEnd Class
   

Is that a little bit of a comparison? SqlHelperThe program logic is clear and easy to understand, the most important thing is not to knock so much code ... Now, do you see anything fishy? Let me just say, it's mostly repeating the database connection .SqlConnection,SqlCommand,SqlDataReaderand so on simplified, that is, through the encapsulation mechanism to extract the duplicated code, those who need to connect to the database, just tell it to connect which database table, parameters areOKout, SoCongratulations, you hired a stupid machine to serve you.

and then the point is, SqlHelper what is it, eh, this or read the encyclopedia it Http://baike.baidu.com/view/2765538.htm?fr=aladdin, the introduction of concise and clear (see not understand the area and the good, practice good elephant is very important), if add oil plus dot vinegar is too cumbersome, after all, in this blog how to use, after the results of the advantages are told you, and then write their own SqlHelper to submit or you can download a. After all, even I will write to use you, not to mention, I believe you will be in the use of a deep understanding. Need to remind one point, I write SqlHelper class is not because want to through the hands to understand deeply, but used not to know that Microsoft has written such a class, so it is recommended that you exercise your finger, a lot of benefits

SqlHelper class

Imports system.dataimports System.Data.SqlClientImports system.configuration ' need to add appropriate references in Manager ' by the type of return value will change the database additions and deletions Divided into two categories: "Delete and change" Simply return the query is successful, and "check" needs to return the information queried. ' Each of these two classes can also be divided into parameters, so there are four processes in the SqlHelper class, representing four classes of operations on the database public class SqlHelper ' Get the database connection string in the configuration file private ReadOnly Strconne ction as String = Configurationmanager.appsettings ("ConnStr") Dim conn as SqlConnection = New SqlConnection (strconnecti  On) ' Define the connection and initialize the SqlHelper class Dim cmd As New SqlCommand ' definition cmd command Dim adp  As New SqlDataAdapter ' defines a database adapter Dim ds As New DataSet ' defines a dataset Dim dt As New DataTable ' defines a data table ' Close database connection private Sub closeconn () ' To determine if the database is closed, close the database without shutting down Connect If Conn. State <> connectionstate.closed then Conn. Close () conn = Nothing ' does not point to the original object end If End Sub ' close database command Private Sub C Losecmd () ' Determines if the cmd command exists, if anyThe If not isnothing (cmd) and then cmd are destroyed. Dispose () cmd = Nothing End If End Sub ' first class, parameter additions and deletions, return Boolean type, true for success, false for failed public funct        Ion Execadddelupdate (ByVal cmdtext as String, ByVal Cmdtype as CommandType, ByVal Sqlparas as SqlParameter ()) as Boolean ' Populates the SqlHelper class's Cmd object cmd with the passed-in parameters. Parameters.addrange (Sqlparas) ' passed in parameter cmd. CommandText = Cmdtext ' Sets the query's statement cmd. CommandType = Cmdtype ' Sets a value that interprets Cmdtext cmd. Connection = Conn ' defines the connection Try Conn. Open () ' Opens the database connection to Return cmd. ExecuteNonQuery ' perform additions and deletions and change operation CMD.            Parameters.clear () ' Clears incoming parameters Catch ex as Exception Return False Finally    Call Closeconn () ' Close connection call Closecmd () ' Close command End Try End Function ' second type, no parameter additions and deletions, return BooleAn type, true for success, false for failed public Function Execadddelupdate (ByVal cmdtext as String, ByVal Cmdtype as CommandType) as Boolea N ' populates the SqlHelper class's Cmd object cmd with the passed-in parameters. CommandText = Cmdtext ' Sets the query statement cmd. CommandType = Cmdtype ' Sets a value that interprets Cmdtext cmd. Connection = Conn ' defines the connection Try Conn. Open () ' Opens the database connection to Return cmd. ExecuteNonQuery ' perform additions and deletions and change operation CMD.            Parameters.clear () ' Clears incoming parameters Catch ex as Exception Return False Finally    Call Closeconn () ' Close connection call Closecmd () ' Close command End Try End Function ' third, query operation with parameters, return DataTable public Function Execselect (ByVal cmdtext as String, ByVal Cmdtype as Command Type, ByVal Sqlparas as SqlParameter ()) as DataTable ' populates the cmd object cmd of the SqlHelper class with the passed in parameters.     Parameters.addrange (Sqlparas) ' Incoming parameters   Cmd. CommandText = Cmdtext ' Sets the query's statement cmd. CommandType = Cmdtype ' Sets a value that interprets Cmdtext cmd.            Connection = conn ' definition of connection ADP = New SqlDataAdapter (cmd) ' instantiation DAP Try Adp. Fill (DS) ' fills the DataSet with adapter dt = ds. Tables (0) ' DataTable is the first table ' cmd of a dataset. Parameters.clear () ' purge parameter Catch ex as Exception MsgBox ("Query Failed", CType (vbOKOnly + msgboxst Yle. Exclamation, MsgBoxStyle), "warning") Finally call Closeconn () ' Close database connection Cal L closecmd () ' Close command End Try return DT ' return query to table E nd function ' fourth, parameter-free query operation, return DataTable public Function Execselect (ByVal cmdtext as String, ByVal Cmdtype as Commandty PE) As DataTable fills the SqlHelper class's Cmd object cmd with the passed in parameters.              CommandText = Cmdtext     ' Set query statement cmd.commandtype = Cmdtype ' To set a value that interprets Cmdtext cmd.            Connection = conn ' definition of connection ADP = New SqlDataAdapter (cmd) ' instantiation DAP Try Adp. Fill (DS) ' fills the DataSet with adapter dt = ds. Tables (0) ' DataTable is the first table of a DataSet Catch ex as Exception MsgBox ("Query Failed", CType (Vboko nly + msgboxstyle.exclamation, MsgBoxStyle), "warning") Finally call Closeconn () ' Close data                                   Library connection Call Closecmd () ' Close command End Try Return DT ' Returns the query to the table End functionend Class

Summary:

The essence of the SqlHelper class is that the idea, that is, by encapsulating the same content, and then through the use of overloading to achieve code reuse, from the overall application of the three-layer architecture "high cohesion, low coupling" thought embodiment. So no matter study or life, do not take the physical strength for the cost of brain power, or the result can only be grooms and drab assortment mediocrities for nothing.

Ps:sqlhelper class belongs to the database operation class, but also feel its content is very concise personally think put d layer most suitable.