Energize Your Data Retrieval Code with ADO.NET Objects—Sample 1
Article
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