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 close database query database more also added SqlHelper. When you have a lot of demands that have the same approach, we don't have to finish. A need to write the code, complete a requirement to write the code again. This will not only kill people (of course, in addition to copy and paste), this will cause the code repeatedly added coupling, resulting in redundancy.

Why not pull out the same code separately and encapsulate it as a class, so that each requirement needs to invoke only the corresponding method of the encapsulated class.

        &NBSP, when there are a number of database additions and deletions to the operation of the use of the sqlhelper , because d Layer is the direct operation of the database, complete database additions and deletions to check, so sqlhelper d layer. But I was taken out alone to become a layer. Put d All of the layers are encapsulated in the way the database is manipulated.

Divided into two kinds of operations, one is to delete and change, one is the query operation, and each is divided into a number of parameters and no parameters. This reduces the burden of D , so that the D layer does not appear too bloated.

In the computer room has been looking at other people's blog, always mentioned SqlHelper. So I looked at it a lot in the public blog.

Just because in the crowd more than look at you, can never forget your face, write down here I am a person to you love a unique ...

Second, SqlHelper use method

1. first, its method

( 1 Why do they have to be divided into numbers and no parameters?

This is a thought, when you want to get all the information and some information of the database, it is necessary to give it all the information to specify which table to find, but when you want to get some information in a table, you need to have the query condition. There will be a number of references.

( 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 details. So the query returns the DataTable type, and the update returns the affected row, and returns the integer 1 when the row of data is updated . This has a relationship with its return value, since the same is necessary to merge into a method. This reduces redundancy and simplifies the code. Learning the object-oriented, in fact, is how simple to come.

   

2. Then, SqlHelper Each method must have a return value, because the return value of the different will cause the corresponding method to call this method also have the same return value.

( 1 ) returns DataTable

DataTable What is it again?

DataTable is a network virtual table that is temporarily saving data and can be created and interviewed when SqlHelper the query will return to DataTable virtual table, infer whether its first row has data to infer whether the record is being traced.

( 2 ) returns Integer

          Returns the number of rows affected, assuming the number of rows affected is greater than 0 Description of the records found, assuming 0 Description no data was queried. Assume that the number of rows affected is greater than 0 0 Indicates that the operation was unsuccessful. We return the number of rows affected to b u layer. This is very good to be implemented in another line. Clear thinking.

Three, sqlhelper example application

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

    ' 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 voluntarily shut down the connection            Dim cmd as SqlCommand = conn After you have finished using it. CreateCommand () '            cmd.commandtext = cmdtext  ' need to run SQL statement            Cmd.commandtype = Cmdtype ' gives the SQL statement type            cmd. Parameters.addrange (sqlparams) ' parameter array, the number of references depends on the actual situation of            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 you infer whether a user exists, after entering username, this username becomes a parameter, calling sqlhelper executenonquery () method. By returning the datatable 0

    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 the SQL statement that needs to be run        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 run the query, and gets the return value returned        DT                                                             ' Returns query result    end Function

Iv. problems with my sqlhelper

then there is another problem, when we delete the user. Or if you need to query for multiple records to appear, although the DataTable type is returned, it defaults to the first row of data that returns a DataTable. There are two ways to resolve this situation:

( 1 Iterate through the thought loop to check the records in the database. Returns the first row of a different record from the previous one.

such as returning users at a certain level

      If dt. Rows.Count > 0 Then for            i = 0 to dt. Rows.count-1  ' found that the content shows                DataGridView.Rows.Add ()  ' There was no row or column at the beginning. So to add a row and a column, avoid the error occurring for                j = 0 to dt. Columns.count-1                    DataGridView (J, i). Value = dt. Rows (i). Item (j)                Next j                ' datagridview.allowusertoaddrows = False    ' Suppose to be placed here. There's going to be a last line            Next I

( 2 ) leverage a bound dataset

Direct datagridview=table

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

The other way is obviously much better than the first one, but why so many people still want to just return DataTable first row of data?

This is due to: ... ^^ ... .. I'm the one who doesn't stop looping it. And then try another method,

Five, my sentiment--sqlhelper     

Just like design patterns, in fact there is no design pattern at the start. Big Plus is just writing code, writing code. But the need to write a lot of code, there will be fewer people programming, it is necessary to improve the efficiency of writing code. So some design patterns appear, object-oriented appearance also has a certain amount of time accumulation, people need to reuse code. The first is to write less code to implement the requirements, and the system software appears to be more concise, to achieve high cohesion and low coupling effect.

Suppose that 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 think it is very difficult to start, but will slowly discover its merits. The greatest improvement in people is the use of tools. When someone has created a tool for us, what we have to do is to use the tool well and create better tools for others to use.

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