In this tutorial you will learn further more about Access and Manipulate SQL Server data – Using Stored Procedures, Creating a Stored Procedure, Running Stored Procedures from .NET, Using Parameters in Stored Procedures, The @@IDENTITY Variable
Access and Manipulate SQL Server data – Using Stored Procedures
Using Stored Procedures
Stored procedures are an important aspect in all database programs. VB.NET applications are no exceptions to this rule. Stored procedures enable users change the business logic without actually tinkering with the application. SQL Server 2005’s (formerly code named Yukon) integration with the .NET CLR makes it possible for developers to author stored procedures, triggers, user defined functions, and create other database objects using a managed language such as VB.NET. This excellent feature provides a number of benefits, including increased productivity, significant performance gains, and the ability to leverage the features of .NET Code Access Security to prevent assemblies from performing certain operations.
Creating a Stored Procedure
Stored procedures for SQL Server 2005 databases can now be written in managed code. SQL Stored procedures can be created by adding Stored Procedure items to SQL Server projects. Once the stored procedure created in managed code, is deployed it can be executed like any other stored procedure. Let us understand this process by working on an example.
1. Create a new Project and
2. Choose Windows application.
3. In the solution explorer right click and add new item.
4. Choose Stored Procedure in the dialog box.
5. Replace the code with the one given below:
Click here to view sample code
The stored procedure has been created. But what does it do? Press F5 to execute the procedure and look at the output.
Using Parameters in Stored Procedures
Parameters allow you to create flexible SQL routines that use values provided at run time. Because the parameters can be changed each time the procedure runs, users should avoid creating one SQL routine for each value that has to be matched. For example, in a Microsoft SQL Server database, we can add a @ProductCategoryID parameter to a stored procedure that searches the Production.productCategory table for Product category name. We can then run the stored procedure each time we want to specify a different ProductCategoryID. When parameters are used in stored procedures or user defined functions it will:
-
Return a status value to another SQL routine to indicate success or failure and the reason for the failure.
-
Pass parameter values to other functions or stored procedures.
The @@IDENTITY Variable
@@IDENTITY is a Global Variable used in SQL Sever. The value of this global @@Identity variable is set to the value of the identity chosen for the row inserted after performing an insert into a table with a column that is an identity. In cases where multiple rows are inserted, the last value will be set. Let us see an illustration:
create table t(
keyvalue int identity(1,1),
datavalue char(10)
)
when these statements are run the value 1 will be printed and then in the next run value 2 will be printed. This is because users tend to make the common mistake of not assigning a local variable immediately on insert.
insert into t (datavalue) values (‘hello’)
print @@Identity
The following code will also not work and it will also degrade performance when the number of records increase.
declare @Inserted_Key int
insert into t (datavalue) values (‘hello’)
select @Inserted_Key = max(keyvalue) from t
Secondly there is no gurantee that correct value of the column would be obtained to the variable, as the SQL Server is a multi-User Server. Alternatively, we can use the following kind of implementation to give a desirable performance:
declare @Inserted_Key int
insert into t (datavalue) values (‘hello’)
set @Inserted_Key = @@Identity
The identity inserted will be saved in a local variable.
|
Note: The user must have Visual Studio Beta 2 and SQL Server 2005 with Adventure works.mdb to see the output. |
The deployment unit for managed code is called an assembly. An assembly is packaged as a DLL or executable (EXE) file. While an executable can run on its own, a DLL must be hosted in an existing application. SQL Server can load and host managed DLL assemblies. To load an assembly into SQL Server, we need to use the Create Assembly statement.
CREATE ASSEMBLY InsertPrCategory
FROM (Path and assembly name.)
Running Stored Procedure from .NET
In Solution Explorer, expand the TestScripts folder and double-click the Test.sql file. Replace the code in the Test.sql file with the following code:
EXEC InsertPrCategoryName ‘Aerospace’
SELECT * from Production.ProductCategory where name = ‘Aerospace’
Press F5 to build, deploy, and debug the stored procedure.
To run a stored procedure:
In Server Explorer, expand the Stored Procedures folder.
(i) Right-click the name of the stored procedure that you want to run.
(ii) Choose Execute on the shortcut menu. The Output window shows the status of the stored procedure.
(iii) If the stored procedure contains parameters, the Run Stored Procedure dialog box prompts you for the parameter values. In the Value column, type a value for each parameter that appears in the Name column, and then click OK. For details about parameters, see Using Parameters in Stored Procedures and User-Defined Functions.
(iv) The Output window is updated with status information as the stored procedure is run. The Output window also displays result sets and return values for the stored procedure.
