sqlhelper--just because I looked at you more in the crowd.

First, SqlHelper appearances

Not because everyone is using SqlHelper So only use, is because the connection database shut down database query database more also added SqlHelper. When many of your needs have a similar approach, we don't have to complete a need to write the code once, complete a requirement to write the code again, so that not only will be exhausted (of course, in addition to copy and paste), which will cause the duplication of code to increase the coupling, resulting in redundancy. Why not pull out the same code separately and encapsulate it as a class, so that each requirement uses just the appropriate method of invoking the encapsulated class.

        when there are multiple changes to the database and additions to the operation of the use of theSqlHelper, becauseDlayer is the direct operation of the database, to complete the database additions and deletions to check, soSqlHelperis part ofDlayer, but I was taken out alone to become a layer. PutDall operations on the database are encapsulated. Divided into two kinds of operations, one is to delete and change, one is the query operation, and each is divided into parameters and no parameters. This eases theDthe burden, so thatDThe layer does not appear too bloated.

In the computer room has been looking at other people's blog, always mentioned SqlHelper, so in the public blog more than look at it one eye. Just because in the crowd to look at you more than one eye, can never forget your face, write down here I am a person to your love has a unique ...

Ii. usage of SqlHelper

1. first, its method

( 1 Why is it divided into parameters and no parameters?

This is a thought, when you want to get all the information of the database and some of the information, you need all the information directly to it to specify which table to find the row, but when you want to get a portion of the information in a table will have the query criteria, there will be parameters.

( 2 Why should be divided into queries and additions and deletions to change two kinds?

Query is to return to the user want to query the information, additions and deletions can be summed up as a change or update, update to return to the user whether the update is successful or unsuccessful, do not need to return specific information. So the query returns the DataTable type, and the update returns the affected row, and returns the integer 1 when the row is updated . This has a relationship with its return value, since the same is going to be combined to become a method, which reduces redundancy and simplifies the code. Learning the object-oriented, in fact, is how simple how to come.

   

2. Then, SqlHelper Each method must have a return value, because the difference in the return value causes the corresponding method to call this method to have the same return value.

( 1 ) returns DataTable

DataTable What is it again?

DataTable is a temporary data-saving network virtual table that can be created and accessed when SqlHelper the query will return to DataTable in a virtual table, determine if the first row has data to determine whether the record is being traced.

( 2 ) returns Integer

          Returns the number of rows affected if the number of rows affected is greater than 0 Description of the records found, if 0 Description no data was queried. If the number of rows affected is greater than 0 description and additions to the operation succeeded, if 0 b u layer, so it is very good to achieve in another line. Clear thinking.

Three, sqlhelper example application

The following is a parameter method that returns a DataTable -type value.

    ' Method (2) query with Parameters    ' return DataTable query out table public    Function ExecuteNonQuery (ByVal cmdtext as String, ByVal Cmdtype as C Ommandtype, ByVal Sqlparams as SqlParameter ()) as DataTable        Using conn as New SqlConnection (strconnection)   ' With connection pooling, you can automatically turn off the connection after you finish using            Dim cmd as SqlCommand = conn. CreateCommand () '            cmd.commandtext = Cmdtext  ' needs to execute the SQL statement            Cmd.commandtype = Cmdtype ' gives the SQL statement type            cmd. Parameters.addrange (sqlparams) ' parameter array, the number of arguments depends on the actual situation            ADP = New SqlDataAdapter (cmd)            Try                Conn. Open ()                ADP. Fill (DS)                Return ds. Tables (0)            Catch ex as Exception                Return nothing                Throw ex            end Try        end Using    End Function

   

          It's used, take it d layer, when the user name is determined when a user is present, the user name becomes a parameter, calling sqlhelper executenonquery () method, by returning the Span lang= "en-US" style= "Font-family:calibri" >datatable Whether the number of rows is greater than 0 To determine if this user exists.

    Public Function Selectuser (ByVal user as Entity.userinfo) as DataTable Implements Idal. Iuser.selectuser        Dim strusername as String = user. UserName        Dim Helper As New Helper.sqlhelper        Dim dt As New DataTable            ' declares a DataTable type variable        Dim cmdtext as Str ing = "SELECT * from User_info where [email protected]"   ' declares and instantiates SQL statement to be executed        Dim Sqlparams as SqlParameter () = {New S Qlparameter ("@UserName", strUserName)} ' declares and instantiates the parameter        dt = helper. ExecuteNonQuery (Cmdtext, CommandType.Text, sqlparams)  ' calls the SqlHelper () method in the Execselect class to execute the query, and gets the return value returned        DT                                                             ' Returns query result    end Function

Iv. problems with my sqlhelper

then there is a problem, when we delete the user, or need to query to multiple records display, although the return is a DataTable type, but it defaults to return The first row of data in a DataTable, there are two ways to resolve this situation:

( 1 Loop through the mind to iterate through the records in the database, returning the first row of a different record.

For example, returning a user at a level

      If dt. Rows.Count > 0 Then for            i = 0 to dt. Rows.count-1  ' Find content shows                DataGridView.Rows.Add ()  ' starts with no rows and columns, so to add a row and a column, avoid an error for                j = 0 to dt. Columns.count-1                    DataGridView (J, i). Value = dt. Rows (i). Item (j)                next J                ' datagridview.allowusertoaddrows = False    ' If you put it here, there will be a last line            next i

( 2 ) leverage a bound dataset

Direct datagridview=table

http://blog.csdn.net/liutengteng130/article/details/8643863

The second method is obviously much better than the first, but why a lot of people still want to just return DataTable first row of data?

This is because: ... ^^ ... .. I'm just not going to loop it around, and then try the second one,

Five, my sentiment--sqlhelper     

Just like the design pattern, in fact, there is no design pattern at the beginning, big plus just write code AH write code, but need to write code is also more, there will be less programming people, it is necessary to improve the efficiency of writing code, so some design patterns appear, Object-oriented appearance is also a certain amount of time accumulation, people need to reuse code, and the first to write less code to achieve the requirements, but also the system software appears more concise, to achieve high cohesion low coupling effect. If the appearance of design pattern is the sublimation of cognition thought, that SqlHelper is the crystallization of people's thought.

When I use the SqlHelper , I find it very difficult to begin with, but I will slowly discover the benefits of its thought. One of the greatest advances is the use of tools, and when someone gives us the tools we have to do is use the tools well and create better tools for others to use.