Use of sqlhelper to reconstruct the charging system for personal IDC Rooms

The sqlhelper class is used to encapsulate data access through a set of static methods. This class cannot be inherited or instantiated, so it is declared as a non-inherited class that contains a dedicated constructor. In
Each method implemented in the sqlhelper class provides a set of consistent overloading. This provides a good way to use the sqlhelper class to execute commands. It also provides necessary flexibility for developers to select a way to access data. Each method overload supports different method parameters, so developers can determine how to pass connection, transaction, and parameter information.

In fact, I think sqlhelper is actually a method encapsulated to complete database changes and queries through the return value. The following describes the sqlhelper I used in the data center billing system. Because only one database (multiple tables) is involved in the charging system of the data center, sqlhelper can connect to the database for query by passing SQL parameters.

Let's talk about the following code:

Database operations can be divided into changes and queries (add insert, delete
Delete, update) and query
Select

1. Change (return Boolean value)

'Database connection public function connectsql () as sqlconnection dim STR as string = "Data Source = 192.168.24.76; initial catalog = charge_system; uid = sa; pwd = 123456 "dim conn as sqlconnection = new sqlconnection (STR) return conn end function 'basic data changes except for querying public function operate (byval SQL as string) as Boolean 'database connection string dim conn as sqlconnection = connectsql () Conn. open () dim cmd as new sqlcommand (SQL, Conn) cmd. executenonquery () cmd. dispose () Conn. close () Conn. dispose () return true end Function

2. Data Query

Data Query has two purposes: Reading data and obtaining data. When I use data query statements in the data room charging system, the following two methods are encapsulated:

'Basic Data Query return reader object SELECT statement public function query (byval SQL as string) as sqldatareader dim conn as sqlconnection = connectsql () Conn. open () dim cmd as new sqlcommand (SQL, Conn) dim reader as sqldatareader reader = cmd. executereader () return reader end function 'datatable object public function querytable (byval SQL as string, byval STR as string) as datatable dim conn as sqlconnection = connectsql () Conn. open () dim myadapter as new sqldataadapter (SQL, Conn) dim myset as new dataset dim mytable as new datatable myadapter. fill (myset, STR) mytable = myset. tables (STR) Conn. close () return mytable end Function

Through the encapsulation of the above 1 and 2 pairs of database connections and operations, the code writing can be reduced in the future call process, in addition, it is possible to avoid personal errors.

For example, in the data center billing system, you need to call the information that meets the requirements in the cardinfo database table and display it. We first define this method to return a able type.

Public Function querycard (byval cardno as string) as datatable dim SQL as string = "fill in the condition to be queried" 'Call the encapsulated method dim mytable as datatable mytable = querytable (SQL, "Student info table") end Function

In the UI Layer, we can return the datatable layer by layer, that is, the mytable in the method. Data Display I am using the datagridview control. You can directly assign a value using the property of the control: datagridview. datasource = mytable

This is sqlhelper in my eyes. If you cannot find it, please correct it.