Storage Structure and transactions, storage structure transactions

Storage Structure and transactions, storage structure transactions

The restructured data center charging system is implemented by three layers. When I started simple addition, deletion, and modification, I was able to say that I had to learn something new when I did recharge and withdraw the card, if you want to return the card, First insert the information to the return table, and change the status of the card to the return status. This requires two SQL statements, but if I write it in a function, first, I cannot guarantee the execution sequence of the functions. Second, if the insertion is completed, but the modification is not executed, this is still stuck, this is not a matter of easy opinion, so now we can find good things, that is, the storage structure and transactions.

Storage Structure and transactions

Let's take a rough look at the storage structure. The complex introduction will not be written, so I will understand it as a structure with Multiple SQL statements. The structure is dead, and only the statements are active, then we can add the statement in the dead structure. Because a function is often not solved by a single statement, you can write these statements in the same way as writing them in a program.

Transactions, he ensures that the SQL statements in the storage structure are executed in order, and they are all executed, and the execution of the SQL statements is unknown. Even if the execution of the preceding statements is performed, the time will be reversed. We understand him as a clothing statement,If Statement 1, Statement 2... else does not execute end ifIs it easier to understand and use.

What if I create a stored procedure?

Right-click the stored procedure, click Create stored procedure, and then click the template.

Next I will show you the back-to-card code and Storage Structure and transactions of the data room charging system, so that you can see how simple it is. However, since this program is not yet complete, this is only the implementation of basic functions. After I complete the system, I will present the complete code for you.

Stored Procedure and transaction

