Tutorials VB.NET 2005In 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
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.
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.
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:
@@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:
insert into t (datavalue) values ('hello')
print @@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:
Production.ProductCategory where name = 'Aerospace'
To run a stored procedure:
In Server Explorer, expand the Stored Procedures folder.
(i)(ii)(iii)Using Parameters in Stored Procedures and User-Defined Functions.
(iv)
