'**************************************************************************** ' (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: TbUse.vb ' ' SAMPLE: How to manipulate table data with the DB2 .Net Data Provider ' and connect to/disconnect from a database ' ' SQL Statements USED: ' SELECT ' INSERT ' UPDATE ' DELETE ' ' DB2 .NET Data Provider Classes USED: ' DB2Connection ' DB2Command ' DB2Transaction ' ' '**************************************************************************** ' ' Building and Running the sample program ' ' 1. Compile the TbUse.vb file with bldapp.bat by entering the following ' at the command prompt: ' ' bldapp TbUse ' ' or compile TbUse.vb with the makefile by entering the following at ' the command prompt: ' ' nmake TbUse ' ' 2. Run the TbUse program by entering the program name at the command ' prompt: ' ' TbUse ' '**************************************************************************** ' ' 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 TbUse Public Shared Sub Main(args() As String) ' Declare a DB2Command, a DB2Transaction and a DB2Connection Dim conn As DB2Connection Dim trans As DB2Transaction Dim cmd As DB2Command Try Console.WriteLine() Console.WriteLine(" THIS SAMPLE SHOWS HOW TO CONNECT" & _ " TO/DISCONNECT FROM A DATABASE" & vbNewLine & _ " AND PERFORM BASIC DATABASE OPERATIONS.") Console.WriteLine() ' Connect to a database Console.WriteLine(" Connecting to a database ...") conn = ConnectDb(args) ' Instantiate the DB2Command trans = conn.BeginTransaction() cmd = conn.CreateCommand() cmd.Connection = conn cmd.Transaction = trans ' Perform a query with the 'org' table BasicQuery(conn,trans,cmd) ' Insert rows into the 'staff' table BasicInsert(conn,trans,cmd) trans=conn.BeginTransaction() cmd.Transaction = trans ' Update a set of rows in the 'staff' table BasicUpdate(conn,trans,cmd) trans=conn.BeginTransaction() cmd.Transaction = trans ' Delete a set of rows from the 'staff' table BasicDelete(conn,trans,cmd) ' 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 ' 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 demonstrates how to perform a standard query Public Shared Sub BasicQuery(conn As DB2Connection, _ trans As DB2Transaction, _ cmd As DB2Command) Try Console.WriteLine() Console.WriteLine( _ " ----------------------------------------------------------" & _ vbNewLine & _ " USE THE SQL STATEMENT:" & vbNewLine & _ " SELECT" & vbNewLine & _ " TO QUERY DATA FROM A TABLE.") ' Set up and execute the query Console.WriteLine( _ vbNewLine & _ " Execute Statement:" & vbNewLine & _ " SELECT deptnumb, location FROM org WHERE deptnumb < 25") cmd.CommandText = "SELECT deptnumb, location " & _ " FROM org " & _ " WHERE deptnumb < 25" Dim reader As DB2DataReader = cmd.ExecuteReader() Console.WriteLine() Console.WriteLine(" Results:" & vbNewLine & _ " DEPTNUMB LOCATION" & vbNewLine & _ " -------- --------------") Dim deptnum As Int16 = 0 Dim location As String ' Output the results of the query Do While reader.Read() deptnum = reader.GetInt16(0) location = reader.GetString(1) Console.WriteLine(" " & _ Format(deptnum, 8) & " " & _ Format(location, 14)) Loop reader.Close() Catch e As Exception Console.WriteLine(e.Message) End Try End Sub ' BasicQuery ' This method demonstrates how to insert rows into a table Public Shared Sub BasicInsert(conn As DB2Connection, _ trans As DB2Transaction, _ cmd As DB2Command) Try Console.WriteLine() Console.WriteLine( _ " ----------------------------------------------------------" & _ vbNewLine & _ " USE THE SQL STATEMENT:" & vbNewLine & _ " INSERT" & vbNewLine & _ " TO INSERT DATA INTO A TABLE USING VALUES.") ' Display contents of the 'staff' table before inserting rows DisplayStaffTable(conn,cmd) ' Use the INSERT statement to insert data into the 'staff' table. Console.WriteLine() Console.WriteLine( _ " Invoke the statement:" & vbNewLine & _ " INSERT INTO staff(id, name, dept, job, salary)" & vbNewLine & _ " VALUES(380, 'Pearce', 38, 'Clerk', 13217.50)," & vbNewLine & _ " (390, 'Hachey', 38, 'Mgr', 21270.00)," & vbNewLine & _ " (400, 'Wagland', 38, 'Clerk', 14575.00)") cmd.CommandText = "INSERT INTO staff(id, name, dept, job, salary)" & _ " VALUES(380, 'Pearce', 38, 'Clerk', 13217.50)," & _ " (390, 'Hachey', 38, 'Mgr', 21270.00)," & _ " (400, 'Wagland', 38, 'Clerk', 14575.00)" cmd.ExecuteNonQuery() ' Display the content in the 'staff' table after the INSERT. DisplayStaffTable(conn,cmd) ' Rollback the transaction trans.Rollback() Catch e As Exception Console.WriteLine(e.Message) trans.Rollback() End Try End Sub ' BasicInsert ' This method demonstrates how to update rows in a table Public Shared Sub BasicUpdate(conn As DB2Connection, _ trans As DB2Transaction, _ cmd As DB2Command) Try Console.WriteLine() Console.WriteLine( _ " ----------------------------------------------------------" & _ vbNewLine & _ " USE THE SQL STATEMENT:" & vbNewLine & _ " UPDATE" & vbNewLine & _ " TO UPDATE TABLE DATA USING SUBQUERY IN THE 'SET' CLAUSE.") ' Display contents of the 'staff' table before updating DisplayStaffTable(conn,cmd) ' Update the data of the table 'staff' by using subquery in the SET ' clause Console.WriteLine() Console.WriteLine( _ " Invoke the statement:" & vbNewLine & _ " UPDATE staff" & vbNewLine & _ " SET salary = (SELECT MIN(salary)" & vbNewLine & _ " FROM staff" & vbNewLine & _ " WHERE id >= 310)" & vbNewLine & _ " WHERE id = 310") cmd.CommandText = "UPDATE staff " & _ " SET salary = (SELECT MIN(salary) " & _ " FROM staff " & _ " WHERE id >= 310) " & _ " WHERE id = 310" cmd.ExecuteNonQuery() ' Display the final content of the 'staff' table DisplayStaffTable(conn,cmd) trans.Rollback() Catch e As Exception Console.WriteLine(e.Message) trans.Rollback() End Try End Sub ' BasicUpdate ' This method demonstrates how to delete rows from a table Public Shared Sub BasicDelete(conn As DB2Connection, _ trans As DB2Transaction, _ cmd As DB2Command) Try Console.WriteLine() Console.WriteLine( _ " ----------------------------------------------------------" & _ vbNewLine & _ " USE THE SQL STATEMENT:" & vbNewLine & _ " DELETE" & vbNewLine & _ " TO DELETE TABLE DATA.") ' Display contents of the 'staff' table DisplayStaffTable(conn,cmd) ' Delete rows from the 'staff' table where id >= 310 and ' salary > 20000 Console.WriteLine() Console.WriteLine( _ " Invoke the statement:" & vbNewLine & _ " DELETE FROM staff WHERE id >= 310 AND salary > 20000") cmd.CommandText = "DELETE FROM staff " & _ " WHERE id >= 310 " & _ " AND salary > 20000" cmd.ExecuteNonQuery() ' Display the final content of the 'staff' table DisplayStaffTable(conn,cmd) trans.Rollback() Catch e As Exception trans.Rollback() Console.WriteLine(e.Message) End Try End Sub ' BasicDelete ' Helping method: Display content from the 'staff' table Public Shared Sub DisplayStaffTable(conn As DB2Connection, _ cmd As DB2Command) Try Dim id As Int16 = 0 Dim name As String Dim dept As Int16 = 0 Dim job As String Dim years As Int16 = 0 Dim salary As Decimal = 0 Dim comm As Decimal = 0 Console.WriteLine() Console.WriteLine( _ " SELECT * FROM staff WHERE id >= 310" & vbNewLine & vbNewLine & _ " ID NAME DEPT JOB YEARS SALARY COMM" & vbNewLine & _ " --- -------- ---- ----- ----- -------- --------") cmd.CommandText = "SELECT * FROM staff WHERE id >= 310" Dim reader As DB2DataReader = cmd.ExecuteReader() Do While reader.Read() id = reader.GetInt16(0) name = reader.GetString(1) dept = reader.GetInt16(2) job = reader.GetString(3) If (reader.IsDBNull(4)) Then years = 0 Else years = reader.GetInt16(4) End If salary = reader.GetDecimal(5) If ( reader.IsDBNull(6) ) Then comm = 0 Else comm = reader.GetDecimal(6) End If Console.Write(" " & Format(id, 3) & _ " " & Format(name, 8) & _ " " & Format(dept, 4)) If (job is System.DBNull.value) Then Console.Write(" -") Else Console.Write(" " & Format(job, 5)) End If If (years <> 0) Then Console.Write(" " & Format(years, 5)) Else Console.Write(" -") End If Console.Write(" " & Format(salary, 7, 2)) if (comm <> 0) Then Console.Write(" " & Format(comm, 7, 2)) Else Console.Write(" -") End If Console.WriteLine() Loop reader.Close() Catch e As Exception Console.WriteLine(e.Message) End Try End Sub ' DisplayStaffTable ' Helping method: 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) ' Helping method: 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) ' Helping method: 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) End Class ' TbUse