'****************************************************************************
' (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: DbDatMap.vb
'
' SAMPLE: How to set up and use DataTable and DataColumn mappings
' with the DB2 .NET Data Provider
'
' SQL Statements USED:
' CREATE TABLE
' DROP TABLE
' INSERT
' SELECT
' DELETE
'
' DB2 .NET Data Provider Classes USED:
' DB2Connection
' DB2Command
'
'
'****************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the DbDatMap.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp DbDatMap
'
' or compile DbDatMap.vb with the makefile by entering the following at
' the command prompt:
'
' nmake DbDatMap
'
' 2. Run the DbDatMap program by entering the program name at the command
' prompt:
'
' DbDatMap
'
'****************************************************************************
'
' 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 System.Data.Common
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class DbDatMap
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 SET UP AND USE" & _
" DataTable AND DataColumn MAPPINGS")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
' 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 A 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 into the empty table 'empsamp'
Console.WriteLine( _
" INSERT THE FOLLOWING ROWS IN empsamp:" & vbNewLine & _
" (260, 'EMP1', 'CLERK', 4500.00)," & vbNewLine & _
" (300, 'EMP2', 'SALES', 11000.40)")
cmd.CommandText = "INSERT INTO empsamp(id, name, job, salary) " & _
" VALUES (260, 'EMP1', 'CLERK', 4500.00), " & _
" (300, 'EMP2', '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)
' Create a DataTableMapping named 'Table' for the 'empsamp' table
SetMapping(adp)
' Modify the 'empsamp' table in the sample database through the
' DataTableMapping, using the DB2DataAdapter
UseMapping(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
Console.WriteLine(e.Message)
If Not (conn is System.DBNull.value) Then
cmd.CommandText = "DROP TABLE empsamp"
cmd.ExecuteNonQuery()
conn.Close()
End If
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 creates a DataTableMapping for the table 'empsamp'
Public Shared Sub SetMapping(adp As DB2DataAdapter)
Try
Console.WriteLine( _
vbNewLine & _
" CREATE A DataTableMapping named 'Table' FOR THE TABLE 'empsamp'" _
& vbNewLine & _
" WHICH IS THE DEFAULT DataTableMapping FOR THE DB2DataAdapter")
Console.WriteLine( _
vbNewLine & _
" MAP COLUMN NAMES IN THE 'empsamp' TABLE OF THE SAMPLE DATABASE" _
& vbNewLine & _
" TO NEW COLUMN NAMES IN THE 'empsamp' TABLE IN THE DATASET:'" & _
vbNewLine & _
" 'ID' MAPPED TO 'newid'" & vbNewLine & _
" 'NAME' MAPPED TO 'newname'" & vbNewLine & _
" 'JOB' MAPPED TO 'newjob'" & vbNewLine & _
" 'SALARY' MAPPED TO 'newsalary'")
' Create a DataTableMapping for the table 'empsamp' and map existing
' column names to new column names
Dim empsamp_map As DataTableMapping = adp.TableMappings.Add("Table", _
"empsamp")
empsamp_map.ColumnMappings.Add("ID", "newid")
empsamp_map.ColumnMappings.Add("NAME", "newname")
empsamp_map.ColumnMappings.Add("JOB", "newjob")
empsamp_map.ColumnMappings.Add("SALARY", "newsalary")
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' SetMapping
' This method modifies the 'empsamp' table in the sample database through
' the DataTableMapping, using the DB2DataAdapter
Public Shared Sub UseMapping(adp As DB2DataAdapter, _
cmd As DB2command, _
dset As DataSet)
Try
Console.WriteLine( _
vbNewLine & _
" MODIFY THE TABLE 'empsamp' IN THE SAMPLE DATABASE THROUGH THE" & _
vbNewLine & " DataTableMapping USING THE DB2DataAdapter")
' Fill the DataSet with the data in table 'empsamp' through the
' default (and so does not have to be specified) DataTableMapping
' 'Table'
Console.WriteLine( _
vbNewLine & " " & _
"FILL THE DATASET WITH THE Fill METHOD OF DB2DataAdapter." & _
vbNewLine & " NO TABLE NAME NEEDS TO BE SPECIFIED BECAUSE " & _
"'Table' IS THE DEFAULT" & _
vbNewLine & " DataTableMapping FOR THE DB2DataAdapter")
adp.Fill(dset)
' Display the table 'empsamp' before any new rows are inserted
Console.WriteLine(vbNewLine & " TABLE BEFORE INSERTING ROWS:")
cmd.CommandText = "SELECT * FROM empsamp"
Dim reader As DB2DataReader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
' Insert some rows in the table 'empsamp'
Console.WriteLine( _
vbNewLine & _
" INSERT THE FOLLOWING ROWS IN EMPSAMP:" & vbNewLine & _
" (270, 'EMP3', 'SALES', 7500)," & vbNewLine & _
" (280, 'EMP4', 'CLERK', 10000.00)," & vbNewLine & _
" (290, 'EMP5', 'MGR', 15000.00)")
Dim row As DataRow = dset.Tables("empsamp").NewRow()
row("newid") = 270
row("newname") = "EMP3"
row("newjob") = "SALES"
row("newsalary") = 7500
dset.Tables("empsamp").Rows.Add(row)
row = dset.Tables("empsamp").NewRow()
row("newid") = 280
row("newname") = "EMP4"
row("newjob") = "CLERK"
row("newsalary") = 10000.00
dset.Tables("empsamp").Rows.Add(row)
row = dset.Tables("empsamp").NewRow()
row("newid") = 290
row("newname") = "EMP5"
row("newjob") = "MGR"
row("newsalary") = 15000.00
dset.Tables("empsamp").Rows.Add(row)
' Update the table 'empsamp' in the sample database through the
' DataTableMapping 'Table' to reflect the insertion of rows in the
' DataSet. The DataTableMapping 'Table' is specified although it does
' not need to be, because it is the default DataTableMapping
adp.Update(dset,"Table")
Console.WriteLine( _
vbNewLine & _
" ROWS INSERTED IN THE TABLE 'empsamp' THROUGH THE " & _
"DataTableMapping 'Table'" & _
vbNewLine & " BY THE Update METHOD OF THE DB2DataAdapter")
Console.WriteLine(vbNewLine & " TABLE AFTER INSERTING ROWS:")
cmd.CommandText = "SELECT * FROM empsamp"
reader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
' Make changes to the Dataset by deleting and changing the contents
' of some rows
Console.WriteLine( _
vbNewLine & _
" DELETE EMPLOYEE ID = 300 AND CHANGE DETAILS OF EMPLOYEE ID = 260")
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)("newid"), Int16)
If(Equals(idnum, 300)) Then
dset.Tables("empsamp").Rows(i).Delete()
Else If(Equals(idnum, 260)) Then
dset.Tables("empsamp").Rows(i)("newjob") = "MGR"
dset.Tables("empsamp").Rows(i)("newsalary") = 20000
End If
Next i
' Update the table 'empsamp' in the sample database to reflect the
' changes made to the table in the DataSet through the default(and
' so does not have to be specified) DataTableMapping 'Table'
adp.Update(dset)
Console.WriteLine( _
vbNewLine & _
" ROWS UPDATED AND DELETED IN THE TABLE 'empsamp' THROUGH THE" & _
" DataTableMapping" & vbNewLine & " 'Table' BY THE Update " & _
"METHOD OF THE DB2DataAdapter")
' Display the table 'empsamp' after updating
Console.WriteLine( _
vbNewLine & _
" TABLE AFTER DELETING EMPLOYEE ID = 300 AND CHANGING DETAILS" & _
vbNewLine & " OF EMPLOYEE ID = 260")
reader = cmd.ExecuteReader()
DisplayData(reader)
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' UseMapping
' This method displays the contents stored in a DB2DataReader instance
Public Shared Sub DisplayData (reader As DB2DataReader)
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 dataType As DB2Type = CType(table.Rows(i)("ProviderType"), _
DB2Type)
If (Equals(dataType, DB2Type.Integer) OR _
Equals(dataType, DB2Type.SmallInt)) Then
Console.Write(" " & reader.GetInt16(i).ToString().PadRight(3))
Else If (Equals(dataType, DB2Type.VarChar) OR _
Equals(dataType, DB2Type.Char)) Then
Console.Write(" " & reader.GetString(i).PadRight(7))
Else
Dim dataRound As Decimal = Decimal.Round(reader.GetDecimal(i),2)
Dim strData As String = String.Format("{0:f2}",dataRound)
Console.Write(" " & strData.PadLeft(8))
End If
Next i
Loop
Console.WriteLine()
End Sub ' DisplayData
End Class ' DbDatMap