COMO: Chamar um procedimento armazenado parametrizado usando ADO .NET e Visual C++ .NET

Há várias maneiras para usar o ADO.NET para chamar um procedimento armazenado e para retornar valores de retorno e parâmetros, incluindo de saída:

• Use um objeto DataAdapter ou DataSet para reunir as linhas retornadas e para trabalhar com essas linhas além para os valores de retorno e os parâmetros de saída.
• Use um objeto DataReader para coletar as linhas retornadas para percorrer essas linhas e para obter valores de retorno e parâmetros de saída.
• Use o método ExecuteScalar para retornar o valor da primeira coluna da primeira linha os resultados com os valores de retorno e os parâmetros de saída. Isso é mais útil com funções agregadas.
• Use o método ExecuteNonQuery para retornar somente os parâmetros de saída e os valores de retorno. As linhas retornadas são descartadas. Isso é mais útil para executar consultas ação.

Este artigo demonstra os três últimos métodos e usa o SqlCommand e os objetos OleDbCommand . Certifique-se de que você copiar apenas o código para o provedor gerenciado que você está usando. Se você não tiver certeza qual provedor gerenciado, você deve usar, visite o seguinte site da Microsoft Developer Network: Em cada um dos exemplos neste artigo, os parâmetros são adicionados à coleção de parâmetros do objeto Command . O provedor de dados .NET do SQL Server (SqlClient) oferece suporte a parâmetros nomeados. Portanto, quando você usa o objeto SqlCommand , você não tem os parâmetros de adicionar em uma ordem específica, mas os parâmetros devem ter o nome correto. Nesse caso, você deve adicionar o sinal de arroba (@) prefixo para os nomes de parâmetro.

Como alternativa, o provedor de dados OLE DB .NET não dá suporte a parâmetros nomeados. Portanto, quando você usa o objeto OleDbCommand , você deve adicionar os parâmetros na ordem correta (que é a mesma ordem em que é definido no procedimento armazenado back-end). Nesse caso, você não precisa adicionar o prefixo "@" para os nomes de parâmetro, e os nomes de parâmetro não precisam corresponder àqueles definidos no procedimento armazenado.

Use o DataReader para retornar linhas e parâmetros

Você pode usar o objeto DataReader para retornar um fluxo somente leitura, somente de encaminhamento de dados. As informações que contém o DataReader podem vir de um procedimento armazenado. Este exemplo usa o objeto DataReader para executar um procedimento armazenado que tem uma entrada e um parâmetro de saída e, em seguida, move pelos registros retornados para exibir os parâmetros de retorno.

1. No banco de dados de exemplo pubs , criar o seguinte procedimento armazenado no computador que está executando 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, criar um novo projeto Managed C++ Application no Visual C++. NET.
3. No Solution Explorer, clique duas vezes o arquivo de origem (.cpp).
4. No arquivo de origem, substitua o código padrão com o código a seguir:

   Cliente SQL

   Observação <username>Você deve alterar a ID de usuário <nomedousuário> valor e a senha = < senha de alta segurança > valor para os valores corretos antes de executar esse código. Certifique-se que a ID de usuário tenha as permissões apropriadas para executar esta operação no banco 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());
       }
   }
   					

   Provedor de dados de BD 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 seqüência de conexão para o objeto de conexão apontar para o computador que está executando o SQL Server.
6. Pressione a combinação de teclas CTRL + F5 para compilar e executar o projeto. A janela de saída exibe os títulos de dois livros, o valor de retorno de 5 e o parâmetro de saída, que contém o número de registros (2). Observe que você deve fechar o DataReader no código para ver os valores de parâmetro. Além disso, observe que não é necessário para percorrer todos os registros para ver os parâmetros de retorno se o DataReader é fechado.

Use o método ExecuteScalar do objeto de comando

Você pode usar o método ExecuteScalar do objeto de comando para recuperar valores de parâmetro. Além disso, ExecuteScalar retorna a primeira coluna da primeira linha do procedimento armazenado. Isso é mais útil para funções agregadas como no exemplo a seguir.

1. Criar o seguinte procedimento armazenado no computador que está executando 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++ gerenciado projeto de aplicativo no Visual C++.NET.
3. No Solution Explorer, clique duas vezes o arquivo de origem (.cpp).
4. No arquivo de origem, substitua o código padrão com o código a seguir:

   Cliente SQL

   Observação <username>Você deve alterar a ID de usuário <nomedousuário> valor e a senha = < senha de alta segurança > valor para os valores corretos antes de executar esse código. Certifique-se que a ID de usuário tenha as permissões apropriadas para executar esta operação no banco 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());
       }
   
   }
   					

   Provedor de dados de BD 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 seqüência de conexão para o objeto de conexão apontar para o computador que está executando o SQL Server.
6. Pressione CTRL + F5 para compilar e executar o projeto. A saída janela exibe o valor da primeira coluna da primeira linha (contagem), bem como o valor de retorno.

Use o método ExecuteNonQuery do objeto de comando

Este exemplo utiliza o método ExecuteNonQuery para executar a consulta e retornar os valores de parâmetro. ExecuteNonQuery também retorna o número de registros que são afetadas após a consulta é executada. No entanto, ExecuteNonQuery não retorna quaisquer linhas ou colunas do procedimento armazenado.

O método ExecuteNonQuery é mais útil quando você usa instruções INSERT, UPDATE ou DELETE caso você precise saber quantas linhas são alteradas. Em um procedimento armazenado no qual você estiver usando apenas uma instrução SELECT, você recebe-1 porque não há linhas são afetadas pela consulta.

1. Criar o seguinte procedimento armazenado no computador que está executando 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++ gerenciado projeto de aplicativo no Visual C++.NET.
3. No Solution Explorer, clique duas vezes o arquivo de origem (.cpp).
4. No arquivo de origem, substitua o código padrão com o código a seguir:

   Cliente SQL

   Observação <username>Você deve alterar a ID de usuário <nomedousuário> valor e a senha = < senha de alta segurança > valor para os valores corretos antes de executar esse código. Certifique-se que a ID de usuário tenha as permissões apropriadas para executar esta operação no banco 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());
       }
   }
   					

   Provedor de dados de BD 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 seqüência de conexão para o objeto de conexão apontar para o computador que está executando o SQL Server.
6. Pressione CTRL + F5 para compilar e executar o projeto. A saída janela exibe o número de linhas que são afetados ( intRowAffected ) e o valor do parâmetro retorno.

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