Create ASP.net data storage layer (3)

asp.net| Create | Data write stored procedures using Visual Studio. NET 2003

A datasheet defines how data is stored in a database, but does not specify how data is accessed. We also need to understand the read and write records to call the details of the selected rows and columns from the table again. Developers typically write special query statements in their code to read and write data. This not only leads to inefficiency, but also poses a security problem. In this application, all data access work will be handled through SQL Server stored procedures (stored procedures, sometimes referred to as "stored procs" or "sprocs"). Using stored procedures can improve the performance of your solution and make it more secure. In addition, using stored procedures can increase the level of abstraction of the data tier, protecting other parts of the solution from the impact of small data layouts and formatting changes. This makes your solution more reliable and easier to maintain.

Why not use special query statements

We often see articles and code examples that are shown below:

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 With
End Function

The above code does not meet the requirements for the following reasons. First, if you nest SQL query statements in your code, you must edit and recompile the layers of code as long as there is any change in the data layer. This will bring a lot of inconvenience. It can also cause other errors, and often creates confusion between the data service and the code.

Second, if you connect by using a string that is not validated by input ("...)." WHERE id= "& ID. ToString ()), which may expose your application to hacker attacks. More importantly, this will give malicious users the opportunity to add additional SQL keywords to your code. For example, depending on your input pattern, a malicious user can enter not only 13 or 21 as a valid table ID, but also 13; DELETE from USERS or other statements that may be harmful. Sophisticated input validation protects your system from most SQL-injected code, so it's a good idea to completely remove all the built-in SQL statements, making it hard for attackers to misuse your application data.

Finally, the built-in SQL statement executes much more slowly than the stored procedure. When you create a stored procedure and store it in a database, SQL Server evaluates its text and stores it in an optimized form, making it easier to use for SQL Server at run time. If you use a built-in special query statement, you must make this assessment before you run the code. For applications that are used by a large number of users, the same query statement may need to be evaluated hundreds of times per minute.

Instead, stored procedures can keep your code simple, provide additional security, and improve the performance of your solution. These are the reasons to discard built-in query statements and use stored procedures.

To add a stored procedure to your Visual Studio. NET database Project

Creating a stored procedure with Visual Studio. NET 2003 is simple. First, you need to open a database project. This is done in the first section of this article. You can then use the code template to create the stored procedure, or you can edit the new stored procedure directly using Visual Studio. NET 2003 for the database that is connected in the Server Explorer window. This article focuses on how to edit a stored procedure directly for a connected database server. Later, you will learn how to generate all the result scripts for a future remote server installation.

Before introducing the mechanism for writing stored procedures using Visual Studio. NET 2003, you should also highlight several general issues related to creating a reliable stored procedure. First, it is a good idea to consider the entire process of creating and executing stored procedures as a full-fledged member of the multi-tier application model. Stored procedures provide a way to program your data access. This allows you to better control the entire solution and improve its efficiency. That is, the collection of stored procedures should be treated as a separate layer in the application. Good data access policies should allow stored procedures to exist as stand-alone components. That is, security, error handling, and other details that make up a good component layer are required in the stored procedure layer. More importantly, you should access the T-SQL language as you would in other high-level programming environments, rather than just use it as a way to generate database queries.

Note: Now, I suspect that some readers may be thinking that they are not going to program SQL Server, or that the work is best left to the DBAs to do. While it helps to have database administrator experience, it's important to be a rocket scientist (a highly skilled programming expert here) to do a good job of SQL Server programming. Like any other language, this language also takes a certain amount of time and is mastered by some practice, and at this point it is not much different from other languages. If you can program in Microsoft Visual Basic. NET, you can also program in T-SQL.