/****************************************************************************
** (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: DbDatAdp.cs
**
** SAMPLE: How to use a DB2DataAdapter with the DB2 .NET Data Provider
**
** SQL Statements USED:
** CREATE TABLE
** DROP TABLE
** INSERT
** SELECT
**
** DB2 .NET Data Provider Classes USED:
** DB2Connection
** DB2Command
**
**
*****************************************************************************
**
** Building and Running the sample program
**
** 1. Compile the DbDatAdp.cs file with bldapp.bat by entering the following
** at the command prompt:
**
** bldapp DbDatAdp
**
** or compile DbDatAdp.cs with the makefile by entering the following at
** the command prompt:
**
** nmake DbDatAdp
**
** 2. Run the DbDatAdp program by entering the program name at the command
** prompt:
**
** DbDatAdp
**
*****************************************************************************
**
** 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 DbDatAdp
{
public static void Main(String[] args)
{
// Declare a DB2Connection and a DB2Command
DB2Connection conn = null;
DB2Command cmd= null;
try
{
Console.WriteLine();
Console.WriteLine(" THIS SAMPLE SHOWS HOW TO USE A DB2DataAdapter");
Console.WriteLine();
// Connect to a database
Console.WriteLine(" Connecting to a database ...");
conn = ConnectDb(args);
Console.WriteLine();
// Create a DB2DataAdapter, a DataSet and a DB2CommandBuilder
DB2DataAdapter adp = new DB2DataAdapter();
DB2CommandBuilder cb = null;
DataSet dset = new DataSet();
cmd = conn.CreateCommand();
// Create a table 'empsamp' in the SAMPLE database
Console.WriteLine(" CREATE TABLE empsamp WITH ATTRIBUTES:\n" +
" ID SMALLINT NOT NULL,\n" +
" NAME VARCHAR(9),\n" +
" JOB CHAR(5),\n" +
" SALARY DEC(7,2),\n" +
" PRIMARY KEY(ID)");
cmd.CommandText = "CREATE TABLE EMPSAMP (" +
" ID SMALLINT NOT NULL," +
" NAME VARCHAR(9)," +
" JOB CHAR(5)," +
" SALARY DEC(7,2)," +
" PRIMARY KEY(ID))";
cmd.ExecuteNonQuery();
Console.WriteLine();
// Insert some rows in the empty table 'empsamp'
Console.WriteLine(
" INSERT THE FOLLOWING ROWS IN EMPSAMP:\n" +
" (270, 'EMP1', 'CLERK', 4500.00),\n" +
" (280, 'EMP2', 'MGR', 13500.50),\n" +
" (290, 'EMP3', 'SALES', 11000.40)");
cmd.CommandText = "INSERT INTO empsamp(id, name, job, salary)" +
" VALUES (270, 'EMP1', 'CLERK', 4500.00)," +
" (280, 'EMP2', 'MGR', 13500.50)," +
" (290, 'EMP3', 'SALES', 11000.40)";
Console.WriteLine();
cmd.ExecuteNonQuery();
// Intialize the SELECT command of the DB2DataAdapter
adp.SelectCommand = new DB2Command("SELECT * FROM empsamp",conn);
Console.WriteLine("\n USE CLASS DB2CommandBuilder TO GENERATE " +
" THE INSERT, UPDATE AND DELETE\n" +
" COMMANDS FOR THE DB2DataAdapter");
// initialize a DB2CommandBuilder instance that generates the UPDATE,
// DELETE and INSERT commands for the DB2DataAdapter
cb = new DB2CommandBuilder(adp);
// Define the parameters for the generated UPDATE, DELETE and
// INSERT commands of the DB2DataAdapter
AddParameters(cb);
Console.WriteLine("\n " +
"FILL THE DATASET WITH THE Fill METHOD OF THE " +
"DB2DataAdapter");
// Fill the DataSet with the data in table 'empsamp'
adp.Fill(dset,"empsamp");
// Display the contents of the DataSet
DisplayDataSet(dset);
// Insert rows in the table 'empsamp' using the DataSet and the
// DB2DataAdapter
InsertRows(adp,cmd,dset);
// Delete rows in the table 'empsamp' using the DataSet and the
// DB2DataAdapter
DeleteRows(adp,cmd,dset);
// Update rows in the table 'empsamp' using the DataSet and the
// DB2DataAdapter
UpdateRows(adp,cmd,dset);
// Delete the table 'empsamp'
cmd.CommandText = "DROP TABLE empsamp";
cmd.ExecuteNonQuery();
// Disconnect from the database
Console.WriteLine("\n Disconnect from the database");
conn.Close();
}
catch (Exception e)
{
cmd.CommandText = "DROP TABLE empsamp";
cmd.ExecuteNonQuery();
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 defines the parameters for the UPDATE, DELETE and INSERT
// commands of the DB2DataAdapter
public static void AddParameters(DB2CommandBuilder cb)
{
try
{
// Define the parameters for the INSERT command in different ways
cb.GetInsertCommand().Parameters.Add("@empid",
DB2Type.SmallInt,
5,
"ID").SourceVersion =
DataRowVersion.Original;
cb.GetInsertCommand().Parameters.Add(
new DB2Parameter("@empname",
DB2Type.VarChar,
9,
ParameterDirection.Input,
false,
0,
0,
"NAME",
DataRowVersion.Current,
""));
cb.GetInsertCommand().Parameters.Add(new DB2Parameter("@empjob",
DB2Type.Char,
5,
"JOB"));
cb.GetInsertCommand().Parameters.Add("@empsalary",
DB2Type.Decimal,
7);
// Define the parameters for the UPDATE command in different ways
cb.GetUpdateCommand().Parameters.Add(new DB2Parameter("@empname",
DB2Type.VarChar,
9));
cb.GetUpdateCommand().Parameters.Add("@empsalary",
DB2Type.Decimal,
7,
"SALARY");
cb.GetUpdateCommand().Parameters.Add("@empid",
DB2Type.SmallInt,
5).SourceVersion =
DataRowVersion.Original;
DB2Parameter param = new DB2Parameter("@empjob", DB2Type.Char);
cb.GetUpdateCommand().Parameters.Add( param );
// Define the parameter for the DELETE command
cb.GetDeleteCommand().Parameters.Add("@empid",
DB2Type.SmallInt).SourceVersion =
DataRowVersion.Original;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // AddParameters
// This method demonstrates how to insert rows in a table using a DataSet
// and a DB2DataAdapter
public static void InsertRows(DB2DataAdapter adp,
DB2Command cmd,
DataSet dset)
{
try
{
Console.WriteLine("\n");
// Display the table 'empsamp' before any new rows are inserted
Console.WriteLine(" TABLE BEFORE INSERTING ROWS:");
cmd.CommandText = "SELECT * FROM empsamp";
DB2DataReader reader = cmd.ExecuteReader();
DisplayData(reader);
reader.Close();
// 10 rows are generated and inserted into the DataSet
int i = 0;
int id = 300;
int name = 4;
int salary = 4000;
String job="MGR";
for (i = 0; i<10; i++)
{
DataRow nrow = dset.Tables["empsamp"].NewRow();
nrow["id"]=id;
nrow["name"]="EMP"+name.ToString();
nrow["job"]=job;
nrow["salary"]=salary;
id=id+10;
name++;
salary=salary+1000;
if (job.Equals("MGR"))
{
job="SALES";
}
else
{
job="MGR";
}
dset.Tables["empsamp"].Rows.Add(nrow);
}
// Update the table 'empsamp' to reflect the insertion of rows into
// the DataSet
adp.Update(dset,"empsamp");
// Display the table 'empsamp' after inserting 10 rows into it
Console.WriteLine("\n TABLE AFTER INSERTING ROWS");
reader = cmd.ExecuteReader();
DisplayData(reader);
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // InsertRows
// This method demonstrates how to delete rows from a table using a
// DataSet and a DB2DataAdapter
public static void DeleteRows(DB2DataAdapter adp,
DB2Command cmd,
DataSet dset)
{
try
{
// Display the table 'empsamp' before any rows are deleted
Console.WriteLine("\n " +
"TABLE BEFORE DELETING ROWS WITH SALARY > 10000 ");
cmd.CommandText = "SELECT * FROM empsamp";
DB2DataReader reader = cmd.ExecuteReader();
DisplayData(reader);
reader.Close();
// Delete all rows in the 'empsamp' table of the DataSet with
// 'salary' > 10000
int i;
for (i = 0; i<dset.Tables["empsamp"].Rows.Count; i++)
{
if ((Decimal)(dset.Tables["empsamp"].Rows[i]["salary"]) > 10000)
{
dset.Tables["empsamp"].Rows[i].Delete();
}
}
// Update the table 'empsamp' to reflect the deletion of rows in
// the DataSet
adp.Update(dset,"empsamp");
// Display the table 'empsamp' after deleting rows from it
Console.WriteLine("\n " +
"TABLE AFTER DELETING ROWS WITH SALARY > 10000 ");
reader = cmd.ExecuteReader();
DisplayData(reader);
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // DeleteRows
// This method demonstrates how to update rows in a table using a DataSet
// and a DB2DataAdapter
public static void UpdateRows(DB2DataAdapter adp,
DB2Command cmd,
DataSet dset)
{
try
{
// Display the details of a particular employee in 'empsamp' before
// they are updated
Console.WriteLine("\n UPDATE THE DETAILS OF THE EMPLOYEE WITH " +
"ID = 310");
cmd.CommandText = "SELECT * FROM empsamp WHERE ID=310";
DB2DataReader reader = cmd.ExecuteReader();
DisplayData(reader);
reader.Close();
// Update the column entires of the row to new values
int i;
for (i=0; i<dset.Tables["empsamp"].Rows.Count; i++)
{
if (((Int16)dset.Tables["empsamp"].Rows[i]["id"]) == 310)
{
dset.Tables["empsamp"].Rows[i]["name"] = "LARRY";
dset.Tables["empsamp"].Rows[i]["job"] = "MGR";
dset.Tables["empsamp"].Rows[i]["salary"] = 3500;
break;
}
}
// Update the table 'empsamp' to reflect the updated row in the
// DataSet
adp.Update(dset,"empsamp");
// Display the details of the employee in 'empsamp' after they have
// been updated
Console.WriteLine("\n " +
"DETAILS OF THE EMPLOYEE WITH ID = 310 " +
"AFTER UPDATING:");
cmd.CommandText = "SELECT * FROM empsamp WHERE ID = 310";
reader = cmd.ExecuteReader();
DisplayData(reader);
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // UpdateRows
// This method displays the entire contents of a DataSet
public static void DisplayDataSet(DataSet dset)
{
try
{
Console.WriteLine("\n CONTENTS OF THE DATASET:");
// Display the contents of each table in the DataSet
foreach (DataTable table in dset.Tables)
{
Console.WriteLine("\n TABLE: " + table.TableName.ToUpper() + "\n");
// Display the column headings for the table
foreach (DataColumn col in table.Columns)
{
Console.Write(" " + col.ColumnName);
}
Console.WriteLine();
Console.Write(" ");
foreach (DataColumn col in table.Columns)
{
int length = 8;
if (col.DataType == Type.GetType("System.Int32") ||
col.DataType == Type.GetType("System.Int16"))
{
length = 3;
}
else if (col.DataType == Type.GetType("System.String"))
{
length = 7;
}
for (int i = 0; i < length; i++)
{
Console.Write("-");
}
Console.Write(" ");
}
// Display the values in each row of the table
foreach (DataRow row in table.Rows)
{
Console.WriteLine();
Console.Write(" ");
foreach (DataColumn col in table.Columns)
{
if (row[col.ColumnName] is Int32 || row[col.ColumnName] is Int16)
{
Console.Write(" " +Format((Int16)row[col.ColumnName],3));
}
else if (row[col.ColumnName] is String)
{
Console.Write(" " +Format((String)row[col.ColumnName],7));
}
else
{
Console.Write(" " +Format((Decimal)row[col.ColumnName],7));
}
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // DisplayDataSet
// This method displays the contents of a DB2DataReader
public static void DisplayData(DB2DataReader reader)
{
try
{
Console.WriteLine();
// DataTable to store the column metadata of the DB2DataReader
DataTable table = reader.GetSchemaTable();
// Display the column headings for the data in the DB2DataReader
foreach (DataRow row in table.Rows)
{
Console.Write(" " + row["ColumnName"]);
}
Console.WriteLine();
Console.Write(" ");
foreach (DataRow row in table.Rows)
{
int length = 8;
if ((DB2Type)row["ProviderType"] == DB2Type.Integer ||
(DB2Type)row["ProviderType"] == DB2Type.SmallInt)
{
length = 3;
}
else if ((DB2Type)row["ProviderType"] == DB2Type.VarChar ||
(DB2Type)row["ProviderType"] == DB2Type.Char)
{
length = 7;
}
for (int i = 0; i < length; i++)
{
Console.Write("-");
}
Console.Write(" ");
}
// Display the contents of each row of the DB2DataReader
while (reader.Read())
{
Console.WriteLine();
Console.Write(" ");
for (int i = 0; i < table.Rows.Count; i++)
{
if ((DB2Type)table.Rows[i]["ProviderType"] == DB2Type.Integer ||
(DB2Type)table.Rows[i]["ProviderType"] == DB2Type.SmallInt)
{
Console.Write(" " + Format(reader.GetInt16(i),3));
}
else if ((DB2Type)table.Rows[i]["ProviderType"] ==
DB2Type.VarChar ||
(DB2Type)table.Rows[i]["ProviderType"] == DB2Type.Char)
{
Console.Write(" " +Format(reader.GetString(i),7));
}
else
{
Console.Write(" " +Format(reader.GetDecimal(i),7));
}
}
}
Console.WriteLine();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // DisplayData
// 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;
int i;
for (i = strData.Length; i < finalLen; i++)
{
finalStr = finalStr + " ";
}
}
return (finalStr);
} // Format(String, int)
// This method takes an Int32 and returns it as a String with length
// 'finalLen'
public static String Format(Int32 intData, int finalLen)
{
String strData = intData.ToString();
String finalStr=null;
if (finalLen <= strData.Length)
{
finalStr = strData.Substring(0, finalLen);
}
else
{
finalStr = "";
int i;
for (i = 0; i < finalLen - strData.Length; i++)
{
finalStr = finalStr + " ";
}
finalStr = finalStr + strData;
}
return (finalStr);
} // Format(Int32, int)
// 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 = "";
int i;
for (i = 0; i < finalLen - strData.Length; i++)
{
finalStr = finalStr + " ";
}
finalStr = finalStr + strData;
}
return (finalStr);
} // Format(Decimal, int, int)
// This method takes a Decimal and returns it as a String with a specified
// precision
public static String Format(Decimal doubData, int precision)
{
return Format(doubData,precision,2);
} // Format(Decimal, int)
} // DbDatAdp