Data tables define how to store data in the database, but not explain how to access data. We also need to understand write records to call the details of the selected line and column again from the table. Developers typically write some special query statements in their code for reading and writing data. This will not only lead to low efficiency, but also provide security issues. In this application, all data access work will be processed through the SQL Server stored procedure (Stored Procedures, sometimes referred to as "Stored Procs" or "SPROCS"). Using a stored procedure can improve the performance of the solution and make it safer. In addition, the use of stored procedures can increase the abstraction level of the data layer, so that other parts of the solution are not affected by small data layout and format changes. This allows your solution more reliable and easier to maintain.
Why not use special query statements
We often see the following articles and code examples:
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
There are several reasons why the above code does not meet the requirements. First, if you nested the SQL query statement in the code, then you must edit and recompile the code layer as long as the data layer changes. This will bring a lot of inconvenience. It may also cause other errors, and usually cause confusion between data services and code.
Secondly, if you use a string connection that is not entered ("... where id =" & id.tostring ()), you may make your application under hacking attack. More importantly, this will provide malicious users with opportunities to add other SQL keywords in your code. For example, according to your input mode, the malicious user can input 13 or 21 as a valid table ID, but also input 13; delete from users, or other statements that may cause hazards. Perfect input verification protects your system from the attack of most SQL insertion code, so it is best to completely delete all built-in SQL statements, so that attackers are difficult to abuse your application data.
Finally, the execution speed of the built-in SQL statement is much slower than the storage process. When you create a stored procedure and store it into a database, SQL Server evaluates them and stores them in an optimized form, making it easier to run for SQL Server at runtime. If you use a built-in special query statement, you must perform this evaluation before each run. For those applications used by users, you may need to conduct hundreds of evaluation of the same query statement per minute.
Instead, the stored procedure can maintain the simplicity of the code, and additional security can be provided and can improve the performance of the solution. These are the reasons for discarding the built-in query statement and the use of stored procedures.
Add the stored procedure to the Visual Studio .NET database project
Creating a stored procedure using Visual Studio .NET 2003 is very simple. First, you need to open a database project. This operation is already
"Creating Database Database Use VS.NET2003" is done. You can then create a stored procedure using the Code Template, or you can use the Visual Studio .NET 2003 to edit new stored procedures using Visual Studio .NET 2003 for databases. This article focuses on how to directly edit the stored procedures directly for the connected database server. Take later how to generate all result scripts for future remote server installations. Introduction Before using the Mechanism of Writing a stored procedure using Visual Studio .NET 2003, it is also important to emphasize several general issues related to creating a reliable stored procedure. First, it is best to see the entire process of creation and execute the stored procedure as a mature member of the multi-layer application model. The stored procedure provides a method of programming your data access. This allows you to better control the entire solution and improve its efficiency. That is, the stored procedure collection should be regarded as a separate layer in the application. Excellent data access policies should allow stored procedures to exist as separate components. That is, the details of security, error handling, and other constituting excellent component layers are required in the stored procedure. More importantly, the T-SQL language should be accessed like in other advanced programming environments, rather than just ways to generate a database query.
Note: Now, I suspect that some readers may think that they don't intend to program SQL Server, or that this work is best to remain to those DBAs. Although the experience of the database administrator will help, it must be a rocket scientist (here the skillful programming expert) can do SQL Server programming work well. Like other languages, this language also needs to spend a certain amount of time and mastery through certain practices, which is not too different from other languages. If you can program in Microsoft Visual Basic® .NET, you can program in T-SQL.
Add a stored procedure using Visual Studio .NET
The following detailed describes how to add the stored procedure to the existing SQL Server database in Visual Studio .NET 2003. You need to open a new stored procedure template using the Server Explorer to edit, and then save it to the database. Here is an example of step-by-step implementation:
· Open Visual Studio .NET and open an existing database item (such as project started in front of this article) or start a new project.
· In Server Explorer, expand the Data Connections tree, find the database you want to use, then right-click on the Stored Procedures node, open the context-related menu .
· Select New Stored Procedure from the context-related menu, open a stored procedure template in the Visual Studio .NET Editor space. Now you can type content.
· After completing editing, simply close the page being edited in the editor, Visual Studio .NET saves the content to the database using the name of the stored procedure. If the type of content is incorrect, the editor reports these errors to you and you can fix these errors before saving stored procedures.
Below is a simple example of the stored procedure, which returns a topic list.
Create Procedure TopicsgetList
AS
Set NoCount ON - Do not return the affected row
SELECT
ID,
Title,
Description
From
Topics
ORDER BY
Titlereturn @@ error
In this example, there is a need to point out. First, please pay attention to the Set NoCount ON line. It tells SQL Server to stop the number of rows that are affected to this query and stop to return to the calling function. This is an unnecessary additional work. Second, Return @@ Error at the end is important. This row code returns an integer value that occurs in SQL Server. You can use this code to complete other diagnostics and error handling operations in the call routine. You don't need to perform any action now, but they are two habits that should be followed when creating a stored procedure.
Here is a more complex stored procedure. This process is used to retrieve a single topic record from the database. You will find some additional items, including input parameters, return the output parameters of a particular value, and check the input parameters and return some of the program code that is incorrect when needed.
Create Procedure TopicsGetItem
(
@Admincode char (3),
@ID INT,
@Title Varchar (30) Output,
@Description VARCHAR (500) OUTPUT
)
AS
Set NoCount ON - Do not return the affected row
- Make sure to be an admin user
IF @admincode <> 'ADM'
Begin
Return 100 - Invalid admin error
End
- Check if the record exists
IF (Select Count (ID) from Topics Where ID = @ ID) = 0
Begin
Return 101 --- invalid ID code
End
- Continue execution and return to this record
SELECT
@ Title = Title,
@ Description = description
From
Topics
WHERE
ID = @ ID
- Return to errors, return 0 if successful
Return @@ error
In this example, there are still some need to point out. First, you will see a list of parameters at the top of the stored procedure. In addition to the first two parameters, other parameters are marked as OUTPUT parameters. These parameters are used to return the value of the selected record. The return value using a record is more efficient than the record collection with the returned with all fields.
Second, you will find the T-SQL data block used to check the @adminCode parameter value to ensure that the correct code is passed. If the passed the code is incorrect, the return code 100 is passed and the process is stopped. First, you will find the check @ID parameter to make sure it represents an existing record. If it is not an existing record, the return code 101 is transmitted and executed. Finally, if the input variable is valid, the stored procedure will attempt to select the record and return the corresponding value. If any errors occur, the last row code of the process will be processed.
Note: Usually, it is best to save custom error code and its meaning in a separate table in the database, or saved in a text file that can be accessed. This will easily update these error codes and share with other subsystems in the solution. Because this is just a short example, only two error codes are used, so I decided to create a document containing a lot of code and messages for other subsystems.
The stored procedure contained in this solution exceeds 25. This article only gives an example, and other code can be downloaded by links starting at this article. Last this example uses a custom built-in scalar function.
Use custom scalar functions
Sometimes a single stored procedure is not enough to solve the problem. For example, there is a program request to list a solution number of questions in our user program. One way to solve this problem is to generate a sub-query that counts questions. Another method is to generate a custom function, return scalar values and contain it in a problem query. This approach has a benefit, that is, we can use the scalar function again during other stored procedures. The operation of adding custom functions is similar to the addition of stored procedures. In the Server Explorer tree, right-click on the Functions node of the selected database and select New Scalar-Valued Function from the context-related menu. The document is then edited in the editor and saves the document as saving the stored procedure.
The following is the code for custom functions:
Create function dbo.fn_questionsgetResponsecount
(
@ID INT
)
Returns Int
AS
Begin
Declare @Responsecount Int
Set @Responsecount =
(
SELECT
Count (responses.id)
From
Responses
WHERE
Responses.questionId=@id
)
Return @Responsecount
End
The following is a stored procedure using a custom function:
Create Procedure QuestionsgetCountWithnoSponses
(
@Total int output
)
AS
Set NoCount ON - Do not return the affected row
SELECT
@ Total = COUNT (ID)
From
Questions
WHERE
DBO.FN_QuestionsGetResponsecount (Questions.ID) = 0
Return @@ error
Understand how to write a stored procedure and custom functions, we will also discuss another problem when using Visual Studio .NET 2003, that is, security issues.
