Create an ASP. NET data storage layer (3)

Use Visual Studio. NET 2003 to write stored procedures
A data table defines how to store data in a database, but does not describe how to access data. We also need to know the Read and Write records so that the detailed information of the selected rows and columns can be called again from the table. Developers usually write special query statements in their code to read and write data. This will not only lead to low efficiency, but also bring security issues. In this application, all data access work will be handled through the SQL Server stored Procedure (stored procedures, sometimes referred to as "stored procs" or "sprocs. Using Stored Procedures improves the performance of the solution and makes it safer. In addition, using stored procedures can increase the abstraction level of the data layer, thus protecting other parts of the solution from the impact of small data la S and format changes. This makes your solution more reliable and easy to maintain.
Why not use special query statements?
We often see the following articles and sample code:
Private Function GetSomeData (ByVal ID As Integer) As SqlDataReader
Dim strSQL As String
StrSQL = "SELECT * FROM MyTable where id =" & ID. ToString ()
Cd = New SqlCommand
With cd
. CommandText = strSQL
. CommandType = CommandType. Text
. Connection = cn
. Connection. Open ()
Return. ExecuteReader (CommandBehavior. CloseConnection)
End
End Function
The above Code does not meet the requirements for the following reasons. First, if you nest SQL query statements in the code, you must edit and re-compile the code layer as long as the data layer changes. This will cause a lot of inconvenience. It may also cause other errors, and usually lead to confusion between data services and code.
Second, if you use a string that is not verified by the input ("... where id = "& ID. toString (), which may expose your application to hacker attacks. More importantly, this gives malicious users the opportunity to add other SQL keywords to your code. For example, based on your input mode, a malicious user can enter not only 13 or 21 as valid table IDs, but also 13; delete from users or other statements that may cause harm. Perfect input verification can protect your system from most SQL insert code attacks. Therefore, it is best to delete all built-in SQL statements completely, making it difficult for attackers to abuse your application data.