/****************************************************************************
** (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