/**************************************************************************** ** (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.cs ** ** 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.cs file with bldapp.bat by entering the following ** at the command prompt: ** ** bldapp DbUse ** ** or compile DbUse.cs 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 ** ****************************************************************************/ using System; using System.Data; using System.IO; using IBM.Data.DB2; class DbUse { public static void Main(String[] args) { // Declare a DB2Connection and a DB2Transaction DB2Connection conn = null; DB2Transaction trans = null; try { Console.WriteLine(); Console.WriteLine(" THIS SAMPLE SHOWS HOW TO USE A DATABASE."); // Connect to a database Console.WriteLine("\n 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("\n Disconnect from the database."); conn.Close(); } catch (Exception e) { Console.WriteLine(e.Message); conn.Close(); } } // Main // Helper method: This method establishes a connection to a database public static DB2Connection ConnectDb(String[] argv) { String server = ""; String alias = ""; String userId = ""; String password = ""; Int32 portNumber = -1; String connectString; if( argv.Length > 5 || ( argv.Length == 1 && ( String.Compare(argv[0],"?") == 0 || String.Compare(argv[0],"-?") == 0 || String.Compare(argv[0],"/?") == 0 || String.Compare(argv[0],"-h",true) == 0 || String.Compare(argv[0],"/h",true) == 0 || String.Compare(argv[0],"-help",true) == 0 || String.Compare(argv[0],"/help",true) == 0 ) ) ) { throw new Exception( "Usage: prog_name [dbAlias] [userId passwd] \n" + " prog_name [dbAlias] server portNum userId passwd"); } switch (argv.Length) { case 0: // Use all defaults alias = "sample"; userId = ""; password = ""; break; case 1: // dbAlias specified alias = argv[0]; userId = ""; password = ""; break; case 2: // userId & passwd specified alias = "sample"; userId = argv[0]; password = argv[1]; break; case 3: // dbAlias, userId & passwd specified alias = argv[0]; userId = argv[1]; password = argv[2]; break; case 4: // use default dbAlias alias = "sample"; server = argv[0]; portNumber = Convert.ToInt32(argv[1]); userId = argv[2]; password = argv[3]; break; case 5: // everything specified alias = argv[0]; server = argv[1]; portNumber = Convert.ToInt32(argv[2]); userId = argv[3]; password = argv[4]; break; } if(portNumber==-1) { connectString = "Database=" + alias; } else { connectString = "Server=" + server + ":" + portNumber + ";Database=" + alias; } if(userId != "") { connectString += ";UID=" + userId + ";PWD=" + password; } DB2Connection conn = new DB2Connection(connectString); conn.Open(); Console.WriteLine(" Connected to the " + alias + " database"); return conn; } // ConnectDb // This method demonstrates how to use a DB2Command to execute an // SQL statement public static void ExecCommand(DB2Connection conn, DB2Transaction trans) { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE DB2 .NET DATA PROVIDER CLASS:\n" + " DB2Command\n" + " TO EXECUTE AN SQL STATEMENT."); try { // Create a DB2Command DB2Command cmd = 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 (Exception e) { Console.WriteLine(e.Message); } } // ExecCommand // This method demonstrates how to execute a prepared // SQL statement using a DB2Command public static void ExecPreparedCommand(DB2Connection conn, DB2Transaction trans) { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE DB2 .NET DATA PROVIDER CLASS:\n" + " DB2Command AND ITS METHOD Prepare()\n" + " TO EXECUTE A PREPARED SQL STATEMENT."); try { Console.WriteLine(); Console.WriteLine(" Prepare the statement:\n" + " DELETE FROM org WHERE deptnumb <= 70"); // Prepare the SQL statement DB2Command cmd = 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 (Exception e) { Console.WriteLine(e.Message); } } // ExecPreparedCommand // This method demonstrates how to execute a parameterized SQL // statement using a DB2Command public static void ExecPreparedCommandWithParam(DB2Connection conn, DB2Transaction trans) { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE DB2 .NET DATA PROVIDER CLASS:\n" + " DB2Command AND ITS METHOD Prepare()\n" + " TO EXECUTE A PARAMETERIZED SQL STATEMENT."); try { // Prepare the statement Console.WriteLine(); Console.WriteLine( " Prepared the command:\n" + " DELETE FROM org WHERE deptnumb <= ? AND division = ?"); DB2Command cmd = 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(" Execute the statement for:\n" + " parameter 1 = 70\n" + " 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("\n Executed the statement"); cmd.ExecuteNonQuery(); // Rollback the transaction Console.WriteLine(); Console.WriteLine(" ROLLBACK"); trans.Rollback(); } catch (Exception e) { Console.WriteLine(e.Message); } } // ExecPreparedCommandWithParam } // DbUse