Microsoft Visual Basic .NET Databases - Lesson 4: Stored Procedures

	Stored Procedures

The Fundamentals of Procedures

Introduction

Imagine that your create a database that includes employees. When you want to perform payroll for employees, you would need their weekly hours and their hourly salaries. To calculate the weekly salary, you would write an equation such as:

Weekly Salary = Weekly Hours * Hourly Salary

Since there are various employees in the salary, you would need a a fast means of performing this kind of assignment, maybe automatically, for each employee. This is the basis of a function.

Referred to as a function or a routine in most other programming languages, a procedure is an (or a relatively small) assignment that can take care of a task in a database so that you can call it as/when needed to get its result.

Practical Learning: Introducing Procedures

Start Microsoft Visual C#
In Server Explorer, expand the server that contains your installation of SQL Server
Right-click the name of your server and click New Database
Set the name of the database to SuperMarket and click OK
In Server Explorer, expand the SuperMarket database. Right-click the Tables node and click New Table
Create a new table as follows:
Save the table as Employees and close it
In Server Explorer, expand the Tables node under SuperMarket and double-click Employees
Fill it up with a few records as follows:
Close the table
Start a new Windows Application named SuperMarket1
In Server Explorer, under the Tables node of the SuperMarket database, drag Employees and drop it on the form
On the main menu, click Data -> Generate Dataset...
Change the name of the dataset to dsEmployees and click OK

Add a DataGrid control to the form as follows:

Control	Name	Text	Other Properties
Form			StartPosition: CenterScreen
DataGrid			Anchor: Top, Bottom, Left, Right AutoFormat: Professional 3 DataSource: dsEmployees1.Employees
Button	btnLoad	Load	Anchor: Bottom, Right
Button	btnClose	Close	Anchor: Bottom, Left

Double-click the Load button
Return to the form and double-click the Close button

Implement the events as follows:

private void Form1_Load(object sender, System::EventArgs  e)
	 {
		this.sqlDataAdapter1.Fill(this.dsEmployees1);
	 }

private void btnClose_Click(object sender, System.EventArgs e)
	 {
		 Close();
	 }

Test the application:
Close the form to return to Visual C#

Creating a Procedure

A procedure can be as simple as calculating 124 + 68 or as complex as finding out if a date range includes a holiday for somebody who is renting a car so the day can be calculated with a different rate as compared to other dates in the same range. As always, we should start with simple examples. To create a procedure, you can use either Enterprise Manager or SQL Query Analyzer.

To create a new procedure in Enterprise Manager, after expanding the database, you can right-click it, position the mouse on New, and click Stored Procedure... You would be presented with a skeleton syntax that you can complete using the techniques we will learn in this lesson.

To create a new procedure in SQL Query Analyzer, after selecting the database (either from the combo box on the toolbar or with the USE keyword), you can type code based on the syntaxes we will learn shortly.

The creation of a procedure starts with the CREATE PROCEDURE expression. You can also use CREATE PROC. Both expressions produce the same result.

Like everything in your database, you must name your procedure. The name of a procedure can be any string that follows the rules we reviewed for naming objects. There are some other rules or suggestions you should or must follow when naming your procedure. For example, refrain from starting the name of a procedure with sp_ because it would conflict with some of the procedures that already ship with SQL Server.

After the name of the procedure, type the keyword AS.

The section, group of words, or group of lines after the AS keyword is called the body of the procedure. It states what you want the procedure to do or what you want it to produce.

Based on this, the simplest syntax of defining a procedure is:

CREATE PROCEDURE ProcedureName
AS
Body of the Procedure

It is important to keep in mind that there are many other issues related to creating a procedure but for now, let's consider that syntax.

The Simplest Procedures

Probably the simplest procedure you can write would consist of selecting columns from a table. This is done with the SELECT keyword. For example, to create a procedure whose job would consist of creating a list of car makes from a table named Cars, you would write:

CREATE PROCEDURE ListOfMakes
AS
SELECT Make

To execute this procedure, you would type:

EXECUTE ListOfMakes

You can also create a procedure that selects more than one column from a table. As done with the SELECT keyword in data analysis, you would separate each item of the list with a comma, except for the last. Here is an example:

CREATE PROCEDURE ListOfCars
AS
SELECT Make, Model, CarYear

Returning a Value

