/**************************************************************************** ** (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.cs ** ** 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.cs file with bldapp.bat by entering the following ** at the command prompt: ** ** bldapp TbUse ** ** or compile TbUse.cs 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 ** ****************************************************************************/ using System; using System.Data; using System.IO; using IBM.Data.DB2; class TbUse { public static void Main(String[] args) { // Declare a DB2Command, a DB2Transaction and a DB2Connection DB2Connection conn=null; DB2Transaction trans= null; DB2Command cmd = null; try { Console.WriteLine(); Console.WriteLine(" THIS SAMPLE SHOWS HOW TO CONNECT TO/DISCONNECT" + " FROM A DATABASE\n 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("\n Disconnect from the database"); conn.Close(); } catch (Exception e) { if( conn != null ) { conn.Close(); trans.Rollback(); } Console.WriteLine(e.Message); } } // Main // This method demonstrates how to perform a standard query public static void BasicQuery(DB2Connection conn, DB2Transaction trans, DB2Command cmd) { try { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE SQL STATEMENT:\n" + " SELECT\n" + " TO QUERY DATA FROM A TABLE."); // Set up and execute the query Console.WriteLine( "\n Execute Statement:\n" + " SELECT deptnumb, location FROM org WHERE deptnumb < 25"); cmd.CommandText = "SELECT deptnumb, location " + " FROM org " + " WHERE deptnumb < 25"; DB2DataReader reader = cmd.ExecuteReader(); Console.WriteLine(); Console.WriteLine(" Results:\n" + " DEPTNUMB LOCATION\n" + " -------- --------------"); Int16 deptnum = 0; String location=""; // Output the results of the query while(reader.Read()) { deptnum = reader.GetInt16(0); location = reader.GetString(1); Console.WriteLine(" " + Format(deptnum, 8) + " " + Format(location, 14)); } reader.Close(); } catch (Exception e) { Console.WriteLine(e.Message); } } // BasicQuery // This method demonstrates how to insert rows into a table public static void BasicInsert(DB2Connection conn, DB2Transaction trans, DB2Command cmd) { try { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE SQL STATEMENT:\n" + " INSERT\n" + " 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:\n" + " INSERT INTO staff(id, name, dept, job, salary)\n" + " VALUES(380, 'Pearce', 38, 'Clerk', 13217.50),\n" + " (390, 'Hachey', 38, 'Mgr', 21270.00),\n" + " (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 (Exception e) { Console.WriteLine(e.Message); trans.Rollback(); } } // BasicInsert // This method demonstrates how to update rows in a table public static void BasicUpdate(DB2Connection conn, DB2Transaction trans, DB2Command cmd) { try { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE SQL STATEMENT:\n" + " UPDATE\n" + " TO UPDATE TABLE DATA USING A SUBQUERY IN THE 'SET' CLAUSE."); // Display contents of the 'staff' table before updating DisplayStaffTable(conn,cmd); // Update the data of table 'staff' by using a subquery in the SET // clause Console.WriteLine(); Console.WriteLine( " Invoke the statement:\n" + " UPDATE staff\n" + " SET salary = (SELECT MIN(salary)\n" + " FROM staff\n" + " WHERE id >= 310)\n" + " 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 (Exception e) { Console.WriteLine(e.Message); trans.Rollback(); } } // BasicUpdate // This method demonstrates how to delete rows from a table public static void BasicDelete(DB2Connection conn, DB2Transaction trans, DB2Command cmd) { try { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE SQL STATEMENT:\n" + " DELETE\n" + " 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:\n" + " 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 (Exception e) { Console.WriteLine(e.Message); trans.Rollback(); } } // BasicDelete // 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 // Helping method: Display content from the 'staff' table public static void DisplayStaffTable(DB2Connection conn, DB2Command cmd) { try { Int16 id = 0; String name = null; Int16 dept = 0; String job = null; Int16 years = 0; Decimal salary = 0; Decimal comm = 0; Console.WriteLine(); Console.WriteLine( " SELECT * FROM staff WHERE id >= 310\n\n" + " ID NAME DEPT JOB YEARS SALARY COMM\n" + " --- -------- ---- ----- ----- -------- --------"); cmd.CommandText = "SELECT * FROM staff WHERE id >= 310"; DB2DataReader reader = cmd.ExecuteReader(); while (reader.Read()) { id = reader.GetInt16(0); name = reader.GetString(1); dept = reader.GetInt16(2); job = reader.GetString(3); if (reader.IsDBNull(4)) { years = 0; } else { years = reader.GetInt16(4); } salary = reader.GetDecimal(5); if ( reader.IsDBNull(6) ) { comm = 0; } else { comm = reader.GetDecimal(6); } Console.Write(" " + Format(id, 3) + " " + Format(name, 8) + " " + Format(dept, 4)); if (job != null) { Console.Write(" " + Format(job, 5)); } else { Console.Write(" -"); } if (years != 0) { Console.Write(" " + Format(years, 5)); } else { Console.Write(" -"); } Console.Write(" " + Format(salary, 7, 2)); if (comm != 0) { Console.Write(" " + Format(comm, 7, 2)); } else { Console.Write(" -"); } Console.WriteLine(); } reader.Close(); } catch (Exception e) { Console.WriteLine(e.Message); } } // DisplayStaffTable // Helping method: This method takes a String and returns it with // length 'finalLen' public static String Format (String strData, int finalLen) { String finalStr; if (finalLen <= strData.Length) { finalStr = strData.Substring(0, finalLen); } else { finalStr = strData; for (int i = strData.Length; i < finalLen; i++) { finalStr = finalStr + " "; } } return (finalStr); } // Format(String, int) // Helping method: This method takes an Int16 and returns it as a String // with length 'finalLen' public static String Format(Int16 intData, int finalLen) { String strData = intData.ToString(); String finalStr = null; if (finalLen <= strData.Length) { finalStr = strData.Substring(0, finalLen); } else { finalStr = ""; for (int i = 0; i < finalLen - strData.Length; i++) { finalStr = finalStr + " "; } finalStr = finalStr + strData; } return (finalStr); } // Format(Int16, int) // Helping method: This method takes a Decimal and returns it as a String // with a specified precision and scale public static String Format(Decimal doubData, int precision, int scale) { Decimal dataRound = Decimal.Round(doubData,scale); String strData = String.Format("{0:f"+scale+"}" ,dataRound); // Prepare the final string int finalLen = precision + 1; String finalStr; if (finalLen <= strData.Length) { finalStr = strData.Substring(0, finalLen); } else { finalStr = ""; for (int i = 0; i < finalLen - strData.Length; i++) { finalStr = finalStr + " "; } finalStr = finalStr + strData; } return (finalStr); } // Format(Decimal, int, int) } // TbUse