- We will continue with the SuperMarket database created in our introduction
to procedures and continued in procedures
with arguments
Open SQL Query Analyzer with a new blank window and create a new
procedure by typing the following code:
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'SetNewMinSalary'
AND type = 'P')
DROP PROCEDURE SetNewMinSalary
GO
CREATE PROCEDURE SetNewMinSalary
@NewSalary SmallMoney
AS
UPDATE Employees
SET Salary = @NewSalary
FROM Employees WHERE Salary < @NewSalary
GO
|
- Execute the statement (F5)
- Start Visual C++ .NET and create a new Windows Forms Application
named SuperMarket3
- In Server Explorer, expand the server that holds the above database
and the database itself followed by its Tables node
- Drag Employees to the form
- On the main menu, click Data -> Generate Dataset...
- Change the name of the dataset to dsEmployees and click OK
- Design the form as follows:
 |
| Control |
Name |
Caption/Text |
Other Properties |
| Form |
|
|
StartPosition: CenterScreen |
| DataGrid |
|
|
Anchor: Top, Bottom, Left, Right
AutoFormat: Professional 3
DataSource: dsEmployees1.Employees |
| Label |
|
Set the company's new minimum salary to $ |
Anchor: Bottom, Left |
| TextBox |
txtNewMinSal |
6.05 |
TextAlign: Right
Anchor: Bottom, Left |
| Button |
btnNewMinSal |
Submit |
Anchor: Bottom, Left, Right |
|
- Double-click an empty the form to access its Load event
- Return to the form and double-click the bottom Submit button
- Implement the events as follows:
#pragma once
namespace SuperMarket1
{
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace System::Data::SqlClient;
/// <summary>
/// Summary for Form1
///
/// WARNING: If you change the name of this class, you will need to change the
/// 'Resource File Name' property for the managed resource compiler tool
/// associated with all .resx files this class depends on. Otherwise,
/// the designers will not be able to interact properly with localized
/// resources associated with this form.
/// </summary>
public __gc class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
}
}
private: System::Void Form1_Load(System::Object * sender, System::EventArgs * e)
{
this->sqlDataAdapter1->Fill(this->dsEmployees1);
}
private: System::Void btnNewMinSal_Click(System::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(S"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 = S"@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(String::Concat(S"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, S"Employees");
// Update the information displayed in the datagrid
this->sqlDataAdapter1->Fill(this->dsEmployees1, S"Employees");
}
};
}
|
- Test the application
|
Practical Learning: Updating Records Using a Stored Procedure
