/**************************************************************************** ** (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: DbValue.cs ** ** SAMPLE: How to obtain a single value from a database ** with the DB2 .Net Data Provider ** ** SQL Statements USED: ** SELECT ** ** DB2 .NET Data Provider Classes USED: ** DB2Connection ** DB2Command ** ** ***************************************************************************** ** ** Building and Running the sample program ** ** 1. Compile the DbValue.cs file with bldapp.bat by entering the following ** at the command prompt: ** ** bldapp DbValue ** ** or compile DbValue.cs with the makefile by entering the following at ** the command prompt: ** ** nmake DbValue ** ** 2. Run the DbValue program by entering the program name at the command ** prompt: ** ** DbValue ** ***************************************************************************** ** ** 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 DbValue { public static void Main(String[] args) { // Declare a DB2Connection DB2Connection conn = null; try { Console.WriteLine(); Console.WriteLine(" THIS SAMPLE SHOWS HOW TO OBTAIN A SINGLE VALUE " + "FROM A DATABASE."); // Connect to a database Console.WriteLine("\n Connecting to a database ..."); conn = ConnectDb(args); // Obtain a single value from a database ObtainValue(conn); // Disconnect from the database Console.WriteLine("\n Disconnect from the database."); conn.Close(); } catch (Exception e) { conn.Close(); Console.WriteLine(e.Message); } } // 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 obtain a // single value from a database public static void ObtainValue(DB2Connection conn) { Console.WriteLine(); Console.WriteLine( " ----------------------------------------------------------\n" + " USE THE DB2 .NET DATA PROVIDER CLASS:\n\n" + " DB2Command AND ITS METHOD ExecuteScalar()\n\n" + " TO OBTAIN A SINGLE VALUE FROM A DATABASE.\n" + " ----------------------------------------------------------"); try { // Create the DB2Command DB2Command cmd = conn.CreateCommand(); Console.WriteLine(); Console.WriteLine(" EXECUTE THE SQL STATEMENT: "); Console.WriteLine(" SELECT salary FROM staff WHERE ID = 310"); cmd.CommandText = "SELECT salary FROM staff WHERE ID = 310"; Console.WriteLine("\n OBTAINING THE SINGLE VALUE FROM THE " + "QUERY USING THE ExecuteScalar\n METHOD OF " + "DB2Command ..."); // Retrieve and display the single value Decimal empsalary = (Decimal)cmd.ExecuteScalar(); Console.WriteLine("\n SINGLE VALUE OBTAINED FROM QUERY"); Console.WriteLine("\n SALARY OF EMPLOYEE WITH ID = 310 : " + String.Format("{0:f2}",empsalary)); } catch (Exception e) { Console.WriteLine(e.Message); } } // ObtainValue } // DbValue