Stored Procedure and I

It can be said that the word stored procedure is really a witness of my learning experience. I still remember that when I was learning ASP, I only used simple SQL statements to write programs, at that time, I learned ASP and made a website for a very small company. The company was a logistics company that operated Yangtze river transportation. In the past, their company homepage was static and published news or something, all of them are very troublesome. No one in the company can do this. They can only make the messages HTML and mount them up. I have the opportunity to get this small project, use ASP for news publishing, and develop the human resources exchange platform. It can be said that all functions were implemented at that time, but security was not taken into account at all. For example, I did not notice the longest SQL injection attack. Fortunately, it was a small company, there is nothing that is too important. Otherwise, the consequences may be very serious. Let's take a look at how I wrote code at that time!

StrSQL = "SELECT Count (*) FROM Admin WHERE username = '" & strUserName & "' AND password = '" & strPwd &"'"
Set rs = SERVER. CreateObject ("ADODB. RecordSet ")
Rs. open conn, strSQL, 1, 1

What is an injection attack? strUserName and strPwd are input from the user, and there is not much test. If the user enters the following Username: 'OR 1 = 1 -- in this case, password verification is not required. At that time, we could say that we were not experienced in the first time.
Later, I was curious to see the stored procedure in the book, but I was not sure about its principle and confused by its complicated usage. The benefits of using stored procedures were not found until a management system was recently developed for a laboratory. NET can be moved to VS.net 2003 for all the operations performed by the Database Enterprise Manager. This is both intuitive and convenient, and I am really excited. Now I have just realized the sweetness of using stored procedures. Let's talk about some of my experiences and the advantages of stored procedures.
Stored procedures are a concept I don't want to talk about. Why use stored procedures,
1. stored procedures are more effective than executing a single SQL statement directly in your program.
Ii. Since stored procedures can be written in a mix of SQL statements and control statements, you can compile complex stored procedures to work on them.
Like a subroutine, a stored procedure can contain parameters, which makes them more flexible than SQL statements constructed on a temporary basis. Another
The NET platform also provides data structures such as DataReader and DataSet, which can return good results.
3. The stored procedure has the same interface independence. We can change the internal code of the stored procedure without modifying the application.
Iv. Security. This is what I mentioned at the beginning. Using Stored Procedures can enhance code security.
V. In addition, stored procedures can work with various controls to facilitate various special functions. I will give an example below.

Well, if you say so much, it may be a bit cool, but there is only one purpose, that is, you want to fall in love with the stored procedure and use it as early as possible, unlike me, it was not found its beauty until now! The following is an example of using a stored procedure to display the DataGrid Control by page.

Problem: As we all know, the DataGrid Control provides the automatic display by PAGE function. However, as the data volume increases, the efficiency of this paging mechanism becomes a question mark. Fortunately, ASP. NET provides an interface for us to define our own pages, so what we need to do is to construct our own dataset. The best method is stored procedure. The following is the practice:
1: Enable custom paging:

<Asp: datagrid runat = "server" id = "grid"
AllowPaging = "true"
AllowCustomPaging = "true"
OnPageIndexChange = "PageIndexChanged">

2: pagination function: void PageIndexChanged (Object sender, DataGridPageChangedEventArgs e ){
Grid. CurrentPageIndex = e. NewPageIndex;
Int n = grid. CurrentPageIndex;
Grid. DataSource = CreateDataSource (n); // create a bound Data Source
Grid. DataBind ();
}

3: Construct a dataset. The following is the stored PROCEDURE: create procedure dbo. SelectSubRecords
/**//*
Function: obtain news records from laboratories of various disciplines
Author: Stone
Created on: 2006-03-02
Modified on: 2006-03-02
*/
(
@ StartRow int = 0,
@ StopRow int,
@ Type int,
@ Lab int
)
AS
Declare @ t_table table/** // * Create a sequence table */
(
Rownum int IDENTITY (1, 1) Primary key not null,
ID int,
Type int,
Lab int,
Subject varchar (200 ),
Content varchar (1, 4000 ),
Author varchar (20 ),
CreateTime datetime
)
Set RowCount @ StopRow

/** // * Fill in the temporary table */
INSERT @ t_table
(ID, Type, Lab, Subject, Content, Author, CreateTime)
Select id, Type, Lab, Subject, Content, Author, CreateTime
FROM Artical_Info
WHERE Type = @ type AND Lab = @ lab
ORDER By CreateTime Desc

/** // * Obtain the dataset from the temporary table */
SELECT * FROM @ t_table WHERE rownum >=@ StartRow
RETURN

In this way, you may achieve paging display as required, and avoid the impact of the ID holes left in the table on the display of records due to the deletion operation.
This example is just one point. I don't want to use the stored procedure well. It is also a good tool for views. I will summarize it when I have a chance next time. I hope that my database knowledge will continue to improve. Now I am working on the database principle and combining theory with practice. I think it is what I want to do now, my girlfriend told me to go down and play badminton. She will have a match tomorrow. No way, so I have to spend time with them!