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 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:
Partial Public Class StoredProcedures
Public Shared Sub InsertPrCategoryName(ByVal CategoryName As SqlString)
Dim InsertPrCategoryCommand As SqlCommand = SqlContext.GetCommand()
InsertPrCategoryCommand.CommandText = "INSERT into Production.ProductCategory(Name) VALUES('" & NameStr & "')" InsertPrCategoryCommand.ExecuteNonQuery()
The stored procedure has been created.