nullable.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient

Public Class Nullable
    Private clsNormal As New Orders
    Private clsPublic As New OrdersPublic
    Private clsPrivate As New OrdersPrivate
    Private cnnNwind As SqlConnection
    Private intValues As Integer
    Private intNulls As Integer

    Private Sub TestPublicClass()
        txtValues.Text = ""
        txtNulls.Text = ""
        Application.DoEvents()
        Dim strConn As String = "Server=localhost;Integrated Security=True;Database=Northwind"
        cnnNwind = New SqlConnection(strConn)
        Try
            Dim cmdOrders As SqlCommand = cnnNwind.CreateCommand
            With cmdOrders
                .CommandType = CommandType.Text
                .CommandText = "SELECT * FROM Orders"
            End With
            cnnNwind.Open()
            Dim drOrders As SqlDataReader = cmdOrders.ExecuteReader()

            Dim intCtr As Integer = 0
            intValues = 0
            intNulls = 0
            With drOrders
                While .Read
                    For intCtr = 0 To .FieldCount - 1
                        Select Case intCtr
                            Case 0
                                clsPublic.OrderID = .GetInt32(intCtr)
                            Case 1
                                clsPublic.CustomerID = .GetString(intCtr)
                            Case 2
                                clsPublic.EmployeeID = .GetInt32(intCtr)
                            Case 3
                                clsPublic.OrderDate = .GetDateTime(intCtr)
                            Case 4
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPublic.RequiredDate = Nothing
                                Else
                                    clsPublic.RequiredDate = .GetDateTime(intCtr)
                                End If
                            Case 5
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPublic.ShippedDate = Nothing
                                Else
                                    clsPublic.ShippedDate = .GetDateTime(intCtr)
                                End If
                            Case 6
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPublic.ShipVia = Nothing
                                Else
                                    clsPublic.ShipVia = .GetInt32(intCtr)
                                End If
                            Case 7
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPublic.Freight = Nothing
                                Else
                                    clsPublic.Freight = .GetDecimal(intCtr)
                                End If
                            Case 8
                                clsPublic.ShipName = .GetString(intCtr)
                            Case 9
                                clsPublic.ShipAddress = .GetString(intCtr)
                            Case 10
                                clsPublic.ShipCity = .GetString(intCtr)
                            Case 11
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPublic.ShipRegion = Nothing
                                Else
                                    clsPublic.ShipRegion = .GetString(intCtr)
                                End If
                            Case 12
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPublic.ShipPostalCode = Nothing
                                Else
                                    clsPublic.ShipPostalCode = .GetString(intCtr)
                                End If
                            Case 13
                                clsPublic.ShipCountry = .GetString(intCtr)
                        End Select
                    Next intCtr

                    'Test for nullable values
                    If clsPublic.ShippedDate.HasValue Then
                        intValues += 1
                    Else
                        intNulls += 1
                    End If
                    If clsPublic.ShipRegion IsNot Nothing Then
                        intValues += 1
                    Else
                        intNulls += 1
                    End If
                    If clsPublic.ShipPostalCode IsNot Nothing Then
                        intValues += 1
                    Else
                        intNulls += 1
                    End If
                    'Add 10 values always present
                    intValues += 10
                End While
                .Close()
            End With
            cnnNwind.Close()
            txtValues.Text = intValues.ToString
            txtNulls.Text = intNulls.ToString
        Catch excRead As Exception
            MsgBox(excRead.Message + excRead.StackTrace)
        End Try
    End Sub

    Private Sub TestPrivateClass()
        txtValues.Text = ""
        txtNulls.Text = ""
        Application.DoEvents()
        Dim strConn As String = "Server=localhost;Integrated Security=True;Database=Northwind"
        cnnNwind = New SqlConnection(strConn)
        Try
            Dim cmdOrders As SqlCommand = cnnNwind.CreateCommand
            With cmdOrders
                .CommandType = CommandType.Text
                .CommandText = "SELECT * FROM Orders"
            End With
            cnnNwind.Open()
            Dim drOrders As SqlDataReader = cmdOrders.ExecuteReader()
            Dim intCtr As Integer = 0
            intValues = 0
            intNulls = 0
            With drOrders
                While .Read
                    For intCtr = 0 To .FieldCount - 1
                        Select Case intCtr
                            Case 0
                                clsPrivate.OrderID = .GetInt32(intCtr)
                            Case 1
                                clsPrivate.CustomerID = .GetString(intCtr)
                            Case 2
                                clsPrivate.EmployeeID = .GetInt32(intCtr)
                            Case 3
                                clsPrivate.OrderDate = .GetDateTime(intCtr)
                            Case 4
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPrivate.RequiredDate = Nothing
                                Else
                                    clsPrivate.RequiredDate = .GetDateTime(intCtr)
                                End If
                            Case 5
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPrivate.ShippedDate = Nothing
                                Else
                                    clsPrivate.ShippedDate = .GetDateTime(intCtr)
                                End If
                            Case 6
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPrivate.ShipVia = Nothing
                                Else
                                    clsPrivate.ShipVia = .GetInt32(intCtr)
                                End If
                            Case 7
                                If .GetValue(intCtr) Is Convert.DBNull Then
                                    clsPrivate.Freight = Nothing
                                Else
                                    clsPrivate.Freight = .GetDecimal(intCtr)