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:

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes 
Imports System.Data Imports System.Data.Sql Imports System.Data.SqlServer Imports System.Data.SqlTypes 
Partial Public Class StoredProcedures 
    <SqlProcedure()> _
   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()
    End Sub
End Class

The stored procedure has been created.

  Modified On Sep-18-2014 01:23:53 PM

Leave Comment