"VB.net computer room charge System"--to SqlHelper don't say "no"

Brief introduction

SqlHelper is based on a · The NETFramework database operations component. The component contains a database action method. SqlHelper is used to simplify your repetition

To write those database connections (SqlConnection), Sqlcommand,sqldatareader and so on. SqlHelper package is usually only needed to pass some parameters such as database connection string, SQL parameters, etc., you can access the database, very convenient.

———— Official Solution

a long list of words, I notice is "very convenient" these three words, that is how convenient??? Still do not use it, also did not find and convenient ah, just look at the code in such blocks of SqlHelper, it is hard to panic. Thus, the use of the data, access data, adding and deleting data, and so on, but time-consuming work. Sometimes, it's not that you don't want to be lazy, but you can't even be lazy.

A successful programmer does not use the time to paste the copy.

Configuration Items

It is best to use Web. config to configure the connection string,<!--sqlserverhelper the connection string before applying SqlHelper-

<addconnectionstring= "server=.; Uid=sa;pwd=123456;database=yourdatabase "name=" Sqlserverhelper "/> As to how to configure the file, self-Baidu, some things can not be an ignorant request, to find themselves.

Source

Writing SqlHelper calling code, SqlHelper has a lot of writing styles, you can choose your own needs and hobbies using static or object mode. Various pros and cons. Choose a situation to use it!

'//******************************************************* ' * * Nancy ' * * Founding Date: 2014-8-23 ' * * Group: ' * * Modified: 2014-9-' * * Description: Personal Computer room Reconstruction ' * * version number: ' **********************************************************imports system.dataimports        System.Data.SqlClientImports system.configuration ' must add reference to public Class sqlhelperdal ' define variable ' in Manager to get the connection string for the database Private ReadOnly strconnection as String = Configurationmanager.appsettings ("connstr") ' Set connection Dim conn as S        Qlconnection = new SqlConnection (strconnection) ' Define cmd command Dim cmd as New SqlCommand ' <summary> "' Execute Add or delete change three operation, (have a parameter) return value is Boolean type, confirm whether execution succeeds ' </summary> define a" execadddelupdate "function method to modify data in a database table" <param name= "Cmdtext" > need to execute statements, typically SQL statements, there are stored procedures </param> "<param name=" Cmdtype "> Determine the type of SQL statements, generally not the storage Stored procedure </param> ' ' <param name= ' paras ' > parameter array, unable to confirm how many parameters </param> ' <returns></returns     > ' <remarks></remarks>   Public Function execadddelupdate (ByVal cmdtext as String, ByVal Cmdtype as CommandType, ByVal paras as SqlParameter ()) As Integer ' will pass in the value, respectively, to the Cmd property assignment cmd. Parameters.addrange (paras) ' passes parameters to cmd. CommandType = Cmdtype ' Sets a value that interprets Cmdtext cmd. Connection = Conn ' Set the connection, global variable cmd. CommandText = Cmdtext ' Sets the query's statement Try Conn. Open () ' Opens the connection to Return cmd. ExecuteNonQuery () ' perform additions and deletions and change operation CMD.            Parameters.clear () ' Clear parameter Catch ex as Exception return 0 ' If there is an error, return 0 Finally call CLOSECONN (conn) call Closecmd (cmd) end Try end Function ' &lt ;summary> "" To perform additions and deletions to three operations, (no reference) "</summary>" <param name= "Cmdtext" > need to execute statements, usually SQL statements,        There are also stored procedures </param> "<param name=" Cmdtype "> Determine the type of SQL statements, generally not stored procedures </param>"<returns>interger, number of rows affected </returns>" <remarks>2013 February 2 8:19:59</remarks> publi            C Function Execadddelupdateno (ByVal cmdtext as String, ByVal Cmdtype as CommandType) as Integer ' command cmd assignment to execute Cmd.commandtext = Cmdtext ' first query SQL statement cmd.commandtype = Cmdtype ' Sets how the SQL statement interprets CMD. Connection = conn ' Set connection ' to execute action Try Conn. Open () Return cmd.            ExecuteNonQuery () Catch ex as Exception Return 0 Finally call CLOSECONN (conn) Call Closecmd (cmd) end Try End Function ' <summary> ' performs the operation of the query, (with parameters), the parameter is unlimited "' </s Ummary> ' <param name= ' cmdtext ' > need to execute statements, typically SQL statements, and stored Procedures </param> ' <param name= ' Cmdtype ' > Determine the type of SQL statements, generally not stored procedures </param> ' <param name= ' paras ' > Incoming parameters </param> ' <returns> </returns> ' <remaRks></remarks> public Function execselect (ByVal cmdtext as String, ByVal Cmdtype as CommandType, ByVal par  As as SqlParameter ()) as DataTable Dim sqladapter As SqlDataAdapter Dim dt As New DataTable Dim ds As New DataSet ' or cmd assignment cmd.commandtext = cmdtext cmd.commandtype = cmdtype cmd. Connection = conn cmd. Parameters.addrange (paras) ' parameter added Sqladapter = New SqlDataAdapter (cmd) ' instantiation adapter Try s Qladapter.fill (DS) ' fills the dataset with the 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 + msgboxstyle.ex        clamation, MsgBoxStyle), "warning") finally ' must destroy CMD call Closecmd (CMD) End Try Return dt End Function ' <summary> ' to perform the operation of the query (without reference) ' </summAry> ' <param name= ' cmdtext ' > need to execute statements, typically SQL statements, and stored Procedures </param> ' <param name= ' Cmdtype To judge the type of SQL statements, generally not stored procedures </param> "' <returns>datatable, query to the table </returns> ' <remarks>&        Lt;/remarks> public Function Execselectno (ByVal cmdtext as String, ByVal Cmdtype as CommandType) as DataTable Dim sqladapter As SqlDataAdapter Dim ds As New DataSet ' or cmd-value cmd. CommandText = Cmdtext cmd. CommandType = Cmdtype cmd.           Connection = conn Sqladapter = New SqlDataAdapter (cmd) ' Instantiation adapter Try Sqladapter.fill (DS) ' Use adapter to populate the DataSet with Return DS.                            Tables (0) ' DataTable is the first table of a DataSet Catch ex as Exception Return nothing Finally ' Finally, be sure to destroy the cmd call Closecmd (cmd) end Try end Function ' &LT;SUMMARY&G        T  "' Close connection ' </summary>      ' <param name= ' conn ' > need to close the connection </param> ' <remarks></remarks> public Sub Clos Econn (ByVal conn as SqlConnection) IF (Conn. State <> connectionstate.closed) and then ' if Conn is not turned off. Close () ' Close Connection conn = Nothing ' does not point to the original object end If end Sub '        ' <summary> ' Close command ' </summary> ' ' <param name= ' cmd ' > command to close </param> ' <remarks></remarks> public Sub closecmd (ByVal cmd as SqlCommand) If not isnothing (cmd) T Hen ' if cmd command exists. Dispose () ' Destroy cmd = Nothing end If End SubEnd Class

