Re: .NET Data Provider

What are the advantages of using the IBM .NET provider over OLE or ODBC?

There isn't a huge amount of difference (if any) in the quantity of code you
have to write but the IBM .NET provider should offer improved performance
and IMHO it is easier to install and use. I did some testing ages ago but it
wasn't sufficient to indicate huge performance increases for either of the
options.

Personally I don't use data adapters and datasets, just mostly Object
Datasources and datatables (you should only really use a dataset when
working with multiple datatables and data relations). I like to return my
own datatables (with friendly field descriptions as opposed to AS/400 field
names that get used by default).

My approach is slightly different (not perfect just different, some code
included below). I haven't come across the problem with Byte arrays
(probably because I haven't used them yet). Other than some minor padding
frustrations I haven't found any problems with the .NET Provider yet (after
several years of quite heavy use).

Regards

Maurice O'Prey
XMLi5 Ltd.

''' <summary>
''' 1b/. GetAllJobTypesDbiDB2(language, searchString) As DataTable
''' </summary>
Private Shared Function GetAllJobTypesDbiDB2(ByVal language As String, ByVal
searchString As String) As DataTable

' Create connection
Dim con As iDB2Connection = New
iDB2Connection(AppSettings.iDB2ConnectionString)

' Build SQL
Dim sb As New StringBuilder
With sb
.Append(" select JT1JOB, JT1ACT, JT1DSCD ")
.Append(" from WPJTP100 ")
.Append(" join WPJTP100D on JT1JOB = JT1JOBD ")
.Append(" where JT1LIDD=? ")
' add search filter if specified
If searchString <> String.Empty Then
.Append(" and UPPER(JT1DSCD) like ?")
End If
.Append(" order by JT1DSCD ")
End With

' Create command
Dim cmd As iDB2Command = New iDB2Command(sb.ToString(), con)

' Add parameters
cmd.Parameters.Add(New iDB2Parameter("@JT1LIDD",
iDB2DbType.iDB2VarChar, 3))
cmd.Parameters("@JT1LIDD").Value = language

If searchString <> String.Empty Then
cmd.Parameters.Add(New iDB2Parameter("@searchString",
iDB2DbType.iDB2VarChar, 40))
cmd.Parameters("@searchString").Value =
searchString.ToUpper.Trim & "%"
End If

Dim dt As DataTable
Dim dr As DataRow

'create a DataTable
dt = CreateJobTypeTable()

' Execute Reader
Using con
con.Open()
Dim reader As iDB2DataReader = cmd.ExecuteReader()
While reader.Read()

dr = dt.NewRow()
dr("JobTypeCode") = reader("JT1JOB").ToString()
dr("JobTypeActive") = reader("JT1ACT").ToString()
dr("JobTypeDescription") = reader("JT1DSCD").ToString()

dt.Rows.Add(dr)

End While
reader.Close()
End Using

Return dt

End Function

''' <summary>
''' 0/. CreateJobTypeTable() As DataTable
''' </summary>
Private Shared Function CreateJobTypeTable() As DataTable
'create a DataTable and add columns
Dim dt As DataTable = New DataTable("JobTypes")
dt.Columns.Add(New DataColumn("JobTypeCode", GetType(String)))
dt.Columns.Add(New DataColumn("JobTypeActive", GetType(String)))
dt.Columns.Add(New DataColumn("JobTypeDescription",
GetType(String)))
Return dt
End Function



<snip>Original message snipped for brevity</snip>