Como chamar um procedimento armazenado parametrizado usando o ADO.NET e Visual Basic .NET

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

• Use um objeto DataSet para reunir as linhas retornadas e trabalhar com essas linhas além para os valores de retorno e os parâmetros de retorno.
• Use um objeto DataReader para coletar as linhas retornadas para percorrer essas linhas e para reunir valores de retorno e parâmetros de retornar.
• 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 retorno. Isso é mais útil com funções agregadas.
• Use o método ExecuteNonQuery para retornar somente os parâmetros de retorno 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 . 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. Quando você usa o objeto OleDbCommand , você deve adicionar os parâmetros na ordem correta e você não pode usar os parâmetros por nome.

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. Criar o seguinte procedimento armazenado no servidor 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. Use a declaração Imports no sistema e os namespaces System.Data para que você não tem que qualificar declarações esses namespaces posteriormente no seu código. Você deve usar a declaração Imports antes para qualquer outra declaração. Certifique-se copiar apenas o código para o provedor que você tenha escolhido. SQL Client

   Imports System.Data.SqlClient					

   Provedor de dados OLEDB

   Imports System.Data.OleDb					

3. Add the following code to the Form_Load event:SQL Client

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim NumTitles As SqlParameter = testCMD.Parameters.Add & _
       ("@numtitlesout", SqlDbType.Int)
   NumTitles.Direction = ParameterDirection.Output
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim myReader As SqlDataReader = testCMD.ExecuteReader()
   Console.WriteLine("Book Titles for this Author:")
   Do While myReader.Read
       Console.WriteLine("{0}", myReader.GetString(2))
   Loop
   myReader.Close()
           
   Console.WriteLine("Return Value: " & (RetValue.Value))
   Console.WriteLine("Number of Records: " & (NumTitles.Value))
   					

   OLE DB Data Provider

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=sqloledb;Data Source=server;" & _
       "integrated security=sspi;initial Catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As OleDbParameter = testCMD.Parameters.Add & _
       ("RetValue", OleDbType.Integer)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim NumTitles As OleDbParameter = testCMD.Parameters.Add & _
       ("@numtitlesout", OleDbType.Integer)
   NumTitles.Direction = ParameterDirection.Output
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim myReader As OleDbDataReader = testCMD.ExecuteReader()
   Console.WriteLine("Book Titles for this Author:")
   Do While myReader.Read
       Console.WriteLine("{0}", myReader.GetString(2))
   Loop
   myReader.Close()
   
   Console.WriteLine("Return Value: " & (RetValue.Value))
   Console.WriteLine("Number of Records: " & (NumTitles.Value))
   					

4. Modificar a seqüência de conexão para o objeto Connection apontar para o servidor que está executando o SQL Server.
5. Execute o código. Observe que o DataReader recupera os registros e, em seguida, retorna os valores de parâmetro. Você pode usar o método de leitura do objeto DataReader para percorrer os registros retornados.
   
   A saída janela 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 servidor que está executando o SQL Server:

   Create Procedure TestProcedure2
    (
      @au_idIN varchar (11)
     )
   As
   /* set nocount on */ 
   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. Use a declaração Imports no sistema e os namespaces System.Data para que você não tem que qualificar declarações esses namespaces posteriormente no seu código. Você deve usar a declaração Imports antes para qualquer outra declaração. Certifique-se que você copiar apenas o código para o provedor que você tenha escolhido. SQL Client

   Imports System.Data.SqlClient					

   Provedor de dados OLEDB

   Imports System.Data.OleDb					

3. Adicione o seguinte código ao evento Form_Load : SQL Client

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure2", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim intCount As Integer = testCMD.ExecuteScalar
   Console.WriteLine(intCount)
   Console.WriteLine("Return Value: " & (RetValue.Value))
   					

   Provedor de dados OLEDB

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=SQLOLEDB;Data Source=server;" & _
       "integrated Security=sspi;initial catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure2", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetVal As OleDbParameter = testCMD.Parameters.Add & _
       ("RetVal", OleDbType.Integer)
   RetVal.Direction = ParameterDirection.ReturnValue
   Dim IdIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   IdIn.Direction = ParameterDirection.Input
   
   IdIn.Value = "213-46-8915"
           
   PubsConn.Open()
   
   Dim intCount As Integer = testCMD.ExecuteScalar
   
   Console.WriteLine("Number of Rows: " & intCount)
   Console.WriteLine(RetVal.Value)
   					

4. Modificar a seqüência de conexão para o objeto Connection apontar para o servidor que está executando o SQL Server.
5. Execute o código. Observe que o método ExecuteScalar do objeto de comando retorna os parâmetros. ExecuteScalar também retorna o valor da coluna 1, linha 1 do conjunto de linhas retornado. Portanto, o valor de intCount é o resultado da função contagem do procedimento armazenado.

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 servidor que está executando o SQL Server:

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

2. Use a declaração Imports no sistema e os namespaces System.Data para que você não tem que qualificar declarações esses namespaces posteriormente no seu código. Você deve usar a declaração Imports antes para qualquer outra declaração. Certifique-se que você copiar apenas o código para o provedor que você tenha escolhido. SQL Client

   Imports System.Data.SqlClient					

   Provedor de dados OLEDB

   Imports System.Data.OleDb					

3. Adicione o seguinte código ao evento Form_Load : SQL Client

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure3", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim auFname As SqlParameter = testCMD.Parameters.Add & _
       ("@au_fnam", SqlDbType.VarChar, 30)
   auFname.Direction = ParameterDirection.Input
   
   auIDIN.Value = "213-46-8915"
   auFname.Value = "Marjorie"
   PubsConn.Open()
   
   Dim rvRows As Integer = testCMD.ExecuteNonQuery
   Console.WriteLine(rvRows)
   Console.WriteLine(RetValue.Value)
   					

   Provedor de dados OLEDB

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=SQLOLEDB;Data Source=server;" & _
       "integrated Security=sspi;initial catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure3", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetVal As OleDbParameter = testCMD.Parameters.Add & _
       ("RetVal", OleDbType.Integer)
   RetVal.Direction = ParameterDirection.ReturnValue
   Dim IdIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   IdIn.Direction = ParameterDirection.Input
   Dim FnameIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_fname", OleDbType.VarChar, 30)
   FnameIn.Direction = ParameterDirection.Input
   
   IdIn.Value = "213-46-8915"
   FnameIn.Value = "Marjorie"
   
   PubsConn.Open()
   
   Dim intRowAffected As Integer = testCMD.ExecuteNonQuery
   
   Console.WriteLine("Number of Rows affected: " & intRowAffected)
   Console.WriteLine(RetVal.Value)
   					

4. Modificar a seqüência de conexão para o objeto Connection apontar para o servidor que está executando o SQL Server.
5. Execute o código. A janela de saída exibe o número de linhas afetadas ( intRowAffect ) e o valor do parâmetro retorno.

Para informações adicionais, visite os seguintes sites da MSDN: Para obter informações mais gerais sobre ADO.NET ou Visual Basic. NET, visite os seguintes grupos de notícias MSDN: Para obter mais informações, consulte o seguinte curso de treinamento e certificação da Microsoft: