Stored Procedures, database Stored Procedures

Stored Procedures, database Stored Procedures

As mentioned in the previous blog, when the child form overrides the GetdbName method of the parent form, you only need to return the database table to be operated.

This blog explains why you only need to return the table name.

I. Stored Procedure

A stored procedure is defined in a database, which is equivalent to directly calling this method when a method is used. It is very abstract.

Is encapsulated in the database for easy use.

Method: A system stored procedure is programmable in your own database. Right-click to create a stored procedure and execute it.

The following describes the stored procedure.

     
USE [charge] GO/****** Object: StoredProcedure [dbo]. [PROC_GroupCheck] Script Date: 20:23:54 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongoalter procedure [dbo]. [PROC_GroupCheck] -- parameter -- @ CombName1 varchar (50), @ CombOper1 varchar (50), @ txtContent1 varchar (50), @ CobRelation1 varchar (50 ), @ CombName2 varchar (50), @ CombOper2 varchar (50), @ txtContent2 varchar (50), @ CobRelation2 varchar (50), @ CombNam E3 varchar (50), @ CombOper3 varchar (50), @ txtContent3 varchar (50), @ tableName varchar (50) AS -- Define parameters for storing SQL statements -- declare @ TempSql varchar (500) BEGIN -- String concatenation Method -- SET @ TempSql = 'select * from' + @ tableName + 'where' + char (32) + @ CombName1 + @ CombOper1 + char (39) + @ txtContent1 + char (39) -- if @ CobRelation1 when link 1 is not empty! = ''Ininset @ TempSql = @ TempSql + char (32) + @ CobRelation1 + char (32) + @ CombName2 + @ CombOper2 + CHAR (39) + @ txtContent2 + CHAR (39) if @ CobRelation2! = ''Ininset @ TempSql = @ TempSql + CHAR (32) + @ CobRelation2 + CHAR (32) + @ CombName3 + @ CombOper3 + CHAR (39) + @ txtContent3 + CHAR (39) ENDEND -- EXECUTE SQL statement -- EXECUTE (@ TempSql) END

There are fixed parameters in this process, which indicates that similar forms are used.

Char (32) represents space; char (39) represents single quotes

The above is the defined stored procedure, which can be called directly at Layer D when used.

     
Public Function CreateGroupCheck (groupcheck As Entity. groupCheckEntity) As DataTable Implements IGroupCheck. createGroupCheck instantiate SqlHelper Dim helper As New SqlHelper Dim SQL As String 'call Stored Procedure SQL = "PROC_GroupCheck" 'to define this operation type As stored procedure Dim ready type As CommandType = CommandType. storedProcedure assigns a value to the parameters in the stored procedure. The parameter name must correspond to Dim paras As SqlParameter () = {New SqlParameter ("@ CombName1", groupcheck. combName1), New SqlParameter ("@ CombOper1", groupcheck. combOper1), New SqlParameter ("@ txtContent1", groupcheck. txtContent1), New SqlParameter ("@ CobRelation1", groupcheck. cobRelation1), New SqlParameter ("@ CombName2", groupcheck. combName2), New SqlParameter ("@ CombOper2", groupcheck. combOper2), New SqlParameter ("@ txtContent2", groupcheck. txtContent2), New SqlParameter ("@ CobRelation2", groupcheck. cobRelation2), New SqlParameter ("@ CombName3", groupcheck. combName3), New SqlParameter ("@ CombOper3", groupcheck. combOper3), New SqlParameter ("@ txtContent3", groupcheck. txtContent3), New SqlParameter ("@ tableName", groupcheck. tableName)} 'Return the queried data Return helper. execSelects (SQL, partition type, paras) End Function

In this way, you only need to pass in the name of the table to be operated when using the stored procedure, which greatly reduces code redundancy.

Ii. triggers

The trigger can automatically synchronize and change the data in the data table. The trigger works in the same way as the stored procedure, but only when the trigger is executed

It is automatic and does not need to be considered as an operation. For example, if both tables have student information, use the trigger to delete or modify the student information in a table.

The student information in the other table will be changed accordingly. This is the working principle of the trigger.

Iii. Summary

Do not be afraid when you are exposed to new technologies. You should be happy because you have learned it again, and after learning it

After learning the full picture of it, I feel that it is not as difficult as it was when I first got to know it.

Solving difficulties is a good opportunity for us to learn and improve.