View code refactoring from sqlhelper

Sqlhelper embodies the abstract and encapsulation ideas. To avoid creating connections and instantiating connection objects and getting connection objects every time a database is connected, I use constructors to initialize Conn, and wrote the getconn () function. Extracting repeated content is an important part of refactoring.

 

Imports system. data. sqlclientimports system. configurationpublic class sqlhelper private conn as sqlconnection private cmd as sqlcommand private SDR as sqldatareader 'constructor sub new () 'dim connstr as string = "Data Source = localhost; initial catalog = newcharge; persist Security info = true; user id = sa; Password = 123456 "dim connstr as string = system. configuration. configurationmanager. appsettings ("connstr") Conn = new sqlclient. sqlconnection (connstr) end sub private function getconn () as sqlconnection if Conn. state = connectionstate. closed then Conn. open () return conn else return conn end if end Function

In addition to accessing the database, each class on the Dal layer performs addition, deletion, modification, and query operations on the database, every time we repeat the work, a large number of code duplication occurs, which is not professional enough. Therefore, we often need to refactor and optimize the code after writing it. Sqlhelper is mainly used to simplify repeated write operations such as sqlconnection, sqlcommand, and sqldatareader. Its application optimizes the entire Dal layer, the reason is that each class can be called directly by instantiating a sqlhelper object. Instead, you only need to input some parameters such as SQL parameters and stored procedures to the method, which greatly reduces the amount of code.

 

There are four main types in sqlhelper:

SQL addition, deletion, modification, or stored procedure without parameters (No rows or values returned)

SQL addition, deletion, modification, or stored procedure with parameters (No rows or values returned)

Returns the datatable result set (with rows or values returned) for SQL query statements or stored procedures without parameters)

Return the datatable result set (with rows or values returned) for SQL query statements or stored procedures with parameters)

 

The following is the first SQL addition, deletion, and modification statement I wrote without parameters.

 

''' <Summary> ''': SQL statement without parameters ''' </Summary> ''' <Param name = "SQL"> SQL statement </ param> ''' <returns> </returns> ''' <remarks> </remarks> Public Function executenonquery (byval SQL as string) as Boolean dim conn = getconn () dim cmd as sqlcommand = new sqlcommand (SQL, Conn) If cmd. executenonquery ()> 0 then Conn. close () return true else return false end if end Function

 

In fact, we often use not only SQL statements, but also stored procedures. Therefore, the SQL statement is converted to plain text, and whether it is an SQL statement or a stored procedure depends on objective type. You need to add a parameter to the function. Therefore, the function is restructured as follows:

''' <Summary> ''' add, delete, modify, and delete SQL statements without parameters or stored procedures ''' </Summary> ''' <Param name = "cmdtxt"> modify statement or stored procedure </param> ''' <Param name = "primitive type"> command-type text or stored procedure </param> ''' <returns> affected rows </returns> ''' <remarks> </remarks> Public Function executenonquery (byval cmdtxt as string, byval primitive type as commandtype) as integer dim conn = getconn () 'defines and obtains a database connection object dim cmd as sqlcommand = new sqlcommand (cmdtxt, Conn)' defines a command object cmd. commandtype = primitive type dim res as integer try res = cmd. executenonquery () catch ex as exception msgbox (ex. message, "Database Operations") Finally if Conn. state = connectionstate. open then' close the database connection Conn. close () end if end try return res end Function

 

In the second method, I used the exception handling try... In fact, throwing exceptions is mainly at the BLL layer. It is not necessary here.

 

The other three methods are as follows:

 

''' <Summary> ''': Execute the SQL addition, deletion, modification, or stored procedure ''' </Summary> ''' <Param name = "plain text"> add, delete, and modify statement or stored procedure </param> ''' <Param name = "primitive type"> command-type text or stored procedure </param> ''' <Param name = "paras"> parameter array </param> ''' <returns> affected rows </returns> ''' <remarks> </remarks> Public Function executenonquery (byval plain text as string, byval ready type as commandtype, byval paras as sqlparameter () as integer dim conn = getconn () dim cmd as sqlcommand = new sqlcommand (plain text, Conn) dim res as integer cmd. commandtype = commantype cmd. parameters. addrange (paras) Try res = cmd. executenonquery () catch ex as exception msgbox (ex. message, "Database Operations") Finally if Conn. state = connectionstate. open then Conn. close () end if end try return res end function ''' <summary> ''' executes SQL query statements or stored procedures without parameters, return the 'able result set ''' </Summary> ''' <Param name = "plain text"> SQL query statement or stored procedure </param> ''' <Param name =" primitive type "> command type text or stored procedure </param> ''' <returns> returns the able result set </returns> ''' <remarks> </remarks> Public Function executequery (byval plain text as string, byval ready type as commandtype) as datatable dim conn = getconn () dim cmd as sqlcommand = new sqlcommand (plain text, Conn) dim mydt as datatable = new datatable dim myreader as sqldatareader cmd. commandtype = primitive type myreader = cmd. executereader () mydt. load (myreader) return mydt Conn. close () end function ''' <summary>, return the 'able result set ''' </Summary> ''' <Param name = "plain text"> SQL query statement or stored procedure </param> ''' <Param name =" primitive type "> command type text or stored procedure </param> ''' <Param name =" paras "> parameter set </param> ''' <returns> returns the datatable result set. </returns> ''' <remarks> </remarks> Public Function executequery (byval plain text as string, byval parameter type as commandtype, byval paras as sqlparameter () as datatable dim conn = getconn () dim cmd as sqlcommand = new sqlcommand (plain text, Conn) dim mydt as datatable = new datatable dim myreader as sqldatareader cmd. commandtype = commantype cmd. parameters. add (paras) myreader = cmd. executereader () mydt. load (myreader) return mydt Conn. close () end Function

 

In fact, we have developed a system based on the three-tier architecture. Since we have defined entity classes, strictly speaking, we need to return all object classes instead of datatable and dataset, we all know that using datatable is very simple. You only need to load (sqldatareader), while returning the object class requires writing n lines of code, but just like the three layers, the UI Layer does not know the fields of each table, so the able transfer violates the three-layer principle. What should we do? The method is to assign the result to the object class at the Dal layer, and then return the result to the UI Layer. Of course, it is very time-consuming. If you want to learn it, you can upload objects in an honest manner. If you want efficiency, it is not cool to upload datatable.