COMO: Chamar um procedimento armazenado com parâmetros através de ADO.NET e Visual C++ .NET

Existem várias formas de utilizar o ADO.NET para chamar um procedimento armazenado e para recuperar valores de retorno e de saída parâmetros, incluindo:

• Utilize um objecto ' DataAdapter ' ou DataSet para recolher as linhas devolvidas e para trabalhar com estas linhas em conjunto com os valores de retorno e os parâmetros de saída.
• Utilize um objecto DataReader para recolher as linhas devolvidas, para percorrer estas linhas e para reunir valores de retorno e parâmetros de saída.
• Utilize o método ExecuteScalar para devolver o valor da primeira coluna da primeira linha os resultados com os valores de retorno e os parâmetros de saída. Isto é útil com funções de agregação.
• Utilize o método ExecuteNonQuery para devolver apenas os parâmetros de saída e os valores de retorno. As linhas devolvidas são eliminadas. Isto é útil para executar consultas de acção.

Este artigo demonstra os últimos três métodos e utiliza SqlCommand e os objectos OleDbCommand . Certifique-se de que copia apenas o código para o fornecedor gerido que está a utilizar. Se não tiver a certeza qual deverá utilizar o fornecedor gerido, visite o seguinte site Microsoft Developer Network Web: Em cada um dos exemplos neste artigo, os parâmetros são adicionados à colecção de parâmetros do objecto Command . O fornecedor de dados do SQL Server .NET (SqlClient) suporta parâmetros com nome. Por conseguinte, quando utiliza o objecto SqlCommand , não tem os parâmetros de adicionar numa ordem específica, mas os parâmetros tem de ter o nome correcto. Neste caso, terá de adicionar a arroba (@) prefixo aos nomes de parâmetro.

Em alternativa, o fornecedor de dados OLE DB .NET não suporta parâmetros com nome. Por conseguinte, quando utiliza o objecto OleDbCommand , terá de adicionar os parâmetros na ordem correcta (que é a mesma ordem em que está definido no procedimento armazenado back-end). Neste caso, não é necessário adicionar o prefixo "@" aos nomes de parâmetro e os nomes de parâmetro não são necessário corresponder às que são definidas no procedimento armazenado.

Utilizar DataReader a devolver a linhas e parâmetros

Pode utilizar o objecto DataReader para devolver uma sequência só de leitura, só de reencaminhamento de dados. As informações contidas DataReader podem ser provenientes um procedimento armazenado. Este exemplo utiliza o objecto DataReader para executar um procedimento armazenado que tem uma entrada e um parâmetro de saída e, em seguida, move pelos registos devolvidos para visualizar os parâmetros de devolução.

1. Na base de dados de exemplo pubs , crie o seguinte procedimento armazenado no computador que esteja a executar o Microsoft SQL Server:

   Create Procedure TestProcedure
       (
           @au_idIN varchar (11),
           @numTitlesOUT Integer OUTPUT
       )
   As
   
   select A.au_fname, A.au_lname, T.title 
   from authors as A join titleauthor as TA on
   A.au_id=TA.au_id
   join titles as T
   on T.title_id=TA.title_id
   where A.au_id=@au_idIN
   set @numTitlesOUT = @@Rowcount
   return (5) 
   					

