Export (0) Print
Expand All

Energize Your Data Retrieval Code with ADO.NET Objects—Sample 1

 
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text


Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    Friend WithEvents StatusBar1 As System.Windows.Forms.StatusBar
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid()
        Me.Button1 = New System.Windows.Forms.Button()
        Me.Button2 = New System.Windows.Forms.Button()
        Me.StatusBar1 = New System.Windows.Forms.StatusBar()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.AllowNavigation = False
        Me.DataGrid1.AlternatingBackColor = System.Drawing.Color.LightGray
        Me.DataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top _
          Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right)
        Me.DataGrid1.BackColor = System.Drawing.Color.Gainsboro
        Me.DataGrid1.BackgroundColor = System.Drawing.Color.Silver
        Me.DataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.None
        Me.DataGrid1.CaptionBackColor = System.Drawing.Color.LightSteelBlue
        Me.DataGrid1.CaptionForeColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.FlatMode = True
        Me.DataGrid1.Font = New System.Drawing.Font("Tahoma", 8.0!)
        Me.DataGrid1.ForeColor = System.Drawing.Color.Black
        Me.DataGrid1.GridLineColor = System.Drawing.Color.DimGray
        Me.DataGrid1.GridLineStyle = System.Windows.Forms.DataGridLineStyle.None
        Me.DataGrid1.HeaderBackColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.HeaderFont = New System.Drawing.Font("Tahoma", _
          8.0!, System.Drawing.FontStyle.Bold)
        Me.DataGrid1.HeaderForeColor = System.Drawing.Color.White
        Me.DataGrid1.LinkColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.Location = New System.Drawing.Point(8, 32)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.ParentRowsBackColor = System.Drawing.Color.DarkGray
        Me.DataGrid1.ParentRowsForeColor = System.Drawing.Color.Black
        Me.DataGrid1.ReadOnly = True
        Me.DataGrid1.SelectionBackColor = System.Drawing.Color.CadetBlue
        Me.DataGrid1.SelectionForeColor = System.Drawing.Color.White
        Me.DataGrid1.Size = New System.Drawing.Size(728, 288)
        Me.DataGrid1.TabIndex = 0
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(360, 8)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(184, 24)
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Run distinct queries..."
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(552, 8)
        Me.Button2.Name = "Button2"
        Me.Button2.Size = New System.Drawing.Size(184, 24)
        Me.Button2.TabIndex = 2
        Me.Button2.Text = "Run single query and splits..."
        '
        'StatusBar1
        '
        Me.StatusBar1.Location = New System.Drawing.Point(0, 303)
        Me.StatusBar1.Name = "StatusBar1"
        Me.StatusBar1.Size = New System.Drawing.Size(744, 22)
        Me.StatusBar1.TabIndex = 3
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(744, 325)
        Me.Controls.AddRange(New System.Windows.Forms.Control() _
          {Me.StatusBar1, Me.Button2, Me.Button1, Me.DataGrid1})
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog
        Me.MaximizeBox = False
        Me.Name = "Form1"
        Me.Text = "Query"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As _
      System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet()

        ' Connection string
        Dim connString As String = "DATABASE=northwind;SERVER=localhost;Integrated Security=SSPI;"

        ' Commands
        Dim cmdEmpl As String = "SELECT employeeid, lastname FROM Employees"
        Dim cmdCust As String = "SELECT customerid, companyname FROM Customers"

        ' Core command
        ' SELECT o.customerid, o.orderid, o.orderdate, 
        '       o.shippeddate, SUM(od.quantity*od.unitprice) AS price, o.employeeid
        ' FROM orders AS o
        ' INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
        '    WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid 
        '    GROUP BY o.customerid, o.orderid, o.orderdate, o.shippeddate, 
        '             o.employeeid
        '    HAVING SUM(od.quantity) >30
        ' ORDER BY o.customerid
        Dim cmdCore As String = ""
        Dim sb As StringBuilder = New StringBuilder("")
        sb.Append("SELECT o.employeeid, o.customerid, o.orderid, o.orderdate,")
        sb.Append("o.shippeddate, SUM(od.quantity*od.unitprice) AS price ")
        sb.Append("FROM orders AS o ")
        sb.Append("INNER JOIN [Order Details] AS od ON _
          o.orderid=od.orderid ")
        sb.Append("WHERE Year(o.orderdate)=@TheYear AND _
          od.orderid=o.orderid ")
        sb.Append("GROUP BY o.customerid, o.orderid, o.orderdate, _
          o.shippeddate, o.employeeid ")
        sb.Append("HAVING SUM(od.quantity) >@TheQuantity ")
        sb.Append("ORDER BY o.customerid")
        cmdCore = sb.ToString()

        ' Execute commands
        Dim conn As SqlConnection = New SqlConnection(connString)
        Dim adapter As SqlDataAdapter = New SqlDataAdapter()

        conn.Open()
        adapter.SelectCommand = New SqlCommand(cmdCore, conn)
        adapter.SelectCommand.Parameters.Add("@TheYear", 1997)
        adapter.SelectCommand.Parameters.Add("@TheQuantity", 30)
        adapter.Fill(ds, "Orders")
        adapter.SelectCommand = New SqlCommand(cmdCust, conn)
        adapter.Fill(ds, "Customers")
        adapter.SelectCommand = New SqlCommand(cmdEmpl, conn)
        adapter.Fill(ds, "Employees")
        conn.Close()

        ' Create Orders2Employees relation (column #0 on Orders)
        Dim relOrder2Employees As DataRelation
        relOrder2Employees = New DataRelation("Orders2Employees", _
            ds.Tables("Employees").Columns("employeeid"), _
            ds.Tables("Orders").Columns("employeeid"))
        ds.Relations.Add(relOrder2Employees)

        ' Create Orders2Customers relation (column #1 on Orders)
        Dim relOrders2Customers As DataRelation
        relOrders2Customers = New DataRelation("Orders2Customers", _
            ds.Tables("Customers").Columns("customerid"), _
            ds.Tables("Orders").Columns("customerid"))
        ds.Relations.Add(relOrders2Customers)

        ' Add related columns for display
        ds.Tables("Orders").Columns.Add("Employee", GetType(String), _
          "Parent(Orders2Employees).lastname")
        ds.Tables("Orders").Columns.Add("Customer", GetType(String), _
         "Parent(Orders2Customers).companyname")

        ' Show data
        DataGrid1.AllowNavigation = False
        DataGrid1.DataSource = ds
        DataGrid1.DataMember = "Orders"
    End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As _
      System.EventArgs) Handles Button2.Click
        Dim ds As New DataSet()

        ' Connection string
        Dim connString As String = "DATABASE=northwind;SERVER=localhost;Integrated Security=SSPI;"

        ' Core command
        'SELECT o.customerid, od.orderid, o.orderdate, o.shippeddate, 
        ' SUM(od.quantity*od.unitprice) AS price,
        ' c.companyname, e.lastname FROM Orders AS o 
        'INNER JOIN Customers AS c ON c.customerid=o.customerid
        'INNER JOIN Employees AS e ON e.employeeid=o.employeeid
        'INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
        '    WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid 
        '    GROUP BY o.customerid, c.companyname, od.orderid, 
        '       o.orderdate, o.shippeddate, e.lastname
        '    HAVING SUM(od.quantity) >@TheAmount
        'ORDER BY o.customerid

        Dim cmdCore As String = ""
        Dim sb As StringBuilder = New StringBuilder("")
        sb.Append("SELECT o.customerid, c.companyname, e.employeeid, e.lastname, ")
        sb.Append("od.orderid, o.orderdate, o.shippeddate, ")
        sb.Append(" SUM(od.quantity*od.unitprice) AS price ")
        sb.Append(" FROM Orders AS o ")
        sb.Append("INNER JOIN Customers AS c ON c.customerid=o.customerid ")
        sb.Append("INNER JOIN Employees AS e ON e.employeeid=o.employeeid ")
        sb.Append("INNER JOIN [Order Details] AS od ON o.orderid=od.orderid  ")
        sb.Append("    WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid  ")
        sb.Append("    GROUP BY o.customerid, c.companyname, od.orderid,  ")
        sb.Append("       o.orderdate, o.shippeddate, e.employeeid, e.lastname ")
        sb.Append("    HAVING SUM(od.quantity) >@TheAmount ")
        sb.Append("ORDER BY o.customerid ")
        cmdCore = sb.ToString()

        ' Execute commands
        Dim conn As SqlConnection = New SqlConnection(connString)
        Dim adapter As SqlDataAdapter = New SqlDataAdapter()

        conn.Open()
        adapter.SelectCommand = New SqlCommand(cmdCore, conn)
        adapter.SelectCommand.Parameters.Add("@TheYear", 1997)
        adapter.SelectCommand.Parameters.Add("@TheAmount", 30)
        adapter.Fill(ds, "Orders")
        conn.Close()

        ' Split data
        Dim table As DataSet = SplitData(ds)

        ' Show data
        DataGrid1.AllowNavigation = True
        DataGrid1.DataSource = table

        ' Status bar
        Dim t As String = String.Format("{0} orders. {1} customers. {2} employees.", _
            table.Tables("Orders").Rows.Count, table.Tables("Customers").Rows.Count, _
              table.Tables("Employees").Rows.Count)
        StatusBar1.Text = t
    End Sub


    Function SplitData(ByVal ds As DataSet) As DataSet
        Dim _dataset As New DataSet()

        ' Make a full worker copy of the DataSet
        _dataset = ds.Copy()

        CreateCustomers(_dataset, ds)
        CreateEmployees(_dataset, ds)

        ' Remove columns from Orders(companyname [2] and lastname [4])
        ' ***************************************************
        _dataset.Tables("Orders").Columns.RemoveAt(1)
        _dataset.Tables("Orders").Columns.RemoveAt(3)
        ' ***************************************************

        Return _dataset
    End Function

    ' Create Customers
    Sub CreateCustomers(ByVal _dataset As DataSet, ByVal orig As DataSet)
        Dim _customers As DataTable = orig.Tables("Orders").Clone()
        _customers.TableName = "Customers"

        ' Remove unneeded columns (no data involved): keep 1-2
        Dim i As Integer
        For i = 2 To _customers.Columns.Count - 1
            _customers.Columns.RemoveAt(2)
        Next

        ' Fill with data
        Dim row As DataRow
        Dim customerKey As String = ""
        For Each row In _dataset.Tables("Orders").Rows
            ' Already sorted by CustomerID
            If customerKey <> row("customerid") Then
                ' select distinct
                _customers.ImportRow(row)
                customerKey = row("customerid")
            End If
        Next

        ' Add to the DataSet
        _dataset.Tables.Add(_customers)
    End Sub


    ' Create Employees
    Sub CreateEmployees(ByVal _dataset As DataSet, ByVal orig As DataSet)
        Dim _employees As DataTable = orig.Tables("Orders").Clone()
        _employees.TableName = "Employees"

        ' Remove unneeded columns (no data involved): keep 3-4
        _employees.Columns.RemoveAt(0)
        _employees.Columns.RemoveAt(0)
        Dim i As Integer
        For i = 2 To _employees.Columns.Count - 1
            _employees.Columns.RemoveAt(2)
        Next

        ' Fill with data (must sort by employeeid first)
        Dim employeeKey As Integer = 0
        Dim view As DataView = New DataView(_dataset.Tables("Orders"))
        view.Sort = "employeeid"

        Dim rowView As DataRowView
        For Each rowView In view

            If employeeKey <> Convert.ToInt32(rowView("employeeid")) Then
                ' select distinct
                _employees.ImportRow(rowView.Row)
                employeeKey = Convert.ToInt32(rowView("employeeid"))
            End If
        Next

        ' Add to the DataSet
        _dataset.Tables.Add(_employees)
    End Sub

End Class
Show:
© 2014 Microsoft