One of the advantages of using procedures is that not only can they produce the same expressions as we saw during analysis but also they can store such expressions to be recalled any time without having to re-write them. Based on this, you can create an expression that combines a first and a last name to produce and store a full name. Here is an example:

CREATE PROC GetFullName
AS
SELECT FullName = LastName + ', ' + LastName
FROM Students

Practical Learning: Creating a Stored Procedure

In Server Explorer, under the SuperMarket node, right-click Stored Procedures and click New Stored Procedure
Complete it as follows:
To save the procedure, on the Standard toolbar, click the Save button
Close the procedure window

Executing a Procedure

After creating a procedure, to get its result, you would need to execute it (in other programming languages, we would say that, in order to use a function, you must call it). To execute a procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXECUTE ProcedureName

Alternatively, instead of EXECUTE, you can use the EXEC keyword:

EXEC ProcedureName

After a procedure has been executed, it is saved using its name. Since it becomes stored as an integral part of the database, a SQL procedure is also called a Stored Procedure.

Practical Learning: Using a Stored Procedure

On the form, add a button
Change its properties as follows:
Text: Generate Email Address
Anchor: Bottom, Right
Name to btnEmailAddress

Double-click the Generate Email Addresses button and implement its event as follows:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace SuperMarket1
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{	
		. . . No Change	
		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void btnLoad_Click(object sender, System.EventArgs e)
		{
			this.sqlDataAdapter1.Fill(this.dsEmployees1);
		}

		private void btnClose_Click(object sender, System.EventArgs e)
		{
			Close();
		}

		private void btnEmailAddress_Click(object sender, System.EventArgs e)
		{
			// Create a new SQL command command, passing it the name of
			// the stored procedure we want to call and attaching it to 
			// the existing SQL connection
			SqlCommand cmdNew     = new SqlCommand("CreateEmailAddress", this.sqlConnection1);
		 
			// Create a new adapter and initialize it with the new SQL command
			SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);
             			// Create a new data set
			DataSet dsEmployees  = new DataSet();

			// We need to specify the type of command we want to use.
			// In this case, it will be a SQL Server stored procedure
			cmdNew.CommandType = CommandType.StoredProcedure;

			// It is time to update the data set with information from the data adapter
			sdaNew.Fill(dsEmployees, "Employees");
			this.sqlDataAdapter1.Fill(this.dsEmployees1, "Employees");

			// Let the user know that the assignment has been carried
			MessageBox.Show("An email address has been created for each employees");
		}
	}
}

Test the application:
Close the form and return to your programming environment

Deleting a Procedure

One of the biggest characteristics of a stored procedure, as compared to functions in traditional languages, is that a procedure is treated like an object in its own right. Therefore, after creating it, if you don't need it anymore, you can get rid of it.

There are various types of procedures, some of which are considered temporary. Those types of procedures delete themselves when not needed anymore, such as when the person who created the procedure disconnects from the database or shuts down the computer. Otherwise, to delete a procedure, you can use either Enterprise Manager or SQL Query Analyzer. As mentioned with tables, even if you create a procedure in Enterprise Manager, you can delete it using SQL Query Analyzer and vice-versa.

To remove a procedure in Enterprise Manager, after expanding the database, click the Stored Procedure node. If you see the needed procedure in the list, fine. If you don't see the procedure in the list but know for sure that the procedure was created, for example if you create a new procedure in SQL Query Analyzer, it doesn't automatically appear in Enterprise Manager, make sure you refresh the list first by pressing F5. After locating the procedure in the right frame, you can either right-click it and click Delete, or click it to select it and then press Delete. Before the procedure gets removed, you would be warned with a dialog box to make your final decision.

To delete a procedure in SQL Query Analyzer using Transact-SQL, the syntax to use is:

DROP PROCEDURE ProcedureName

Of course, you should make sure you are in the right database and also that the ProcedureName exists.

Arguments and Parameters

Introduction

Imagine you are creating an application for a department store that sometimes applies discounts of 10%, 20%, 40%, 55%, 70%, etc on items its sells. Since the management decides when and what discount would be applied on an item, you cannot predict all possibilities. One way to solve this type of problem is to create a procedure that would receive the discount applied on an item and then apply this discount to the price of the item.

All of the procedures we have created and used so far assumed that the values they needed were already in a table of the database. In some cases, you may need to create a procedure that involves values that are not part of the database. On such a scenario, for the procedure to carry its assignment, you would supply it with one or more values.

