- We will continue with the SuperMarket database created in our introduction
to procedures
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'GiveGeneralRaise'
AND type = 'P')
DROP PROCEDURE GiveGeneralRaise
GO
CREATE PROCEDURE GiveGeneralRaise
@NewRaise SmallMoney
AS
UPDATE Employees
SET Salary = Salary + @NewRaise
GO
|
- Execute the statement (F5)
- Start Visual C++ .NET and create a new Windows Forms Application named
SuperMarket2
- 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 (only the mention controls are important,
you can ignore the others):
|
|
| Control |
Name |
Caption/Text |
Other Properties |
| Form |
|
|
StartPosition: CenterScreen |
| DataGrid |
|
|
Anchor: Top, Bottom, Left, Right
AutoFormat: Professional 3
DataSource: dsEmployees1.Employees |
| Label |
|
Give a raise to all employeees for $: |
Anchor: Bottom, Left |
| TextBox |
txtNewRaise |
0.15 |
TextAlign: Right
Anchor: Bottom, Left |
| Button |
btnNewRaise |
Submit |
Anchor: Bottom, Left, Right |
|
- Double-click an empty the form to access its Load event
- Return to the form and double-click the 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 btnNewRaise_Click(System::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(S"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 = S"@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(S"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, S"Employees");
// Update the information displayed in the datagrid
this->sqlDataAdapter1->Fill(this->dsEmployees1, S"Employees");
}
};
}
|
- Test the application
|
Practical Learning: Using a Stored Procedure With Argument
