'****************************************************************************
' (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: DbEvent.vb
'
' SAMPLE: How to use DB2DataAdapter events: RowUpdating and RowUpdated
' 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 DbEvent.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp DbEvent
'
' or compile DbEvent.vb with the makefile by entering the following at
' the command prompt:
'
' nmake DbEvent
'
' 2. Run the DbEvent program by entering the program name at the command
' prompt:
'
' DbEvent
'
'****************************************************************************
'
' 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 Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class DbEvent
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" & _
" EVENTS: ")
Console.WriteLine(" RowUpdating and RowUpdated")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
' Create a DB2DataAdapter, a DB2CommandBuilder and a DataSet
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( _
vbNewLine & _
" 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 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 DB2DataAdapter
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")
' Initailize 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")
' Make changes to the DataSet and respond to the RowUpdating and
' RowUpdated events raised before and after the Update() method of
' DB2DataAdapter is processed for each row
EventHandler(adp,dset)
' Drop 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
conn.Close()
End If
End Try
End Sub
' 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 and demonstrates the use of event handlers
Public Shared Sub EventHandler(adp As DB2DataAdapter, dset As DataSet)
Try
' Add handlers.
AddHandler adp.RowUpdating, _
New DB2RowUpdatingEventHandler(AddressOf UpdatingRow)
AddHandler adp.RowUpdated, _
New DB2RowUpdatedEventHandler(AddressOf UpdatedRow)
' Make changes to the DataSet
Console.WriteLine(vbNewLine & " MAKE CHANGES TO THE DATASET")
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, 270)) 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
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, 290)) Then
dset.Tables("empsamp").Rows(i).Delete()
Exit For
End If
Next i
' Update 'empsamp' to reflect the changes made to the Dataset
' and in the process, raise events: RowUpdating and RowUpdated
Console.WriteLine( _
vbNewLine & _
" UPDATE 'empsamp' TO REFLECT CHANGES MADE TO THE" & _
" DATASET," & vbNewLine & " IN THE PROCESS INVOKING EVENTS: " & _
"RowUpdating and RowUpdated")
adp.Update(dset, "empsamp")
Console.WriteLine(vbNewLine & " EMPSAMP UPDATED")
' Remove event handlers.
RemoveHandler adp.RowUpdating, _
New DB2RowUpdatingEventHandler(AddressOf UpdatingRow)
RemoveHandler adp.RowUpdated, _
New DB2RowUpdatedEventHandler(AddressOf UpdatedRow)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' EventHandler
' This method handles the 'RowUpdating' event
Private Shared Sub UpdatingRow(sender As Object, _
args As DB2RowUpdatingEventArgs)
' Display what type of operation is to be performed
Console.WriteLine(vbNewLine & " EVENT: RowUpdating")
Console.WriteLine( _
vbNewLine & _
" ATTEMPTING TO " & args.StatementType.ToString().ToUpper() & _
" THIS ROW " & vbNewLine)
' Display contents of the row before it is updated or deleted
If (args.StatementType = StatementType.Delete OR _
args.StatementType = StatementType.Update) Then
DisplayRow(args.Row,DataRowVersion.Original)
End If
End Sub ' UpdatingRow
' This method handles the 'RowUpdated' event
Private Shared Sub UpdatedRow(sender As Object, _
args As DB2RowUpdatedEventArgs)
Console.WriteLine(vbNewLine & " EVENT: RowUpdated")
' Check if errors occurred when the row was being updated
If (args.Status = UpdateStatus.ErrorsOccurred) Then
' Skip updating this row and procedd to updating the next row
Console.WriteLine( _
vbNewLine & " AN ERROR OCCURRED WHILE UPDATING THIS ROW")
args.Row.RowError = args.Errors.Message
args.Status = UpdateStatus.SkipCurrentRow
Else
Console.WriteLine( _
vbNewLine & _
" THE " & args.StatementType.ToString().ToUpper() & _
" OPERATION WAS PERFORMED SUCCESFULLY")
' Display contents of the row after if has been updated
If (args.StatementType = StatementType.Update) Then
Console.WriteLine( _
vbNewLine & " DETAILS OF ROW AFTER UPDATE:" & vbNewLine)
DisplayRow(args.Row,DataRowVersion.Current)
End If
End If
End Sub ' UpdatedRow
' Helper method: This method displays the contents of a DataRow
Public Shared Sub DisplayRow(row As DataRow, version As DataRowVersion)
' Obtain the DataTable corresponding to the DataRow
Dim table As DataTable = row.Table
' Display column names
Console.Write(" ")
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")))
length = 7
End If
Dim i As Integer
For i = 0 To length-1
Console.Write("-")
Next i
Console.Write(" ")
Next col
Console.WriteLine()
' Display column entries of each row
For Each col in table.Columns
Dim data As Object = row(col.ColumnName, version)
If (Equals(col.DataType, Type.GetType("System.Int32")) OR _
Equals(col.DataType, Type.GetType("System.Int32"))) Then
Console.Write(" " & data.ToString().PadRight(3))
Else If (Equals(col.DataType, Type.GetType("System.String"))) Then
Console.Write(" " & data.ToString().PadRight(7))
Else
Console.Write(" " & data.ToString().PadLeft(8))
End If
Next col
Console.WriteLine()
End Sub ' DisplayRow
End Class ' DbEvent