<Span style = "font-size: 14px;"> <span style = "font-size: 14px;"> <span style = "font-size: 18px; "> USE [jf_Charge] GO/****** Object: StoredProcedure [dbo]. [Pro_CancelCard] Script Date: 07/28/2014 19:41:05 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: meng Haibin -- Create date: 2014-7-27 -- Description: back-to-card storage process -- =========================================== ========== = Alter procedure [dbo]. [Pro_CancelCard] @ Card_ID char (11), @ head char (11) -- Define the ASBEGIN tran parameter -- execute the transaction set nocount on; DECLARE @ SumRowCount INT -- DECLARE a variable, used to determine whether the statement has been successful for several days. Declare @ cancelMoney numeric () -- declare the variable declare @ Student_Name char (11) SET @ SumRowCount = @ ROWCOUNTSelect * from StudentView Where Card_ID = @ Card_ID -- get student information from the view SET @ SumRowCount = @ SumRowCount + @ ROWCOUNTset @ CancelMoney = (Select charge from StudentView where Card_ID = @ Card_ID) -- copy the amount information to @ CancelMOneyinsert into CancelCard_info (Card_ID, CancelMoney, head) values (@ Card_ID, @ cancelMoney, @ head) -- insert information SET @ SumRowCount = @ SumRowCount + @ ROWCOUNTupdate Card_Info set IsExist = 'yes' where Card_ID = @ Card_ID and IsExist = 'No' to the return table -- modify the status information in Card_info SET @ SumRowCount = @ SumRowCount + @ ROWCOUNTIF @ SumRowCount = 4 begin commit tran -- if all operations are successful, then begin to execute the transaction endelsebegin rollback transaction -- if the TRANSACTION fails, it will roll back to the previous time and END the time. </span>

DAL Layer

<Span style = "font-size: 14px;"> <span style = "font-size: 14px;"> <span style = "font-size: 18px; "> Imports EntityImports System. data. sqlClientPublic Class SQLServerCancelCard: Implements IDAL. ICancelCard Public Function QueryCancelCard (cancel As CancelCardEntity) As CancelCardEntity Implements IDAL. ICancelCard. queryCancelCard Return Nothing End Function ''' <summary> ''' this method is used to Return the card and display the card Return information using the stored procedure. Multiple statements need to be executed, this includes inserting the backoff information ''' to check whether the card information exists and view information, ''' </summary> ''' <param name = "cancel"> </param> ''' <returns> the returned type is StudentInfoEntity, because the information displayed after the card is returned must be called from the Student Information </returns> ''' <remarks> </remarks> Public Function CancelCard (cancel As CancelCardEntity) as StudentInfoEntity Implements IDAL. ICancelCard. cancelCard Dim table As able defines a DataTable Dim cancelcardinfo As New Entity. studentInfoEntity defines an entity to return the Dim sqlhelper As New SqlHelper Dim User_ID As New PublicUserEnity public entity, the attribute Dim strSQL As String = "Pro_CancelCard" 'is used to assign the name of the creator to the entity to define parameters to prevent SQL injection into Dim paras As SqlParameter () = {New SqlParameter ("@ Card_ID", cancel. card_ID), New SqlParameter ("@ head", cancel. head)} 'execute the SQL statement and return Datatable table = sqlhelper. execSelect (strSQL, CommandType. storedProcedure, paras) 'If the queried table is not empty, the partial score is returned to the object attribute, and the object If table is returned. rows. count <> 0 Then cancelcardinfo. student_Name = table. rows (0 ). item ("Student_Name") cancelcardinfo. charge = table. rows (0 ). item ("Charge") End If 'returns the object Return cancelcardinfo End FunctionEnd Class </span>

SQLHelper

<Span style = "font-size: 14px;"> <span style = "font-size: 14px;"> <span style = "font-size: 18px; "> ''' <summary> ''': query operations (with parameters ), the parameter is not limited to ''' </summary> ''' <param name = "plain text"> the statement needs to be executed, 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 = "paras"> input parameter </param> ''' <returns> </returns> ''' <remarks> </remarks> Public Function ExecSelect (ByVal plain text As String, byVal parameter type As CommandType, ByVal paras As SqlParameter () As DataTable Dim sqlAdapter As SqlDataAdapter Dim dt As New DataTable Dim ds As New DataSet 'or assign cmd to cmd. commandText = plain text cmd. commandType = commantype cmd. connection = conn cmd. parameters. add sqlAdapter = New SqlDataAdapter (cmd) 'to the AddRange (paras)' parameter to instantiate the adapter Try sqlAdapter. fill (ds) 'uses the adapter to Fill the dataSet with dt = ds. 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 CloseCmd (cmd) End Try Return dt End Function </span>

Abstract Factory + Interface + configuration file Factory Layer

<Span style = "font-size: 14px;"> <span style = "font-size: 14px;"> <span style = "font-size: 18px; "> Imports System. configurationImports System. reflection 'add configuration file reference Imports IDALPublic Class dataaccess' configuration file, abstract factory Private ReadOnly assemblyName As String = "DAL" Private ReadOnly db As String = ConfigurationManager. the abstract factory Public Function CreateOnline () As IDAL. IOnline ClassName = assemblyName + ". "+ db +" CancelCardDAL "Return CType (Assembly. load (assemblyName ). createInstance (ClassName), ICancelCard) End FunctionEnd Class </span>

Configuration File

<span style="font-size:14px;"><span style="font-size:14px;"><span style="font-size:18px;"><?xml version="1.0" encoding="utf-8" ?><configuration>    <startup>        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />    </startup>      <appSettings>        <add key="strCnn" value="server=***.***.**.***;database=jf_Charge;uid=sa;pwd=123456" />       <add key="DB" value="SQLServer"/>     </appSettings>              </configuration></span></span></span>

Because the storage structure and transactions are introduced here, we just give you the relevant code. Because the B and U layers are the same as those without stored procedures, we will not waste your time here, at that time, you can also find that in the program, it is executed as an SQL statement, and the specific effect is indeed not comparable to adding a few SQL statements in the program. 1. first of all, his structure is fixed and his learning is also convenient. What we need to change is not very good. although the role of the transaction and the If statement is very much desired, If we add too much on the D layer will inevitably feel redundant, but the transaction will not. 3. the running effect is faster. As for the reason, the stored procedure only compiles during creation and does not need to be re-compiled every time the stored procedure is executed. Generally, the SQL statement is compiled every time it is executed, therefore, using storage improves the execution speed of the database.

Differences and Similarities between logical structures and storage structures

Interesting copying... Explain that you have read the copied one.
The logical structure of data is also called the data structure, which is divided into two categories: linear structure and non-linear structure.
There are four storage structures: sequential storage, link storage, index storage, and Hash Storage.
The linear structure includes sequential algorithms and linked lists. That is to say, the first two types of storage structures use linear structure algorithms. A non-linear structure has at least one data element, which has two or more pioneers or successors. tree and binary tree are typical examples. The Index algorithm uses the tree structure, that is, it belongs to a non-linear structure algorithm. It is best to store hash columns. A typical example is that hash uses a random hash function, which is of course a non-linear structure algorithm.
It can be seen that the storage structure uses different logical structures, that is, two different algorithms. This is the relationship between them.

Storage structures of Linear Structures

There are two different Representation Methods for the relationship between data elements: sequential and non-sequential images, and two different storage structures are obtained: sequential and chained storage structures. Sequential storage method: stores logically adjacent nodes in storage units adjacent to physical locations. The logical relationship between nodes is reflected by the adjacent relationship of storage units, the resulting storage representation is called a sequential storage structure. The sequential storage structure is the most basic storage representation method. It is usually implemented by arrays in programming languages. Link storage method: it does not require logically adjacent nodes to be physically adjacent. The logical relationship between nodes is represented by an additional pointer field. The resulting storage representation is called the chain storage structure. The chain storage structure is usually implemented by the pointer type in the programming language. Ordered storage and linked storage are two basic data storage structures. In ordered storage, each memory contains information about the stored elements. The logical relationship between the elements is calculated by the position of the array subscript, if the subscript position of an element stored in the corresponding array is I, the subscript position of the element in the array is I 1, the subscript position of its successor element in the corresponding array is I + 1. A storage node in the Link contains not only the information of the stored elements, but also the information of the logical relationship between elements. Data indicates the value range, which is used to store. An element. Pl, p2 ,..., Pill (1n ≥ 1) is a pointer field. Each Wei value is the node where the corresponding successor element or precursor element is located (hereinafter referred to as the successor node or precursor node) you can access the corresponding successor node or precursor node through the pointer field (also called the chain field) of the node) the node of the link. If a pointer field in a node does not need to refer to point f, the value is null, represented by a constant N-LILL, NIJ] On iostream. h is defined as 0. Data Link storage is also called a chain table. When each node in the chain table contains only one pointer, it is called a single-chain table.