VB
.NET - Executando uma função que retorna um valor escalar no
SQL Server
VB
.NET - Executando uma função que retorna um valor escalar no
SQL Server
Hoje vou mostrar como recuperar um valor de uma função definida pelo usuário no SQL Server que retorna um valor escalar usando ADO .NET.
Para fazer isso vamos criar a Function (Função) no SQL Server e tratá-la como se fosse um procedimento armazenado.
A partir do SQL Server 2000 temos o recurso chamado Functions que permite criar funções para auxiliar na consulta e obtenção de informação.
Uma função definida pelo usuário é uma rotina Transact-SQL ou CLR (Common Language Runtime) que aceita parâmetros, executa uma ação, como um cálculo complexo, e retorna o resultado dessa ação como um valor. O valor de retorno pode ser um valor escalar (único) ou uma tabela.
A criação de uma user function é similar à criação de uma stored procedure ou uma view.
Atualmente temos a possibilidade de criar funções dos seguintes tipos:
Vamos então criar uma Scalar-valued Function e mostrar como podemos tratar o seu retorno usando ADO .NET.
Os recursos necessários usados neste artigo foram:
Abra o Visual Basic 2010 Express Edition e crie um novo projeto (File-> New Project) do tipo Windows Forms Application com o nome SQLServer_FuncaoEscalar:
 |
Agora vamos abrir a janela DataBase Explorer e exibir os objetos do banco de dados Northwind.mdf;
Clique com o botão direito sobre o item Functions e selecione Add New -> Scalar-valued Function;
 |
Será aberto uma janela com uma estrutura pronto para criarmos a nossa função:
 |
Defina a função com o nome ValorEstoque conforme mostra a figura a seguir:
 |
Esta função calcula o valor do estoque para um produto multiplicando o seu preço unitário pelo sua quantidade em estoque.
Agora vamos usar a instrução com um DataAdapter para preencher um DataTable com os cinco primeiros registros da tabela Products do banco de dados Northwind.mdf e obter o valor do estoque para cada registro usando a função criada com o valor escalar.
Na janela Solution Explorer selecione o formulário form1 e a partir da ToolBox vamos incluir os seguintes controles no formulário:
Conforme o leiaute abaixo:
 |
No evento Click do botão - Executar - vamos incluir o código abaixo:
Private Sub btnExecutar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecutar.Click
Try
Dim sqlConnectString As String = "Data Source=.\SQLEXPRESS;Integrated security=SSPI;Initial Catalog=Northwind;"
'Dim sql1 As String = "SELECT TOP 5 *, " + "dbo.ValorProdutoEstoque(UnitPrice, UnitsinStock) as " + "ValorEstoque FROM Products"
Dim sql2 As String = "SELECT ProductID, ProductName, ValorEstoque=dbo.ValorEstoque(UnitPrice, UnitsInStock) FROM Products"
Dim da As New SqlDataAdapter(sql2, sqlConnectString)
Dim dt As New DataTable()
da.Fill(dt)
gdvDados.DataSource = dt
formatagrid()
Catch ex As Exception
MessageBox.Show("Erro : " + ex.Message)
End Try
End Sub
|
Estamos chamando a função na instrução SQL :
Dim sql2 As String = "SELECT ProductID, ProductName, ValorEstoque=dbo.ValorEstoque(UnitPrice, UnitsInStock) FROM Products"
Note que temos que informar o ower da function ao chamá-la, por isso usamos dbo.ValorEstoque(UnitPrice, UnitsInStock);
Se você chamar ValorEstoque(UnitPrice, UnitsInStock) não vai funcionar.
A rotina formatagrid() tem o seguinte código, e , é usada para formatar o controle datagridview:
Private Sub formatagrid()
With gdvDados
'.AutoGenerateColumns = True
.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders
.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
'altera a cor das linhas alternadas no grid
.RowsDefaultCellStyle.BackColor = Color.White
.AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine
'altera o nome das colunas
.Columns(0).HeaderText = "Código"
.Columns(1).HeaderText = "Produto"
.Columns(2).HeaderText = "Valor do Estoque"
.Columns(0).Width = 50
.Columns(1).Width = 300
.Columns(2).Width = 200
'esconde a coluna
.Columns("ProductID").Visible = False
'formata as colunas valor, vencimento e pagamento
.Columns(2).DefaultCellStyle.Format = "c"
'seleciona a linha inteira
.SelectionMode = DataGridViewSelectionMode.FullRowSelect
'não permite seleção de multiplas linhas
.MultiSelect = False
' exibe nulos formatados
.DefaultCellStyle.NullValue = " - "
'permite que o texto maior que célula não seja truncado
.DefaultCellStyle.WrapMode = DataGridViewTriState.True
'define o alinhamamento
.Columns("ValorEstoque").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
End With
End Sub
|
Executando o projeto iremos obter:
 |
Percebeu como é fácil criar e utilizar uma função definida pelo usuário (FUNCTION) no SQL Server ?
Você pode então criar Functions para obter informações e realizar operações.
Mas qual a vantagem das Functions sobre os Procedimentos armazenados ?
Uma das vantagens de funções definidas pelo usuário sobre os procedimentos armazenados, é o fato de que uma UDF pode ser usado em instruções Select, Where ou em uma declaração Case.
Elas também podem ser usadas para criar associações (Joins). Além disso, funções definidas pelo usuário são mais simples para invocar do que procedimentos armazenados de dentro de outra instrução SQL.
Pegue o projeto completo
aqui: 
SQLServer_FuncaoEscalar.zip
Veja os Destaques e novidades do SUPER CD VB 2012 (sempre atualizado) : clique e confira ! Quer migrar para o VB .NET ? Veja mais sistemas completos para a plataforma .NET no Super CD .NET e no Super DVD .NET , confira... Quer aprender C# ?? Chegou o
Super DVD C# 2012
com exclusivo material de suporte e vídeo aulas
com curso básico sobre C# |
1Pe 2:11
Amados, exorto-vos, como a peregrinos e forasteiros, que vos abstenhais das concupiscências da carne, as quais combatem contra a alma;Referências: