'****************************************************************************
' (c) Copyright IBM Corp. 2007 All rights reserved.
'
' The following sample of source code ("Sample") is owned by International
' Business Machines Corporation or one of its subsidiaries ("IBM") and is
' copyrighted and licensed, not sold. You may use, copy, modify, and
' distribute the Sample in any form without payment to IBM, for the purpose of
' assisting you in the development of your applications.
'
' The Sample code is provided to you on an "AS IS" basis, without warranty of
' any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
' IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
' not allow for the exclusion or limitation of implied warranties, so the above
' limitations or exclusions may not apply to you. IBM shall not be liable for
' any damages you suffer as a result of using, copying, modifying or
' distributing the Sample, even if IBM has been advised of the possibility of
' such damages.
'****************************************************************************
'
' SOURCE FILE NAME: DbDatAdp.vb
'
' SAMPLE: How to use a DB2DataAdapter with the DB2 .NET Data Provider
'
' SQL Statements USED:
' CREATE TABLE
' DROP TABLE
' INSERT
' SELECT
'
' DB2 .NET Data Provider Classes USED:
' DB2Connection
' DB2Command
'
'
'****************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the DbDatAdp.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp DbDatAdp
'
' or compile DbDatAdp.vb with the makefile by entering the following at
' the command prompt:
'
' nmake DbDatAdp
'
' 2. Run the DbDatAdp program by entering the program name at the command
' prompt:
'
' DbDatAdp
'
'****************************************************************************
'
' For more information on the sample programs, see the README file.
'
' For information on developing applications, see the Application
' Development Guide.
'
' For information on using SQL statements, see the SQL Reference.
'
' For the latest information on programming, compiling, and running DB2
' applications, visit the DB2 Information Center at
' http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
'
'****************************************************************************
Imports System
Imports System.Data
Imports IBM.Data.DB2
Imports Microsoft.VisualBasic
Public Class DataAdapt
Public Shared Sub Main(args() As String)
' Declare a DB2Connection and a DB2Command
Dim conn AS DB2Connection
Dim cmd As DB2Command
Try
Console.WriteLine()
Console.WriteLine(" THIS SAMPLE SHOWS HOW TO USE A DB2DataAdapter ")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
Console.WriteLine()
' Create a DB2DataAdapter, a DataSet and a DB2CommandBuilder
Dim adp As DB2DataAdapter = new DB2DataAdapter()
Dim cb As DB2CommandBuilder
Dim dset As DataSet = new DataSet()
cmd = conn.CreateCommand()
' Create a table 'empsamp' in the SAMPLE database
Console.WriteLine(" CREATE TABLE empsamp WITH ATTRIBUTES:" & _
vbNewLine & _
" ID SMALLINT NOT NULL," & vbNewLine & _
" NAME VARCHAR(9)," & vbNewLine & _
" JOB CHAR(5)," & vbNewLine & _
" SALARY DEC(7,2)," & vbNewLine & _
" PRIMARY KEY(ID)")
cmd.CommandText = "CREATE TABLE empsamp ( " & _
" ID SMALLINT NOT NULL, " & _
" NAME VARCHAR(9), " & _
" JOB CHAR(5), " & _
" SALARY DEC(7,2), " & _
" PRIMARY KEY(ID))"
cmd.ExecuteNonQuery()
Console.WriteLine()
' Insert some rows in the empty table 'empsamp'
Console.WriteLine(" INSERT THE FOLLOWING ROWS IN EMPSAMP:" & _
vbNewLine & _
" (270, 'EMP1', 'CLERK', 4500)," & vbNewLine & _
" (280, 'EMP2', 'MGR', 13500.50)," & vbNewLine & _
" (290, 'EMP3', 'SALES', 11000.40)")
cmd.CommandText= "INSERT INTO empsamp(id, name, job, salary)" & _
" VALUES (270, 'EMP1', 'CLERK', 4500)," & _
" (280, 'EMP2', 'MGR', 13500.50)," & _
" (290, 'EMP3', 'SALES', 11000.40)"
Console.WriteLine()
cmd.ExecuteNonQuery()
' Intialize the SELECT command of the DB2DataAdpater
adp.SelectCommand = new DB2Command("SELECT * FROM empsamp",conn)
Console.WriteLine(vbNewLine & _
" USE CLASS DB2CommandBuilder TO GENERATE" & _
" THE INSERT, UPDATE AND DELETE" & vbNewLine & _
" COMMANDS FOR THE DB2DataAdapter")
' Initialize a DB2CommandBuilder instance that generates the UPDATE,
' DELETE and INSERT commands for the DB2DataAdapter
cb = new DB2CommandBuilder(adp)
' Define the parameters for the generated UPDATE, DELETE and INSERT
' commands of the DB2DataAdapter
AddParameters(cb)
Console.WriteLine(vbNewLine & " " & _
"FILL THE DATASET WITH THE Fill METHOD OF THE " & _
"DB2DataAdapter")
' Fill the DataSet with the data in table 'empsamp'
adp.Fill(dset,"empsamp")
' Display the contents of the DataSet
DisplayDataSet(dset)
' Insert rows in the table 'empsamp' using the DataSet and the
' DB2DataAdapter
InsertRows(adp,cmd,dset)
' Delete rows in the table 'empsamp' using the DataSet and the
' DB2DataAdapter
DeleteRows(adp,cmd,dset)
' Update rows in the table 'empsamp' using the DataSet and the
' DB2DataAdapter
UpdateRows(adp,cmd,dset)
' Delete the table 'empsamp'
cmd.CommandText = "DROP TABLE empsamp"
cmd.ExecuteNonQuery()
' Disconnect from the database
Console.WriteLine(vbNewLine & " Disconnect from the database")
conn.Close()
Catch e As Exception
If Not (conn is System.DBNull.value) Then
cmd.CommandText = "DROP TABLE empsamp"
cmd.ExecuteNonQuery()
conn.Close()
End If
Console.WriteLine(e.Message)
End Try
End Sub ' Main
' This method establishes a connection to a database
Public Shared Function ConnectDb(argv() As String) As DB2Connection
Dim server As String
Dim dbalias As String
Dim userId As String
Dim password As String
Dim portNumber As Int32 = -1
Dim connectString As String
If (argv.Length > 5) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
Else
If (argv.Length = 1) Then
If( String.Compare(argv(0),"?") = 0 Or _
String.Compare(argv(0),"-?") = 0 Or _
String.Compare(argv(0),"/?") = 0 Or _
String.Compare(argv(0),"-h",true) = 0 Or _
String.Compare(argv(0),"/h",true) = 0 Or _
String.Compare(argv(0),"-help",true) = 0 Or _
String.Compare(argv(0),"/help",true) = 0 ) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
End If
End If
End If
Select Case (argv.Length)
Case 0 ' Use all defaults
dbalias = "sample"
userId = ""
password = ""
Case 1 ' dbAlias specified
dbalias = argv(0)
userId = ""
password = ""
Case 2 ' userId & passwd specified
dbalias = "sample"
userId = argv(0)
password = argv(1)
Case 3 ' dbAlias, userId & passwd specified
dbalias = argv(0)
userId = argv(1)
password = argv(2)
Case 4 ' use default dbAlias
dbalias = "sample"
server = argv(0)
portNumber = Convert.ToInt32(argv(1))
userId = argv(2)
password = argv(3)
Case 5 ' everything specified
dbalias = argv(0)
server = argv(1)
portNumber = Convert.ToInt32(argv(2))
userId = argv(3)
password = argv(4)
End Select
If(portNumber = -1) Then
connectString = "Database=" & dbalias
Else
connectString = "Server=" & server & ":" & portNumber & _
";Database=" & dbalias
End If
If (userId <> "")
connectString += ";UID=" & userId & ";PWD=" & password
End If
Dim conn As DB2Connection = new DB2Connection(connectString)
conn.Open()
Console.WriteLine(" Connected to the " & dbalias & " database")
Return conn
End Function ' ConnectDb
' This method defines the parameters for the UPDATE, DELETE and INSERT
' commands of the DB2DataAdapter
Public Shared Sub AddParameters(cb As DB2CommandBuilder)
Try
' Define the parameters for the INSERT command in different ways
cb.GetInsertCommand().Parameters.Add("@empid", _
DB2Type.SmallInt, _
5, _
"ID").SourceVersion = _
DataRowVersion.Original
cb.GetInsertCommand().Parameters.Add( _
new DB2Parameter("@empname", _
DB2Type.VarChar, _
9, _
ParameterDirection.Input, _
false, _
0, _
0, _
"NAME", _
DataRowVersion.Current, _
""))
cb.GetInsertCommand().Parameters.Add(new DB2Parameter("@empjob", _
DB2Type.Char, _
5, _
"JOB"))
cb.GetInsertCommand().Parameters.Add("@empsalary", _
DB2Type.Decimal, _
7)
' Define the parameters for the UPDATE command in different ways
cb.GetUpdateCommand().Parameters.Add( _
new DB2Parameter("@empname", _
DB2Type.VarChar, _
9))
cb.GetUpdateCommand().Parameters.Add("@empsalary", _
DB2Type.Decimal, _
7, _
"SALARY")
cb.GetUpdateCommand().Parameters.Add("@empid", _
DB2Type.SmallInt, _
5).SourceVersion = _
DataRowVersion.Original
Dim param As DB2Parameter = new DB2Parameter("@empjob", DB2Type.Char)
cb.GetUpdateCommand().Parameters.Add(param)
' Define the parameter for the DELETE command
cb.GetDeleteCommand().Parameters.Add( _
"@empid", _
DB2Type.SmallInt).SourceVersion = _
DataRowVersion.Original
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' AddParameters
' This method demonstrates how to insert rows in a table using a DataSet
' and a DB2DataAdapter
Public Shared Sub InsertRows(adp As DB2DataAdapter, _
cmd As DB2Command, _
dset As DataSet)
Try
Console.WriteLine(vbNewLine)
' Display the table 'empsamp' before any new rows are inserted
Console.WriteLine(" TABLE BEFORE INSERTING ROWS:")
cmd.CommandText = "SELECT * FROM empsamp"
Dim reader As DB2DataReader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
' 10 rows are generated and inserted into the DataSet
Dim i As Integer
Dim id As Integer = 300
Dim name As Integer = 4
Dim salary As Decimal = 4000
Dim job As String = "MGR"
For i = 0 to 9
Dim nrow As DataRow = dset.Tables("empsamp").NewRow()
nrow("id") = id
nrow("name") = "EMP"&name.ToString()
nrow("job") = job
nrow("salary") = salary
id+ = 10
name+ = 1
salary += 1000
If (job.Equals("MGR")) Then
job = "SALES"
Else
job = "MGR"
End If
dset.Tables("empsamp").Rows.Add(nrow)
Next i
' Update the table 'empsamp' to reflect the insertion of rows into
' the DataSet
adp.Update(dset,"empsamp")
' Display the table 'empsamp' after inserting 10 rows into it
Console.WriteLine(vbNewLine & " TABLE AFTER INSERTING ROWS")
reader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' InsertRows
' This method demonstrates how to delete rows from a table using a DataSet
' and a DB2DataAdapter
Public Shared Sub DeleteRows(adp As DB2DataAdapter, _
cmd As DB2Command, _
dset As DataSet)
Try
' Display the table 'empsamp' before any rows are deleted
Console.WriteLine(vbNewLine & _
" TABLE BEFORE DELETING ROWS WITH SALARY > 10000 ")
cmd.CommandText = "SELECT * FROM empsamp"
Dim reader As DB2DataReader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
' Delete all rows in the 'empsamp' table of the DataSet with
' 'salary' > 10000
Dim i As Integer
For i = 0 to dset.Tables("empsamp").Rows.Count-1
Dim salaryData As Decimal
salaryData = CType(dset.Tables("empsamp").Rows(i)("salary"),Decimal)
If (salaryData > 10000) Then
dset.Tables("empsamp").Rows(i).Delete()
End If
Next i
' Update the table 'empsamp' to reflect the deletion of rows in
' the DataSet
adp.Update(dset,"empsamp")
' Display the table 'empsamp' after deleting rows from it
Console.WriteLine(vbNewLine & _
" TABLE AFTER DELETING ROWS WITH SALARY > 10000 ")
reader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' DeleteRows
' This method demonstrates how to update rows in a table using a DataSet
' and a DB2DataAdapter
Public Shared Sub UpdateRows(adp As DB2DataAdapter, _
cmd As DB2Command, _
dset As DataSet)
Try
' Display the details of a particular employee in 'empsamp' before
' they are updated
Console.WriteLine(vbNewLine & _
" UPDATING THE DETAILS OF THE EMPLOYEE WITH" & _
" ID = 310")
cmd.CommandText = "SELECT * FROM empsamp WHERE ID = 310"
Dim reader As DB2DataReader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
' Update the column entires of the row to new values
Dim i As Integer
For i = 0 to dset.Tables("empsamp").Rows.Count-1
Dim idnum As Integer
idnum = CType(dset.Tables("empsamp").Rows(i)("id"),Integer)
If (Equals(idnum,310)) Then
dset.Tables("empsamp").Rows(i)("name") = "LARRY"
dset.Tables("empsamp").Rows(i)("job") = "MGR"
dset.Tables("empsamp").Rows(i)("salary") = 3500
Exit For
End If
Next i
' Update the table 'empsamp' to reflect the updated row in the
' DataSet
adp.Update(dset,"empsamp")
' Display the details of the employee in 'empsamp' after they have
' been updated
Console.WriteLine(vbNewLine & _
" DETAILS OF THE EMPLOYEE WITH ID = 310" & _
" AFTER UPDATING:")
cmd.CommandText = "SELECT * FROM empsamp WHERE ID = 310"
reader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' UpdateRows
' This method displays the contents of a DataSet
Public Shared Sub DisplayDataSet(dset As DataSet)
Try
Console.WriteLine(vbNewLine & " CONTENTS OF THE DATASET:")
' Display the contents of each table in the DataSet
Dim table As DataTable
For Each table in dset.Tables
Console.WriteLine(vbNewLine & " TABLE: " & _
table.TableName.ToUpper() & _
vbNewLine)
' Display the column headings for the table
Dim col As DataColumn
For Each col in table.Columns
Console.Write(" " & col.ColumnName)
Next col
Console.WriteLine()
Console.Write(" ")
For Each col in table.Columns
Dim length As Integer = 8
If (Equals(col.DataType, Type.GetType("System.Int32")) OR _
Equals(col.DataType, Type.GetType("System.Int16"))) Then
length = 3
Else If (Equals(col.DataType, Type.GetType("System.String"))) Then
length = 7
End If
Dim i As Integer
For i = 0 To length - 1
Console.Write("-")
Next i
Console.Write(" ")
Next col
' Display the values in each row of the table
Dim row As DataRow
For Each row in table.Rows
Console.WriteLine()
Console.Write(" ")
For Each col in table.Columns
If (Equals(col.DataType, Type.GetType("System.Int32")) OR _
Equals(col.DataType, Type.GetType("System.Int16"))) Then
Console.Write(" " & Format(CType(row(col.ColumnName),Int16),3))
Else If (Equals(col.DataType, _
Type.GetType("System.String"))) Then
Console.Write(" " & _
Format(CType(row(col.ColumnName),String),7))
Else
Console.Write(" " & _
Format(CType(row(col.ColumnName),Decimal),7))
End If
Next col
Next row
Next table
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' DisplayDataSet
' This method displays the contents of a DB2DataReader
Public Shared Sub DisplayData (reader As DB2DataReader)
Try
Console.WriteLine()
' DataTable to store the column metadata of the DB2DataReader
Dim table As DataTable = reader.GetSchemaTable()
' Display the column headings for the data in the DB2DataReader
Dim row As DataRow
For Each row in table.Rows
Console.Write(" " & row("ColumnName"))
Next row
Console.WriteLine()
Console.Write(" ")
Dim i As Integer
For Each row in table.Rows
Dim length As Integer = 8
Dim typeData As DB2Type = CType(row("ProviderType"),DB2Type)
If (Equals(typeData, DB2Type.Integer) OR _
Equals(typeData, DB2Type.SmallInt)) Then
length = 3
Else If (Equals(typeData, DB2Type.VarChar) OR _
Equals(typeData, DB2Type.Char)) Then
length = 7
End If
For i = 0 to length - 1
Console.Write("-")
Next i
Console.Write(" ")
Next row
' Display the contents of each row of the DB2DataReader
Do While reader.Read()
Console.WriteLine()
Console.Write(" ")
For i = 0 To table.Rows.Count - 1
Dim typeData As DB2Type
typeData = CType(table.Rows(i)("ProviderType"),DB2Type)
If (Equals(typeData, DB2Type.Integer) OR _
Equals(typeData, DB2Type.SmallInt)) Then
Console.Write(" " & Format(reader.GetInt16(i),3))
Else If (Equals(typeData, DB2Type.VarChar) OR _
Equals(typeData, DB2Type.Char)) Then
Console.Write(" " & Format(reader.GetString(i),7))
Else
Console.Write(" " & Format(reader.GetDecimal(i),7))
End If
Next i
Loop
Console.WriteLine()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' DisplayData
' This method takes a String and returns it with length 'finalLen'
Public Shared Function Format(strData As String, _
finalLen As Integer) As String
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = strData
Dim i As Integer
For i = strData.Length to (finalLen-1)
finalStr = finalStr & " "
Next i
End If
Return finalStr
End Function ' Format(String, Integer)
' This method takes an Int16 and returns it as a String with length
' 'finalLen'
Public Shared Function Format (IntData As Int16, _
finalLen As Integer) As String
Dim strData As String = IntData.ToString()
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = ""
Dim i As Integer
For i = 0 to (finalLen - strData.Length - 1)
finalStr = finalStr & " "
Next i
finalStr = finalStr & strData
End If
Return finalStr
End Function ' Format(Int16, Integer)
' This method takes a Decimal and returns it as a String with a specified
' precision and scale
Public Shared Function Format (doubData As Decimal, _
precision As Integer, _
scale As Integer) As String
Dim dataRound As Decimal = Decimal.Round(doubData,scale)
Dim strData As String = String.Format("{0:f" & scale & "}",dataRound)
' Prepare the final string
Dim finalLen As Integer = precision + 1
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = ""
Dim i As Integer
For i = 0 to (finalLen - strData.Length-1)
finalStr = finalStr & " "
Next i
finalStr = finalStr & strData
End If
Return finalStr
End Function ' Format(Decimal, Integer, Integer)
' This method takes a Decimal and returns it as a String with a specified
' precision
Public Shared Function Format (doubData As Decimal, precision As Integer)
return Format(doubData,precision,2)
End Function ' Format(Decimal, Integer)
End Class ' DbDatAdp