Login   |   Signup  
Exforsys

VB.NET 2005

  1. VB.NET 2005 Free Training
  2. The .NET Framework Architecture Part 1
  3. The .NET Framework Architecture Part 2
  4. Application Class and Message Class
  5. Implementing Class Library Object
  6. Visual Studio.NET Namespaces
  7. .NET Assemblies
  8. Differences between VB.NET 1.0 and VB.NET 2.0
  9. Introducing VB.NET Windows Forms
  10. Visual Studio Windows Forms Designer
  11. Exploring the Forms Designer generated code
  12. Setting and Adding Properties to Windows Form
  13. Implementing Inheritance
  14. Event Handling In Visual Basic .NET
  15. Building Graphical Interface elements
  16. .NET Common Windows Forms Controls Part 1
  17. .NET Common Windows Forms Controls Part 2
  18. Common Controls and Handling Control Events
  19. DomainUpDown and NumericUpDown Controls
  20. Dialog Boxes in Visual Basic .NET
  21. Visual Studio Adding Controls to Windows Form
  22. VB.NET Validation Controls
  23. Working with Menu Controls
  24. VB.NET MDI Applications
  25. .NET Exceptions
  26. VB.NET Creating and Managing Components Part 1
  27. VB.NET Creating and Managing Components Part 2
  28. Simple Data Binding
  29. .NET Complex Data Binding
  30. .NET Data Form Wizard
  31. Data Manipulation with ADO.NET
  32. SQL Server Stored Procedures
  33. SQL Server Ad Hoc Queries
  34. Finding and Sorting Data in DataSets
  35. ADO.NET Object Model
  36. Working with DataSets
  37. Using XML Data
  38. Working with File System in .NET
  39. Creating Web Service
  40. Instantiating - Invoking Web Services, Creating Proxy Classes with WSDL
  41. Web Reference and Web Services
  42. Web Services - SOAP, WSDL, Disco and UDDI
  43. Web Application Testing in VB.NET 2005
  44. Web Application Tracing and Debugging
  45. Working with Legacy Code and COM Components
  46. ActiveX Controls and Legacy Code
  47. Windows Application Testing
  48. VB.NET Windows Application Testing
  49. Tracing VB.NET Windows Application
  50. Debugging Windows Applications In Visual Studio.NET 2005
  51. Deploying Windows Applications In Visual Studio.NET 2005
  52. Customizing Setup Project in Visual Studio.NET 2005
  53. Shared Assembly
  54. Microsoft .NET Creating Installation Components
  55. The Registry Editor in Visual Studio.NET 2005
  56. The File Types Editor

Career Training

Technical Training

Skill Test

Ads


Home arrow Technical Training arrow VB.NET 2005

SQL Server Stored Procedures

Author : Exforsys Inc.     Published on: 11th Jul 2005    |   Last Updated on: 9th Feb 2011

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

Ads

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.

Ads

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:

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)



 
This tutorial is part of a VB.NET 2005 tutorial series. Read it from the beginning and learn yourself.

VB.NET 2005

  1. VB.NET 2005 Free Training
  2. The .NET Framework Architecture Part 1
  3. The .NET Framework Architecture Part 2
  4. Application Class and Message Class
  5. Implementing Class Library Object
  6. Visual Studio.NET Namespaces
  7. .NET Assemblies
  8. Differences between VB.NET 1.0 and VB.NET 2.0
  9. Introducing VB.NET Windows Forms
  10. Visual Studio Windows Forms Designer
  11. Exploring the Forms Designer generated code
  12. Setting and Adding Properties to Windows Form
  13. Implementing Inheritance
  14. Event Handling In Visual Basic .NET
  15. Building Graphical Interface elements
  16. .NET Common Windows Forms Controls Part 1
  17. .NET Common Windows Forms Controls Part 2
  18. Common Controls and Handling Control Events
  19. DomainUpDown and NumericUpDown Controls
  20. Dialog Boxes in Visual Basic .NET
  21. Visual Studio Adding Controls to Windows Form
  22. VB.NET Validation Controls
  23. Working with Menu Controls
  24. VB.NET MDI Applications
  25. .NET Exceptions
  26. VB.NET Creating and Managing Components Part 1
  27. VB.NET Creating and Managing Components Part 2
  28. Simple Data Binding
  29. .NET Complex Data Binding
  30. .NET Data Form Wizard
  31. Data Manipulation with ADO.NET
  32. SQL Server Stored Procedures
  33. SQL Server Ad Hoc Queries
  34. Finding and Sorting Data in DataSets
  35. ADO.NET Object Model
  36. Working with DataSets
  37. Using XML Data
  38. Working with File System in .NET
  39. Creating Web Service
  40. Instantiating - Invoking Web Services, Creating Proxy Classes with WSDL
  41. Web Reference and Web Services
  42. Web Services - SOAP, WSDL, Disco and UDDI
  43. Web Application Testing in VB.NET 2005
  44. Web Application Tracing and Debugging
  45. Working with Legacy Code and COM Components
  46. ActiveX Controls and Legacy Code
  47. Windows Application Testing
  48. VB.NET Windows Application Testing
  49. Tracing VB.NET Windows Application
  50. Debugging Windows Applications In Visual Studio.NET 2005
  51. Deploying Windows Applications In Visual Studio.NET 2005
  52. Customizing Setup Project in Visual Studio.NET 2005
  53. Shared Assembly
  54. Microsoft .NET Creating Installation Components
  55. The Registry Editor in Visual Studio.NET 2005
  56. The File Types Editor
 

Comments

 
Privacy and Legal | Contact Us | DMCA | Sitemap

Copyright © 2000 - 2012 exforsys.com. All Rights Reserved