Re: Better way to get all columns in the DataReader

[Barry Zubel]

I ended up doing it very like you, Guy.

I did, however, use DataAdapter classes as the speed slowdown was 
negligible, and the ability to reference row headers directly just made 
for neater code.

Something along the lines of the following:


     Public Function Retrieve(ByVal JobNo As String) As Order
         If JobNo = "" Then
             Return Nothing
         End If

         Dim ord As New Order

         Dim sSql As String
         sSql = "SELECT * FROM S_MASTER WHERE JOB_NO=" & JobNo
         Dim dt As DataTable = DBControl.RetrieveTable(sSql)

         If dt.Rows.Count = 0 Then
             SetStatus()
             Return Nothing
         End If

         Dim row As DataRow = dt.Rows(0)

         ord.JobNo = row("JOB_NO")
         ord.CNO = row("C_NO")
         ord.EnNo = row("EN_NO")
         ord.SQty = row("SQTY")
         If Not IsDBNull(row("CUSTONO")) Then
             ord.CustONo = row("CUSTONO")
         End If
         If Not IsDBNull(row("ISSUED")) Then
             ord.Issued = row("ISSUED")
         Else
             ord.Issued = Nothing
         End If

         If row.IsNull("REQD") Then
             ord.Reqd = Nothing
         Else
             ord.Reqd = row("REQD")
         End If

         If row.IsNull("INVOICED") Then
             ord.Invoiced = Nothing
         Else
             ord.Invoiced = row("INVOICED")
         End If

         ord.UnitPrice = row("UNITPRICE")
         ord.OrdVal = row("ORDVAL")
         ord.InvVal = row("INVVAL")
         ord.InvQty = row("INVQTY")
         ord.VAT = row("VAT")
         ord.VatRate = row("VATRATE")
         ord.ToolVal = row("TOOLVAL")
         ord.OtherVal = row("OTHERVAL")
         ord.ExVat = row("EXVAT")
         ord.IncVat = row("INCVAT")
         ord.Tonnes = row("TONNES")
         ord.DelQty = row("DELQTY")
         If Not IsDBNull(row("DELREASON")) Then
             ord.DelReason = row("DELREASON")
         End If

        If row.IsNull("DELIVERD") Then
             ord.Delivered = Nothing
         Else
             ord.Delivered = row("DELIVERD")
         End If

         ord.pallets = row("PALLETS")
         If Not IsDBNull(row("DPRIORITY")) Then
             ord.DelPriority = row("DPRIORITY")
         End If
         ord.Rep = row("REP")
         ord.EstVal = row("ESTVAL")
         ord.MatVal = row("MATVAL")
         If Not IsDBNull(row("DQ")) Then
             ord.DQ = row("DQ")
         End If
         If Not IsDBNull(row("OWNER")) Then
             ord.Owner = row("OWNER")
         End If
         If Val(row("EXPORTED")) = 1 Then
             ord.Exported = True
         Else
             ord.Exported = False
         End If
         If Not IsDBNull(row("DNOTES")) Then
             ord.DNotes = row("DNOTES")
         Else
             ord.DNotes = ""
         End If
         ord.ProForma = row("PROFORMA")
         ord.IsChanged = False

         dt.Dispose()

         SetStatus()

         Return ord
     End Function


The RetrieveTable function of DBControl takes care of creating and 
disposing all the various connection/command objects, and simply returns 
a datatable.  For a readonly table, this seems to be the neatest 
solution that I've found.

My class consists of hidden member variables accessed through 
properties. Upon setting any property, I set the internal 'mIsChanged' 
variable to true, so when I come to attempt to save the item, I can 
immediately skip any database round-trips if 'mIsChanged' is false (i.e. 
the object hasn't changed)

B.