An external value that is provided to a stored procedure is called a parameter. When you create a procedure, you must also create the parameter if you judge it necessary. When a procedure's creation is equipped with a parameter, it is said that the procedure takes an argument. A procedure can also take more than one argument.

When you execute a procedure that takes one or more arguments, you must provide a value for each argument. In this case, you are said to pass a value for the argument. There are cases when you don't have to provide an argument. We will learn how this is done.

Passing Arguments

To create a procedure that takes an argument, type the formula CREATE PROCEDURE or CREATE PROC followed by the name of the procedure, then type the name of the argument that starts with @. The parameter is created like a column of a table. That is, a parameter must have a name, a data type and an optional length. Here is the syntax you would use:

CREATE PROCEDURE ProcedureName
@ParameterName DataType
AS
Body of the Procedure

When implementing the procedure, you can define what you want to do with the parameters, in the body of the procedure. One way you can use a parameter is to run a query whose factor the user would provide. For example, imagine you want to create a procedure that, whenever executed, it would be supplied with a gender, then it would display the list of students of that gender. Since you want the user to specify the gender of students to display, you can create a procedure that receives the gender. Here is an example:

CREATE PROC GetListOfStudentsByGender
@Gdr VARCHAR(12)
AS
SELECT FirstName, LastName, DateOfBirth, HomeOfBirth, HomePhone, Gender
FROM Students
WHERE Gender = @Gdr

As mentioned already, when executing a procedure that takes a parameter, make sure you provide a value for the parameter. The syntax used is:

EXEC ProcedureName ParameterValue

If the parameter is Boolean or numeric, make sure you provide an appropriate value. If the parameter is a character or a string, type its value in single-quotes. Here is an example:

Notice that we could/should have omitted to include the Gender column in the statement since it would be implied to the user.

Another type of procedure can be made to take more than one parameter. In this case, create the parameter in the section before the AS keyword, separated by a comma. The syntax you would use is:

CREATE PROCEDURE ProcedureName
@ParameterName1 DataType, @ParameterName2 DataType, @ParameterName_n DataType
AS
Body of the Procedure

When calling a procedure that takes more than one parameter, you must still provide a value for each parameter but you have two alternatives. The simplest technique consists of providing a value for each parameter in the exact order they appear in the procedure.

Alternatively, you can provide the value for each parameter in the order of your choice. In this case, you must type the name of each parameter and assign it the corresponding value.

Practical Learning: Passing Arguments to a Stored Procedure

We will continue with the SuperMarket database
In Server Explorer, under the SuperMarket node, right-click Stored Procedures and click New Stored Procedure
Complete it as follows:
Save and close the procedure window
Display the form (Form1.cs [Design])

Change the design of the form as follows:

New Control	Name	Text	Other Properties
Label		Give a raise of $	Anchor: Bottom, Left
TextBox	txtNewRaise	0.15	Anchor: Bottom, Left AlignText: Right
Label		to all employees	Anchor: Bottom, Left
Button	btnSubmit	Submit	Anchor: Bottom, Left

Double-click the Submit button and implement its Click event as follows:

private void btnSubmit_Click(object sender, System.EventArgs e)
{
	// Create a new SQL command, passing it the name of
	// the stored procedure we want to call and attaching it to 
	// the existing SQL connection
	SqlCommand cmdNew     = new SqlCommand("GiveGeneralRaise", this.sqlConnection1);
	SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);
	DataSet  dsEmployees  = new DataSet();

	// We will need a SQL parameter to carry the argument
	// Declare its variable
	SqlParameter parNew   = new SqlParameter();
	// Specify the name of the argument
	parNew.ParameterName  = "@NewRaise";
	// Specify the SQL data type of the argument
	parNew.SqlDbType      = SqlDbType.SmallMoney;
	// Specify the value passed as argument
	parNew.Value          = this.txtNewRaise.Text;

	// Once the argument is ready, add it to the list of arguments
	cmdNew.Parameters.Add(parNew);
	// Let the SQL command know the type of command we are going to use
	// In this case, it is a stored procedure
	cmdNew.CommandType = CommandType.StoredProcedure;

	// Inform the user about the action that is going to occur
	// (You can also do this after the action has been carried
	MessageBox.Show(String.Concat("All employees are going to receive a new raise of $" +
		                          this.txtNewRaise.Text));

	// Update the data set with the new information from the data adapter
	sdaNew.Fill(dsEmployees, "Employees");
	// Update the information displayed in the datagrid
	this.sqlDataAdapter1.Fill(this.dsEmployees1, "Employees");
}

