sábado, 8 de noviembre de 2014

Get SQL data using LinQ Connection

Imports System.Data.SqlClient

Public Class InvoiceDB

    Public Shared Function GetInvoices() _
            As List(Of Invoice)
        Dim invoiceList As New List(Of Invoice)
        Dim connection As SqlConnection = LinqDB.GetConnection
        Dim selectstatement As String =
            "SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, " &
            "PaymentTotal, CreditTotal, TermsID, DueDate, PaymentDate " &
            "FROM Invoices " &
            "ORDER BY InvoiceDate"
        Dim selectCommand As New SqlCommand(selectstatement, connection)
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader()
            Dim invoice As Invoice
            Do While reader.Read
                invoice = New Invoice
                invoice.InvoiceID = CInt(reader("InvoiceID"))
                invoice.VendorID = CInt(reader("VendorID"))
                invoice.InvoiceNumber = reader("InvoiceNumber").ToString
                invoice.InvoiceDate = CDate(reader("InvoiceDate"))
                invoice.InvoiceTotal = CDec(reader("InvoiceTotal"))
                invoice.PaymentTotal = CDec(reader("PaymentTotal"))
                invoice.CreditTotal = CDec(reader("CreditTotal"))
                invoice.TermsID = CDec(reader("TermsID"))
                invoice.DueDate = CDate(reader("DueDate"))
                If IsDBNull(reader("PaymentDate")) Then
                    invoice.PaymentDate = Nothing
                Else
                    invoice.PaymentDate = CDate(reader("PaymentDate"))
                End If
                invoiceList.Add(invoice)
            Loop
            reader.Close()
        Catch ex As SqlException
            Throw ex
        Finally
            connection.Close()
        End Try
        Return invoiceList
    End Function

End Class

No hay comentarios:

Publicar un comentario