C# - Valores de retorno duma Stored Procedure (OutPut e InputOutPut)
  Neste artigo veremos como obter o valor de retorno de uma stored procedure.

Você esta usando uma stored procedure em sua aplicação e precisa obter o seu valor de retorno.

Como fazer isso ?

Para fazer isso podemos definir um parâmetro usando a propriedade ParameterDirection de ReturnValue para acessar o valor de retorno.

No exemplo do artigo vamos usar o banco de dados Northwind.mdf e vamos criar uma stored procedure chamada GetNumeroLinhaProdutos que acessa a tabela Products e seleciona todos os registros da tabela e retorna o número de linhas da mesma.

Acima vemos o código da stored procedure que irá retornar o valor @@ROWCOUNT para a consulta em um parâmetro de saída.

Nota: @@ROWCOUNT retorna o número de linhas afetada pela última instrução. (Se o número de linhas for maior que 2 bilhões use ROWCOUNT_BIG.)

Cada stored procedure retorna um valor inteiro para o seu chamador. Se o valor para o código de retorno não for explicitamente definido, o valor padrão é 0.

O valor de retorno é acessado via ADO .NET através de um parâmetro que o representa. O parâmetro é definido com a propriedade ParameterDirection de ReturnValue.

O tipo de dados do parâmetro ReturnValue tem que ser inteiro.  A tabela abaixo descreve todos os valores na enumeração ParameterDirection:

Valor Descrição
Input O parâmetro é um parâmetro de entrada permitindo ao chamado passar u m valor para a stored procedure. (é o mais comum)
InputOutput O parâmetro é tanto de entrada como de saída permitindo que o chamador passe um valor para a stored procedure e que ela retorne um valor ao chamador.
Output O parâmetro é de saída permitindo que a stored procedure passe um valor de volta ao chamador.
ReturnValue O parâmetro representa o valor retornado a partir da stored procedure.

Objetivos

Obter o valor de retorno de uma stored procedure.

Recursos usados:

Criando o projeto

Abra o Visual Studio 2012 Express for Windows Desktop e clique em New Project;

Selecione a linguagem Visual Basic  e o template Windows Forms Application e informe o nome valorRetorno_StoredProcedure;

No formulário form1.vb do projeto vamos incluir os seguintes controles:

Disponha os controles conforme o leiaute da figura abaixo:


No formulário form1.cs vamos também declarar os namespaces usados no projeto:


using System;

using System.Data;

using System.Data.SqlClient;

using System.Windows.Forms;

using System.Configuration;
 

A seguir, logo após a declaração da classe Form1, vamos definir uma variável sqlConnectString do tipo string que será visível em todo o formulário:

string sqlConnectString="";

Para fazer a conexão com o banco de dados vamos obter a string de conexão do arquivo App.config usando a classe ConfigurationManger. Para fazer isso devemos incluir uma referência no projeto ao namespace System.Configuration.

 

Abaixo vemos a string e conexão no arquivo App.Config:
 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="ConexaoSQLClientes"
            connectionString="Data Source=(LocalDB)\v11.0;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

 

O código do evento Click do botão de comando - Obter String de conexão - é dado a seguir:

 

private void btnStringConexao_Click(object sender, EventArgs e)
 {
            try
            {
                txtStringConexao.Text =  ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
                sqlConnectString = txtStringConexao.Text;
                btnExecutar.Enabled=true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro " + ex.Message);
            }
 }

 

