/****************************************************************************
** (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: TbSel.cs
**
** SAMPLE: How to select from each of: insert, update, delete.
**
** SQL Statements USED:
** INCLUDE
** CREATE TABLE
** INSERT
** SELECT FROM INSERT
** SELECT FROM UPDATE
** SELECT FROM DELETE
** PREPARE
** DROP TABLE
**
** DB2 .NET Data Provider Classes USED:
** DB2Connection
** DB2Command
** DB2Transaction
**
**
*****************************************************************************
**
** Building and Running the sample program
**
** 1. Compile the FILENAME file with bldapp.bat by entering the following
** at the command prompt:
**
** bldapp TbSel
**
** or compile FILENAME with the makefile by entering the following at
** the command prompt:
**
** nmake TbSel
**
** 2. Run the TbConstr program by entering the program name at the command
** prompt:
**
** TbSel
**
*****************************************************************************
**
** 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 TbSel
{
public static void Main(String[] args)
{
DB2Connection conn = null;
DB2Transaction trans = null;
try
{
Console.WriteLine();
Console.WriteLine("THIS EXAMPLE SHOWS HOW TO SELECT FROM EACH OF: " +
"INSERT, UPDATE, DELETE.");
// Connect to database.
conn = ConnectDb(args);
trans = conn.BeginTransaction();
Create(conn,trans);
trans = conn.BeginTransaction();
Print(conn,trans);
trans = conn.BeginTransaction();
Buy_Company(conn,trans);
trans = conn.BeginTransaction();
Print(conn,trans);
trans = conn.BeginTransaction();
Drop(conn,trans);
// Disconnect from database.
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
conn.Close();
}
} // Main
/* The ConnectDB function establishes a database connection. */
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();
return conn;
} // ConnectDb
/* The Create function creates and populates tables used by this sample. */
public static void Create(DB2Connection conn, DB2Transaction trans)
{
try
{
/* The context for this sample is that of a Company B taking over
a Company A. This sample illustrates how company B incorporates
data from table company_b into table company_a.
*/
Console.WriteLine("\nCREATE TABLE company_a \n" +
" (ID SMALLINT NOT NULL UNIQUE, \n" +
" NAME VARCHAR(9), \n" +
" DEPARTMENT SMALLINT, \n" +
" JOB CHAR(5), \n" +
" YEARS SMALLINT, \n" +
" SALARY DECIMAL(7,2))");
Console.WriteLine();
// Company A is being bought out.
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"CREATE TABLE company_a " +
"(ID SMALLINT NOT NULL UNIQUE, " +
"NAME VARCHAR(9), " +
"DEPARTMENT SMALLINT, " +
"JOB CHAR(5), " +
"YEARS SMALLINT," +
"SALARY DECIMAL(7,2))";
cmd.ExecuteNonQuery();
Console.WriteLine("CREATE TABLE company_b \n" +
" (ID SMALLINT GENERATED BY DEFAULT AS IDENTITY (START WITH 2000, " +
"INCREMENT BY 1) NOT NULL, \n" +
" NAME VARCHAR(9), \n" +
" DEPARTMENT SMALLINT, \n" +
" JOB CHAR(5), \n" +
" YEARS SMALLINT, \n" +
" SALARY DECIMAL(7,2), \n" +
" BENEFITS VARCHAR(50), \n" +
" OLD_ID SMALLINT)");
Console.WriteLine();
// Company B is buying out Company A. This table has a few additional
// columns and differences from the previous table. Specifically,
// the ID column is generated.
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"CREATE TABLE company_b " +
"(ID SMALLINT GENERATED BY DEFAULT AS IDENTITY (START WITH 2000, " +
"INCREMENT BY 1) NOT NULL, " +
"NAME VARCHAR(9), " +
"DEPARTMENT SMALLINT, " +
"JOB CHAR(5), " +
"YEARS SMALLINT, " +
"SALARY DECIMAL(7,2), " +
"BENEFITS VARCHAR(50), " +
"OLD_ID SMALLINT)";
cmd.ExecuteNonQuery();
Console.WriteLine("CREATE TABLE salary_change \n" +
" (ID SMALLINT NOT NULL UNIQUE, \n" +
" OLD_SALARY DECIMAL(7,2), \n" +
" SALARY DECIMAL(7,2))");
Console.WriteLine();
// This table can be used by the management of Company B to see how
// much of a raise they gave to employees from Company A for joining
// Company B (in a dollar amount, as opposed to a 5% increase).
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"CREATE TABLE salary_change " +
"(ID SMALLINT NOT NULL UNIQUE, " +
"OLD_SALARY DECIMAL(7,2), " +
"SALARY DECIMAL(7,2)) ";
cmd.ExecuteNonQuery();
// Populate table company_a with data.
Console.WriteLine("INSERT INTO company_a VALUES(5275, 'Sanders', 20, 'Mgr', 15, 18357.50), \n" +
" (5265, 'Pernal', 20, 'Sales', 1, 18171.25), \n" +
" (5791, 'O''Brien', 38, 'Sales', 10, 18006.00)");
Console.WriteLine();
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"INSERT INTO company_a VALUES(5275, 'Sanders', 20, 'Mgr', 15, 18357.50), " +
"(5265, 'Pernal', 20, 'Sales', NULL, 18171.25), " +
"(5791, 'O''Brien', 38, 'Sales', 9, 18006.00)";
cmd.ExecuteNonQuery();
Console.WriteLine("INSERT INTO company_b VALUES(default, 'Naughton', 38, " +
" 'Clerk', 0, 12954.75, 'No Benefits', 0), \n" +
" (default, 'Yamaguchi', 42, 'Clerk', 6, 10505.90, " +
" 'Basic Health Coverage', 0), \n" +
" (default, 'Fraye', 51, 'Mgr', 6, 21150.00, " +
" 'Basic Health Coverage', 0), \n" +
" (default, 'Williams', 51, 'Sales', 6, 19456.50, " +
" 'Basic Health Coverage', 0), \n" +
" (default, 'Molinare', 10, 'Mgr', 7, 22959.20, " +
" 'Basic Health Coverage', 0)");
// Populate table company_b with data.
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"INSERT INTO company_b VALUES(default, 'Naughton', 38, 'Clerk', NULL, " +
" 12954.75, 'No Benefits', NULL), " +
"(default, 'Yamaguchi', 42, 'Clerk', 5, 10505.90, " +
" 'Basic Health Coverage', NULL), " +
"(default, 'Fraye', 51, 'Mgr', 8, 21150.00, " +
" 'Basic Health Coverage', NULL), " +
"(default, 'Williams', 51, 'Sales', 10, 19456.50, " +
" 'Advanced Health Coverage', NULL), " +
"(default, 'Molinare', 10, 'Mgr', 15, 22959.20, " +
" 'Advanced Health Coverage and Pension Plan', NULL)";
cmd.ExecuteNonQuery();
// Commit
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Create
/* The Buy_Company function encapsulates the table updates after Company B
takes over Company A. Each employees from table company_a is allocated
a benefits package. The employee data is moved into table company_b.
Each employee's salary is increased by 5%. The old and new salaries are
recorded in a table salary_change.
*/
public static void Buy_Company(DB2Connection conn, DB2Transaction trans)
{
DB2Connection conn2 = null;
DB2Transaction trans2 = null;
DB2DataReader reader = null;
DB2DataReader reader2 = null;
try
{
/* A new connection is needed due to the structure of this sample
since we have nested commands/readers.
*/
conn2 = ConnectDb(new String[] {});
trans2 = conn2.BeginTransaction();
DB2Command cmd = null;
DB2Command cmd2 = null;
int id = 0; // Employee's ID
int department = 0; // Employee's department
int years = 0; // Number of years employee has worked with the
// company
int new_id = 0; // Employee's new ID when they switch companies
string name = null; // Employee's name
string job = null; // Employee's job title
string benefits = null; // Employee's benefits
decimal salary = 0.0M; // Employees current salary
decimal old_salary = 0.0M; // Employees old salary
/* The following SELECT statement references a DELETE statement in its
FROM clause. It deletes all rows from company_a, selecting all
deleted rows into DB2DataReader reader.
*/
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY " +
"FROM OLD TABLE (DELETE FROM company_a)";
cmd.Transaction = trans;
reader = cmd.ExecuteReader();
/* The following while loop iterates through each employee of
table company_a.
*/
while (reader.Read())
{
id = reader.GetInt16(0);
name = reader.GetString(1);
department = reader.GetInt16(2);
job = reader.GetString(3);
if(reader.IsDBNull(4))
years = 0;
else
years = reader.GetInt16(4);
salary = reader.GetDecimal(5);
/* The following if statement sets the new employee's benefits based
on their years of experience.
*/
if(years > 14)
benefits = "Advanced Health Coverage and Pension Plan";
else if(years > 9)
benefits = "Advanced Health Coverage";
else if(years > 4)
benefits = "Basic Health Coverage";
else
benefits = "No Benefits";
/* The following SELECT statement references an INSERT statement in
its FROM clause. It inserts an employee record from host
variables into table company_b. The current employee ID from the
cursor is selected into the host variable new_id via the
DB2DataReader reader2. The keywords FROM FINAL TABLE determine
that the value in new_id is the value of ID after the INSERT
statement is complete.
Note that the ID column in table company_b is generated and
without the SELECT statement an additional query would have
to be made in order to retreive the employee's ID number.
*/
cmd2 = conn2.CreateCommand();
cmd2.CommandText =
"SELECT ID " +
"FROM FINAL TABLE " +
"(INSERT INTO company_b VALUES(default, ?, ?, ?, ?, ?, ?, ?))";
cmd2.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd2.Parameters.Add("@department", DB2Type.SmallInt, 6);
cmd2.Parameters.Add("@job", DB2Type.Char, 5);
cmd2.Parameters.Add("@years", DB2Type.SmallInt, 6);
cmd2.Parameters.Add("@salary", DB2Type.Decimal, 7);
cmd2.Parameters.Add("@benefits", DB2Type.VarChar, 50);
cmd2.Parameters.Add("@id", DB2Type.SmallInt, 6);
cmd2.Parameters["@name"].Value = name;
cmd2.Parameters["@department"].Value = department;
cmd2.Parameters["@job"].Value = job;
cmd2.Parameters["@years"].Value = years;
cmd2.Parameters["@salary"].Value = salary;
cmd2.Parameters["@benefits"].Value = benefits;
cmd2.Parameters["@id"].Value = id;
cmd2.Transaction = trans2;
reader2 = cmd2.ExecuteReader();
reader2.Read();
new_id = reader2.GetInt16(0);
reader2.Close();
/* The following SELECT statement references an UPDATE statement in
its FROM clause. It updates an employee's salary by giving them
a 5% raise. The employee's id, old salary and current salary are
all read into reader2 for later use in this function.
The INCLUDE statement works by creating a temporary column to
keep track of the old salary. This temporary column is only
available for this statement and is gone once the statement
completes. The only way to keep this data after the statement
completes is to read it into a reader object.
*/
cmd2 = conn2.CreateCommand();
cmd2.CommandText = "SELECT ID, OLD_SALARY, SALARY " +
" FROM FINAL TABLE (UPDATE company_b INCLUDE " +
" (OLD_SALARY DECIMAL(7,2)) " +
" SET OLD_SALARY = SALARY, " +
" SALARY = SALARY * 1.05 " +
" WHERE ID = ?)";
cmd2.Parameters.Add("@new_id", DB2Type.SmallInt, 6);
cmd2.Parameters["@new_id"].Value = new_id;
cmd2.Transaction = trans2;
reader2 = cmd2.ExecuteReader();
reader2.Read();
id = reader2.GetInt16(0);
old_salary = reader2.GetDecimal(1);
salary = reader2.GetDecimal(2);
reader2.Close();
/* This INSERT statement inserts an employee's id, old salary and
current salary into the salary_change table.
*/
cmd2 = conn2.CreateCommand();
cmd2.CommandText = " INSERT INTO salary_change VALUES(?, ?, ?)";
cmd2.Parameters.Add("@id", DB2Type.SmallInt, 6);
cmd2.Parameters.Add("@old_salary", DB2Type.Decimal, 6);
cmd2.Parameters.Add("@new_salary", DB2Type.Decimal, 6);
cmd2.Parameters["@id"].Value = new_id;
cmd2.Parameters["@old_salary"].Value = old_salary;
cmd2.Parameters["@new_salary"].Value = salary;
cmd2.Transaction = trans2;
cmd2.ExecuteNonQuery();
}
reader.Close();
/* The following DELETE statement references a SELECT statement in
its FROM clause. It lays off the highest paid manager. This
DELETE statement removes the manager from the table company_b.
*/
cmd = conn2.CreateCommand();
cmd2.CommandText = "DELETE FROM (SELECT * FROM company_b ORDER BY" +
" SALARY DESC FETCH FIRST ROW ONLY)";
cmd2.Transaction = trans2;
cmd2.ExecuteNonQuery();
/* The following UPDATE statement references a SELECT statement in
its FROM clause. It gives the most senior employee a $10000 bonus.
This UPDATE statement raises the employee's salary in the table
company_b.
*/
cmd2 = conn2.CreateCommand();
cmd2.CommandText = "UPDATE (SELECT MAX(YEARS) OVER() AS max_years, " +
"YEARS, " +
"SALARY " +
"FROM company_b) " +
"SET SALARY = SALARY + 10000 " +
"WHERE max_years = YEARS ";
cmd2.Transaction = trans2;
cmd2.ExecuteNonQuery();
// Commit
trans.Commit();
trans2.Commit();
// Disconnect from the database.
conn2.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
conn2.Close();
}
} // Buy_Company
/* The Print function outputs the data in the tables: company_a, company_b
and salary_change. For each table, a while loop and reader object are
used to fetch and display row data.
*/
public static void Print(DB2Connection conn, DB2Transaction trans)
{
DB2DataReader reader = null;
try
{
int id = 0; // Employee's ID
int department = 0; // Employee's department
int years = 0; // Number of years employee has worked with the
// company
int new_id = 0; // Employee's new ID when they switch companies
string name = null; // Employee's name
string job = null; // Employee's job title
string benefits = null; // Employee's benefits
decimal salary = 0.0M; // Employee's current salary
decimal old_salary = 0.0M; // Employee's old salary
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY" +
" FROM company_a";
cmd.Transaction = trans;
reader = cmd.ExecuteReader();
Console.WriteLine("\nSELECT * FROM company_a\n");
Console.WriteLine("ID NAME DEPARTMENT JOB YEARS SALARY");
Console.WriteLine("------ --------- ---------- ----- ------ ----------");
while (reader.Read())
{
id = reader.GetInt16(0);
name = reader.GetString(1);
department = reader.GetInt16(2);
job = reader.GetString(3);
if(reader.IsDBNull(4))
years = 0;
else
years = reader.GetInt16(4);
salary = reader.GetDecimal(5);
Console.WriteLine("{0,6} {1,9} {2,10} {3,5} {4,6} {5,9:C}",
id, name, department, job, years, salary);
}
reader.Close();
Console.WriteLine();
cmd.CommandText = "SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY," +
" BENEFITS, OLD_ID FROM company_b";
cmd.Transaction = trans;
reader = cmd.ExecuteReader();
Console.WriteLine("SELECT * FROM company_b\n");
Console.WriteLine("ID NAME DEPARTMENT JOB YEARS SALARY \nBENEFITS OLD_ID");
Console.WriteLine("------ --------- ---------- ----- ------ ---------- \n-------------------------------------------------- ------");
while (reader.Read())
{
id = reader.GetInt16(0);
name = reader.GetString(1);
department = reader.GetInt16(2);
job = reader.GetString(3);
if(reader.IsDBNull(4))
years = 0;
else
years = reader.GetInt16(4);
salary = reader.GetDecimal(5);
benefits = reader.GetString(6);
if(reader.IsDBNull(7))
new_id = 0;
else
new_id = reader.GetInt16(7);
Console.WriteLine("{0,6} {1,9} {2,10} {3,5} {4,6} {5,9:C}",
id, name, department, job, years, salary);
Console.WriteLine("{0,50} {1,6}", benefits, new_id);
Console.WriteLine();
}
reader.Close();
cmd.CommandText = "SELECT ID, OLD_SALARY, SALARY FROM salary_change";
cmd.Transaction = trans;
reader = cmd.ExecuteReader();
Console.WriteLine("SELECT * FROM salary_change\n");
Console.WriteLine("ID OLD_SALARY SALARY");
Console.WriteLine("------ ---------- ----------");
while (reader.Read())
{
id = reader.GetInt16(0);
old_salary = reader.GetDecimal(1);
salary = reader.GetDecimal(2);
Console.WriteLine("{0,6} {1,10:C} {2,9:C}", id, old_salary, salary);
}
reader.Close();
Console.WriteLine();
// Commit
trans.Commit();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
} // Print
/* The Drop function drops the tables used by this sample. */
public static void Drop(DB2Connection conn, DB2Transaction trans)
{
try
{
Console.WriteLine("DROP TABLE company_a\n");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"DROP TABLE company_a ";
cmd.ExecuteNonQuery();
Console.WriteLine("DROP TABLE company_b\n");
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"DROP TABLE company_b";
cmd.ExecuteNonQuery();
Console.WriteLine("DROP TABLE salary_change\n");
cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"DROP TABLE salary_change";
cmd.ExecuteNonQuery();
// Commit
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Drop
} // TbSel