Visual C++ Net Databases: Data Entry With a Stored Procedure

	Data Entry With a Stored Procedure

Introduction

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. Here is an example:

In this example, the only control that is bound is the combo box to show the records from its external table.

Here is the code to pass the values of the form to the stored procedure when the user clicks the Submit button:

#pragma once


namespace FamiliaProc1
{
	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();
		}
  
	protected:
		void Dispose(Boolean disposing)
		{
			if (disposing && components)
			{
				components->Dispose();
			}
			__super::Dispose(disposing);
		}
	private: System::Windows::Forms::Button *  button2;
	private: System::Windows::Forms::Button *  button1;
	private: System::Windows::Forms::TextBox *  txtNotes;
	private: System::Windows::Forms::Label *  label4;
	private: System::Windows::Forms::ComboBox *  cboGender;
	private: System::Windows::Forms::Label *  label3;
	private: System::Windows::Forms::TextBox *  txtLastName;
	private: System::Windows::Forms::Label *  label2;
	private: System::Windows::Forms::TextBox *  txtFirstName;
	private: System::Windows::Forms::Label *  label1;
	private: System::Data::SqlClient::SqlCommand *  sqlSelectCommand1;
	private: System::Data::SqlClient::SqlCommand *  sqlInsertCommand1;
	private: System::Data::SqlClient::SqlCommand *  sqlUpdateCommand1;
	private: System::Data::SqlClient::SqlCommand *  sqlDeleteCommand1;
	private: System::Data::SqlClient::SqlConnection *  sqlConnection1;
	private: System::Data::SqlClient::SqlDataAdapter *  sqlDataAdapter1;
	private: FamiliaProc1::dsFamily *  dsFamily1;

	private:
		/// <summary>
		/// Required designer variable.
		/// </summary>
		System::ComponentModel::Container * components;

		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		void InitializeComponent(void)
		{
			System::Resources::ResourceManager *  resources = new System::Resources::ResourceManager(__typeof(FamiliaProc1::Form1));
			this->button2 = new System::Windows::Forms::Button();
			this->button1 = new System::Windows::Forms::Button();
			this->txtNotes = new System::Windows::Forms::TextBox();
			this->label4 = new System::Windows::Forms::Label();
			this->cboGender = new System::Windows::Forms::ComboBox();
			this->dsFamily1 = new FamiliaProc1::dsFamily();
			this->label3 = new System::Windows::Forms::Label();
			this->txtLastName = new System::Windows::Forms::TextBox();
			this->label2 = new System::Windows::Forms::Label();
			this->txtFirstName = new System::Windows::Forms::TextBox();
			this->label1 = new System::Windows::Forms::Label();
			this->sqlSelectCommand1 = new System::Data::SqlClient::SqlCommand();
			this->sqlConnection1 = new System::Data::SqlClient::SqlConnection();
			this->sqlInsertCommand1 = new System::Data::SqlClient::SqlCommand();
			this->sqlUpdateCommand1 = new System::Data::SqlClient::SqlCommand();
			this->sqlDeleteCommand1 = new System::Data::SqlClient::SqlCommand();
			this->sqlDataAdapter1 = new System::Data::SqlClient::SqlDataAdapter();
			(__try_cast<System::ComponentModel::ISupportInitialize *  >(this->dsFamily1))->BeginInit();
			this->SuspendLayout();
			// 
			// button2
			// 
			this->button2->Location = System::Drawing::Point(232, 200);
			this->button2->Name = S"button2";
			this->button2->Size = System::Drawing::Size(112, 23);
			this->button2->TabIndex = 19;
			this->button2->Text = S"Close";
			this->button2->Click += new System::EventHandler(this, button2_Click);
			// 
			// button1
			// 
			this->button1->Location = System::Drawing::Point(96, 200);
			this->button1->Name = S"button1";
			this->button1->Size = System::Drawing::Size(112, 23);
			this->button1->TabIndex = 18;
			this->button1->Text = S"Submit";
			this->button1->Click += new System::EventHandler(this, button1_Click);
			// 
			// txtNotes
			// 
			this->txtNotes->Location = System::Drawing::Point(96, 112);
			this->txtNotes->Multiline = true;
			this->txtNotes->Name = S"txtNotes";
			this->txtNotes->ScrollBars = System::Windows::Forms::ScrollBars::Vertical;
			this->txtNotes->Size = System::Drawing::Size(248, 72);
			this->txtNotes->TabIndex = 17;
			this->txtNotes->Text = S"";
			// 
			// label4
			// 
			this->label4->Location = System::Drawing::Point(16, 112);
			this->label4->Name = S"label4";
			this->label4->Size = System::Drawing::Size(72, 16);
			this->label4->TabIndex = 16;
			this->label4->Text = S"Notes:";
			// 
			// cboGender
			// 
			this->cboGender->DataSource = this->dsFamily1->Genders;
			this->cboGender->DisplayMember = S"Gender";
			this->cboGender->DropDownStyle = System::Windows::Forms::ComboBoxStyle::DropDownList;
			this->cboGender->Location = System::Drawing::Point(96, 80);
			this->cboGender->Name = S"cboGender";
			this->cboGender->Size = System::Drawing::Size(104, 21);
			this->cboGender->TabIndex = 15;
			this->cboGender->ValueMember = S"GenderID";
			// 
			// dsFamily1
			// 
			this->dsFamily1->DataSetName = S"dsFamily";
			this->dsFamily1->Locale = new System::Globalization::CultureInfo(S"en-US");
			// 
			// label3
			// 
			this->label3->Location = System::Drawing::Point(16, 80);
			this->label3->Name = S"label3";
			this->label3->Size = System::Drawing::Size(56, 16);
			this->label3->TabIndex = 14;
			this->label3->Text = S"Gender:";
			// 
			// txtLastName
			// 
			this->txtLastName->Location = System::Drawing::Point(96, 48);
			this->txtLastName->Name = S"txtLastName";
			this->txtLastName->Size = System::Drawing::Size(104, 20);
			this->txtLastName->TabIndex = 13;
			this->txtLastName->Text = S"";
			// 
			// label2
			// 
			this->label2->Location = System::Drawing::Point(16, 48);
			this->label2->Name = S"label2";
			this->label2->Size = System::Drawing::Size(64, 16);
			this->label2->TabIndex = 12;
			this->label2->Text = S"Last Name:";
			// 
			// txtFirstName
			// 
			this->txtFirstName->Location = System::Drawing::Point(96, 16);
			this->txtFirstName->Name = S"txtFirstName";
			this->txtFirstName->Size = System::Drawing::Size(104, 20);
			this->txtFirstName->TabIndex = 11;
			this->txtFirstName->Text = S"";
			// 
			// label1
			// 
			this->label1->Location = System::Drawing::Point(16, 16);
			this->label1->Name = S"label1";
			this->label1->Size = System::Drawing::Size(64, 16);
			this->label1->TabIndex = 10;
			this->label1->Text = S"First Name:";
			// 
			// sqlSelectCommand1
			// 
			this->sqlSelectCommand1->CommandText = S"SELECT GenderID, Gender FROM Genders";
			this->sqlSelectCommand1->Connection = this->sqlConnection1;
			// 
			// sqlConnection1
			// 
			this->sqlConnection1->ConnectionString = S"workstation id=TELES;packet size=4096;integrated security=SSPI;data source=TELES;" 
				S"persist security info=False;initial catalog=Familia";
			// 
			// sqlInsertCommand1
			// 
			this->sqlInsertCommand1->CommandText = S"INSERT INTO Genders(Gender) VALUES (@Gender); SELECT GenderID, Gender FROM Gender" 
				S"s WHERE (GenderID = @@IDENTITY)";
			this->sqlInsertCommand1->Connection = this->sqlConnection1;
			this->sqlInsertCommand1->Parameters->Add(new System::Data::SqlClient::SqlParameter(S"@Gender", System::Data::SqlDbType::VarChar, 20, S"Gender"));
			// 
			// sqlUpdateCommand1
			// 
			this->sqlUpdateCommand1->CommandText = S"UPDATE Genders SET Gender = @Gender WHERE (GenderID = @Original_GenderID) AND (Ge" 
				S"nder = @Original_Gender); SELECT GenderID, Gender FROM Genders WHERE (GenderID =" 
				S" @GenderID)";
			this->sqlUpdateCommand1->Connection = this->sqlConnection1;
			this->sqlUpdateCommand1->Parameters->Add(new System::Data::SqlClient::SqlParameter(S"@Gender", System::Data::SqlDbType::VarChar, 20, S"Gender"));
			this->sqlUpdateCommand1->Parameters->Add(new System::Data::SqlClient::SqlParameter(S"@Original_GenderID", System::Data::SqlDbType::Int, 4, System::Data::ParameterDirection::Input, false, (System::Byte)0, (System::Byte)0, S"GenderID", System::Data::DataRowVersion::Original, 0));
			this->sqlUpdateCommand1->Parameters->Add(new System::Data::SqlClient::SqlParameter(S"@Original_Gender", System::Data::SqlDbType::VarChar, 20, System::Data::ParameterDirection::Input, false, (System::Byte)0, (System::Byte)0, S"Gender", System::Data::DataRowVersion::Original, 0));
			this->sqlUpdateCommand1->Parameters->Add(new System::Data::SqlClient::SqlParameter(S"@GenderID", System::Data::SqlDbType::Int, 4, S"GenderID"));
			// 
			// sqlDeleteCommand1
			// 
			this->sqlDeleteCommand1->CommandText = S"DELETE FROM Genders WHERE (GenderID = @Original_GenderID) AND (Gender = @Original" 
				S"_Gender)";
			this->sqlDeleteCommand1->Connection = this->sqlConnection1;
			this->sqlDeleteCommand1->Parameters->Add(new System::Data::SqlClient::SqlParameter(S"@Original_GenderID", System::Data::SqlDbType::Int, 4, System::Data::ParameterDirection::Input, false, (System::Byte)0, (System::Byte)0, S"GenderID", System::Data::DataRowVersion::Original, 0));
			this->sqlDeleteCommand1->Parameters->Add(new System::Data::SqlClient::SqlParameter(S"@Original_Gender", System::Data::SqlDbType::VarChar, 20, System::Data::ParameterDirection::Input, false, (System::Byte)0, (System::Byte)0, S"Gender", System::Data::DataRowVersion::Original, 0));
			// 
			// sqlDataAdapter1
			// 
			this->sqlDataAdapter1->DeleteCommand = this->sqlDeleteCommand1;
			this->sqlDataAdapter1->InsertCommand = this->sqlInsertCommand1;
			this->sqlDataAdapter1->SelectCommand = this->sqlSelectCommand1;
			System::Data::Common::DataTableMapping* __mcTemp__1[] = new System::Data::Common::DataTableMapping*[1];
			System::Data::Common::DataColumnMapping* __mcTemp__2[] = new System::Data::Common::DataColumnMapping*[2];
			__mcTemp__2[0] = new System::Data::Common::DataColumnMapping(S"GenderID", S"GenderID");
			__mcTemp__2[1] = new System::Data::Common::DataColumnMapping(S"Gender", S"Gender");
			__mcTemp__1[0] = new System::Data::Common::DataTableMapping(S"Table", S"Genders", __mcTemp__2);
			this->sqlDataAdapter1->TableMappings->AddRange(__mcTemp__1);
			this->sqlDataAdapter1->UpdateCommand = this->sqlUpdateCommand1;
			// 
			// Form1
			// 
			this->AutoScaleBaseSize = System::Drawing::Size(5, 13);
			this->ClientSize = System::Drawing::Size(360, 238);
			this->Controls->Add(this->button2);
			this->Controls->Add(this->button1);
			this->Controls->Add(this->txtNotes);
			this->Controls->Add(this->label4);
			this->Controls->Add(this->cboGender);
			this->Controls->Add(this->label3);
			this->Controls->Add(this->txtLastName);
			this->Controls->Add(this->label2);
			this->Controls->Add(this->txtFirstName);
			this->Controls->Add(this->label1);
			this->Icon = (__try_cast<System::Drawing::Icon *  >(resources->GetObject(S"$this.Icon")));
			this->MaximizeBox = false;
			this->Name = S"Form1";
			this->StartPosition = System::Windows::Forms::FormStartPosition::CenterScreen;
			this->Text = S"Family Members";
			this->Load += new System::EventHandler(this, Form1_Load);
			(__try_cast<System::ComponentModel::ISupportInitialize *  >(this->dsFamily1))->EndInit();
			this->ResumeLayout(false);

		}	
	private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
			 {
				 this->sqlDataAdapter1->Fill(this->dsFamily1, S"Genders");
			 }