Agora no evento Click do botão de comando btnExecutar vamos definir o código abaixo que vai executar a stored procedure, definir os parâmetros de saída e obter o valor de retorno:

 

 private void btnExecutar_Click(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection conexaoSQL = new SqlConnection(sqlConnectString))
                {
                    lbResultado.Items.Add("### Executando a stored Procedure << GetNumeroLinhaProdutos que retorna o no. de linhas da tabela Produtos  ### ");
                    lbResultado.Items.Add(" ");
                    // Define o comando como a stored proceure para usar em um DataReader.
                    SqlCommand command = new SqlCommand("GetNumeroLinhaProdutos", conexaoSQL);
                    command.CommandType = CommandType.StoredProcedure;
                    // Cria o parametro de saida
                    command.Parameters.Add("@NumeroLInhas", SqlDbType.Int).Direction = ParameterDirection.Output;
                    // Cria o parametro de retorno
                    SqlParameter parametroRetorno = command.Parameters.Add("@NumeroLInhas", SqlDbType.Int);
                    parametroRetorno.Direction = ParameterDirection.ReturnValue;

                    lbResultado.Items.Add("Antes da execução.  Numero de Linhas = " + command.Parameters["@NumeroLInhas"].Value);
                    // Cria um DataReader para o result set retornado pela stored procedure
                    conexaoSQL.Open();
                    SqlDataReader dr = command.ExecuteReader();
                    lbResultado.Items.Add("Depois da execução. Numero de Linhas = " + command.Parameters["@NumeroLInhas"].Value);

                    // Itera sobre os registros para o  DataReader.
                    int contadorLinhas = 0;
                    while (dr.Read())
                    {
                        //vou apenas contar as linhas
                        contadorLinhas++;
                    }

                    lbResultado.Items.Add("Depois de ler todas as " + contadorLinhas + " linhas.  Valor retornado  = " + command.Parameters["@NumeroLInhas"].Value);

                    // Fecha o DataReader
                    dr.Close();

                    lbResultado.Items.Add("Depois de fechar o DataReader  << dr.Close() >>. Valor Retornado =  " + command.Parameters["@NumeroLInhas"].Value);
                    conexaoSQL.Close();
                    lbResultado.Items.Add("Depois de fechar a conexão << Connection.Close() >>. Valor Retornado =  " + command.Parameters["@NumeroLInhas"].Value);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Neste exemplo criamos um DataReader a partir da stored procedure que retorna o número de registros da tabela Products como valor de retorno.

O código exibe o valor do parâmetro de retorno em 5 diferentes estágios da execução da tarefa :

Os parâmetros de retornos da stored procedure usados para montar o DataReader não estão disponíveis até que o DataReader esteja fechado pela chamada do método Close() ou pelo método Dispose().( Você não tem que ler quaisquer registros no DataReader para obter o valor de retorno.)

Se você usar o método Fill() do DataAdpater para preencher o DataTable o valor de retorno estará disponível imediatamente após o método Fill() for chamado.

Entendendo os principais pontos do código:

1-     SqlCommand command = new SqlCommand("GetNumeroLinhaProdutos", conexaoSQL);
       command.CommandType = CommandType.StoredProcedure;

Cria um novo objeto Command definindo o nome da stored procedure que será executada na conexão e define o tipo como StoredProcedure.

2-   command.Parameters.Add("@NumeroLInhas", SqlDbType.Int).Direction = ParameterDirection.Output;
     //aqui definimos o parâmetro de retorno e obtemo o seu valor
     SqlParameter parametroRetorno = command.Parameters.Add("@NumeroLInhas", SqlDbType.Int);
     parametroRetorno.Direction = ParameterDirection.ReturnValue;

Definimos o parâmetro @NumeroLinhas usado na stored Procudure como um parâmetro de saída - ParameterDirection.Output;
Definimos também parâmetro parametroRetorno como do tipo SqlParameter para receber o valor em @NumeroLinhas;

Executando o projeto veremos a exibição do resultado da execução da tarefa conforme abaixo:

Vamos mostrar agora como tratar com parâmetros de retorno do tipo InputOutPut. Neste exemplo vamos definir um parâmetro do tipo InputOutPut e atribuir a ele um valor igual a 1; durante a execução ele irá mudar de valor retornando o número de registros da tabela Categories do banco de dados Northwind.mdf.

Para obter o valor de retorno de um parâmetro do tipo InputOutPut podemos incluir um novo botão de comando  - btnInputOutPut - no formulário e no seu evento Click definir o seguinte código:

 private void btnInputOutPut_Click(object sender, EventArgs e)
  {
     try
     {
       using (SqlConnection conexaoSQL = new SqlConnection(sqlConnectString))
       {
        lbResultado.Items.Add("### Exemplo de obtenção de valor do parâmetro do tipo InputOutPut  ###");
           conexaoSQL.Open();
           using (SqlCommand command1 = new SqlCommand())
           {
                command1.CommandType = CommandType.Text;
                command1.Connection = conexaoSQL;
           // DIREÇÃO :: Input
            command1.CommandText = "SELECT @MeuParametro = @MeuParametro + Count(*) FROM Categories";
            SqlParameter parametroRetorno = command1.Parameters.Add("@MeuParametro", SqlDbType.SmallInt);
                //aqui estou atribuindo um valor ao parâmetro
                 parametroRetorno.Value = 1;
                 lbResultado.Items.Add("Valor atribuido ao parâmetro de retorno =  " + parametroRetorno.Value);
                        //estou definindo o tipo de retorno
                        parametroRetorno.Direction = ParameterDirection.InputOutput;
                        //executando o comando
                        command1.ExecuteNonQuery();
                        //O valor agora é 9 depois de ser inicialmente definido como 1
                        int novoValorParametro = Convert.ToInt32(parametroRetorno.Value);
            lbResultado.Items.Add("O novo valor do parâmetro de retorno agora é =  " + novoValorParametro);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
 }

Executando novamente o projeto e obtendo o retorno para o parâmetro do tipo InputOutPut teremos:

Você pode definir outros valores para obter a partir da stored procedure dependendo da sua necessidade. O exemplo serviu apenas para mostrar como você pode fazer a tarefa.

Pegue o projeto completo aqui :    ValorRetorno_StoreProcedure.zip 

Filipenses 2:9 Pelo que também Deus o exaltou soberanamente, e lhe deu o nome que é sobre todo nome;

Filipenses 2:10 para que ao nome de Jesus se dobre todo joelho dos que estão nos céus, e na terra, e debaixo da terra,

Filipenses 2:11 e toda língua confesse que Jesus Cristo é Senhor, para glória de Deus Pai.

Veja os Destaques e novidades do SUPER DVD Visual Basic (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Quer aprender C# ??

 

Referências:

José Carlos Macoratti