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
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