Data room Charging System Reconstruction-storage process usage and Charging System Reconstruction

Data room Charging System Reconstruction-storage process usage and Charging System Reconstruction

From the beginning to the end of the computer room charging system, it is nearing the end. Like the fog in winter, it is becoming clearer and clearer as time passes away, this learning process is really wonderful.

Of course, it is meaningless not to sum up learning. Today, I will summarize the usage of stored procedures. In terms of user registration, it is to add users to the database to facilitate the use of more users. So what should we notice when registering users?

1. Identify whether the user already exists by using the card number.

If the user does not exist:

2. Write information into the student table.

3. Update the recharge record table

4. Update the balance in the card table.

The analysis result shows that if the user does not exist, we need to change the three tables at the same time. Of course, you can write one by one, but this is not what we want. Today there are three tables, and the next time there are thirty tables, are you writing one by one? So here we introduce the stored procedure.

1. What is a stored procedure?

Definition: store common or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.

At this point, someone may ask: is the stored procedure just a bunch of SQL statements? Why does Microsoft add this technology?

What is the difference between a stored procedure and a general SQL statement?

Ii. Advantages of stored procedures:

1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.

2. when performing complex operations on the database (for example, performing Update, Insert, Query, and Delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.

3. The stored procedure can be reused to reduce the workload of database developers.

4. High security. You can set that only one user has the right to use the specified stored procedure.

Iii. Types of stored procedures:

1. system stored procedure: starts with sp _. It is used to set the system and obtain information. related management work. For example, sp_help is used to obtain information about the specified object.

2. The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system.

3. User-Defined stored procedures, which we refer to as stored procedures

Common formats

Create proceduresp_name

@ [Parameter name] [type], @ [parameter name] [type]

As

Begin

.........

End

4. Call the Stored Procedure

1. Basic Syntax: exec sp_name [parameter name]

5. delete a stored procedure

1. Basic Syntax:

Drop procedure sp_name

2. Notes

(1) You cannot delete another stored procedure in one stored procedure, but you can only call another stored procedure.

It is like what is used in registration in the data room charging system:

Create procedure [dbo]. [PROC_Register] @ cardNo char (10), @ studentNo char (10), @ name ntext, @ sex nchar (10), @ department char (10 ), @ grade nchar (10), @ classgrade nchar (10), @ cash decimal (18, 2), @ cardStatue char (16), @ cardType char (8 ), @ chargeMoney decimal (10, 4), @ chargeDateTime datetime, @ userName char (10), @ statue ntext, @ registerDateTime date, @ checkStatue char (10) ASBEGIN -- insert into T_CardInfo (cardNo, studentNo, cardStatue, cash, cardType, registerDateTime, userName, checkStatue) values (@ cardNo, @ override, @ cardStatue, @ cash, @ cardType, @ registerDateTime, @ userName, @ checkStatue) -- insert into T_StudentInfo (studentNo, name, sex, department, classgrade, statue, grade) values (@ studentNo, @ name, @ sex, @ department, @ classgrade, @ statue, @ grade) -- insert into T_Recharge (cardNo, chargeDateTime, chargeMoney, userName) values (@ cardNo, @ chargeDateTime, @ chargeMoney, @ userName) END

In this way, you can easily operate on three tables. Of course, the stored procedure is far more than that, and we will continue to study in the future.

Just like we need to modify three tables, when we need to do the same thing three times, we have to dare to think of a more efficient way, we don't know how many people have done this before.

How can we crack our school's data center charging system?

Zhidao.baidu.com/question/21454140.html
 
Summary of sqlCommand Methods

This class cannot be inherited. I think this class serves as a bridge. He connected. NETt and SQL Server. He passed the SQL statement we set on the client to SQL, and then passed the SQL value to the client.
This class defines a lot of methods and attributes to help us complete different SQL statements. It summarizes the three methods used in the data center charging system.
Method 1: ExecuteScalar
Purpose: Execute the query return value: return the first column of the First row in the returned result set of the query. Ignore other columns or rows. System. the first column in the first row of the Object result set. If the result set is empty, it is a null reference (Nothing in Visual Basic). Applicability: use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from the database ). This operation requires less code than using the ExecuteReader method and then using the data returned by SqlDataReader to execute the operation required to generate a single value. Sample Code:
Retrieve a single value from the database in VB. NET-use the statement and
Imports System. the space Imports System to be referenced when the Data 'creates a table-type variable. data. sqlClient 'create the space Imports Entity Imports DateBaseHelper Public Class ConsumeInfoDAL 'to get all the Recharge Amount in T_Recharge Public Function GetBlance (ByVal strID As String, ByVal consumeCash As String) as String 'sets the T_ SQL statement in the incoming SQL statement: Get the full amount of recharge Dim strRecharge As String = "SELECT SUM (cast (RechargeCash as int )) FROM T_RechargeInfo where CardID = '"+ strID +"' "'Open the database and execute the preceding SQL statement. The database Dim con As New DBHelper Dim recharge As String con is disabled. open () 'needs to be processed. If there is no record in the table, false is returned. If false is returned, the value is 0 If con. executesqlScalar (strRecharge) = False Then recharge = "0" Else recharge = con. executesqlScalar (strRecharge) End If con. close () 'Return result Return recharge End Function End Class DBHelper Class, which defines common database operations as the method Imports System. data Imports System. data. sqlClient Imports System. data. oleDb Imports System. text Imports System. configuration Imports Entity Public Class DBHelper Private myConnection As New SqlConnection 'indicates the Data Connection Control Private connector String As String', indicating the SQL statement command Priva ...... remaining full text>