Stored procedures in SQL SERVER

A stored procedure is a set of SQL statements used to perform specific tasks. A stored procedure resides on the SQL server and can be executed by any user who has the appropriate permissions. Because the stored procedures reside on the SQL server, you do not need to transfer SQL statements to the server each time you want to perform a task on the server.

This reduces the network traffic. When you want to execute a procedure, you only need to transfer the name of the procedure. However, if the procedure takes any parameters, you also need to transfer the parameters along with the procedure name.

You can create a stored procedure by using the Create Procedure statement as follows:

Create Procedure ProcName As SQL statements Return

In this statement:

ProcName: Represents the name of the stored procedure.

SQL statements: Represents the set of SQL statements in the stored procedure.

Return: Represents the end of the procedure. Each stored procedure must end with a Return statement. After the stored procedure is created, the SQL server scrutinizes it for any errors.

The procedure can be executed by using the Execute or Exec keyword, as follows:

Execute ProcName

You can also pass parameters or arguments to a stored procedure to perform a specific task based on the parameter.

Implementing T-SQL in Web Applications

Many situations require Web applications to retrieve, add, modify, and delete data stored in a database on a server. For example, consider a Web application that enables users to register as customers. When a customer fills out the Registration form and submits it, the customer registration information must be stored in a database on a server so as to maintain the registered customer's records.

After the registration, the customer might need to change their customer details, such as telephone number or address. Later, the customer might want to discontinue purchasing from the same store. In such a situation, the Web application must take care of addition, modification, and deletion of data in the respective database on a server.

Accessing data

After designing the forms, you'll add the desired functionality to them. First, you'll add the functionality to the Order form. The form should enable customers to view the complete product list by clicking the View Product List button. Also, the form should enable customers to view the details of a specific product by clicking the View Product Details button.

To implement this functionality, open the code behind file (with .vb extension) of the Order form. At the top of the Order form, import the two namespaces as follows:

Imports System.Data

Imports System.Data.SqlClient

Modifying and deleting data

The DataGrid control enables users to modify and delete records. To allow rows to be edited, the EditItemIndex property of the DataGrid control is used. By default, this property is set to -1, indicating that no rows are editable.

The DataGrid control has a property called Columns that you can use to add buttons to allow user interaction with individual data rows. To add a button column, follow these steps:

1. Open the Property Window of the DataGrid control.
2. Click the ellipsis in the Columns property to open the Properties dialog box.

The DataGrid control can have three types of button columns, described as follows:

The Select button column renders a Select link button used to access a specific row.

The Edit, Update, Cancel button column renders three link buttons: Edit, Update, and Cancel. The Edit button is used to display the row in Edit mode. After the row switches to Edit mode, the column displays Update and Cancel buttons, which are used to update or cancel the changes made to the row.

The Delete button column renders a Delete button that enables users to delete a specific row.

To add the update functionality, add the Edit, Update, Cancel button column to your DataGrid control. When the Edit button is clicked, the EditCommand method of the DataGrid control is called. The UpdateCommand method is called when the Update button is clicked. And, when the Cancel button is clicked, the CancelCommand method is called. Therefore, you need to write appropriate code in these methods to implement the desired functionality.

In the EditCommand method of the DataGrid control, set the EditItemIndex property as follows:

Public Sub MyDataGrid_EditCommand(ByVal source As Object,

ByVale As System.Web.UI.WebControls.DataGridCommandEventArgs)

Handles MyDataGrid.EditCommand

'Setting the EditItemIndex property of the DataGrid

control to indicate the row to be edited

MyDataGrid.EditItemIndex = e.Item.ItemIndex

RELATED POST

CREATING ASSEMBLIES WITH ASP.NET

BUILDING HANDLERS IN ASP.NET

INTERFACES AND CLASSES CREATION IN ASP.NET

CACHING IN ASP.NET

CACHING IN ASP.NET PART TWO

WIRE LESS APPLICATION WITH ASP.NET

SECURITY IN ASP.NET PART ONE

SECURITY IN ASP.NET PART TWO

LOCALIZING ASP.NET APPLICATIONS

DEPLOYING ASP.NET APPLICATIONS

ASP.NET CONFIGURATION SYSTEM

WEB SERVICES IN ASP.NET

WEB SERVICES PART TWO

WEB SERVICE INFRASTRUCTURE

WEB SERVICE INFRASTRUCTURE PART TWO

EXCHANGING MESSAGES IN ASP.NET

MICROSOFT HAIL STORM

SOAP AND DOT NET PART ONE

SOAP AND DOT NET PART TWO


No comments:

Post a Comment