Create ASP.net data storage layer (4)

Asp.net| Create | Data add stored procedures using Visual Studio. Net

The following is a detailed description of how to add a stored procedure to an existing SQL Server database in Visual Studio. NET 2003. You need to use Server Explorer to open a new stored procedure template, edit it, and then save it to the database. Here is an example of step-by-step implementation of this process:

Open Visual Studio. NET, and then open an existing database project, such as the one that was started earlier in this article, or start a new project.

In Server Explorer, expand the Data connections tree, locate the database (DotNetKB) that you want to use, and then right-click the Stored procedures (stored procedure) node, Open the context-related menu.

Select New Stored Procedure (new stored procedure) from the context-related menu to open a stored procedure template in the Visual Studio. NET editor space. Now, you can type the content.

When you finish editing, just close the page that you are editing in the editor, and Visual Studio. NET will use the name of the stored procedure to save the item to the database. If you type incorrectly, the editor reports these errors to you, and you can fix them before you save the stored procedure (see Figure 11).

The following is a simple example of a stored procedure that returns a list of topics.

CREATE PROCEDURE Topicsgetlist
As
SET NOCOUNT on--does not return the value of the affected row
SELECT
Id
Title,
Description
From
Topics
ORDER BY
Title
return @ @ERROR

In this example, there are a few points to be noted. First, note the SET NOCOUNT on line. It tells SQL Server to stop calculating the number of affected rows for the query and stops returning the value to the calling function. This is an unnecessary extra work. Second, the return @ @ERROR line at the end is important. This line of code returns the integer value of the error that occurred in SQL Server. You can use this code in the calling routine to complete other diagnostics and error handling operations. You don't need to do anything now, but they are two good habits to follow when creating a stored procedure.

The following is a more complex stored procedure. This procedure is used to retrieve a single topic record from the database. You will find additional items, including input parameters, output parameters that return a specific value, and some program code that examines the input parameters and returns an error if needed.

CREATE PROCEDURE Topicsgetitem
(
@AdminCode char (3),
@ID int,
@Title varchar () OUTPUT,
@Description varchar (+) OUTPUT
)
As
SET NOCOUNT on--does not return the value of the affected row
--Be 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---invalid ID code
End
--Continue execution and return the record
SELECT
@Title =title,
@Description =description
From
Topics
WHERE
Id= @ID
--Returns an error, returns 0 if successful
return @ @ERROR
In this example, there are a few points to be noted. First, you'll see a list of parameters at the top of the stored procedure. Except for the first two parameters, the other parameters are marked as OUTPUT parameters. These parameters are used to return the value of the selected record. It is more efficient to use a record return value than to return a collection of records with all the fields.

Second, you'll find T-SQL data blocks used to check @AdminCode parameter values to ensure that the correct code is passed. If the code passed is incorrect, pass return code 100 and stop executing the procedure. Second, you'll find that checking @ID parameters to ensure that they represent an existing record. If it is not an existing record, the transfer returns code 101 and terminates execution. Finally, if the input variable is valid, the stored procedure attempts to select the record and return the corresponding value. If any errors occur at this time, the last line of code for the procedure is processed.

Note: In general, it is a good idea to save your custom error code and its meaning in a separate table in the database, or in a text file that the solution can access. This makes it easy to update these error codes and share them with other subsystems in the solution. Because this is just a short example with only two error codes, I decided to create a document that contains a lot of code and messages for reference by other subsystems.

The solution contains more than 25 stored procedures. This article is illustrated by just one example, and other code can be downloaded from the link at the beginning of this article. The last example uses a custom built-in scalar function.

Using custom scalar functions

Sometimes, a single stored procedure is not sufficient to resolve the problem. For example, there is one scenario in our user scenario that requires you to list the number of answers to a problem. One way to resolve this problem is to generate a subquery that counts the answers to the questions. Another method is to generate a custom function that returns a scalar value and includes it in the problem query. Another benefit of this approach is that we can use the scalar function again in other stored procedures.

The action to add a custom function is similar to adding a stored procedure. In the Server Explorer tree, right-click the functions (function) node of the selected database, and then select the new scalar-valued function (new scalar value function) from the context-related menu. Then edit the document in the editor and save the document as you would save the stored procedure.

The following is the code for the custom function:

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 are stored procedures that use custom functions:

CREATE PROCEDURE questionsgetcountwithnoresponses
(
@Total int OUTPUT
)
As
SET NOCOUNT on--does not return the value of the affected row

SELECT
@Total =count (ID)
From
Questions
WHERE
Dbo.fn_questionsgetresponsecount (questions.id) =0

return @ @ERROR
After understanding how to write stored procedures and custom functions, we will also discuss another issue with creating a data tier using Visual Studio. NET 2003, security issues.