FunctionX Practical Learning Logo

Introduction to Procedures
 

Introduction

When programming in SQL, you have probably created various types of SQL statements to execute many common actions. You use SQL statements to create databases, create tables, modify tables, or perform data entry. These actions are performed by you the programmer as you need them. In SQL, you can create a statement to be treated as an object and use it only when needed. You can create the statement as an object called a stored procedure.

Practical LearningPractical Learning: Creating the Database
  1. Start Microsoft SQL Server Management Studio and click Connect to connect to the database server
  2. To create a database, right-click the Databases node and click New Database...
  3. Set the Database Name to SuperMarket and click OK
  4. Expand the SuperMarket node and the Tables node
  5. Right-click Tables and click click New Table...
  6. Set the name of the first column to EmployeeID and its data Type to int
  7. In the lower section of the window, expand the Identity Specification and set the (Is Identity) to Yes
  8. Right-click EmployeeID and click Set Primary Key
  9. Create the other columns as follows:
     
    Column Name Data Type Allow Nulls
    EmployeeID    
    FirstName varchar(20)  
    LastName varchar(20) Unchecked
    FullName varchar(50)  
    HourlySalary smallmoney  
  10. Close the table
  11. When asked whether you want to save, click Yes
  12. Set the table name to Employees and press Enter
  13. Under the Tables node of the SuperMarket database, right-click dbo.Employees and click Open Table
  14. Fill it up with a few records as follows:
     
    FirstName LastName HourlySalary
    Anselme Roberts 14.82
    Justine Keys 5.85
    Edward Ross 22.15
    Tracey Kirkland 6.88
    Kimberly Eisner 8.58
    Jonathan Adamson 10.95
    Steve Fox 9.44
    Andrew Buroughs 6.15
    Randy Ettenson 18.04
    Patrick Swanson 12.48
  15. Close the table

A Stored Procedure

A stored procedure is a SQL statement created and saved in a database as an object. Before creating a stored procedure, you must identify the desired database, expand its Programmability node, right-click its Stored Procedures node, and click New Stored Procedure... The code of a stored procedure primarily that of a regular SQL statement. After creating the SQL statement, you must execute it. You can do this by clicking the Execute button. Once the stored procedure has been executed, its name is added as a new item to the Stored Procedures node of the database. This indicates that it is ready to be used.

  
 

Practical LearningPractical Learning: Creating a Stored Procedure
  1. Under the SuperMarket database, expand the Programmability node and the Stored Procedures node
  2. Right-click Stored Procedures and click New Stored Procedure...
  3. Complete the file as follows:
      
    -- ======================================================
-- Author:	Mon'a Zo'o
-- Create date: 10 October 2006
-- Description:	Specifies the full name of each employee
-- ======================================================
CREATE PROCEDURE CreateFullNames 
AS
BEGIN
UPDATE  Employees
               SET FullName = LastName + ', ' + FirstName
	FROM Employees
END
GO
-- ======================================================
  4. On the Standard toolbar, click the Execute button
  5. Right-click Stored Procedures and click Refresh. Notice that it has a new node named dbo.CreateFullNames
 

A Stored Procedure in a Windows Application

There are certainly various ways you can use a SQL stored procedure in a Windows application but the classic way consists of using a command object. The .NET Framework's SqlCommand class has a constructor that takes two arguments, the first of which is a string that represents the name of the stored procedure. When passing the string to the command object, you must indicate that it (the string) represents the name of a stored procedure. To support this, the  command object has a property named CommandType, which is an enumeration and one of its elements is called StoredProcedure. After creating the command object, pass it to a data adapter, fill the table of the data set with the data from the data adapter, then fill the table adapter.
 

Practical LearningPractical Learning: Creating a Stored Procedure
  1. Start Microsoft Visual C# and create a new Windows Application named SuperMarket1
  2. On the main menu, click Data -> Show Data Sources
  3. In the Data Sources window, click Add New Data Source...
  4. In the Data Source Configuration Wizard, accept the Database option and click Next
  5. Click New Connection...
  6. In the Server Name combo box and select the name of the server
  7. In the Select Or Enter A Database Name combo box, select SuperMarket
     
  8. Click OK
  9. In the Data Source Configuration Wizard, make sure the SuperMarket connection is selected and click Next
  10. Change the name of the connection string to cstSuperMarket and click Next
  11. Expand Tables and Stored Procedures
  12. Click the check boxes of Employees and CreateFullNames
  13. Change the name of the DataSet to dsSuperMarket
     
  14. Click Finish
  15. In the Data Sources window, click Employees and click the arrow of its combo box to select DataGridView (it should be selected already as the default).
    Drag the Employees node and drop it on the form
  16. While the DataGridView control is still selected on the form, in the Properties window, click the ellipsis of the Columns field and make the following changes:
     
    Selected Columns HeaderText Width
    EmployeeID Employee ID 75
    FirstName First Name 80
    LastName Last Name 80
    EmailAddress Email Address 130
    HourlySalary Hourly Salary 75
  17. Click OK
  18. Design the form as follows:
     
    Control Name Text Other Properties
    DataGridView     Anchor: Top, Bottom, Left, Right
    Button btnCreateFullNames Create Full Names Anchor: Bottom,  Right
    Button btnClose Close Anchor: Bottom,  Right
  19. Double-click the Create Full Names button
  20. In the top section of the file, under the other using lines, type
    using System.Data.SqlClient
  21. Implement the Click event as follows:
      
    private void btnCreateFullNames_Click(object sender, EventArgs e)
{
    SqlCommand cmdSuperMarket = new SqlCommand("CreateFullNames",
				 employeesTableAdapter.Connection);
    cmdSuperMarket.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter sdaSuperMarket = new SqlDataAdapter(cmdSuperMarket);
            
    sdaSuperMarket.Fill(dsSuperMarket.Employees);
    employeesTableAdapter.Fill(dsSuperMarket.Employees);

    MessageBox.Show("The full names of employees have been specified");
}
  22. Return to the form
  23. Double-click the Close button and implement its even as follows:
      
    private void btnClose_Click(object sender, EventArgs e)
{
     Close();
}
  24. Execute the application to see the result
  25. Click the Create Full Names button:
     
 
Home Copyright © 2006-2016, FunctionX, Inc.