2. Inicie o Microsoft Visual Studio .NET e, em seguida, crie um novo projecto de aplicação C++ gerida no Visual C++. NET.
3. No Solution Explorer, faça duplo clique em ficheiro de origem (.cpp).
4. No ficheiro de origem, substitua o código por defeito o seguinte código:

   Cliente SQL

   Nota <username>Tem de alterar o ID de utilizador <utilizador> valor e a palavra-passe = valor < palavra-passe segura > para os valores correctos antes de executar este código. Certifique-se que o ID de utilizador tem as permissões adequadas para efectuar esta operação na base de dados.

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   using namespace System::Data::SqlClient;
   
   // This is the entry point for this application.
   int _tmain(void)
   {
       try{
           SqlConnection *myCon = new SqlConnection("Data Source=mySQLServer;User ID=<username>;
                                                     Password=<strong password>;initial catalog=pubs;");
           myCon->Open();
           
           SqlCommand *myCmd = new SqlCommand("TestProcedure", myCon);
           myCmd->CommandType = CommandType::StoredProcedure;
                   
           // Parameter order does not matter because SqlClient supports named 
           // parameters. Type the exact parameter names as declared in the
           // stored procedure definition at the backend.
           // Parameters should be prefixed with @.
           myCmd->Parameters->Add("@au_idIN",SqlDbType::VarChar,11);
           myCmd->Parameters->get_Item(0)->Value=S"213-46-8915";
   		
           SqlParameter *retParam;
           retParam=myCmd->Parameters->Add("RetVal",SqlDbType::Int,4);
           retParam->Direction=ParameterDirection::ReturnValue;
   
           SqlParameter *outParam;
           outParam=myCmd->Parameters->Add("@numTitlesOUT",SqlDbType::Int,4);
           outParam->Direction=ParameterDirection::Output;
   	
           SqlDataReader *myReader;	
           myReader=myCmd->ExecuteReader();
   	
           while(myReader->Read())
           {
               for(int col=0;col<myReader->FieldCount;col++)
               {
                   Console::Write("{0}: {1}",(myReader->GetName(col))->ToString(),
                                             (myReader->GetValue(col))->ToString());
                   Console::WriteLine();
               }
                   Console::WriteLine();
           }
           myReader->Close();
           Console::WriteLine("Output Param:{0}; Return Value:{1}",
                               outParam->Value,retParam->Value);
           myCon->Close();
       }
       catch(SqlException *mySqlEx)
       {	
           for(int i=0;i<mySqlEx->Errors->Count;i++)
           {
               Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                  Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
           }
       }
       catch(System::Exception *ex)
       {
           Console::WriteLine(ex->get_Message());
       }
   }
   					

   Fornecedor de dados de base de dados de OLE

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   using namespace System::Data::OleDb;
   
   // This is the entry point for this application.
   int _tmain(void)
   {
       try{
           OleDbConnection *myCon = new OleDbConnection("Provider=SQLOLEDB.1;
                                                         Data Source=mySQLServer;User ID=<username>;
                                                         Password=<strong password>;initial catalog=pubs;");
           myCon->Open();
           
           OleDbCommand *myCmd = new OleDbCommand("TestProcedure", myCon);
           myCmd->CommandType = CommandType::StoredProcedure;
   
           // The following notation also works. To test this notation,
           // comment out the above two lines, and uncomment the next two lines.
           // OleDbCommand *myCmd = new OleDbCommand("{?=call TestProcedure(?,?)}", myCon);
           // myCmd->CommandType = CommandType::Text;
   
   
           // The parameter order is important in this sample. Parameters are
           // matched with stored procedure parameters in the order they are supplied.
           // Names can be anything. You do not have to prefix them with "@".
           OleDbParameter* retParam=myCmd->Parameters->Add("RetVal",OleDbType::Integer,4);
           retParam->Direction=ParameterDirection::ReturnValue;
   
           myCmd->Parameters->Add("au_idIN",OleDbType::VarChar,11);
           myCmd->Parameters->Item[1]->Value=S"213-46-8915";
   
           OleDbParameter *outParam=myCmd->Parameters->Add("numTitlesOUT",OleDbType::Integer,4);
           outParam->Direction=ParameterDirection::Output;
   	
           OleDbDataReader *myReader;	
           myReader=myCmd->ExecuteReader();
   	
           int rowCnt=0;
   	
           while(myReader->Read())
           {
               for(int col=0;col<myReader->FieldCount;col++)
               {
                   Console::Write("{0}: {1}",(myReader->GetName(col))->ToString(),
                                             (myReader->GetValue(col))->ToString());
                   Console::WriteLine();
               }
            			
               Console::WriteLine();
               rowCnt++;
           }
   
           myReader->Close();
           Console::WriteLine("Output Param:{0}; Return Value:{1}",outParam->
                               Value,retParam->Value);
           myCon->Close();
       }
       catch(OleDbException *mySqlEx)
       {
           for(int i=0;i<mySqlEx->Errors->Count;i++)
           {
               Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                   Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
           }
       }
       catch(System::Exception *ex)
       {
           Console::WriteLine(ex->get_Message());
       }
   }
   					

5. Modificar a cadeia de ligação para o objeto Connection apontar para o computador que está a executar o SQL Server.
6. Prima a combinação de teclas CTRL + F5 para compilar e executar o projecto. A janela Output apresenta os títulos dos dois livros, o valor devolvido da 5 e o parâmetro de saída, que contém o número de registos (2). Repare que tem de fechar o DataReader no código para ver os valores de parâmetro. Além disso, note que não é necessário percorrer todos os registos para ver os parâmetros de retorno se DataReader está fechado.

Utilize o método ExecuteScalar do objecto Command

Pode utilizar o método ExecuteScalar do objeto Command para obter valores de parâmetro. Além disso, ExecuteScalar devolve a primeira coluna da primeira linha do procedimento armazenado. Isto é útil para funções de agregação como no exemplo seguinte.

1. Crie o seguinte procedimento armazenado no computador que esteja a executar o SQL Server:

   Create Procedure TestProcedure2
       (
           @au_idIN varchar (11)
       )
   As
   select count (T.title) 
   from authors as A join titleauthor as TA on
   A.au_id=TA.au_id
   join titles as T
   on T.title_id=TA.title_id
   where A.au_id=@au_idIN
   Return(5)
   					

2. Inicie o Visual Studio .NET e crie um novo C++ gerido pelo projecto de aplicação no Visual C++. NET.
3. No Solution Explorer, faça duplo clique em ficheiro de origem (.cpp).
4. No ficheiro de origem, substitua o código por defeito o seguinte código:

   Cliente SQL

   Nota <username>Tem de alterar o ID de utilizador <utilizador> valor e a palavra-passe = valor < palavra-passe segura > para os valores correctos antes de executar este código. Certifique-se que o ID de utilizador tem as permissões adequadas para efectuar esta operação na base de dados.

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   using namespace System::Data::SqlClient;
   // This is the entry point for this application.
   int _tmain(void)
   {
   
       try{
           SqlConnection *myCon = new SqlConnection("Data Source=mySQLServer;User ID=<username>;
                                                     Password=<strong password>;initial catalog=pubs;");
           myCon->Open();
           
           SqlCommand *myCmd = new SqlCommand("TestProcedure2", myCon);
           myCmd->CommandType = CommandType::StoredProcedure;
   
           SqlParameter* retParam=myCmd->Parameters->Add("RetVal",SqlDbType::Int,4);
           retParam->Direction=ParameterDirection::ReturnValue;
   
           myCmd->Parameters->Add("@au_idIN",SqlDbType::VarChar,11);
           myCmd->Parameters->Item[1]->Value=S"213-46-8915";
   	
           String *cnt;
           cnt=myCmd->ExecuteScalar()->ToString();
           Console::WriteLine("Cnt:{0}; Return Value:{1}",cnt,retParam->Value);
   
           myCon->Close();
   
       }
       catch(SqlException *mySqlEx)
       {
           for(int i=0;i<mySqlEx->Errors->Count;i++)
           {
               Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                   Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
           }
       }
       catch(System::Exception *ex)
       {
           Console::WriteLine(ex->get_Message());
       }
   
   }
   					

   Fornecedor de dados de base de dados de OLE

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   using namespace System::Data::OleDb;
   
   // This is the entry point for this application.
   int _tmain(void)
   {
       try{
           OleDbConnection *myCon = new OleDbConnection("Provider=SQLOLEDB.1;
                                                         Data Source=mySQLServer;User ID=<username>;
                                                         Password=<strong password>;initial catalog=pubs;");
           myCon->Open();
           
           OleDbCommand *myCmd = new OleDbCommand("{?=call TestProcedure2(?)}", myCon);
           myCmd->CommandType = CommandType::Text;
   
           OleDbParameter * retParam=myCmd->Parameters->Add("RetVal",OleDbType::Integer,4);
           retParam->Direction=ParameterDirection::ReturnValue;
   
           myCmd->Parameters->Add("au_idIN",OleDbType::VarChar,11);
           myCmd->Parameters->Item[1]->Value=S"213-46-8915";
   	
           String *cnt;
           cnt=myCmd->ExecuteScalar()->ToString();
           Console::WriteLine("Count:{0}; Return Value:{1}",cnt,retParam->Value);
   
           myCon->Close();
       
       }
       catch(OleDbException *mySqlEx)
       {
           for(int i=0;i<mySqlEx->Errors->Count;i++)
           {
               Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                   Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
           }
       }
       catch(System::Exception *ex)
       {
           Console::WriteLine(ex->get_Message());
       }
   
   }	
   					

5. Modificar a cadeia de ligação para o objeto Connection apontar para o computador que está a executar o SQL Server.
6. Prima CTRL + F5 para compilar e executar o projecto. A saída de janela apresenta o valor da primeira coluna da primeira linha (contagem), bem como o valor devolvido.

Utilize o método ExecuteNonQuery do objecto Command

Este exemplo utiliza o método ExecuteNonQuery para executar a consulta e devolver os valores de parâmetro. ExecuteNonQuery também devolve o número de registos que são afectadas a consulta é executada. No entanto, ExecuteNonQuery não devolve quaisquer linhas ou colunas do procedimento armazenado.

O método ExecuteNonQuery é útil quando utiliza instruções INSERT, UPDATE ou DELETE se tiver apenas saber quantas linhas são alteradas. Num procedimento armazenado que está a utilizar uma instrução SELECT, receberá-1 porque existem linhas são afectadas pela consulta.

1. Crie o seguinte procedimento armazenado no computador que esteja a executar o SQL Server:

   Create Procedure TestProcedure3
       (
           @au_idIN varchar (11),
           @au_fnam varchar (30)
       )
   
   As
   Update authors set au_fname = @au_fnam
   where au_id = @au_idin	
   return (5)
   					

2. Inicie o Visual Studio .NET e crie um novo C++ gerido pelo projecto de aplicação no Visual C++. NET.
3. No Solution Explorer, faça duplo clique em ficheiro de origem (.cpp).
4. No ficheiro de origem, substitua o código por defeito o seguinte código:

   Cliente SQL

   Nota <username>Tem de alterar o ID de utilizador <utilizador> valor e a palavra-passe = valor < palavra-passe segura > para os valores correctos antes de executar este código. Certifique-se que o ID de utilizador tem as permissões adequadas para efectuar esta operação na base de dados.

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   using namespace System::Data::SqlClient;
   // This is the entry point for this application.
   int _tmain(void)
   {
       try{
           SqlConnection *myCon = new SqlConnection("Data Source=mySQLServer;User ID=<username>;
                                                     Password=<strong password>;initial catalog=pubs;");
           myCon->Open();
           
           SqlCommand *myCmd = new SqlCommand("TestProcedure3", myCon);
           myCmd->CommandType = CommandType::StoredProcedure;
   
           SqlParameter* retParam=myCmd->Parameters->Add("RetVal",SqlDbType::Int,4);
           retParam->Direction=ParameterDirection::ReturnValue;
   
           myCmd->Parameters->Add("@au_idIN",SqlDbType::VarChar,11);
           myCmd->Parameters->Item[1]->Value=S"213-46-8915";
   
           myCmd->Parameters->Add("@au_fnam",SqlDbType::VarChar,30);
           myCmd->Parameters->Item[2]->Value=S"Marjorie";
   
           int cnt;
           cnt=myCmd->ExecuteNonQuery();
           Console::WriteLine("Number of rows affected:{0}; Return Value:{1}",
                               cnt.ToString(),retParam->Value);
   
           myCon->Close();
   
       }
       catch(SqlException *mySqlEx)
       {
           for(int i=0;i<mySqlEx->Errors->Count;i++)
           {
               Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                       Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
           }
       }
       catch(System::Exception *ex)
       {
           Console::WriteLine(ex->get_Message());
       }
   }
   					

   Fornecedor de dados de base de dados de OLE

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   using namespace System::Data::OleDb;
   
   // This is the entry point for this application.
   int _tmain(void)
   {
       try{
           OleDbConnection *myCon = new OleDbConnection("Provider=SQLOLEDB.1;
                                                         Data Source=mySQLServer;User ID=<username>;
                                                         Password=<strong password>;initial catalog=pubs;");
           myCon->Open();
           
           OleDbCommand *myCmd = new OleDbCommand("{?=call TestProcedure3(?,?)}", myCon);
           myCmd->CommandType = CommandType::Text;
   
           OleDbParameter *retParam=myCmd->Parameters->Add("RetVal",OleDbType::Integer,4);
           retParam->Direction=ParameterDirection::ReturnValue;
   
           myCmd->Parameters->Add("au_idIN",OleDbType::VarChar,11);
           myCmd->Parameters->Item[1]->Value=S"213-46-8915";
   			
           myCmd->Parameters->Add("au_fnam",OleDbType::VarChar,30);
           myCmd->Parameters->Item[2]->Value=S"Marjorie";
   	
           int cnt;
           cnt=myCmd->ExecuteNonQuery();
           Console::WriteLine("Number of rows affected:{0}; Return Value:{1}",
                               cnt.ToString(),retParam->Value);
   
           myCon->Close();
   
       }
       catch(OleDbException *mySqlEx)
       {
           for(int i=0;i<mySqlEx->Errors->Count;i++)
           {
               Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                   Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
           }
       }
       catch(System::Exception *ex)
       {
           Console::WriteLine(ex->get_Message());
       }
   
   }	 
   					

5. Modificar a cadeia de ligação para o objeto Connection apontar para o computador que está a executar o SQL Server.
6. Prima CTRL + F5 para compilar e executar o projecto. A saída de janela apresenta o número de linhas que são afectados ( intRowAffected ) e o valor do parâmetro devolvido.

Para obter informações adicionais, visite os seguintes Web sites da MSDN: