What did you learn about VB. NET (3)

Next, let's take a look at sqlhelper.

1. UML diagram 2, three-tier architecture 3, sqlhelper 4, Configuration File 5, interface 6, design mode

I want to begin by saying, in fact, I have successively published several articles about "VB. net <Data room charging system individual restructuring version> what have you learned ", there is no technology at all, it is a stupid bird, will learn something, through their own perspective. After all, when people are not born, they will do everything. In the initial stage, there must be some confusions and confusions when they were walking. Maybe when others are on the same path, there will also be such confusion, so I will discuss and share it with you.

Let's talk about this sqlhelper today. When I used the three-tier Data Center charging system for the first time, I didn't use sqlhelper at all, nor did I know what sqlhelper was doing. It took a long time to check the Internet and read other people's blogs. In fact, I think we should mention the functions of sqlhelper. When writing code, we often see repeated code. When there are a lot of repeated code, we should consider extracting the public part of the repeated code, once needed, you only need to call it. This seems like a public module, or a function. Extracting the public part reduces the complexity caused by a large number of duplicates. This is why we use sqlhelper. In the data room charging system, the sqlhelper we extracted may not be so perfect at the beginning, and the SQL helper we extracted is not so streamlined yet, but it depends on others' experiences and others' blogs, when searching for information online, it is nice to find sqlhelper for the charging system of the data center drawn by others. In the charging system of the data center, there are no doubt that only four functions are enough, they are functions with parameters and without parameters that return values of the boolean type, and those with parameters and without parameters that return values of the able type. As long as these four functions can meet the data query problem in the charging system of the entire data room. It should be noted that we need to understand what we want to get when deciding which function in sqlhelper is called. This is actually very important. It also determines whether your program can run correctly and normally.

Next I will take a function of sqlhelper to illustrate its usage and specific calls.

This is a function with parameters and return values of the boolean type.

''' <Summary> ''': adds, deletes, and modifies three operations. The (with parameters) return values of the boolean type, confirm whether the execution is successful ''' </Summary> ''' <Param name = "strsql"> you need to execute the statement, which is generally an SQL statement, there are also stored procedures </param> ''' <Param name = "primitive type"> to determine the type of SQL statement, generally, it is not a stored procedure </param> ''' <Param name = "sqlparams"> parameter array, you cannot determine the number of parameters </param> ''' <returns>. The return value is a Boolean value, and the success value is true, false for unsuccessful execution </returns> ''' <remarks> </remarks> Public Function executeadddelupdate (byval strsql as string, byval primitive type as commandtype, byval sqlparams as sqlparameter ()) as Boolean 'fill In the CMD object of this class with the passed parameter. parameters. the addrange (sqlparams) parameter is passed into cmd. commandtype = commantype cmd. connection = CNN 'set connection cmd. commandtext = strsql' set the query statement try CNN. open () 'open connection return cmd. executenonquery () ': Execute the add, delete, modify, and delete operation cmd. parameters. clear () 'clear the catch ex as exception return false' if an error occurs, return false finally call closesqlcnn () call closesqlcmd () end try end Function

This is a function with parameters and returned values of the datatable type.

''' <Summary> ''' to perform the query operation (with parameters) the returned value is ''' of the able type </Summary> ''' <Param name = "strsql"> which is an SQL statement, there are also stored procedures </param> ''' <Param name = "primitive type"> to determine the type of SQL statement, generally, it is not a stored procedure </param> ''' <Param name = "sqlparams"> parameter array, you cannot confirm how many parameters </param> ''' <returns> return a table </returns> ''' <remarks> </remarks> Public Function executeselect (byval STR as string, byval parameter type as commandtype, byval sqlparams as sqlparameter ()) as datatable dim sqladapter as sqldataadapter dim dtsql as new datatable dim dssql as new dataset fill in the CMD object of the class with the passed parameters. commandtext = strsql cmd. commandtype = commantype cmd. connection = CNN cmd. parameters. addrange (sqlparams) 'parameter add sqladapter = new sqldataadapter (CMD)' instantiate the adapter try sqladapter. fill (dssql) 'uses the adapter to fill the dataset with dtsql = dssql. tables (0) 'able able is the first table cmd of dataset. parameters. clear () 'clear the catch ex as exception msgbox ("query failed", ctype (vbokonly + msgboxstyle. exclamation, msgboxstyle), "warning") Finally 'at last be sure to destroy cmd call closesqlcmd () end try return dtsql end Function

In sqlhelper, This is a function with parameters, Boolean returned values, and a function with parameters, and a datatable row returned values. Remember that the first function can be called only during addition, deletion, and modification. The specific "query" operation must be a function of the datatable type. How is this divided? Is there any basis? The specific difference is that the operations performed inside the function are different, which leads to calling different functions in different query statements. The following is an example of a specific call. Take logon as an example to call a function with parameters and returned values of the able type in sqlhelper. The procedure is as follows:

Imports system. data. sqlclientimports sqlhelperpublic class logindal: Implements idal. loginidal ''' <summary> ''' constructor, query whether the user table exists ''' </Summary> ''' <Param name = "user"> object class parameters, used to pass the user name and password </param> ''' <returns> returned value as a table </returns> ''' <remarks> </remarks> Public Function queryuser (byval user entity. usertableentity) as system. data. datatable implements idal. loginidal. queryuser'database query statement dim sqlstr as string sqlstr = "select * From usertable where userid = @ userid and userpwd = @ userpwd" 'defines a sqlhelper object dim sqlhelp as new sqlhelper. sqlhelper dim result as able 'sets the parameter dim sqlparams as sqlparameter () = {New sqlparameter ("@ userid", user. userid), new sqlparameter ("@ userpwd", user. userpwd)} 'Call sqlhelperResult = sqlhelp. executeselect (sqlstr, commandtype. Text, sqlparams)Return result end functionend class

In this example, you must first specify what is needed and what is returned to determine which function to call. In this login example, we want to check whether the user exists in the Database. If so, the login is successful, and if not, the login fails. Query the data in the database. Because sqlhelper defines the "query" part and calls the function whose return value is of the datatable type, the called function is executeselect. The SQL statement must be correctly written during the call process. To prevent SQL injection, we can set corresponding parameters for the SQL statement. If you do not set parameters, you do not need to write "sqlparams" in the final sqlhelper brackets ".

In short, sqlhelper should be well studied and used. It seems that the use of sqlhelper makes our program look more things, but in essence it also makes our program more streamlined, avoiding the emergence of a large number of repeated code, it complies with the encoding rules and the coding principles of a programmer.