Execute the application
Close the form and return to your programming environment

Default Arguments

Imagine you create a table for a department store and the table would be used to hold the names and prices of items (in this example, the table is called SaleItems):

Table Properties

Supposed you have filled the table with a few items as follows:

Sale Items

Imagine you want to create a mechanism of calculating the price of an item after a discount has been applied to it. Such a procedure can be created as follows:

CREATE PROC CalculateNetPrice
@discount Decimal
AS
SELECT ItemName, ItemPrice - (ItemPrice * @discount / 100)
FROM SaleItems

This can be executed as follows:

Procedure Executed

If you are planning to create a procedure that takes an argument and know that the argument will likely have the same value most of the time, you can provide that value as parameter but leave a room for other values of that argument. A value given to an argument is referred to as default. What this implies is that, when the user calls that stored procedure, if the user doesn't provide a value for the argument, the default value would be used.

To create a procedure that takes an argument that carries a default value, after declaring the value, on its right side, type = followed by the desired value. Here is an example applied to the above database:

CREATE PROC CalculateNetPrice
@discount Decimal = 20.00
AS
SELECT ItemName, ItemPrice - (ItemPrice * @discount / 100)
FROM SaleItems

When executing a procedure that takes a default argument, you don't have to provide a value for the argument if the default value suits you. Based on this, the above procedure can be called as follows:

If the default value doesn't apply to your current calculation, you can provide a value for the argument. Here is an example:

Using this same approach, you can create a procedure that takes more than one argument with default values. To provide a default value for each argument, after declaring it, type the desired value to its right side. Here is an example of a procedure that takes two arguments, each with a default value:

CREATE PROC CalculateSalePrice2
@Discount Decimal = 20.00,
@TaxRate  Decimal = 7.75
AS
SELECT ItemName As [Item Description],
       ItemPrice As [Marked Price],
       ItemPrice * @Discount / 100 As [Discount Amt],
       ItemPrice - (ItemPrice * @Discount / 100) As [After Discount],
       ItemPrice * @TaxRate / 100 As [Tax Amount],
       (ItemPrice * @TaxRate / 100) + ItemPrice - (ItemPrice * @Discount / 100) + (@TaxRate / 100) As [Net Price]
FROM SaleItems

Here is an example of executing the procedure:

When calling a procedure that takes more than one argument and all arguments having default values, you don't need to provide a value for each argument, you can provide a value for only one or some of the arguments. The above procedure can be called with one argument as follows:

EXEC CalculateSalePrice2 55.00

In this case, the other argument(s) would use their default value.

We saw that, when calling a procedure that takes more than one argument, you didn't have to provide the values of the argument in the exact order they appear in the procedure, you just had to type the name of each argument and assign it the corresponding value. In the same way, if a procedure takes more than one argument and some of the arguments have default values, when calling it, you can provide the values in the order of your choice, by typing the name of each argument and assigning it the desired value. Based on this, the above procedure can be called with only the value of the second argument as follows:

EXEC CalculateSalePrice2 @TaxRate = 8.55

In this case, the first argument would use its default value.

Output Parameter

Many languages use the notion of passing an argument by reference. This type of argument is passed to a procedure but it is meant to return a value. Transact-SQL uses the same technique. In other words, you can create a procedure that takes a parameter but the purpose of the parameter is to carry a new value when the procedure ends so you can use that value as you see fit.

To create a parameter that will return a value from the procedure, type the OUTPUT keyword on the right side of the parameter. A syntax you can use is:

CREATE PROCEDURE ProcedureName
@ParameterName DataType OUTPUT
AS
Body of the Procedure

You can also create a procedure that takes a mix of value and output parameters.

Data Entry and Update Using Stored Procedures

Data Entry Using a Stored Procedure

Probably the best attribute of a stored procedure is that it allows the developer to have a direct programmatic with to the back-end database. Based on this relationship, you can use a stored procedure to perform data entry.

