'**************************************************************************** ' (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: DbUse.vb ' ' SAMPLE: How to use a database with the DB2 .Net Data Provider ' ' SQL Statements USED: ' CREATE TABLE ' DROP TABLE ' DELETE ' ' DB2 .NET Data Provider Classes USED: ' DB2Connection ' DB2Command ' DB2Transaction ' ' '**************************************************************************** ' ' Building and Running the sample program ' ' 1. Compile the DbUse.vb file with bldapp.bat by entering the following ' at the command prompt: ' ' bldapp DbUse ' ' or compile DbUse.vb with the makefile by entering the following at ' the command prompt: ' ' nmake DbUse ' ' 2. Run the DbUse program by entering the program name at the command ' prompt: ' ' DbUse ' '**************************************************************************** ' ' 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 DbUse Public Shared Sub Main(args() As String) ' Declare a DB2Connection and a DB2Transaction Dim conn As DB2Connection Dim trans As DB2Transaction Try Console.WriteLine() Console.WriteLine(" THIS SAMPLE SHOWS HOW TO USE A DATABASE.") Console.WriteLine() ' Connect to a database Console.WriteLine(" Connecting to a database ...") conn = ConnectDb(args) ' Demonstrate how to execute an SQL statement with a DB2Command trans = conn.BeginTransaction() ExecCommand(conn,trans) ' Demonstrate how to execute a prepared SQL statement with a ' DB2Command trans = conn.BeginTransaction() ExecPreparedCommand(conn,trans) ' Demonstrate how to execute a parameterized SQL statement with a ' DB2Command trans = conn.BeginTransaction() ExecPreparedCommandWithParam(conn,trans) ' 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 use a DB2Command to execute an ' SQL statement Public Shared Sub ExecCommand(conn As DB2Connection, _ trans As DB2Transaction) Console.WriteLine() Console.WriteLine( _ " ----------------------------------------------------------" & _ vbNewLine & _ " USE THE DB2 .NET DATA PROVIDER CLASS:" & vbNewLine & _ " DB2Command" & vbNewLine & _ " TO EXECUTE AN SQL STATEMENT.") Try ' Create a DB2Command Dim cmd As DB2Command = conn.CreateCommand() ' Execute an SQL statement to create a table 't1' Console.WriteLine() Console.WriteLine(" CREATE TABLE t1(col1 INTEGER)") cmd.CommandText = "CREATE TABLE t1(col1 INTEGER)" cmd.Transaction = trans cmd.ExecuteNonQuery() ' Commit the transaction Console.WriteLine(" COMMIT") trans.Commit() ' Execute an SQL statement that drops table 't1' trans = conn.BeginTransaction() Console.WriteLine(" DROP TABLE t1") cmd.CommandText = "DROP TABLE t1" cmd.ExecuteNonQuery() ' Commit the transaction Console.WriteLine(" COMMIT") trans.Commit() Catch e As Exception Console.WriteLine(e.Message) End Try End Sub ' ExecCommand ' This method demonstrates how to execute a prepared ' SQL statement using a DB2Command Public Shared Sub ExecPreparedCommand(conn As DB2Connection, _ trans As DB2Transaction) Console.WriteLine() Console.WriteLine( _ " ----------------------------------------------------------" & _ vbNewLine & _ " USE THE DB2 .NET DATA PROVIDER CLASS:" & vbNewLine & _ " DB2Command AND ITS METHOD Prepare()" & vbNewLine & _ " TO EXECUTE A PREPARED SQL STATEMENT.") Try Console.WriteLine() Console.WriteLine(" Prepared the statement:" & vbNewLine & _ " DELETE FROM org WHERE deptnumb <= 70") ' Prepare the SQL statement Dim cmd As DB2Command = conn.CreateCommand() cmd.CommandText = "DELETE FROM org WHERE deptnumb <= 70" cmd.Transaction = trans cmd.Prepare() ' Execute the statement Console.WriteLine() Console.WriteLine(" Executed the statement") cmd.ExecuteNonQuery() ' Rollback the transaction Console.WriteLine() Console.WriteLine(" ROLLBACK") trans.Rollback() Catch e As Exception Console.WriteLine(e.Message) End Try End Sub ' ExecPreparedCommand ' This method demonstrates how to execute a parameterized SQL ' statement using a DB2Command Public Shared Sub ExecPreparedCommandWithParam(conn As DB2Connection, _ trans As DB2Transaction) Console.WriteLine() Console.WriteLine( _ " ----------------------------------------------------------" & _ vbNewLine & _ " USE THE DB2 .NET DATA PROVIDER CLASS:" & vbNewLine & _ " DB2Command AND ITS METHOD Prepare()" & vbNewLine & _ " TO EXECUTE A PARAMETERIZED SQL STATEMENT.") Try ' Prepare the statement Console.WriteLine() Console.WriteLine( _ " Prepared the command:" & vbNewLine & _ " DELETE FROM org WHERE deptnumb <= ? AND division = ?") Dim cmd As DB2Command = conn.CreateCommand() cmd.CommandText = "DELETE FROM org " & _ " WHERE deptnumb <= ? " & _ " AND division = ?" cmd.Transaction = trans cmd.Prepare() ' Declare the parameters for the statement and set their value Console.WriteLine() Console.WriteLine(" Executed the command for:" & vbNewLine & _ " parameter 1 = 70" & vbNewLine & _ " parameter 2 = 'Eastern'") cmd.Parameters.Add("@dept", DB2Type.SmallInt).Value = 70 cmd.Parameters.Add("@division", DB2Type.VarChar, 10).Value = "Eastern" ' Execute the statement Console.WriteLine(vbNewLine & " Executed the statement") cmd.ExecuteNonQuery() ' Rollback the transaction Console.WriteLine() Console.WriteLine(" ROLLBACK") trans.Rollback() Catch e As Exception Console.WriteLine(e.Message) End Try End Sub ' ExecPreparedCommandWithParam End Class ' DbUse