In other words, how to use the source code, this is a problem. In the beginning of the computer room I have been in the D layer plus SqlHelper class, the new things always exist in conflict with the mentality, not it. After knocking a few forms, gradually found every time the access data, are the few lines of code, change to change the data is the addition and deletion of the change, and every time to write. There is treasure in the home, not to use the loss of big. This is the only way I can SqlHelper How to use it.

1. observation code ____ Span lang= "ZH-CN" style= "font-size:14pt" > find different

        By definition of method name, divided into query and delete and change two categories. The query is returned to the user who wants to see the information, the returned           is DataTable 1 ( integer

※ A DataTable is a network virtual table that temporarily holds data that can be created and accessed and returned to a DataTable virtual table after SqlHelper queries. To determine if the first row has data to insert into the record

※ Integerreturns the number of rows affected. In the computer room charge system, we can find that when the number of rows affected is greater than0description to find the corresponding record, if the0the description does not query the data. Similarly, if the number of rows affected is greater than0Note that adding and deleting the operation succeeded,0the operation fails. Thus, you can return the number of rows affected toBlayer to make logical judgments, and then uploadUlayer, such a layer of progressive, wonderful.

1. Meditation Thinking ____ Ming principle

in the query and additions and deletions of the two categories, and subdivided the parameters and no parameter two small class. When you want to get all the information of the database, such as on duty teacher log query, you can not query information, all in DataGridView display. When you want to get some information, you can query with parameters.

I am on the computer room charge system login as an example. It is implemented in SqlHelper and without SqlHelper, respectively.

in order not to destroy the three layers of thought: the transfer of entities between the layers. The DT obtained in this example is converted to an entity. The careful classmate will find that the query to the DT results assigned to the properties of the entity object, and then return the entity of the process, perhaps the value of the property in this example is less, or can be a one-pass, but there are many, or hundreds of times, then a one of the pass , which obviously does not work, leads to the generic collection. You can convert the dt directly to a generic collection containing entities, and then return the collection as a result each time you return.

Please look forward to my next blog: Entity classes return generic collections.

Follow-up: Not afraid do not know, afraid do not know .... As long as you want to learn, you will never learn. Say Goodbye, "No."

"VB.net computer room charge System"--to SqlHelper don't say "no"