To create a new record in a table, you can use the INSERT TABLE expression of the SQL. If the table doesn't have a primary key, you can create an argument for each column of the table. If the table has a primary key, you can create an argument for each column of the table but you should/must omit one for the primary key. Here is an example of such a procedure from a database called Familia that has a table named Persons with the PersonID(Primary Key), FirstName, LastName, GenderID, and Notes columns:

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'CreateNewRecord' 
	   AND 	  type = 'P')
    DROP PROCEDURE CreateNewRecord
GO

CREATE PROCEDURE CreateNewRecord 
  @FirstName VarChar(20),
  @LastName VarChar(20),
  @Gender int,
  @Notes Text
AS
  INSERT Persons(FirstName, LastName, GenderID, Notes)
              VALUES(@FirstName, @LastName, @Gender, @Notes)
GO

After creating the stored procedure, you can then create an application design the form to include the fields for columns represented in the table.

Practical Learning: Performing Data Entry Using a Stored Procedure

In Server Explorer, expand the ISC1 database. Right-click its Store Procedure node and click New Stored Procedure
Complete it as follows:
Save and close the procedure window
Open the CIS2 project and display the OrderProcessing form (OrderProcessing.cs [Design])
Add a new button to the form
Change its Name to btnNewOrder and its Text to New Order

Double-click the New Order button and implement its Click event as follows:

using System;
using System.Data;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace CIS2
{
	/// <summary>
	/// Summary description for OrderProcessing.
	/// </summary>
	public class OrderProcessing : System.Windows.Forms.Form
	{
		. . . No Change
		


		private void btnNewRecord_Click(object sender, System.EventArgs e)
		{
			// Find out the state (actually caption) of the New Order button
			if( this.btnNewOrder.Text.Equals("New Order") )
			{
				// If it is displaying New Order, then reset the form to get ready
				// for a new customer order
				this.txtOrderID.Text = "";
				this.cboProcessedBy.SelectedIndex = 0;
				this.dtpOrderDate.Value = DateTime.Today;
				this.dtpOrderTime.Value = DateTime.Now;
				this.cboFlavor.SelectedIndex = 0;
				this.cboContainer.SelectedIndex = 0;
				this.cboIngredient.SelectedIndex = 0;
				this.txtScoops.Text = "1";
				// Set the caption of the button to Submit
				// to indicate that the button will need to be clicked 
				// to validate an order
				this.btnNewOrder.Text = "Submit";
			}
			else // If the button is displaying Submit, then process an order
			{
				// Create a new SqlCommand command, passing it the name of
				// the stored procedure we want to call and attaching it to 
				// the existing SQL connection
				SqlCommand cmdNew     = new SqlCommand("ProcessNewOrder", this.sqlConnection1);
				SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);

				// Let the SQL command know the type of command we are going to use
				// In this case, it is a stored procedure
				cmdNew.CommandType = CommandType.StoredProcedure;

				// We will need a SQL parameter to carry the arguments
				// Declare its variable
				SqlParameter parNew   = new SqlParameter();
				// Specify the name of the argument
				parNew.ParameterName  = "@ProcessedBy";
				// Specify the SQL data type of the argument
				parNew.SqlDbType      = SqlDbType.Int;
				// Specify the value passed as argument
				parNew.Value          = this.cboProcessedBy.SelectedIndex;

				// Once the argument is ready, add it to the list of arguments
				cmdNew.Parameters.Add(parNew);

				parNew   = new SqlParameter();
				parNew.ParameterName  = "@DateOrdered";
				parNew.SqlDbType      = SqlDbType.SmallDateTime;
				parNew.Value          = this.dtpOrderDate.Value;
				cmdNew.Parameters.Add(parNew);

				parNew   = new SqlParameter();
				parNew.ParameterName  = "@TimeOrdered";
				parNew.SqlDbType      = SqlDbType.SmallDateTime;
				parNew.Value          = this.dtpOrderTime.Value;
				cmdNew.Parameters.Add(parNew);

				parNew   = new SqlParameter();
				parNew.ParameterName  = "@ContainerType";
				parNew.SqlDbType      = SqlDbType.Int;
				parNew.Value          = this.cboContainer.SelectedIndex;
				cmdNew.Parameters.Add(parNew);
			 
				parNew   = new SqlParameter();
				parNew.ParameterName  = "@SelectedFlavor";
				parNew.SqlDbType      = SqlDbType.Int;
				parNew.Value          = this.cboFlavor.SelectedIndex;
				cmdNew.Parameters.Add(parNew);
			 
				parNew   = new SqlParameter();
				parNew.ParameterName  = "@NbrOfScoops";
				parNew.SqlDbType      = SqlDbType.Int;
				parNew.Value          = int.Parse(this.txtScoops.Text);
				cmdNew.Parameters.Add(parNew);
			 
				parNew   = new SqlParameter();
				parNew.ParameterName  = "@WhatIngredient";
				parNew.SqlDbType      = SqlDbType.Int;
				parNew.Value          = this.cboIngredient.SelectedIndex;
				cmdNew.Parameters.Add(parNew);
			
				// Inform the user about the action that is going to occur
				// (You can also do this after the action has been carried
				MessageBox.Show("A new customer order has been created");

				// Update the data set with the new information from the data adapter
				sdaNew.Fill(this.dsOrders1, "Orders");

				// Change the caption of the button to inform the user
				this.btnNewOrder.Text = "New Order";

				// And move to the last record
				this.BindingContext[this.dsOrders1, "Orders"].Position = 
					this.BindingContext[this.dsOrders1, "Orders"].Count - 1;
			}
		}
	}
}

