Como chamar um procedimento armazenado com parâmetros utilizando o ADO.NET e Visual Basic .NET

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

• Utilize um objecto de DataSet para recolher as linhas devolvidas e para trabalhar com estas linhas em conjunto com os valores de retorno e os parâmetros devolução.
• Utilize um objecto DataReader para recolher as linhas devolvidas, para percorrer estas linhas e para reunir valores de retorno e devolver parâmetros.
• Utilize o método ExecuteScalar para devolver o valor da primeira coluna da primeira linha com os valores de retorno e os parâmetros devolver os resultados. Isto é útil com funções de agregação.
• Utilize o método ExecuteNonQuery para devolver apenas os parâmetros de retorno 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 . 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. Quando utiliza o objecto OleDbCommand , terá de adicionar os parâmetros na ordem correcta e não é possível utilizar os parâmetros por nome.

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. Crie o seguinte procedimento armazenado no servidor 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. Utilize a instrução de importação no sistema e os espaços de nomes System.data para que não é necessário que qualificar declarações os espaços de nomes mais tarde no código. Tem de utilizar a instrução de importação anteriores para quaisquer outras declarações. Certifique-se copiar apenas o código para o fornecedor que tenha escolhido. SQL Client

   Imports System.Data.SqlClient					

   Fornecedor OLE DB data

   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 cadeia de ligação para o objeto Connection apontar para o servidor que esteja a executar o SQL Server.
5. Execute o código. Repare que DataReader obtém os registos e, em seguida, devolve os valores de parâmetro. Pode utilizar o método de leitura do objecto DataReader para percorrer os registos devolvidos.
   
   A saída de janela 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. Criar o procedimento armazenado seguinte no servidor que esteja a executar 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. Utilize a instrução de importação no sistema e os espaços de nomes System.data para que não é necessário que qualificar declarações os espaços de nomes mais tarde no código. Tem de utilizar a instrução de importação anteriores para quaisquer outras declarações. Certifique-se que copie apenas o código para o fornecedor que tenha escolhido. SQL Client

   Imports System.Data.SqlClient					

   Fornecedor OLE DB data

   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))
   					

   Fornecedor OLE DB data

   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 cadeia de ligação para o objeto Connection apontar para o servidor que esteja a executar o SQL Server.
5. Execute o código. Repare que o método ExecuteScalar do objeto Command devolve os parâmetros. ExecuteScalar também devolve o valor da coluna 1, linha 1 do conjunto de linhas devolvida. Assim, o valor de intCount é o resultado da função contagem do procedimento armazenado.

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. Criar o procedimento armazenado seguinte no servidor que esteja a executar 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. Utilize a instrução de importação no sistema e os espaços de nomes System.data para que não é necessário que qualificar declarações os espaços de nomes mais tarde no código. Tem de utilizar a instrução de importação anteriores para quaisquer outras declarações. Certifique-se que copie apenas o código para o fornecedor que tenha escolhido. SQL Client

   Imports System.Data.SqlClient					

   Fornecedor OLE DB data

   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)
   					

   Fornecedor OLE DB data

   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 cadeia de ligação para o objeto Connection apontar para o servidor que esteja a executar o SQL Server.
5. Execute o código. A janela de saída apresenta o número de linhas afectadas ( intRowAffect ) e o valor do parâmetro devolvido.

Para obter informações adicionais, visite os seguintes Web sites da MSDN: Para mais informações gerais sobre o ADO.NET ou Visual Basic. NET, visite os seguintes newsgroups da MSDN: Para mais informações, consulte o seguinte curso de formação & certificação da Microsoft: