cool hit counter Server Room Charging System (VB.NET) - Stored Procedure Practice_Intefrankly

Server Room Charging System (VB.NET) - Stored Procedure Practice


Copyright: This is an original post by the blogger and may not be reproduced without the blogger's permission. https://blog.csdn.net/huyuyang6688/article/details/38170191

The initial contact with stored procedures is in Mr. Geng Jianling's video, when only skimmed through, just an impression, know the term; sophomore year also has SqlServer database this course, but the teacher did not speak, they did not see; real understanding of stored procedures from the self-study exam in the "Principles of Database Systems", in the exam, know what stored procedures are for, how to write on paper, but never in the DBMS knocked by hand. So the server room charging system gave me that opportunity. Not much more will be said here about the stored procedure Basic knowledge of Germany, Just write about how it was used in the small project of the server room charging system stored procedure of。

Background.

There is a registration function in the server room charging system and the prototype diagram is as follows.

at the time of registration , need Perform updates to three tables in the database( wickham tableT_Card、 student formT_Student、 recharge formT_Register Add a new record in each), consequently, execution time, If you use the executionSQL The way the statement, That would require three executionsSQL statement:

1: Adding records to the card table T_Card

 insert into T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )

2: Adding records to the student table T_Student

 insert into T_Student (stuNumber,stuName,stuSex,stuMajor,stuGrade,stuClass  ,comment ) values (@stuNumber ,@stuName ,@stuSex ,@stuMajor ,@stuGrade ,@stuClass,@comment )

3: Add a record to the recharge table T_Register

insert into chargesystem.dbo.T_Recharge (userID,cardNumber,rechargeCash ,rechargeDate ,rechargeTime ,isChecked )values(@userID,@cardNumber ,@balance, CONVERT(varchar,getdate(),120),CONVERT(varchar,GETDATE(),108),' Outstanding accounts')

Use of Stored Procedures

For the above requirement, if you use a stored procedure, after creating the procedure in the database, you are only responsible for executing this procedure directly in the code, instead of connecting and manipulating the database multiple times in a row.

one、 establish stored procedure

There are two ways to create a stored procedure (since the database used for the system is SqlServer 2008, this is used as an example here).

(1), Manual creation of stored procedures.

Object Explorer: Database → ChargeSystem (database name) → Programmability → right-click "Stored Procedure" → New Stored Procedure

new construction stored procedure It can be said to be a well-established stored procedure German template, We just need to modify the above stored procedure name (of a thing)、 parameters、 Code to execute statements etc. onOK finish。

(2), SQL statements to add stored procedures

Direct new query, Write in the code edit window stored procedureSQL code, The basic syntax is: CREATE PROCEDURE PROC_NAME @[ Parameter Name] [ types],@[ Parameter Name] [ types]…… AS BEGIN [ process agent]......... END

created with the first method stored procedure It's basically the same structure, After adding the appropriate parameters and procedure body, complete stored procedure because of:

CREATE PROCEDURE PROC_Register
	--  Defining Parameters
	@cardNumber varchar(6),@balance decimal(5,1),@type nvarchar(20),@status nvarchar(50),@isChecked nvarchar(10),
	@stuNumber varchar(18),@stuName nvarchar(10),@stuSex varchar(6),@stuMajor nvarchar(30),@stuGrade nvarchar(20),@stuClass nvarchar(20),@comment nvarchar(100),
	@userID varchar(18)
AS
BEGIN
			
        -- Inserting data into a table
	insert into ChargeSystem .dbo.T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )
	insert into ChargeSystem .dbo.T_Student (stuNumber,stuName,stuSex,stuMajor,stuGrade,stuClass  ,comment ) values (@stuNumber ,@stuName ,@stuSex ,@stuMajor ,@stuGrade ,@stuClass,@comment )
	insert into chargesystem.dbo.T_Recharge (userID,cardNumber,rechargeCash ,rechargeDate ,rechargeTime ,isChecked )values(@userID,@cardNumber ,@balance, CONVERT(char(10),getdate(),120),CONVERT(varchar,GETDATE(),108),' Outstanding accounts')
END

note : Either way the procedure is created, it needs to be clicked and executed to save it to the server before it can be executed by a later call, simply saving (ctrl+C) just saves the procedure file locally and not to the database server.

When the procedure is created, you can create a new query and execute "exec procedure name @parameter1=value1,@parameter2=value2......" to verify if the procedure is correct.

Second, the code to call the stored procedure

Calling a stored procedure is much the same as executing a SQL statement. It should be noted that when executing a SQL statement, the CommandType of the Command object Command has the value CommandType.Text, while when executing a stored procedure, the CommandType has the value CommandType.StoredProcedure. In this example the specific code is:

D-layer code.

Public Class SqlServerRegisterDAL : Implements IDAL.IRegister
    Public Function Insert(ByVal enCard As Entity.CardEntity, ByVal enStudent As Entity.StudentEntity, ByVal userID As String) As Boolean Implements IDAL.IRegister.Insert
        Dim sqlHelper As New SqlHelper                                 ' meaningSqlHelper an actual example
        Dim cmdType As CommandType = CommandType.StoredProcedure       ' Defining the database command type
        Dim cmdText As String = "PROC_Register"                        ' Database execution string
        Dim parameters As SqlParameter()                               ' Defining Parameters arrays, accountable to stored procedure Variable passing values in
        ' Assign values to each of the parameters in the parameter array
        parameters = {New SqlParameter("@cardNumber", enCard.CardNumber), New SqlParameter("@balance", enCard.Balance),
                      New SqlParameter("@type ", enCard.CardType), New SqlParameter("@status", enCard.Status),
                      New SqlParameter("@isChecked", enCard.IsChecked), New SqlParameter("@stuNumber", enStudent.StuNumber),
                      New SqlParameter("@stuName", enStudent.StuName), New SqlParameter("@stuSex", enStudent.StuSex),
                      New SqlParameter("@stuMajor", enStudent.StuMajor), New SqlParameter("@stuGrade", enStudent.StuGrade),
                      New SqlParameter("@stuClass", enStudent.StuClass), New SqlParameter("@comment", enStudent.StuComment),
                      New SqlParameter("@userID", userID)}
        ' Determine if there are query results
        If sqlHelper.ExecuteNonQuery(cmdText, cmdType, parameters) Then
            Return True
        Else
            Return False
        End If
    End Function
End Class

SqlHelper Code.

Public Class SqlHelper
    ' Get the value of the connection string from the configuration file
    Dim strConnection As String = ConfigurationSettings.AppSettings("strConnection")
    ' Creating a database connection objectconn
    Dim conn As SqlConnection
    ' Creating database operation classescmd
    Dim cmd As New SqlCommand

    ''' <summary>
    '''  constructor (computing), Initialize the database connection object when instantiating the class
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub New()
        conn = New SqlConnection(strConnection)
    End Sub

    ''' <summary>
    '''  Close ReleaseSqlCommand targets
    ''' </summary>
    ''' <param name="cmd"> Required closuresSqlCommand targets</param>
    ''' <remarks>cmd.Dispose() Direct releasecommand sources, I wonder how this will affect system performance, Let's start with this., Optimization will continue later</remarks>
    Public Sub CloseCommand(ByVal cmd As SqlCommand)
        If Not IsNothing(cmd) Then
            cmd.Dispose()
            cmd = Nothing
        End If
    End Sub

    ''' <summary>
    '''  Close the database connection
    ''' </summary>
    ''' <param name="conn"> Required closuresSqlConnection targets</param>
    ''' <remarks> Close the database connection, But it didn't release, Instead, it is stored in the connection pool , need The time when you can also pass theOpen() Method to open the connection</remarks>
    Public Sub CloseConnection(ByVal conn As SqlConnection)
        If Not IsNothing(conn) Then
            conn.Close()
        End If
    End Sub

    ''' <summary>
    '''  parameterized  add  delete  change  operations
    ''' </summary>
    ''' <param name="cmdText"> Required implementationSQL command</param>
    ''' <param name="cmdType"> of the executed command, Generally.sql statement、 stored procedure or table</param>
    ''' <param name="sqlParameters"> Parameter array</param>
    ''' <returns> Returns the number of rows affected  Type is integer</returns>
    ''' <remarks></remarks>
    Public Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As String, ByVal sqlParameters As SqlParameter()) As Integer

       Try
           conn.Open()                                 ' Open database connection
           cmd.CommandText = cmdText                   ' Setting up a query statement
           cmd.CommandType = cmdType                   ' Set a value, account forcmdType( If the value ofStoredProcedure time, The call is for stored procedure)
           cmd.Connection = conn                       ' Setting up the connection
           cmd.Parameters.AddRange(sqlParameters)      ' Incoming parameters
    
           Dim affectedRows As Integer
           affectedRows = cmd.ExecuteNonQuery
           Return affectedRows                         ' Returns the number of lines affected by the execution
        Catch ex As Exception
           MsgBox(ex.Message, MsgBoxStyle.OkOnly, " Please note.")
           Return 0                                    ' If there is an error, return0
        Finally 
           cmd.Parameters.Clear()                      ' Clear parameters
           Call CloseCommand(cmd)                      ' Close and releaseCommand
           Call CloseConnection(conn)                  ' Close the connectionconn
        End Try

    End Function   
End Class

Why Use Stored Procedures

Registering student card numbers is not a big module in this system, but this small requirement requires dealing with data from three tables in the database, and in the previous operation, it was surprising that the database was connected three times and SQL statements were executed three times.

This frequently opens and closes connections to the database , need Consumes significant system resources, Reduced execution speed。 This is the time to consider using stored procedureto execute so many SQL statements instead.

1, general SQL statements need to be compiled every time they are executed, while stored procedures are only compiled at the time of creation, and do not need to be compiled again for each subsequent execution.

2, the stored procedure is the equivalent of a collection of multiple SQL statements that need to be executed, into a SQL statement, of course, you only need to connect and execute once to get the results.

3. High security. You can specify the usage rights of stored procedures to prevent SQL injection.

4. It is easier to upgrade and maintain the system.

To sum up.

★ When it comes to Multiple SQL statement execution , need Multiple connections to the database or need Working with multiple tables When you can encapsulate these operations together, i.e., create stored procedures and call them directly each time you need them later to perform all operations, avoiding opening and closing database connections multiple times.

★ When it comes to More complex requirements When (for example, the machine room charge system in the next machine to settle the amount of consumption can be used stored procedures), such as sorting, calculation, etc., you can pass the data directly to the stored procedures, a series of operations in the database server, reducing the data traffic between the client and the server, but also to ensure the security of the system.

By this point you may have a question in your head: since you are having a stored procedure perform multiple tasks, what if one or more of these tasks do not complete during execution? That's where affairs come in handy... Go practice...


Recommended>>
1、What is the first thing you learn first when you first start Python Get your first program across the board
2、Teach you how to quickly convert Word to PDF
3、UG CNC programming tips clever use of the slope analysis command out of the horizontal and vertical plane
4、Getting to know Python for the first time
5、The day humans finally felt the fear of being replaced by AI

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号