Visual C++ .NET/Database ADO.net/SqlCommand

Call a stored procedure with parameters

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main(){

   String ^Name = "Doors";
   SqlConnection ^connection = gcnew SqlConnection();
   connection->ConnectionString = "SQLConnection";
   try{
       SqlCommand ^cmd = gcnew SqlCommand();
       cmd->Connection = connection;
       cmd->CommandType = CommandType::Text;
       cmd->CommandText =String::Format("SELECT FirstName, LastName FROM Authors WHERE LastName = "{0}"",Name);
       connection->Open();
       SqlDataReader ^reader = cmd->ExecuteReader();
       while(reader->Read()){
           Console::WriteLine("{0} {1}",reader["FirstName"], reader["LastName"]);
       }
       reader->Close();
       // CREATE PROCEDURE dbo.StoriesWhereLastName(@LastName NVARCHAR(32) = NULL) AS
       //   SELECT StoryID, Headline, Story FROM Stories WHERE  LastName = @LastName
       // RETURN
       cmd->CommandType = CommandType::StoredProcedure;
       cmd->CommandText = "StoriesWhereLastName";
       cmd->Parameters->Add(gcnew SqlParameter("@LastName",SqlDbType::VarChar));
       cmd->Parameters["@LastName"]->Value = Name;
       reader = cmd->ExecuteReader();
       while(reader->Read())
       {
           Console::WriteLine(reader["StoryID"]);
           Console::WriteLine(reader["Headline"]);
           Console::WriteLine(reader["Story"]);
           Console::WriteLine();
       }
       reader->Close();
   }
   catch (SqlException ^e)
   {
       Console::WriteLine("No connection the following error occurred: {0}",
           e->Message);
   }
   finally
   {
       connection->Close();
   }

}

 </source>

Execute delete command

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main() {

   String ^Name = "Doors";
   SqlConnection ^connection = gcnew SqlConnection();
   connection->ConnectionString = "SQLConnection";
   try{
      SqlCommand ^cmd = gcnew SqlCommand();
      cmd->Connection = connection;
      connection->Open();
      cmd->CommandType = CommandType::StoredProcedure;
      cmd->CommandText = "InsertAuthor";
      cmd->Parameters->Add(gcnew SqlParameter("@LastName", SqlDbType::VarChar));
      cmd->Parameters->Add(gcnew SqlParameter("@FirstName",SqlDbType::VarChar));
      cmd->Parameters["@LastName"]->Value  = "A";
      cmd->Parameters["@FirstName"]->Value = "B";
      int affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Insert - {0} rows are affected", affected);
      cmd->CommandType = CommandType::Text;
      cmd->CommandText = "UPDATE Authors SET LastName = "Doe" WHERE LastName = "A"";
      affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Update - {0} rows are affected", affected);
      cmd->CommandType = CommandType::Text;
      cmd->CommandText = "DELETE FROM Authors WHERE LastName = "Doe"";
      affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Delete - {0} rows are affected", affected);
   }catch (SqlException ^e){
       Console::WriteLine("No connection the following error occurred: {0}",
           e->Message);
   }finally{
       connection->Close();
   }

}

 </source>

Execute update command

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main() {

   String ^Name = "Doors";
   SqlConnection ^connection = gcnew SqlConnection();
   connection->ConnectionString = "SQLConnection";
   try{
      SqlCommand ^cmd = gcnew SqlCommand();
      cmd->Connection = connection;
      connection->Open();
      cmd->CommandType = CommandType::StoredProcedure;
      cmd->CommandText = "InsertAuthor";
      cmd->Parameters->Add(gcnew SqlParameter("@LastName", SqlDbType::VarChar));
      cmd->Parameters->Add(gcnew SqlParameter("@FirstName",SqlDbType::VarChar));
      cmd->Parameters["@LastName"]->Value  = "A";
      cmd->Parameters["@FirstName"]->Value = "B";
      int affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Insert - {0} rows are affected", affected);
      cmd->CommandType = CommandType::Text;
      cmd->CommandText = "UPDATE Authors SET LastName = "Doe" WHERE LastName = "A"";
      affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Update - {0} rows are affected", affected);
      cmd->CommandType = CommandType::Text;
      cmd->CommandText = "DELETE FROM Authors WHERE LastName = "Doe"";
      affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Delete - {0} rows are affected", affected);
   }catch (SqlException ^e){
       Console::WriteLine("No connection the following error occurred: {0}",
           e->Message);
   }finally{
       connection->Close();
   }

}

 </source>

use SqlCommand to call stored procedure

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main() {

   String ^Name = "Doors";
   SqlConnection ^connection = gcnew SqlConnection();
   connection->ConnectionString = "SQLConnection";
   try{
      SqlCommand ^cmd = gcnew SqlCommand();
      cmd->Connection = connection;
      connection->Open();
      cmd->CommandType = CommandType::StoredProcedure;
      cmd->CommandText = "InsertAuthor";
      cmd->Parameters->Add(gcnew SqlParameter("@LastName", SqlDbType::VarChar));
      cmd->Parameters->Add(gcnew SqlParameter("@FirstName",SqlDbType::VarChar));
      cmd->Parameters["@LastName"]->Value  = "A";
      cmd->Parameters["@FirstName"]->Value = "B";
      int affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Insert - {0} rows are affected", affected);
      cmd->CommandType = CommandType::Text;
      cmd->CommandText = "UPDATE Authors SET LastName = "Doe" WHERE LastName = "A"";
      affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Update - {0} rows are affected", affected);
      cmd->CommandType = CommandType::Text;
      cmd->CommandText = "DELETE FROM Authors WHERE LastName = "Doe"";
      affected = cmd->ExecuteNonQuery();
      Console::WriteLine("Delete - {0} rows are affected", affected);
   }catch (SqlException ^e){
       Console::WriteLine("No connection the following error occurred: {0}",
           e->Message);
   }finally{
       connection->Close();
   }

}

 </source>