sqlhelper--Database Small Helper

SqlHelper is actually a class.

I've heard of the term "SqlHelper". Also checked the relevant information. But it's still confused. When you take it seriously, you'll find that it's actually not that magical.

When I hit the first window. function is realized, we will think very happy. It's done. But when you finish the second window and the third window, slowly start thinking that the code is too repetitive. The more you knock, the more you feel guilty. Although the design pattern is not very good, but at least let me have this awareness to how to make the code less. Make the coupling between each other as low as possible.

In the three tier, the code for the D-tier is the most similar, in summary, the connection database. Additions and deletions to the function of the search. So. We are fully able to put together this part of the common thing. This can reduce the amount of code, but also very good to do the encapsulation.

There are several ways to SqlHelper in this class .

In the SqlHelper, the most important thing is that the few additions and deletions to change the method.

Check a lot of blogs, the methods in the class are also very many, including ExecuteNonQuery, ExecuteReader, ExecuteDataset, ExecuteScalar, ExecuteXmlReader and so on. But we're not going to use them all. There are some ways to understand the good. Method can divide the fine. But suppose the points are too thin. It's going to be too complicated.

I'm used to getting less of a method.

Public Class sqlHelper ' <summary> ' has the added, deleted, changed operation ' </summary> ' <param name= ' Cmdtext ' &G T;</param> "<param name=" Cmdtype "></param>" <param Name= "paras" ></param> " <returns></returns> ' <remarks></remarks> public Function executenoquery (Cmdtext as String , Cmdtype as CommandType, paras as SqlParameter ()) as Integer Dim connstr as String = System.Configuration.Configur        Ationmanager.appsettings ("ConnStr") Dim conn as SqlConnection = New SqlConnection (connstr) ' Define a Command object Dim cmd as new SqlCommand cmd = new SqlCommand (CMDTEXT, conn) ' Budong cmd. CommandType = Cmdtype cmd. Parameters.addrange (paras) Dim res as Integer ' defines a variable that the user holds to return the result of the Try ' Open database If conn . State = Connectionstate.closed Then Conn. Open () End If res = cmd. ExecuteNonQuery () Catch ex as Exception MsgBox (ex. Message, "database operation") Finally ' closes the database If Conn. State = ConnectionState.Open Then Conn. Close () End If End Try return res ' returns the number of rows affected End Function ' <summary> ' no reference increment, delete , change operation "</summary>" "<param name=" Cmdtext "></param>" "<param name=" Cmdtype "></par Am> ' <returns></returns> ' <remarks></remarks> public Shared Function Executenoqu Ery (Cmdtext As String, Cmdtype as CommandType) as Integer Dim connstr As String = System.Configuration.Configuratio Nmanager.appsettings ("ConnStr") Dim conn As SqlConnection = new SqlConnection (connstr) Dim cmd As New sqlcom Mand ' defines a command object cmd. CommandType = cmdtype cmd = New SqlCommand (cmdtext, conn) Dim res as Integer ' defines a variable where the user holds the return result ' cmd. Parameters.addrange (paras) The method has no parameters and does not need to join the Try ' Open database If Conn. STate = Connectionstate.closed then Conn. Open () End If res = cmd. ExecuteNonQuery () Catch ex as Exception MsgBox (ex. Message, "database operation") Finally ' closes the database If Conn. State = ConnectionState.Open Then Conn. Close () End If End Try ' Returns the number of rows affected return res End Function ' <summary> ' Query operation "</summary>" "<param name=" Cmdtxt "></param>" <param name= "Cmdtype" ></ Param> "<param Name=" paras "></param>" <returns></returns> "<remarks>&lt ;/remarks> public Function getdatatable (ByVal cmdtxt as String, ByVal Cmdtype as CommandType, ByVal paras as Sqlpara        Meter ()) as DataTable Dim strconnstr as String = System.Configuration.ConfigurationManager.AppSettings ("ConnStr") ' Define a database connection object Dim conn as SqlConnection = New SqlConnection (strconnstr) ' Define aA Command object Dim cmd As New SqlCommand Dim adataset As DataSet ' defines an adapter object Dim adaptor As Sqldataa        Dapter cmd = new SqlCommand (cmdtxt, conn) adaptor = new SqlDataAdapter (cmd) adataset = new DataSet Cmd.commandtype = Cmdtype cmd. Parameters.addrange (paras) Try ' Open database connection If Conn. State = Connectionstate.closed Then Conn. Open () End If ' fills the dataset adaptor. Fill (Adataset) Return Adataset. Tables (0) Catch ex as Exception MsgBox (ex. Message) Finally ' closes the database connection If Conn. State = ConnectionState.Open Then Conn.    Close () End If end Try End Function ' <summary> ' no parameter query operation ' </summary> "' <param name=" cmdtxt "></param>" "<param name=" Cmdtype "></param>" <returns>&lt ;/returns> ' &LT;REMARKS&GT;&LT;/REMARKS&GT Public Function getdatatable (ByVal cmdtxt as String, ByVal Cmdtype as CommandType) as DataTable Dim Strconnstr as S Tring = System.Configuration.ConfigurationManager.AppSettings ("connstr") ' defines a database connection object Dim conn as Sqlconne        ction = new SqlConnection (STRCONNSTR) ' defines a Command object Dim cmd As New SqlCommand Dim adataset As DataSet ' Define an Adapter object Dim adaptor as SqlDataAdapter cmd = new SqlCommand (cmdtxt, conn) adaptor = new Sqld Ataadapter (cmd) adataset = New DataSet cmd.commandtype = Cmdtype ' cmd. Parameters.addrange (paras) has no parameters, no need to join the Try ' Open database connection If Conn. State = Connectionstate.closed Then Conn. Open () End If ' fills the dataset adaptor. Fill (Adataset) Return Adataset. Tables (0) Catch ex as Exception MsgBox (ex. Message, "database operation") Finally ' closes the database connection If Conn. State = Connectionstate.opeN then Conn. Close () End If end Try End Function End Class

The class has. How does that work?

With the number of references, it is not possible to invoke it.

Cmdtxt is the SQL statement.

CommandType has three properties: StoredProcedure (Stored procedure). TableDirect (table). Text (SQL text command, more frequently used).

SqlParameter property: We define variables when we write SQL statements, andSqlparAmeter is used to pass values to variables defined in the database.

Like what:

Dim paras as SqlParameter () = {New SqlParameter ("@cardno", Student.cardno)}

@cardno is a variable. Student.cardno is the value of the entity.

Note: the difference between add and AddRange

Add: Adds the specified object to the ... AddRange: To ... At the end, add an array

Add This method can only add one SqlParameter at a time.

AddRange can join multiple SqlParameter. when operating in a group, use AddRange instead of Add.

Such Our SqlHelper assistants will be able to help us get our code intact. Encounter a problem, do not fear, first simplify it. When we have a certain understanding of it and then go to see it complex place. When we are able to use ExecuteNonQuery, ExecuteReader, ExecuteDataset methods flexibly. The rest of the content will not baffle us.

sqlhelper--Database Small Helper