Execute the application
Close the form and return to your programming environment

General Data Update Using a Stored Procedure

When a user is navigating through records and find information that is not accurate, if the user makes a change, you must provide a mechanism to keep the updated value as we did in the previous lesson. This scenario that involves changing existing data of records can be handled by a stored procedure. This is usually done by including an UPDATE statement as part of the procedure.

Criterion-Based Data Update Using a Stored Procedure

One of the jobs of a database developer or administrator is to update records when there is a new request. For example, imagine a company has decided to change the minimum salary of all employees and the manager asks you to make this change. If the company is small as having less than 10 employees, you can easily open the table or the form that holds the employees records, manually examine the employees with the lowest salary, and then change those who have a salary below the company's new set. You can also create an update query that would perform the task. The job here is to check all salaries but to make a change only to those that respond to a specific criterion. For example, if you must change the minimum salary to 8.55, your change would affect only those employees who are making less than $8.55/hr. This type of assignment can be handled by a stored procedure by including an UPDATE statement as part of the procedure.

Practical Learning: Updating Data Using a Stored Procedure

In Server Explorer, right-click the Stored Procedure node of the SuperMarket database and click New Stored Procedure
Change the statement as follows:
Save and close the procedure window
Open the SuperMarket1 project

Display the form (Form1.cs [Design]) and change its design as follows:

Control	Name	Caption/Text	Other Properties
Label		Set the company's new minimum salary to $	Anchor: Bottom, Left
TextBox	txtNewMinSal	6.55	TextAlign: Right Anchor: Bottom, Left
Button	btnNewMinSal	Submit	Anchor: Bottom, Left

Double-click the new Submit button and implement its Click event as follows:

private void btnNewMinSal_Click(object sender, System.EventArgs e)
{
	// Create a new SqlCommand command, passing it the name of
	// the stored procedure we want to call and attaching it to 
	// the existing SQL connection
	SqlCommand cmdNew     = new SqlCommand("SetNewMinSalary", this.sqlConnection1);
	SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);
	DataSet dsEmployees   = new DataSet();

	// We will need a SQL parameter to carry the argument
	// Declare its variable
	SqlParameter parNew   = new SqlParameter();
	// Specify the name of the argument
	parNew.ParameterName  = "@NewSalary";
	// Specify the SQL data type of the argument
	parNew.SqlDbType      = SqlDbType.SmallMoney;
	// Specify the value passed as argument
	parNew.Value          = this.txtNewMinSal.Text;
            
	// Once the argument is ready, add it to the list of arguments
	cmdNew.Parameters.Add(parNew);
	// Let the SQL command know the type of command we are going to use
	// In this case, it is a stored procedure
	cmdNew.CommandType = CommandType.StoredProcedure;
			
	// Inform the user about the action that is going to occur
	// (You can also do this after the action has been carried
	MessageBox.Show("The new minimum salary throughout the company has been set to $" +
                            this.txtNewMinSal.Text);

	// Update the data set with the new information from the data adapter
	sdaNew.Fill(dsEmployees, "Employees");
	// Update the information displayed in the datagrid
	this.sqlDataAdapter1.Fill(this.dsEmployees1, "Employees");
}

Execute the application
Close the form


Previous	Copyright © 2004-2010 FunctionX, Inc.