private: System::Void button1_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"CreateNewRecord", this->sqlConnection1);
			 SqlDataAdapter *sdaNew = new SqlDataAdapter(cmdNew);
			 DataSet * dsEmployees  = new DataSet();

			 // 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  = S"@FirstName";
			 // Specify the SQL data type of the argument
			 parNew->SqlDbType      = SqlDbType::VarChar;
			 // Specify the value passed as argument
			 parNew->Value          = this->txtFirstName->Text;

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

			 parNew   = new SqlParameter();
			 parNew->ParameterName  = S"@LastName";
			 parNew->SqlDbType      = SqlDbType::VarChar;
			 parNew->Value          = this->txtLastName->Text;
			 cmdNew->Parameters->Add(parNew);

			 parNew   = new SqlParameter();
			 parNew->ParameterName  = S"@Gender";
			 parNew->SqlDbType      = SqlDbType::Int;
			 parNew->Value          = __box(this->cboGender->SelectedIndex);
			 cmdNew->Parameters->Add(parNew);
			 
			 parNew   = new SqlParameter();
			 parNew->ParameterName  = S"@Notes";
			 parNew->SqlDbType      = SqlDbType::Text;
			 parNew->Value          = this->txtNotes->Text;
			 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(S"A new record has been created in the Persons table");

			 this->txtFirstName->Text = "";
			 this->txtLastName->Text  = "";
			 this->cboGender->SelectedIndex = 0;
			 this->txtNotes->Text = S"";

			 // Update the data set with the new information from the data adapter
			 sdaNew->Fill(dsEmployees, S"Persons");
		 }

private: System::Void button2_Click(System::Object *  sender, System::EventArgs *  e)
		 {
			 Close();
		 }

};
}


Home	Copyright © 2004-2012, FunctionX