VB .NET - Passando múltiplos registros para uma Stored Procedure no SQL Server

 Neste artigo eu vou explicar como podemos passar múltiplos registros (linhas) para uma Stored Procedure no SQL Server.

Podemos passar uma tabela contendo múltiplas linhas para uma stored procedure usando parâmetros Table Valued no SQL Server,

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:

  1. Inline Function - São usadas para parametrizar views;
  2. Table-valued Function - Usada para lógicas complexas; retorna uma tabela;
  3. Scalar-valued Function - Retorna apenas um parâmetro; são semelhantes as funções pré-existentes no SQL Server(Ex: getDate())

Recursos Usados :

Criando o banco de dados, a tabela e a stored procedure

Para o exemplo mostrado neste artigo eu estou usando o SQL Server 2012 Express Edition e o SQL Server Management Studio Express Edition.

Todas as instruções SQL serão executadas no SQL Server Management Studio. (SSMS)

Abaixo vemos a janela do SSMS, onde selecionamos o banco de dados Cadastro.mdf e visualizamos nos objetos que iremos usar para criar o Type e a Stored Procedure.

A tabela Alunos possui a seguinte estrutura:

Vamos criar uma User Defined Table Type no SQL Server clicando com o botão direito do mouse sobre o item User-Defined Table Types no item Types:

A seguir na janela de consultas vamos definir a instrução para criar o tipo AlunoType conforme mostrado abaixo:

Este tipo será usado para passar as linhas da tabela contendo os campos Id, Nome e Pais para a nossa stored procedure.

Agora vamos criar um novo procedimento armazenando clicando com o botão direito sobre Stored Procedure a seguir em New Store Procedure.

E a seguir vamos definir a stored procedure Inserir_Alunos conforme o código abaixo:

Esta stored procedure irá usar o nosso type tblAlunos, que é do tipo AlunoType, recebendo os campos Id, Nome e Pais e incluindo-os na tabela Alunos com a instrução INSERT INTO.

Para concluir vamos incluir no arquivo Web.config uma seção <connectionstrings> onde vamos definir o nome da conexão e a string de conexão com o banco de dados Cadastro.mdf:

....
<connectionStrings>

    <add name="ConexaoBD" connectionString="Data Source=(LocalDB)\v11.0;Initial Catalog=Cadastro;Integrated Security=True" providerName="System.Data.SqlClient"/>

</connectionStrings>
...

Criando o projeto no Visual Studio 2013 Express for Web

Eu vou usar uma aplicação ASP .NET Web Forms onde teremos um componente GridView exibindo informações a partir de um arquivo XML.

Abra o VS Express 2013 for Web e clique em New Web Site;

A seguir selecione a linguagem Visual C# e o template ASP .NET Empty Web Site;

Informe o nome Asp_GridXml e clique no botão OK;

No menu WEBSITE clique em Add New Item;

Selecione o template Web Form e informe o nome Default.aspx e clique no botão Add;

A seguir, a partir da ToolBox, inclua os seguinte controles:

Disponha os controles conforme o leiaute da figura abaixo:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <h2>Macoratti .net</h2>
        <hr />
    <div>
        <asp:GridView ID="gdvDados" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:CheckBox ID="Chkbox1" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
            <asp:BoundField DataField="Nome" HeaderText="Nome" ItemStyle-Width="150" />
            <asp:BoundField DataField="Pais" HeaderText="Pais" ItemStyle-Width="150" />
        </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="btnIncluir" Text="Incluir Dados" runat="server" />
    </div>
    </form>
</body>
</html>

Agora vamos criar o arquivo XML Alunos.xml que irá conter os dados que iremos exibir no GridView.

No menu WEBSITE clique em Add New Item;

Selecione o template XML File e informe o nome Alunos.xml e clique no botão Add;

Agora vamos definir o seguinte conteúdo neste arquivo XML:

Para exibir as informações deste arquivo XML no GridView vamos usar o evento Load da página Default.aspx.

Abra o arquivo code-behind Default.aspx.vb e inclua o código abaixo no evento Load :

Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            Dim ds As New DataSet()
            ds.ReadXml(Server.MapPath("~/Alunos.xml"))
            gdvDados.DataSource = ds.Tables(0)
            gdvDados.DataBind()
        End If
    End Sub
End Class

Executando o web site devemos visualizar a página Default.aspx exibindo as informações no GridView:

Agora estamos prontos para selecionar as linhas da tabela e passar para nossa stored procedure Inserir_Alunos. Para isso vamos incluir o código abaixo no evento btn_Incluir do controle Button:

        Imports System.Data
        Imports System.Data.SqlClient

       Protected Sub IncluirDados(sender As Object, e As EventArgs) Handles btnIncluir.Click
        Dim dt As New DataTable()
        Try
            dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Nome", GetType(String)), New DataColumn("Pais", GetType(String))})
            For Each row As GridViewRow In gdvDados.Rows
                If TryCast(row.FindControl("Chkbox1"), CheckBox).Checked Then
                    Dim id As Integer = Integer.Parse(row.Cells(1).Text)
                    Dim nome As String = row.Cells(2).Text
                    Dim pais As String = row.Cells(3).Text
                    dt.Rows.Add(id, nome, pais)
                End If
            Next
            If dt.Rows.Count > 0 Then
                Dim consString As String = ConfigurationManager.ConnectionStrings("ConexaoBD").ConnectionString
                Using con As New SqlConnection(consString)
                    Using cmd As New SqlCommand("Inserir_Alunos")
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.Connection = con
                        cmd.Parameters.AddWithValue("@tblAlunos", dt)
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
                    End Using
                End Using
            End If
            lblmsg.Text = "As linhas selecionadas foram incluídas na tabela Alunos"
        Catch ex As Exception
            lblmsg.Text = ex.Message
        End Try
    End Sub

No código acima criamos um DataTable com o esquema igual ao definido no User Defined Table Type que criamos anteriormente,  e, em seguida, executamos um laço com as linhas do GridView.

Dentro do loop, os valores das células das linhas selecionadas são inseridos no DataTable.

Finalmente, após verificar se alguma linha foi selecionada e conseqüentemente se existem linhas no DataTable criado,  nosso procedimento armazenado Inserir_Alunos é executado com o DataTable passado como parâmetro para ele.

Agora podemos executar o web site e selecionar as linhas do GridView que desejamos passar como parâmetro para nossa stored procedure - Inserir_Alunos -  para incluir na tabela Alunos.

Antes vamos mostrar que nossa tabela Alunos esta vazia:

Selecionando as linhas e clicando no botão Incluir Dados teremos:

Agora vemos a tabela Alunos com os dados incluídos:

E assim confirmamos o funcionamento do nosso projeto, da nossa stored procedure e da passagem dos parâmetros.

Pegue o projeto completo aqui:  Asp_GridXml.zip

João 5:24 Na verdade, na verdade vos digo que quem ouve a minha palavra, e crê naquele que me enviou, tem a vida eterna, e não entrará em condenação, mas passou da morte para a vida.

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

Quer migrar para o VB .NET ?

Quer aprender C# ??

Quer aprender os conceitos da Programação Orientada a objetos ?

Quer aprender o gerar relatórios com o ReportViewer no VS 2013 ?

  Gostou ?   Compartilhe no Facebook   Compartilhe no Twitter

Referências:

José Carlos Macoratti